CREATE TABLE IF NOT EXISTS security (
    ticker VARCHAR(16) NOT NULL COMMENT '带市场后缀的证券代码',
    company VARCHAR(128) NOT NULL COMMENT '公司简称',
    market VARCHAR(16) NOT NULL COMMENT '交易市场',
    currency CHAR(3) NOT NULL COMMENT 'CNY或HKD',
    active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否纳入每日更新',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (ticker),
    KEY idx_security_active (active, market),
    CONSTRAINT chk_security_active CHECK (active IN (0, 1)),
    CONSTRAINT chk_security_currency CHECK (currency IN ('CNY', 'HKD'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS valuation_version (
    valuation_id VARCHAR(64) NOT NULL COMMENT '可稳定复现的估值版本ID',
    ticker VARCHAR(16) NOT NULL,
    valuation_date DATE NOT NULL COMMENT '正式估值基准日',
    method VARCHAR(32) NOT NULL COMMENT '主估值模型',
    pessimistic_low DECIMAL(20,6) NULL,
    pessimistic_high DECIMAL(20,6) NULL,
    base_low DECIMAL(20,6) NOT NULL,
    base_high DECIMAL(20,6) NOT NULL,
    optimistic_low DECIMAL(20,6) NULL,
    optimistic_high DECIMAL(20,6) NULL,
    normalized_profit DECIMAL(24,6) NULL,
    normalized_pe DECIMAL(20,6) NULL,
    pb DECIMAL(20,6) NULL,
    ps DECIMAL(20,6) NULL,
    consensus_year SMALLINT UNSIGNED NULL,
    consensus_profit DECIMAL(24,6) NULL,
    consensus_count INT UNSIGNED NULL,
    report_path VARCHAR(1024) NOT NULL,
    snapshot_path VARCHAR(1024) NULL,
    source_hash CHAR(64) NOT NULL,
    active_from DATE NOT NULL,
    active_to DATE NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (valuation_id),
    UNIQUE KEY uq_valuation_source (ticker, valuation_date, source_hash),
    KEY idx_valuation_active (ticker, active_from, active_to),
    CONSTRAINT fk_valuation_security FOREIGN KEY (ticker) REFERENCES security(ticker),
    CONSTRAINT chk_valuation_active_dates CHECK (active_to IS NULL OR active_to >= active_from),
    CONSTRAINT chk_valuation_base CHECK (base_low <= base_high),
    CONSTRAINT chk_valuation_optimistic CHECK (optimistic_low IS NULL OR optimistic_high IS NULL OR optimistic_low <= optimistic_high),
    CONSTRAINT chk_valuation_pessimistic CHECK (pessimistic_low IS NULL OR pessimistic_high IS NULL OR pessimistic_low <= pessimistic_high)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS daily_price (
    ticker VARCHAR(16) NOT NULL,
    trade_date DATE NOT NULL,
    close DECIMAL(20,6) NOT NULL,
    currency CHAR(3) NOT NULL,
    source_id VARCHAR(64) NOT NULL,
    source_timestamp DATETIME(6) NULL,
    ingested_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (ticker, trade_date),
    KEY idx_daily_price_date (trade_date, ticker),
    CONSTRAINT fk_daily_price_security FOREIGN KEY (ticker) REFERENCES security(ticker),
    CONSTRAINT chk_daily_price_close CHECK (close > 0),
    CONSTRAINT chk_daily_price_currency CHECK (currency IN ('CNY', 'HKD'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS daily_judgement (
    ticker VARCHAR(16) NOT NULL,
    trade_date DATE NOT NULL,
    valuation_id VARCHAR(64) NOT NULL,
    close DECIMAL(20,6) NOT NULL,
    base_low DECIMAL(20,6) NOT NULL,
    base_high DECIMAL(20,6) NOT NULL,
    optimistic_high DECIMAL(20,6) NULL,
    label VARCHAR(16) NOT NULL,
    distance_to_base_low DECIMAL(20,8) NOT NULL,
    distance_to_base_high DECIMAL(20,8) NOT NULL,
    computed_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (ticker, trade_date),
    KEY idx_judgement_date_label (trade_date, label, ticker),
    KEY idx_judgement_valuation (valuation_id),
    CONSTRAINT fk_judgement_security FOREIGN KEY (ticker) REFERENCES security(ticker),
    CONSTRAINT fk_judgement_valuation FOREIGN KEY (valuation_id) REFERENCES valuation_version(valuation_id),
    CONSTRAINT chk_judgement_base CHECK (base_low <= base_high),
    CONSTRAINT chk_judgement_label CHECK (label IN ('偏低', '基本合理', '偏贵', '明显偏贵'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;