<?php
// 資料庫連接配置
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'Staff_CH');

// 嘗試連接到資料庫
$conn = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD);

// 檢查連接是否成功
if ($conn->connect_error) {
    die("連接失敗: " . $conn->connect_error);
}

// 創建資料庫（如果不存在）
$sql = "CREATE DATABASE IF NOT EXISTS " . DB_NAME . " DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci";
if ($conn->query($sql) !== TRUE) {
    die("創建資料庫失敗: " . $conn->error);
}

// 選擇資料庫
$conn->select_db(DB_NAME);

// 創建人員資訊表
$sql = "CREATE TABLE IF NOT EXISTS `personnel` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `photo` varchar(255) DEFAULT NULL COMMENT '照片路徑',
    `english_name` varchar(100) DEFAULT NULL COMMENT '英文姓名',
    `chinese_code` varchar(100) DEFAULT NULL COMMENT '中文電碼',
    `chinese_name` varchar(100) DEFAULT NULL COMMENT '中文姓名',
    `nickname` varchar(100) DEFAULT NULL COMMENT '乳名',
    `position` varchar(100) DEFAULT NULL COMMENT '職位',
    `phone` varchar(50) DEFAULT NULL COMMENT '電話',
    `email` varchar(100) DEFAULT NULL COMMENT '郵箱',
    `id_passport` varchar(100) DEFAULT NULL COMMENT '身份證或護照',
    `data_center_access` tinyint(1) DEFAULT 0 COMMENT '是否可以進入資料中心',
    `execution_team` tinyint(1) DEFAULT 0 COMMENT '執行組',
    `maintenance_team` tinyint(1) DEFAULT 0 COMMENT '保養組',
    `gender` enum('男','女') DEFAULT NULL COMMENT '性別',
    `employment_type` enum('全職','兼職') DEFAULT NULL COMMENT '雇傭類型',
    `company_home` varchar(100) DEFAULT NULL COMMENT '公司/家',
    `work_commitment` tinyint(1) DEFAULT 0 COMMENT '工作承諾書',
    `criminal_check` tinyint(1) DEFAULT 0 COMMENT '罪行核查',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

if ($conn->query($sql) !== TRUE) {
    die("創建人員資訊表失敗: " . $conn->error);
}

// 創建訪客許可證表
$sql = "CREATE TABLE IF NOT EXISTS `visitor_permits` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `personnel_id` int(11) NOT NULL COMMENT '關聯人員ID',
    `system_name` varchar(100) NOT NULL COMMENT '系統名稱',
    `subcontractor_name` varchar(100) NOT NULL COMMENT '分判商名稱',
    `work_date` date NOT NULL COMMENT '人員工作日期',
    `expiry_date` date NOT NULL COMMENT '臨時許可證有效期',
    `status` enum('有效','即將到期','已過期','已歸還') NOT NULL DEFAULT '有效' COMMENT '狀態',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),
    KEY `personnel_id` (`personnel_id`),
    CONSTRAINT `visitor_permits_ibfk_1` FOREIGN KEY (`personnel_id`) REFERENCES `personnel` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

if ($conn->query($sql) !== TRUE) {
    die("創建訪客許可證表失敗: " . $conn->error);
}

// 創建進入許可證表
$sql = "CREATE TABLE IF NOT EXISTS `entry_permits` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `personnel_id` int(11) NOT NULL COMMENT '關聯人員ID',
    `application_submitted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '已申請',
    `photo_submitted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '已提交照片',
    `permit_number` varchar(50) DEFAULT NULL COMMENT '許可證號碼',
    `expiry_date` date DEFAULT NULL COMMENT '有效期',
    `return_date` date DEFAULT NULL COMMENT '歸還日期',
    `status` enum('有效','即將到期','已過期','已歸還') NOT NULL DEFAULT '有效' COMMENT '狀態',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),
    KEY `personnel_id` (`personnel_id`),
    CONSTRAINT `entry_permits_ibfk_1` FOREIGN KEY (`personnel_id`) REFERENCES `personnel` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

if ($conn->query($sql) !== TRUE) {
    die("創建進入許可證表失敗: " . $conn->error);
}

