<?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 '设备昵称',
    `owner_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '绑定用户ID (NULL=未绑定)',
    `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`),
    KEY `idx_owner` (`owner_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 '手机号',
    `password` VARCHAR(255) NOT NULL COMMENT '密码 Hash',
    `wx_openid` VARCHAR(64) DEFAULT NULL COMMENT '微信 OpenID',
    `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`),
    UNIQUE KEY `uk_wx_openid` (`wx_openid`)
) 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);
}