<?php
require_once __DIR__ . '/vendor/autoload.php';

// Load .env (simple loader since we don't have dotenv lib installed in the basic container, 
// or relying on docker-compose env injection)
// Actually docker-compose injects env vars, so getenv() works.

$host = getenv('DB_HOST');
$user = getenv('DB_USER');
$pass = getenv('DB_PASSWORD');
$name = getenv('DB_NAME');
$port = getenv('DB_PORT') ?: 3306;

echo "Connecting to MySQL at $host...\n";

try {
    $dsn = "mysql:host=$host;port=$port;charset=utf8mb4";
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);

    echo "Connected successfully.\n";

    // Create Database if not exists
    $pdo->exec("CREATE DATABASE IF NOT EXISTS `$name` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    $pdo->exec("USE `$name`");
    echo "Selected database '$name'.\n";

    $sql = <<<SQL
-- Devices Table
CREATE TABLE IF NOT EXISTS `devices` (
    `id` VARCHAR(64) NOT NULL COMMENT '设备ID (Device ID)',
    `secret` VARCHAR(128) NOT NULL COMMENT '连接密钥 (Hash)',
    `name` VARCHAR(50) DEFAULT NULL COMMENT '设备昵称',
    `status` ENUM('online', 'offline') DEFAULT 'offline' COMMENT '在线状态',
    `last_seen` TIMESTAMP NULL DEFAULT NULL COMMENT '最后心跳时间',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='设备管理表';

-- Users Table
CREATE TABLE IF NOT EXISTS `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `phone` VARCHAR(20) NOT NULL COMMENT '手机号',
    `wx_openid` VARCHAR(64) DEFAULT NULL COMMENT '微信 OpenID',
    `wx_unionid` VARCHAR(64) DEFAULT NULL COMMENT '微信 UnionID',
    `nickname` VARCHAR(50) DEFAULT NULL COMMENT '显示的昵称',
    `avatar_url` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_phone` (`phone`),
    KEY `idx_wx_openid` (`wx_openid`)
) ENGINE=InnoDB COMMENT='用户表';

-- Sessions Table (用户登录会话)
CREATE TABLE IF NOT EXISTS `sessions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `token` VARCHAR(128) NOT NULL COMMENT 'Session Token',
    `device_info` JSON DEFAULT NULL COMMENT '登录设备信息',
    `expires_at` TIMESTAMP NOT NULL COMMENT '过期时间',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_token` (`token`),
    KEY `idx_user` (`user_id`),
    KEY `idx_expires` (`expires_at`)
) ENGINE=InnoDB COMMENT='用户会话表';

-- User Device Bindings Table (手机号-设备绑定关系)
CREATE TABLE IF NOT EXISTS `user_device_bindings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `device_id` VARCHAR(64) NOT NULL COMMENT '设备ID',
    `is_primary` TINYINT(1) DEFAULT 0 COMMENT '是否主设备',
    `bound_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '绑定时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_device` (`user_id`, `device_id`),
    KEY `idx_device` (`device_id`)
) ENGINE=InnoDB COMMENT='用户设备绑定表';

-- Device Logs Table
CREATE TABLE IF NOT EXISTS `device_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id` VARCHAR(64) NOT NULL,
    `level` VARCHAR(10) DEFAULT 'INFO',
    `message` TEXT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_device_time` (`device_id`, `created_at`)
) ENGINE=InnoDB COMMENT='设备日志表';
SQL;

    $pdo->exec($sql);
    echo "Tables created successfully.\n";

    // Insert sample device if not exists
    $pdo->exec("INSERT IGNORE INTO `devices` (`id`, `secret`, `name`, `status`) VALUES ('dev_test_001', '123456', 'My First Moltbot', 'offline')");
    echo "Sample data inserted.\n";

} catch (PDOException $e) {
    echo "Database Error: " . $e->getMessage() . "\n";
    exit(1);
}