<?php
/**
 * 数据库安装脚本
 * 访问此文件创建/更新数据库表
 * 安装完成后请删除此文件
 */

// 数据库配置 - 根据你的 .env 文件配置
$host = '127.0.0.1';
$port = '3306';
$database = 'reminder';
$username = 'root';
$password = '';

// 尝试从 Laravel .env 文件读取配置
$envPath = __DIR__ . '/../.env';
if (file_exists($envPath)) {
    $envContent = file_get_contents($envPath);
    
    if (preg_match('/DB_HOST=(.*)/', $envContent, $matches)) {
        $host = trim($matches[1]);
    }
    if (preg_match('/DB_PORT=(.*)/', $envContent, $matches)) {
        $port = trim($matches[1]);
    }
    if (preg_match('/DB_DATABASE=(.*)/', $envContent, $matches)) {
        $database = trim($matches[1]);
    }
    if (preg_match('/DB_USERNAME=(.*)/', $envContent, $matches)) {
        $username = trim($matches[1]);
    }
    if (preg_match('/DB_PASSWORD=(.*)/', $envContent, $matches)) {
        $password = trim($matches[1]);
    }
}

$messages = [];
$error = '';

try {
    $dsn = "mysql:host={$host};port={$port};dbname={$database};charset=utf8mb4";
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 1. 创建 users 表
    $sql = "
        CREATE TABLE IF NOT EXISTS `users` (
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            `name` varchar(255) NOT NULL COMMENT '姓名',
            `email` varchar(255) NOT NULL COMMENT '邮箱',
            `email_verified_at` timestamp NULL DEFAULT NULL,
            `password` varchar(255) NOT NULL COMMENT '密码',
            `remember_token` varchar(100) DEFAULT NULL,
            `created_at` timestamp NULL DEFAULT NULL,
            `updated_at` timestamp NULL DEFAULT NULL,
            PRIMARY KEY (`id`),
            UNIQUE KEY `users_email_unique` (`email`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($sql);
    $messages[] = '✅ 用户表 users 创建/已存在';
    
    // 2. 检查 patients 表是否存在
    $stmt = $pdo->query("SHOW TABLES LIKE 'patients'");
    $patientsTableExists = $stmt->rowCount() > 0;
    
    if ($patientsTableExists) {
        // 表已存在，检查是否有 user_id 字段
        $stmt = $pdo->query("SHOW COLUMNS FROM `patients` LIKE 'user_id'");
        $hasUserId = $stmt->rowCount() > 0;
        
        if (!$hasUserId) {
            // 检查是否有数据
            $countStmt = $pdo->query("SELECT COUNT(*) FROM `patients`");
            $count = $countStmt->fetchColumn();
            
            if ($count > 0) {
                // 有数据，需要先创建一个默认用户
                // 检查是否已有用户
                $userCountStmt = $pdo->query("SELECT COUNT(*) FROM `users`");
                $userCount = $userCountStmt->fetchColumn();
                
                if ($userCount == 0) {
                    // 创建默认管理员用户
                    $defaultPassword = password_hash('admin123', PASSWORD_BCRYPT);
                    $pdo->exec("INSERT INTO `users` (`name`, `email`, `password`, `created_at`, `updated_at`) 
                                VALUES ('管理员', 'admin@example.com', '{$defaultPassword}', NOW(), NOW())");
                    $messages[] = '✅ 已创建默认用户: admin@example.com (密码: admin123)';
                }
                
                // 获取第一个用户的 ID
                $firstUserStmt = $pdo->query("SELECT id FROM `users` ORDER BY id ASC LIMIT 1");
                $firstUserId = $firstUserStmt->fetchColumn();
                
                // 添加 user_id 字段（允许 NULL）
                $pdo->exec("ALTER TABLE `patients` ADD COLUMN `user_id` bigint(20) unsigned NULL AFTER `id`");
                $messages[] = '✅ 已添加 user_id 字段到 patients 表';
                
                // 将现有数据关联到第一个用户
                $pdo->exec("UPDATE `patients` SET `user_id` = {$firstUserId} WHERE `user_id` IS NULL");
                $messages[] = "✅ 已将现有 {$count} 条患者数据关联到用户 ID: {$firstUserId}";
                
                // 修改字段为 NOT NULL
                $pdo->exec("ALTER TABLE `patients` MODIFY COLUMN `user_id` bigint(20) unsigned NOT NULL");
                
                // 添加外键约束
                try {
                    $pdo->exec("ALTER TABLE `patients` ADD KEY `patients_user_id_foreign` (`user_id`)");
                    $pdo->exec("ALTER TABLE `patients` ADD CONSTRAINT `patients_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE");
                    $messages[] = '✅ 已添加外键约束';
                } catch (PDOException $e) {
                    // 外键可能已存在，忽略
                    $messages[] = '⚠️ 外键约束已存在或无法添加';
                }
            } else {
                // 没有数据，直接添加字段和约束
                $pdo->exec("ALTER TABLE `patients` ADD COLUMN `user_id` bigint(20) unsigned NOT NULL AFTER `id`");
                try {
                    $pdo->exec("ALTER TABLE `patients` ADD KEY `patients_user_id_foreign` (`user_id`)");
                    $pdo->exec("ALTER TABLE `patients` ADD CONSTRAINT `patients_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE");
                } catch (PDOException $e) {
                    // 忽略
                }
                $messages[] = '✅ 已添加 user_id 字段到 patients 表';
            }
        } else {
            $messages[] = '✅ patients 表已有 user_id 字段';
        }
    } else {
        // 创建新的 patients 表（包含 user_id）
        $sql = "
            CREATE TABLE `patients` (
                `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                `user_id` bigint(20) unsigned NOT NULL COMMENT '所属用户',
                `name` varchar(255) NOT NULL COMMENT '姓名',
                `gender` varchar(255) NOT NULL COMMENT '性别',
                `age` int(11) NOT NULL COMMENT '年龄',
                `diagnosis` varchar(255) NOT NULL COMMENT '出院诊断',
                `discharge_date` date NOT NULL COMMENT '转诊时间',
                `address` varchar(255) DEFAULT NULL COMMENT '户籍地址',
                `phone` varchar(255) DEFAULT NULL COMMENT '联系方式',
                `remark` varchar(255) DEFAULT NULL COMMENT '备注',
                `follow_up_count` int(11) NOT NULL DEFAULT 0 COMMENT '已随访次数',
                `last_follow_up_date` date DEFAULT NULL COMMENT '上次随访日期',
                `created_at` timestamp NULL DEFAULT NULL,
                `updated_at` timestamp NULL DEFAULT NULL,
                PRIMARY KEY (`id`),
                KEY `patients_user_id_foreign` (`user_id`),
                CONSTRAINT `patients_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        ";
        $pdo->exec($sql);
        $messages[] = '✅ 患者表 patients 创建成功';
    }
    
    // 3. 创建 sessions 表
    $sql = "
        CREATE TABLE IF NOT EXISTS `sessions` (
            `id` varchar(255) NOT NULL,
            `user_id` bigint(20) unsigned DEFAULT NULL,
            `ip_address` varchar(45) DEFAULT NULL,
            `user_agent` text DEFAULT NULL,
            `payload` longtext NOT NULL,
            `last_activity` int(11) NOT NULL,
            PRIMARY KEY (`id`),
            KEY `sessions_user_id_index` (`user_id`),
            KEY `sessions_last_activity_index` (`last_activity`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($sql);
    $messages[] = '✅ 会话表 sessions 创建/已存在';
    
    // 显示表结构
    $stmt = $pdo->query("SHOW TABLES");
    $tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
    $messages[] = '📋 当前数据库表: ' . implode(', ', $tables);
    
} catch (PDOException $e) {
    $error = '❌ 数据库操作失败: ' . $e->getMessage();
}
?>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>数据库安装 - 病例回访提醒系统</title>
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body {
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', 'Microsoft YaHei', sans-serif;
            background: linear-gradient(135deg, #f0f4f8 0%, #e8f2ff 100%);
            min-height: 100vh;
            display: flex;
            align-items: center;
            justify-content: center;
            padding: 20px;
        }
        .container {
            background: #ffffff;
            border-radius: 16px;
            padding: 40px;
            max-width: 650px;
            width: 100%;
            box-shadow: 0 20px 60px rgba(0, 102, 204, 0.15);
        }
        h1 {
            color: #333;
            font-size: 24px;
            margin-bottom: 20px;
            display: flex;
            align-items: center;
            gap: 12px;
        }
        .message {
            padding: 12px 16px;
            border-radius: 8px;
            margin-bottom: 12px;
            line-height: 1.6;
            font-size: 14px;
        }
        .success {
            background: rgba(16, 185, 129, 0.1);
            border: 1px solid #10b981;
            color: #059669;
        }
        .error {
            background: rgba(239, 68, 68, 0.1);
            border: 1px solid #ef4444;
            color: #ef4444;
        }
        .warning {
            background: rgba(245, 158, 11, 0.1);
            border: 1px solid #f59e0b;
            color: #d97706;
        }
        .info {
            background: rgba(99, 102, 241, 0.1);
            border: 1px solid #6366f1;
            color: #666;
        }
        .info strong { color: #333; }
        .btn {
            display: inline-block;
            padding: 12px 24px;
            background: linear-gradient(135deg, #6366f1 0%, #8b5cf6 100%);
            color: white;
            text-decoration: none;
            border-radius: 8px;
            font-weight: 500;
            transition: all 0.2s;
            margin-top: 10px;
            margin-right: 10px;
        }
        .btn:hover {
            transform: translateY(-2px);
            box-shadow: 0 4px 12px rgba(99, 102, 241, 0.4);
        }
        .btn-secondary {
            background: linear-gradient(135deg, #10b981 0%, #059669 100%);
        }
        .config {
            background: #f8fafc;
            border-radius: 8px;
            padding: 16px;
            margin: 20px 0;
            font-family: monospace;
            font-size: 13px;
            color: #666;
            border: 1px solid #e2e8f0;
        }
        .config div { margin: 4px 0; }
        .config span { color: #6366f1; }
    </style>
</head>
<body>
    <div class="container">
        <h1>🏥 数据库安装/升级</h1>
        
        <?php if (!empty($messages)): ?>
            <?php foreach ($messages as $msg): ?>
                <?php 
                $class = 'success';
                if (strpos($msg, '⚠️') !== false) $class = 'warning';
                if (strpos($msg, '📋') !== false) $class = 'info';
                ?>
                <div class="message <?= $class ?>"><?= $msg ?></div>
            <?php endforeach; ?>
            <div class="message info">
                <strong>⚠️ 安全提示：</strong>安装完成后，请删除此文件 (install.php) 以确保系统安全。
            </div>
            <div>
                <a href="/login" class="btn">进入登录页 →</a>
                <a href="/register" class="btn btn-secondary">注册新用户 →</a>
            </div>
        <?php endif; ?>
        
        <?php if ($error): ?>
            <div class="message error"><?= htmlspecialchars($error) ?></div>
            <div class="config">
                <div>当前数据库配置：</div>
                <div>Host: <span><?= htmlspecialchars($host) ?></span></div>
                <div>Port: <span><?= htmlspecialchars($port) ?></span></div>
                <div>Database: <span><?= htmlspecialchars($database) ?></span></div>
                <div>Username: <span><?= htmlspecialchars($username) ?></span></div>
            </div>
            <div class="message info">
                <strong>解决方法：</strong><br>
                1. 确保 MySQL 服务已启动<br>
                2. 确保数据库 "<?= htmlspecialchars($database) ?>" 已创建<br>
                3. 检查 .env 文件中的数据库配置是否正确
            </div>
        <?php endif; ?>
    </div>
</body>
</html>