// 創建門禁卡表
$sql = "CREATE TABLE IF NOT EXISTS `access_cards` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `personnel_id` int(11) NOT NULL COMMENT '關聯人員ID',
    `card_number` varchar(50) NOT NULL COMMENT '卡號碼',
    `chip_number` varchar(50) DEFAULT NULL COMMENT '卡晶片號碼',
    `issue_date` date NOT NULL COMMENT '發出日期',
    `expiry_date` date NOT NULL COMMENT '有效期',
    `return_date` date DEFAULT NULL COMMENT '歸還日期',
    `status` enum('有效','即將到期','已過期','已歸還') NOT NULL DEFAULT '有效' COMMENT '狀態',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),
    KEY `personnel_id` (`personnel_id`),
    CONSTRAINT `access_cards_ibfk_1` FOREIGN KEY (`personnel_id`) REFERENCES `personnel` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

if ($conn->query($sql) !== TRUE) {
    die("創建門禁卡表失敗: " . $conn->error);
}

// 創建機場證表
$sql = "CREATE TABLE IF NOT EXISTS `airport_passes` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `personnel_id` int(11) NOT NULL COMMENT '關聯人員ID',
    `airport_number` varchar(50) NOT NULL COMMENT '機場號碼',
    `pass_type` varchar(50) NOT NULL COMMENT '證件類別',
    `expiry_date` date NOT NULL COMMENT '有效期',
    `return_date` date DEFAULT NULL COMMENT '歸還日期',
    `status` enum('有效','即將到期','已過期','已歸還') NOT NULL DEFAULT '有效' COMMENT '狀態',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),
    KEY `personnel_id` (`personnel_id`),
    CONSTRAINT `airport_passes_ibfk_1` FOREIGN KEY (`personnel_id`) REFERENCES `personnel` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

if ($conn->query($sql) !== TRUE) {
    die("創建機場證表失敗: " . $conn->error);
}

// 創建禁區證表
$sql = "CREATE TABLE IF NOT EXISTS `restricted_area_passes` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `personnel_id` int(11) NOT NULL COMMENT '關聯人員ID',
    `pass_number` varchar(50) NOT NULL COMMENT '禁區證號碼',
    `restricted_area` varchar(100) NOT NULL COMMENT '禁區區域',
    `expiry_date` date NOT NULL COMMENT '有效日期',
    `return_date` date DEFAULT NULL COMMENT '歸還日期',
    `status` enum('有效','即將到期','已過期','已歸還') NOT NULL DEFAULT '有效' COMMENT '狀態',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),
    KEY `personnel_id` (`personnel_id`),
    CONSTRAINT `restricted_area_passes_ibfk_1` FOREIGN KEY (`personnel_id`) REFERENCES `personnel` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

if ($conn->query($sql) !== TRUE) {
    die("創建禁區證表失敗: " . $conn->error);
}

// 創建車輛禁區證表
$sql = "CREATE TABLE IF NOT EXISTS `vehicle_restricted_passes` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `personnel_id` int(11) NOT NULL COMMENT '關聯人員ID',
    `pass_number` varchar(50) NOT NULL COMMENT '禁區證號碼',
    `vehicle_number` varchar(50) NOT NULL COMMENT '車輛號碼',
    `restricted_area` varchar(100) NOT NULL COMMENT '禁區區域',
    `expiry_date` date NOT NULL COMMENT '有效日期',
    `return_date` date DEFAULT NULL COMMENT '歸還日期',
    `status` enum('有效','即將到期','已過期','已歸還') NOT NULL DEFAULT '有效' COMMENT '狀態',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),
    KEY `personnel_id` (`personnel_id`),
    CONSTRAINT `vehicle_restricted_passes_ibfk_1` FOREIGN KEY (`personnel_id`) REFERENCES `personnel` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

if ($conn->query($sql) !== TRUE) {
    die("創建車輛禁區證表失敗: " . $conn->error);
}

// 創建管理員表
$sql = "CREATE TABLE IF NOT EXISTS `admin` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(50) NOT NULL,
    `password` varchar(255) NOT NULL,
    `role` enum('超級管理員','普通管理員') NOT NULL DEFAULT '普通管理員' COMMENT '角色',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

