#02-数据架构与表结构设计

CREATE TABLE IF NOT EXISTS dim_index (
    index_code TEXT PRIMARY KEY,        -- 指数代码，如 000300
    index_name TEXT NOT NULL,           -- 指数简称，如 沪深300
    index_full_name TEXT,               -- 全称
    category TEXT NOT NULL,             -- 分类：宽基/行业/主题/策略/海外
    sub_category TEXT,                  -- 子分类，如 行业下分 半导体/医药
    publisher TEXT,                     -- 发布机构，如 中证指数公司
    base_date TEXT,                     -- 基准日
    base_point REAL,                    -- 基准点数
    updated_at TEXT NOT NULL            -- 最后更新时间
);

CREATE TABLE IF NOT EXISTS dim_fund (
    fund_code TEXT PRIMARY KEY,         -- 基金代码，如 510300
    fund_name TEXT NOT NULL,            -- 基金简称
    fund_type TEXT NOT NULL,            -- 类型：ETF/LOF/场外指数/主动
    tracking_index TEXT,                -- 跟踪指数代码（指数基金）
    management_company TEXT,            -- 基金公司
    manager TEXT,                       -- 基金经理
    management_fee REAL,                -- 管理费率
    custody_fee REAL,                   -- 托管费率
    inception_date TEXT,                -- 成立日
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS dim_stock (
    stock_code TEXT PRIMARY KEY,        -- 股票代码，如 600519
    stock_name TEXT NOT NULL,           -- 股票简称
    exchange TEXT NOT NULL,             -- 交易所：SH/SZ/BJ
    industry_code TEXT,                 -- 所属申万行业代码
    industry_name TEXT,                 -- 所属申万行业名称
    list_date TEXT,                     -- 上市日
    updated_at TEXT NOT NULL,
    FOREIGN KEY (industry_code) REFERENCES dim_industry(industry_code)
);

CREATE TABLE IF NOT EXISTS dim_industry (
    industry_code TEXT PRIMARY KEY,     -- 申万行业代码，如 801080
    industry_name TEXT NOT NULL,        -- 行业名称，如 电子
    industry_level INTEGER NOT NULL,    -- 行业级别：1/2/3
    parent_code TEXT,                   -- 父行业代码
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS index_daily (
    index_code TEXT NOT NULL,
    trade_date TEXT NOT NULL,           -- YYYY-MM-DD
    open REAL,
    close REAL,
    high REAL,
    low REAL,
    volume REAL,                        -- 成交量（万手）
    amount REAL,                        -- 成交额（万元）
    pct_change REAL,                    -- 涨跌幅 %
    PRIMARY KEY (index_code, trade_date),
    FOREIGN KEY (index_code) REFERENCES dim_index(index_code)
);

CREATE INDEX IF NOT EXISTS idx_index_daily_date
    ON index_daily(trade_date);

CREATE TABLE IF NOT EXISTS fund_daily (
    fund_code TEXT NOT NULL,
    trade_date TEXT NOT NULL,
    open REAL,
    close REAL,
    high REAL,
    low REAL,
    volume REAL,
    amount REAL,
    pct_change REAL,
    unit_nav REAL,                      -- 单位净值（场外基金）
    accum_nav REAL,                     -- 累计净值
    PRIMARY KEY (fund_code, trade_date),
    FOREIGN KEY (fund_code) REFERENCES dim_fund(fund_code)
);

CREATE INDEX IF NOT EXISTS idx_fund_daily_date
    ON fund_daily(trade_date);

CREATE TABLE IF NOT EXISTS stock_daily (
    stock_code TEXT NOT NULL,
    trade_date TEXT NOT NULL,
    open REAL,
    close REAL,
    high REAL,
    low REAL,
    volume REAL,                        -- 成交量（手）
    amount REAL,                        -- 成交额（元）
    pct_change REAL,
    turnover_rate REAL,                 -- 换手率 %
    pe_ttm REAL,                        -- 滚动市盈率
    pb REAL,                            -- 市净率
    total_market_cap REAL,              -- 总市值（元）
    circ_market_cap REAL,               -- 流通市值（元）
    PRIMARY KEY (stock_code, trade_date),
    FOREIGN KEY (stock_code) REFERENCES dim_stock(stock_code)
);

CREATE INDEX IF NOT EXISTS idx_stock_daily_date
    ON stock_daily(trade_date);

CREATE TABLE IF NOT EXISTS industry_daily (
    industry_code TEXT NOT NULL,
    trade_date TEXT NOT NULL,
    open REAL,
    close REAL,
    high REAL,
    low REAL,
    volume REAL,
    amount REAL,
    pct_change REAL,
    PRIMARY KEY (industry_code, trade_date),
    FOREIGN KEY (industry_code) REFERENCES dim_industry(industry_code)
);

CREATE INDEX IF NOT EXISTS idx_industry_daily_date
    ON industry_daily(trade_date);

CREATE TABLE IF NOT EXISTS index_valuation (
    index_code TEXT NOT NULL,
    trade_date TEXT NOT NULL,
    pe REAL,                            -- 市盈率
    pe_ttm REAL,                        -- 滚动市盈率
    pb REAL,                            -- 市净率
    ps REAL,                            -- 市销率
    dividend_yield REAL,                -- 股息率 %
    pe_percentile REAL,                 -- PE 历史分位（0-1）
    pb_percentile REAL,                 -- PB 历史分位
    PRIMARY KEY (index_code, trade_date),
    FOREIGN KEY (index_code) REFERENCES dim_index(index_code)
);

CREATE INDEX IF NOT EXISTS idx_index_val_date
    ON index_valuation(trade_date);

CREATE TABLE IF NOT EXISTS stock_valuation (
    stock_code TEXT NOT NULL,
    trade_date TEXT NOT NULL,
    pe_ttm REAL,
    pb REAL,
    ps_ttm REAL,
    dividend_yield REAL,
    PRIMARY KEY (stock_code, trade_date),
    FOREIGN KEY (stock_code) REFERENCES dim_stock(stock_code)
);

CREATE TABLE IF NOT EXISTS fund_holdings (
    fund_code TEXT NOT NULL,
    report_date TEXT NOT NULL,          -- 报告期，如 2025-06-30
    stock_code TEXT NOT NULL,
    stock_name TEXT,
    hold_ratio REAL,                    -- 占净值比例 %
    hold_amount REAL,                   -- 持仓股数
    hold_value REAL,                    -- 持仓市值
    rank INTEGER,                       -- 持仓排名
    PRIMARY KEY (fund_code, report_date, stock_code),
    FOREIGN KEY (fund_code) REFERENCES dim_fund(fund_code)
);

CREATE INDEX IF NOT EXISTS idx_fund_holdings_stock
    ON fund_holdings(stock_code);

CREATE TABLE IF NOT EXISTS stock_financial (
    stock_code TEXT NOT NULL,
    report_date TEXT NOT NULL,          -- 报告期
    revenue REAL,                       -- 营业收入
    net_profit REAL,                    -- 净利润
    revenue_yoy REAL,                   -- 营收同比 %
    net_profit_yoy REAL,                -- 净利润同比 %
    roe REAL,                           -- 净资产收益率 %
    roa REAL,                           -- 总资产收益率 %
    gross_margin REAL,                  -- 毛利率 %
    net_margin REAL,                    -- 净利率 %
    debt_ratio REAL,                    -- 资产负债率 %
    current_ratio REAL,                 -- 流动比率
    quick_ratio REAL,                   -- 速动比率
    eps REAL,                           -- 每股收益
    bps REAL,                           -- 每股净资产
    operating_cash_flow REAL,           -- 经营性现金流
    PRIMARY KEY (stock_code, report_date),
    FOREIGN KEY (stock_code) REFERENCES dim_stock(stock_code)
);

CREATE TABLE IF NOT EXISTS macro_indicator (
    indicator_code TEXT NOT NULL,       -- 指标代码，如 CPI_YOY
    indicator_name TEXT NOT NULL,       -- 指标名称
    report_date TEXT NOT NULL,          -- 数据日期
    value REAL,                         -- 指标值
    unit TEXT,                          -- 单位
    frequency TEXT,                     -- 频率：日/月/季
    source TEXT,                        -- 数据来源
    PRIMARY KEY (indicator_code, report_date)
);

CREATE TABLE IF NOT EXISTS money_flow (
    flow_code TEXT NOT NULL,            -- 流向代码，如 NORTH_FLOW
    flow_name TEXT NOT NULL,            -- 流向名称
    trade_date TEXT NOT NULL,
    value REAL,                         -- 流入/流出金额（万元）
    cumulative REAL,                    -- 累计金额
    PRIMARY KEY (flow_code, trade_date)
);

CREATE TABLE IF NOT EXISTS trade_calendar (
    trade_date TEXT PRIMARY KEY,
    is_open INTEGER NOT NULL,           -- 1=交易日, 0=非交易日
    exchange TEXT NOT NULL              -- SSE/SZSE
);

CREATE TABLE IF NOT EXISTS update_failure_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    run_id TEXT NOT NULL,               -- 本次更新批次 ID（时间戳）
    fetcher TEXT NOT NULL,              -- 失败的 fetcher 名
    target_code TEXT,                   -- 失败的标的代码
    api_name TEXT,                      -- 接口名
    error_type TEXT,                    -- 错误类型：network/parse/empty/unknown
    error_message TEXT,                 -- 错误详情
    occurred_at TEXT NOT NULL           -- 发生时间
);

CREATE INDEX IF NOT EXISTS idx_failure_run
    ON update_failure_log(run_id);
CREATE INDEX IF NOT EXISTS idx_failure_target
    ON update_failure_log(target_code);

CREATE TABLE IF NOT EXISTS validation_result (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    run_id TEXT NOT NULL,
    table_name TEXT NOT NULL,
    check_type TEXT NOT NULL,           -- integrity/accuracy/consistency/timeliness/cross_validation
    check_name TEXT NOT NULL,
    status TEXT NOT NULL,               -- pass/warning/fail
    detail TEXT,
    checked_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_validation_run
    ON validation_result(run_id);

CREATE TABLE IF NOT EXISTS cross_validation_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    run_id TEXT NOT NULL,
    target_code TEXT NOT NULL,          -- 标的代码
    trade_date TEXT NOT NULL,           -- 验证日期
    field_name TEXT NOT NULL,           -- 验证字段，如 close/pe/pb
    source_primary TEXT NOT NULL,       -- 主源，如 em（东方财富）
    source_secondary TEXT NOT NULL,     -- 备源，如 sina（新浪）
    value_primary REAL,                 -- 主源值
    value_secondary REAL,               -- 备源值
    diff REAL,                          -- 绝对差
    diff_pct REAL,                      -- 相对差 %
    status TEXT NOT NULL,               -- match/mismatch/missing/single_source
    checked_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_xval_run
    ON cross_validation_log(run_id);
CREATE INDEX IF NOT EXISTS idx_xval_target
    ON cross_validation_log(target_code);
CREATE INDEX IF NOT EXISTS idx_xval_status
    ON cross_validation_log(status);

CREATE TABLE IF NOT EXISTS update_progress (
    fetcher_name TEXT NOT NULL,          -- fetcher 名称，如 fetch_index_daily
    target_code TEXT NOT NULL,           -- 标的代码
    last_trade_date TEXT,                -- 最后成功更新的交易日
    last_updated_at TEXT NOT NULL,       -- 最后更新时间
    status TEXT NOT NULL,                -- pending/in_progress/completed/failed
    run_id TEXT,                         -- 最近一次运行的 run_id
    PRIMARY KEY (fetcher_name, target_code)
);

CREATE INDEX IF NOT EXISTS idx_progress_status
    ON update_progress(status);
CREATE INDEX IF NOT EXISTS idx_progress_run
    ON update_progress(run_id);

CREATE TABLE IF NOT EXISTS decision_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    decision_date TEXT NOT NULL,       -- 决策日期 YYYY-MM-DD
    action TEXT NOT NULL,              -- buy/sell/hold/stop_loss/take_profit
    target_type TEXT NOT NULL,         -- index/fund/stock
    target_code TEXT NOT NULL,
    target_name TEXT,
    amount REAL,                       -- 成交金额（元）
    shares REAL,                       -- 成交份额/股数
    price REAL,                        -- 成交价格
    pe_percentile REAL,                -- 决策时 PE 分位快照
    pb_percentile REAL,                -- 决策时 PB 分位快照
    reason TEXT,                       -- 决策理由
    strategy TEXT,                     -- 策略标签
    emotion TEXT,                      -- 情绪标签
    linked_doc TEXT,                   -- 关联的复盘文档路径
    created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_decision_date
    ON decision_log(decision_date);
CREATE INDEX IF NOT EXISTS idx_decision_target
    ON decision_log(target_code);
CREATE INDEX IF NOT EXISTS idx_decision_action
    ON decision_log(action);

CREATE TABLE IF NOT EXISTS portfolio_snapshot (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    snapshot_date TEXT NOT NULL,       -- 快照日期 YYYY-MM-DD
    target_code TEXT NOT NULL,
    target_name TEXT,
    target_type TEXT NOT NULL,         -- index/fund/stock
    shares REAL,                       -- 持有份额
    cost_price REAL,                   -- 成本价
    current_price REAL,                -- 当前价格
    market_value REAL,                 -- 当前市值
    cost_value REAL,                   -- 成本市值
    profit_loss REAL,                  -- 盈亏金额
    profit_loss_pct REAL,              -- 盈亏比例 %
    weight REAL,                       -- 占组合比例 %
    pe_percentile REAL,                -- 快照时 PE 分位
    pb_percentile REAL,                -- 快照时 PB 分位
    created_at TEXT NOT NULL,
    UNIQUE (snapshot_date, target_code)
);

CREATE INDEX IF NOT EXISTS idx_portfolio_date
    ON portfolio_snapshot(snapshot_date);
CREATE INDEX IF NOT EXISTS idx_portfolio_target
    ON portfolio_snapshot(target_code);

CREATE TABLE IF NOT EXISTS watchlist (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    added_date TEXT NOT NULL,          -- 加入清单日期
    target_type TEXT NOT NULL,         -- index/fund
    target_code TEXT NOT NULL,
    target_name TEXT,
    source TEXT NOT NULL,              -- 来源：政策分析/行业分析/产业趋势/机会分析
    source_doc TEXT,                   -- 来源文档路径
    thesis TEXT,                       -- 投资逻辑
    priority TEXT,                     -- high/medium/low
    status TEXT NOT NULL,              -- pending/watching/entered/exited/rejected
    pe_percentile_at_add REAL,         -- 加入时 PE 分位
    expected_entry_pe_pct REAL,        -- 预期入场 PE 分位
    notes TEXT,
    UNIQUE (target_code, source)
);

CREATE INDEX IF NOT EXISTS idx_watchlist_status
    ON watchlist(status);
CREATE INDEX IF NOT EXISTS idx_watchlist_priority
    ON watchlist(priority);