-- 充电桩监控系统数据库初始化脚本
-- 使用方法: mysql -u root -p < init.sql

-- 创建数据库
CREATE DATABASE IF NOT EXISTS charger_monitor DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建用户（如果不存在）
-- CREATE USER IF NOT EXISTS 'charger_user'@'localhost' IDENTIFIED BY 'your_password_here';
-- GRANT ALL PRIVILEGES ON charger_monitor.* TO 'charger_user'@'localhost';
-- FLUSH PRIVILEGES;

USE charger_monitor;

-- 订阅表
CREATE TABLE IF NOT EXISTS subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL COMMENT '订阅邮箱',
    feishu_name VARCHAR(64) DEFAULT '' COMMENT '飞书花名',
    charger_type VARCHAR(10) DEFAULT 'all' COMMENT '充电桩类型: AC/DC/all',
    vendor VARCHAR(16) DEFAULT 'all' COMMENT '订阅厂商: all/nio/gongniu',
    week_start DATE COMMENT '订阅所在周（用于判断是否本周有效）',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    notified_at DATETIME NULL COMMENT '通知发送时间',
    is_priority TINYINT(1) DEFAULT 0 COMMENT 'VIP优先级',
    expired TINYINT(1) DEFAULT 0 COMMENT '是否已过期',
    INDEX idx_email (email),
    INDEX idx_expired (expired),
    INDEX idx_vendor (vendor),
    INDEX idx_week_start (week_start),
    INDEX idx_notified (notified_at),
    INDEX idx_priority (is_priority, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订阅表';

-- 充电桩状态缓存表
CREATE TABLE IF NOT EXISTS charger_state_cache (
    id INT AUTO_INCREMENT PRIMARY KEY,
    station_id VARCHAR(64) NOT NULL COMMENT '充电站ID',
    connector_key VARCHAR(64) NOT NULL COMMENT '充电桩标识',
    state_type VARCHAR(20) NOT NULL COMMENT '状态类型: free/charging/occupied',
    started_at DATETIME NOT NULL COMMENT '状态开始时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_station_connector (station_id, connector_key),
    INDEX idx_state_type (state_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='充电桩状态缓存表';

-- 充电桩状态历史表（用于分析）
CREATE TABLE IF NOT EXISTS charger_status_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    station_id VARCHAR(64) NOT NULL COMMENT '充电站ID',
    connector_id VARCHAR(64) NOT NULL COMMENT '充电桩ID',
    connector_name VARCHAR(64) NOT NULL COMMENT '充电桩名称',
    connector_type VARCHAR(10) NOT NULL COMMENT '类型: AC/DC',
    vendor VARCHAR(32) DEFAULT 'nio' COMMENT '厂商标识: nio/gongniu',
    power DECIMAL(10,2) DEFAULT 0 COMMENT '功率(kW)',
    state_type VARCHAR(20) NOT NULL COMMENT '状态: free/charging/occupied/offline',
    state_desc VARCHAR(64) DEFAULT '' COMMENT '状态描述',
    idle_seconds INT DEFAULT 0 COMMENT '空闲时长(秒)',
    charging_seconds INT DEFAULT 0 COMMENT '充电时长(秒)',
    occupied_seconds INT DEFAULT 0 COMMENT '占位时长(秒)',
    remaining_minutes INT DEFAULT NULL COMMENT 'DC桩剩余充电时长(分钟)',
    fetch_time DATETIME NOT NULL COMMENT '数据获取时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_station_name_time (station_id, connector_name, fetch_time),
    INDEX idx_station_time (station_id, fetch_time),
    INDEX idx_connector_time (connector_id, fetch_time),
    INDEX idx_state_type (state_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='充电桩状态历史表';

-- 充电桩最新状态快照表（实时接口使用）
CREATE TABLE IF NOT EXISTS charger_latest_status (
    station_id VARCHAR(64) NOT NULL COMMENT '充电站ID',
    connector_name VARCHAR(64) NOT NULL COMMENT '充电桩名称',
    connector_id VARCHAR(64) NOT NULL COMMENT '充电桩ID',
    connector_type VARCHAR(10) NOT NULL COMMENT '类型: AC/DC',
    vendor VARCHAR(32) DEFAULT 'nio' COMMENT '厂商标识: nio/gongniu',
    power DECIMAL(10,2) DEFAULT 0 COMMENT '功率(kW)',
    state_type VARCHAR(20) NOT NULL COMMENT '状态: free/charging/occupied/offline',
    state_desc VARCHAR(64) DEFAULT '' COMMENT '状态描述',
    idle_seconds INT DEFAULT 0 COMMENT '空闲时长(秒)',
    charging_seconds INT DEFAULT 0 COMMENT '充电时长(秒)',
    occupied_seconds INT DEFAULT 0 COMMENT '占位时长(秒)',
    remaining_minutes INT DEFAULT NULL COMMENT 'DC桩剩余充电时长(分钟)',
    fetch_time DATETIME NOT NULL COMMENT '数据获取时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (station_id, connector_name),
    INDEX idx_station_fetch_time (station_id, fetch_time),
    INDEX idx_latest_state_type (state_type),
    INDEX idx_latest_vendor (vendor)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='充电桩最新状态快照表';

-- 系统配置表
CREATE TABLE IF NOT EXISTS system_config (
    `key` VARCHAR(64) PRIMARY KEY COMMENT '配置键',
    `value` TEXT COMMENT '配置值',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';

-- 充电桩留言表
CREATE TABLE IF NOT EXISTS charger_comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    connector_id VARCHAR(64) NOT NULL COMMENT '充电桩ID',
    connector_name VARCHAR(64) NOT NULL COMMENT '充电桩名称',
    feishu_name VARCHAR(20) NOT NULL COMMENT '留言人飞书花名',
    content VARCHAR(100) NOT NULL COMMENT '留言内容',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '留言时间',
    INDEX idx_connector (connector_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='充电桩留言表';

-- 访问日志表
CREATE TABLE IF NOT EXISTS visit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_hash VARCHAR(16) NOT NULL COMMENT '设备指纹哈希(SHA256前16位)',
    visited_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '访问时间',
    INDEX idx_visited_date (visited_at),
    INDEX idx_device_date (device_hash, visited_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='访问日志表';

-- 插入示例数据（可选）
-- INSERT INTO subscriptions (email) VALUES ('demo@example.com');