if ($conn->query($sql) !== TRUE) {
    die("創建管理員表失敗: " . $conn->error);
}

// 檢查管理員表是否有資料，如果沒有則添加默認管理員
$sql = "SELECT COUNT(*) as count FROM admin";
$result = $conn->query($sql);
$row = $result->fetch_assoc();

if ($row['count'] == 0) {
    // 添加默認管理員帳號 admin/admin123
    $username = 'admin';
    $password = password_hash('admin123', PASSWORD_DEFAULT);
    $sql = "INSERT INTO admin (username, password, role) VALUES ('$username', '$password', '超級管理員')";
    if ($conn->query($sql) !== TRUE) {
        die("添加默認管理員失敗: " . $conn->error);
    }
}

// 創建系統設置表
$sql = "CREATE TABLE IF NOT EXISTS `settings` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `setting_key` varchar(50) NOT NULL,
    `setting_value` text NOT NULL,
    `description` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

if ($conn->query($sql) !== TRUE) {
    die("創建系統設置表失敗: " . $conn->error);
}

// 添加默認設置
$defaultSettings = [
    ['expiry_warning_days', '30', '證件到期提前警告天數'],
    ['system_name', '證件到期提醒系統', '系統名稱'],
    ['company_name', '公司名稱', '公司名稱'],
    ['email_notification', '0', '是否啟用郵件通知']
];

foreach ($defaultSettings as $setting) {
    $key = $setting[0];
    $value = $setting[1];
    $description = $setting[2];
    
    $sql = "INSERT IGNORE INTO settings (setting_key, setting_value, description) VALUES ('$key', '$value', '$description')";
    $conn->query($sql);
}

// 輔助函數：防止SQL注入
function sanitize($conn, $input) {
    if (is_array($input)) {
        $output = array();
        foreach ($input as $key => $val) {
            $output[$key] = sanitize($conn, $val);
        }
        return $output;
    } else {
        return $conn->real_escape_string($input);
    }
}

// 輔助函數：驗證日期格式
function validateDate($date, $format = 'Y-m-d') {
    $d = DateTime::createFromFormat($format, $date);
    return $d && $d->format($format) === $date;
}

// 輔助函數：獲取系統設置
function getSetting($conn, $key, $default = '') {
    $key = sanitize($conn, $key);
    $sql = "SELECT setting_value FROM settings WHERE setting_key = '$key' LIMIT 1";
    $result = $conn->query($sql);
    
    if ($result && $result->num_rows > 0) {
        $row = $result->fetch_assoc();
        return $row['setting_value'];
    }
    
    return $default;
}

// 輔助函數：更新證件狀態
function updateDocumentStatus($conn, $table, $expiryField = 'expiry_date', $returnField = 'return_date') {
    // 獲取警告天數
    $warningDays = (int)getSetting($conn, 'expiry_warning_days', 30);
    $today = date('Y-m-d');
    $warningDate = date('Y-m-d', strtotime("+$warningDays days"));
    
    // 更新已過期狀態
    $sql = "UPDATE $table SET status = '已過期' 
            WHERE $expiryField < '$today' 
            AND (status = '有效' OR status = '即將到期') 
            AND ($returnField IS NULL OR $returnField = '0000-00-00')";
    $conn->query($sql);
    
    // 更新即將到期狀態
    $sql = "UPDATE $table SET status = '即將到期' 
            WHERE $expiryField BETWEEN '$today' AND '$warningDate' 
            AND status = '有效' 
            AND ($returnField IS NULL OR $returnField = '0000-00-00')";
    $conn->query($sql);
    
    // 更新已歸還狀態
    $sql = "UPDATE $table SET status = '已歸還' 
            WHERE $returnField IS NOT NULL 
            AND $returnField != '0000-00-00'";
    $conn->query($sql);
}

// 初始化更新所有證件狀態
updateDocumentStatus($conn, 'visitor_permits');
updateDocumentStatus($conn, 'entry_permits');
updateDocumentStatus($conn, 'access_cards');
updateDocumentStatus($conn, 'airport_passes');
updateDocumentStatus($conn, 'restricted_area_passes');
updateDocumentStatus($conn, 'vehicle_restricted_passes');
?>
