-- 股票监控系统数据库表结构
-- Database: stock_monitor

-- 1. 股票基础信息表
CREATE TABLE IF NOT EXISTS stocks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stock_code VARCHAR(10) NOT NULL UNIQUE COMMENT '股票代码',
    stock_name VARCHAR(50) NOT NULL COMMENT '股票名称',
    market VARCHAR(10) NOT NULL COMMENT '市场(SH/SZ)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_stock_code (stock_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='股票基础信息表';

-- 2. 股票实时数据表
CREATE TABLE IF NOT EXISTS stock_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stock_code VARCHAR(10) NOT NULL,
    data_date DATE NOT NULL COMMENT '数据日期',

    -- 基本信息
    price DECIMAL(10,3) DEFAULT NULL COMMENT '当前股价',
    change_percent DECIMAL(8,4) DEFAULT NULL COMMENT '涨跌幅',
    market_value DECIMAL(12,3) DEFAULT NULL COMMENT '总市值(亿元)',

    -- 估值指标
    pe_ratio DECIMAL(8,4) DEFAULT NULL COMMENT '市盈率',
    pb_ratio DECIMAL(8,4) DEFAULT NULL COMMENT '市净率',
    ps_ratio DECIMAL(8,4) DEFAULT NULL COMMENT '市销率',
    dividend_yield DECIMAL(8,4) DEFAULT NULL COMMENT '股息率',

    -- 财务指标
    roe DECIMAL(8,4) DEFAULT NULL COMMENT '净资产收益率',
    gross_profit_margin DECIMAL(8,4) DEFAULT NULL COMMENT '销售毛利率',
    net_profit_margin DECIMAL(8,4) DEFAULT NULL COMMENT '销售净利率',
    debt_to_assets DECIMAL(8,4) DEFAULT NULL COMMENT '资产负债率',
    revenue_yoy DECIMAL(8,4) DEFAULT NULL COMMENT '营收同比增长率',
    net_profit_yoy DECIMAL(8,4) DEFAULT NULL COMMENT '净利润同比增长率',
    bps DECIMAL(8,4) DEFAULT NULL COMMENT '每股净资产',
    ocfps DECIMAL(8,4) DEFAULT NULL COMMENT '每股经营现金流',

    -- 元数据
    from_cache BOOLEAN DEFAULT FALSE COMMENT '是否来自缓存',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_stock_date (stock_code, data_date),
    INDEX idx_stock_code (stock_code),
    INDEX idx_data_date (data_date),
    FOREIGN KEY (stock_code) REFERENCES stocks(stock_code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='股票实时数据表';

-- 3. 用户监控列表表
CREATE TABLE IF NOT EXISTS watchlist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stock_code VARCHAR(10) NOT NULL,
    target_market_value_min DECIMAL(12,3) DEFAULT NULL COMMENT '目标市值最小值(亿元)',
    target_market_value_max DECIMAL(12,3) DEFAULT NULL COMMENT '目标市值最大值(亿元)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_stock_code (stock_code),
    FOREIGN KEY (stock_code) REFERENCES stocks(stock_code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户监控列表表';

-- 4. AI分析结果表
CREATE TABLE IF NOT EXISTS ai_analysis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stock_code VARCHAR(10) NOT NULL,
    analysis_type VARCHAR(20) NOT NULL COMMENT '分析类型(stock/dao/daka)',
    analysis_date DATE NOT NULL COMMENT '分析日期',

    -- 投资建议
    investment_summary TEXT COMMENT '投资建议摘要',
    investment_action TEXT COMMENT '建议操作',
    investment_key_points JSON COMMENT '关键要点',

    -- 详细分析
    valuation_analysis TEXT COMMENT '估值分析',
    financial_analysis TEXT COMMENT '财务分析',
    growth_analysis TEXT COMMENT '成长性分析',
    risk_analysis TEXT COMMENT '风险分析',

    -- 价格分析
    reasonable_price_min DECIMAL(10,3) DEFAULT NULL COMMENT '合理价格最小值',
    reasonable_price_max DECIMAL(10,3) DEFAULT NULL COMMENT '合理价格最大值',
    target_market_value_min DECIMAL(12,3) DEFAULT NULL COMMENT '目标市值最小值(亿元)',
    target_market_value_max DECIMAL(12,3) DEFAULT NULL COMMENT '目标市值最大值(亿元)',

    -- 元数据
    from_cache BOOLEAN DEFAULT FALSE COMMENT '是否来自缓存',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_stock_type_date (stock_code, analysis_type, analysis_date),
    INDEX idx_stock_code (stock_code),
    INDEX idx_analysis_type (analysis_type),
    INDEX idx_analysis_date (analysis_date),
    FOREIGN KEY (stock_code) REFERENCES stocks(stock_code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI分析结果表';

-- 5. 系统配置表
CREATE TABLE IF NOT EXISTS system_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_key VARCHAR(50) NOT NULL UNIQUE COMMENT '配置键',
    config_value TEXT COMMENT '配置值',
    config_type VARCHAR(20) DEFAULT 'string' COMMENT '配置类型',
    description VARCHAR(200) DEFAULT NULL COMMENT '配置描述',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_config_key (config_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置表';

-- 6. 数据更新日志表
CREATE TABLE IF NOT EXISTS data_update_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data_type VARCHAR(20) NOT NULL COMMENT '数据类型',
    stock_code VARCHAR(10) DEFAULT NULL COMMENT '股票代码',
    update_status ENUM('success', 'failed', 'partial') NOT NULL COMMENT '更新状态',
    update_message TEXT COMMENT '更新消息',
    execution_time DECIMAL(8,3) DEFAULT NULL COMMENT '执行时间(秒)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_data_type (data_type),
    INDEX idx_stock_code (stock_code),
    INDEX idx_update_status (update_status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='数据更新日志表';

-- 插入系统默认配置
INSERT INTO system_config (config_key, config_value, config_type, description) VALUES
('tushare_api_calls_today', '0', 'integer', 'Tushare API calls today'),
('last_data_update_date', '', 'date', 'Last data update date'),
('cache_expiration_hours', '24', 'integer', 'Cache expiration time in hours'),
('max_watchlist_size', '50', 'integer', 'Maximum watchlist size')
ON DUPLICATE KEY UPDATE config_value = VALUES(config_value);