-- ============================================================================
-- L3 Schema: Player Features Data Mart (Version 2.0)
-- ============================================================================
-- Based on: L3_ARCHITECTURE_PLAN.md
-- Design: 5-Tier Feature Hierarchy (CORE → TACTICAL → INTELLIGENCE → META → COMPOSITE)
-- Granularity: One row per player (Aggregated Profile)
-- Total Columns: 207 features + 6 metadata = 213 columns
-- ============================================================================

-- ============================================================================
-- Main Table: dm_player_features
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_player_features (
    -- ========================================================================
    -- Metadata (6 columns)
    -- ========================================================================
    steam_id_64 TEXT PRIMARY KEY,
    total_matches INTEGER NOT NULL DEFAULT 0,
    total_rounds INTEGER NOT NULL DEFAULT 0,
    first_match_date INTEGER,  -- Unix timestamp
    last_match_date INTEGER,   -- Unix timestamp
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- ========================================================================
    -- TIER 1: CORE (41 columns)
    -- Direct aggregations from fact_match_players
    -- ========================================================================
    
    -- Basic Performance (15 columns)
    core_avg_rating REAL DEFAULT 0.0,
    core_avg_rating2 REAL DEFAULT 0.0,
    core_avg_kd REAL DEFAULT 0.0,
    core_avg_adr REAL DEFAULT 0.0,
    core_avg_kast REAL DEFAULT 0.0,
    core_avg_rws REAL DEFAULT 0.0,
    core_avg_hs_kills REAL DEFAULT 0.0,
    core_hs_rate REAL DEFAULT 0.0,  -- hs/total_kills
    core_total_kills INTEGER DEFAULT 0,
    core_total_deaths INTEGER DEFAULT 0,
    core_total_assists INTEGER DEFAULT 0,
    core_avg_assists REAL DEFAULT 0.0,
    core_kpr REAL DEFAULT 0.0,  -- kills per round
    core_dpr REAL DEFAULT 0.0,  -- deaths per round
    core_survival_rate REAL DEFAULT 0.0,
    
    -- Match Stats (8 columns)
    core_win_rate REAL DEFAULT 0.0,
    core_wins INTEGER DEFAULT 0,
    core_losses INTEGER DEFAULT 0,
    core_avg_match_duration INTEGER DEFAULT 0,  -- seconds
    core_avg_mvps REAL DEFAULT 0.0,
    core_mvp_rate REAL DEFAULT 0.0,
    core_avg_elo_change REAL DEFAULT 0.0,
    core_total_elo_gained REAL DEFAULT 0.0,
    
    -- Weapon Stats (12 columns)
    core_avg_awp_kills REAL DEFAULT 0.0,
    core_awp_usage_rate REAL DEFAULT 0.0,
    core_avg_knife_kills REAL DEFAULT 0.0,
    core_avg_zeus_kills REAL DEFAULT 0.0,
    core_zeus_buy_rate REAL DEFAULT 0.0,
    core_top_weapon TEXT,
    core_top_weapon_kills INTEGER DEFAULT 0,
    core_top_weapon_hs_rate REAL DEFAULT 0.0,
    core_weapon_diversity REAL DEFAULT 0.0,
    core_rifle_hs_rate REAL DEFAULT 0.0,
    core_pistol_hs_rate REAL DEFAULT 0.0,
    core_smg_kills_total INTEGER DEFAULT 0,
    
    -- Objective Stats (6 columns)
    core_avg_plants REAL DEFAULT 0.0,
    core_avg_defuses REAL DEFAULT 0.0,
    core_avg_flash_assists REAL DEFAULT 0.0,
    core_plant_success_rate REAL DEFAULT 0.0,
    core_defuse_success_rate REAL DEFAULT 0.0,
    core_objective_impact REAL DEFAULT 0.0,
    
    -- ========================================================================
    -- TIER 2: TACTICAL (44 columns)
    -- Multi-table JOINs, conditional aggregations
    -- ========================================================================
    
    -- Opening Impact (8 columns)
    tac_avg_fk REAL DEFAULT 0.0,
    tac_avg_fd REAL DEFAULT 0.0,
    tac_fk_rate REAL DEFAULT 0.0,
    tac_fd_rate REAL DEFAULT 0.0,
    tac_fk_success_rate REAL DEFAULT 0.0,
    tac_entry_kill_rate REAL DEFAULT 0.0,
    tac_entry_death_rate REAL DEFAULT 0.0,
    tac_opening_duel_winrate REAL DEFAULT 0.0,
    
    -- Multi-Kill (6 columns)
    tac_avg_2k REAL DEFAULT 0.0,
    tac_avg_3k REAL DEFAULT 0.0,
    tac_avg_4k REAL DEFAULT 0.0,
    tac_avg_5k REAL DEFAULT 0.0,
    tac_multikill_rate REAL DEFAULT 0.0,
    tac_ace_count INTEGER DEFAULT 0,
    
    -- Clutch Performance (10 columns)
    tac_clutch_1v1_attempts INTEGER DEFAULT 0,
    tac_clutch_1v1_wins INTEGER DEFAULT 0,
    tac_clutch_1v1_rate REAL DEFAULT 0.0,
    tac_clutch_1v2_attempts INTEGER DEFAULT 0,
    tac_clutch_1v2_wins INTEGER DEFAULT 0,
    tac_clutch_1v2_rate REAL DEFAULT 0.0,
    tac_clutch_1v3_plus_attempts INTEGER DEFAULT 0,
    tac_clutch_1v3_plus_wins INTEGER DEFAULT 0,
    tac_clutch_1v3_plus_rate REAL DEFAULT 0.0,
    tac_clutch_impact_score REAL DEFAULT 0.0,
    
    -- Utility Mastery (13 columns)
    tac_util_flash_per_round REAL DEFAULT 0.0,
    tac_util_smoke_per_round REAL DEFAULT 0.0,
    tac_util_molotov_per_round REAL DEFAULT 0.0,
    tac_util_he_per_round REAL DEFAULT 0.0,
    tac_util_usage_rate REAL DEFAULT 0.0,
    tac_util_nade_dmg_per_round REAL DEFAULT 0.0,
    tac_util_nade_dmg_per_nade REAL DEFAULT 0.0,
    tac_util_flash_time_per_round REAL DEFAULT 0.0,
    tac_util_flash_enemies_per_round REAL DEFAULT 0.0,
    tac_util_flash_efficiency REAL DEFAULT 0.0,
    tac_util_impact_score REAL DEFAULT 0.0,
    tac_util_zeus_equipped_count INTEGER DEFAULT 0,
    -- tac_util_zeus_kills REMOVED
    
    -- Economy Efficiency (8 columns)
    tac_eco_dmg_per_1k REAL DEFAULT 0.0,
    tac_eco_kpr_eco_rounds REAL DEFAULT 0.0,
    tac_eco_kd_eco_rounds REAL DEFAULT 0.0,
    tac_eco_kpr_force_rounds REAL DEFAULT 0.0,
    tac_eco_kpr_full_rounds REAL DEFAULT 0.0,
    tac_eco_save_discipline REAL DEFAULT 0.0,
    tac_eco_force_success_rate REAL DEFAULT 0.0,
    tac_eco_efficiency_score REAL DEFAULT 0.0,
    
    -- ========================================================================
    -- TIER 3: INTELLIGENCE (53 columns)
    -- Advanced analytics on fact_round_events
    -- ========================================================================
    
    -- High IQ Kills (9 columns)
    int_wallbang_kills INTEGER DEFAULT 0,
    int_wallbang_rate REAL DEFAULT 0.0,
    int_smoke_kills INTEGER DEFAULT 0,
    int_smoke_kill_rate REAL DEFAULT 0.0,
    int_blind_kills INTEGER DEFAULT 0,
    int_blind_kill_rate REAL DEFAULT 0.0,
    int_noscope_kills INTEGER DEFAULT 0,
    int_noscope_rate REAL DEFAULT 0.0,
    int_high_iq_score REAL DEFAULT 0.0,
    
    -- Timing Analysis (12 columns)
    int_timing_early_kills INTEGER DEFAULT 0,
    int_timing_mid_kills INTEGER DEFAULT 0,
    int_timing_late_kills INTEGER DEFAULT 0,
    int_timing_early_kill_share REAL DEFAULT 0.0,
    int_timing_mid_kill_share REAL DEFAULT 0.0,
    int_timing_late_kill_share REAL DEFAULT 0.0,
    int_timing_avg_kill_time REAL DEFAULT 0.0,
    int_timing_early_deaths INTEGER DEFAULT 0,
    int_timing_early_death_rate REAL DEFAULT 0.0,
    int_timing_aggression_index REAL DEFAULT 0.0,
    int_timing_patience_score REAL DEFAULT 0.0,
    int_timing_first_contact_time REAL DEFAULT 0.0,
    
    -- Pressure Performance (9 columns)
    int_pressure_comeback_kd REAL DEFAULT 0.0,
    int_pressure_comeback_rating REAL DEFAULT 0.0,
    int_pressure_losing_streak_kd REAL DEFAULT 0.0,
    int_pressure_matchpoint_kpr REAL DEFAULT 0.0,
    int_pressure_clutch_composure REAL DEFAULT 0.0,
    int_pressure_entry_in_loss REAL DEFAULT 0.0,
    int_pressure_performance_index REAL DEFAULT 0.0,
    int_pressure_big_moment_score REAL DEFAULT 0.0,
    int_pressure_tilt_resistance REAL DEFAULT 0.0,
    
    -- Position Mastery (14 columns)
    int_pos_site_a_control_rate REAL DEFAULT 0.0,
    int_pos_site_b_control_rate REAL DEFAULT 0.0,
    int_pos_mid_control_rate REAL DEFAULT 0.0,
    int_pos_favorite_position TEXT,
    int_pos_position_diversity REAL DEFAULT 0.0,
    int_pos_rotation_speed REAL DEFAULT 0.0,
    int_pos_map_coverage REAL DEFAULT 0.0,
    int_pos_lurk_tendency REAL DEFAULT 0.0,
    int_pos_site_anchor_score REAL DEFAULT 0.0,
    int_pos_entry_route_diversity REAL DEFAULT 0.0,
    int_pos_retake_positioning REAL DEFAULT 0.0,
    int_pos_postplant_positioning REAL DEFAULT 0.0,
    int_pos_spatial_iq_score REAL DEFAULT 0.0,
    int_pos_avg_distance_from_teammates REAL DEFAULT 0.0,
    
    -- Trade Network (8 columns)
    int_trade_kill_count INTEGER DEFAULT 0,
    int_trade_kill_rate REAL DEFAULT 0.0,
    int_trade_response_time REAL DEFAULT 0.0,
    int_trade_given_count INTEGER DEFAULT 0,
    int_trade_given_rate REAL DEFAULT 0.0,
    int_trade_balance REAL DEFAULT 0.0,
    int_trade_efficiency REAL DEFAULT 0.0,
    int_teamwork_score REAL DEFAULT 0.0,
    
    -- ========================================================================
    -- TIER 4: META (52 columns)
    -- Long-term patterns and meta-features
    -- ========================================================================
    
    -- Stability (8 columns)
    meta_rating_volatility REAL DEFAULT 0.0,
    meta_recent_form_rating REAL DEFAULT 0.0,
    meta_win_rating REAL DEFAULT 0.0,
    meta_loss_rating REAL DEFAULT 0.0,
    meta_rating_consistency REAL DEFAULT 0.0,
    meta_time_rating_correlation REAL DEFAULT 0.0,
    meta_map_stability REAL DEFAULT 0.0,
    meta_elo_tier_stability REAL DEFAULT 0.0,
    
    -- Side Preference (14 columns)
    meta_side_ct_rating REAL DEFAULT 0.0,
    meta_side_t_rating REAL DEFAULT 0.0,
    meta_side_ct_kd REAL DEFAULT 0.0,
    meta_side_t_kd REAL DEFAULT 0.0,
    meta_side_ct_win_rate REAL DEFAULT 0.0,
    meta_side_t_win_rate REAL DEFAULT 0.0,
    meta_side_ct_fk_rate REAL DEFAULT 0.0,
    meta_side_t_fk_rate REAL DEFAULT 0.0,
    meta_side_ct_kast REAL DEFAULT 0.0,
    meta_side_t_kast REAL DEFAULT 0.0,
    meta_side_rating_diff REAL DEFAULT 0.0,
    meta_side_kd_diff REAL DEFAULT 0.0,
    meta_side_preference TEXT,
    meta_side_balance_score REAL DEFAULT 0.0,
    
    -- Opponent Adaptation (12 columns)
    meta_opp_vs_lower_elo_rating REAL DEFAULT 0.0,
    meta_opp_vs_similar_elo_rating REAL DEFAULT 0.0,
    meta_opp_vs_higher_elo_rating REAL DEFAULT 0.0,
    meta_opp_vs_lower_elo_kd REAL DEFAULT 0.0,
    meta_opp_vs_similar_elo_kd REAL DEFAULT 0.0,
    meta_opp_vs_higher_elo_kd REAL DEFAULT 0.0,
    meta_opp_elo_adaptation REAL DEFAULT 0.0,
    meta_opp_stomping_score REAL DEFAULT 0.0,
    meta_opp_upset_score REAL DEFAULT 0.0,
    meta_opp_consistency_across_elos REAL DEFAULT 0.0,
    meta_opp_rank_resistance REAL DEFAULT 0.0,
    meta_opp_smurf_detection REAL DEFAULT 0.0,
    
    -- Map Specialization (10 columns)
    meta_map_best_map TEXT,
    meta_map_best_rating REAL DEFAULT 0.0,
    meta_map_worst_map TEXT,
    meta_map_worst_rating REAL DEFAULT 0.0,
    meta_map_diversity REAL DEFAULT 0.0,
    meta_map_pool_size INTEGER DEFAULT 0,
    meta_map_specialist_score REAL DEFAULT 0.0,
    meta_map_versatility REAL DEFAULT 0.0,
    meta_map_comfort_zone_rate REAL DEFAULT 0.0,
    meta_map_adaptation REAL DEFAULT 0.0,
    
    -- Session Pattern (8 columns)
    meta_session_avg_matches_per_day REAL DEFAULT 0.0,
    meta_session_longest_streak INTEGER DEFAULT 0,
    meta_session_weekend_rating REAL DEFAULT 0.0,
    meta_session_weekday_rating REAL DEFAULT 0.0,
    meta_session_morning_rating REAL DEFAULT 0.0,
    meta_session_afternoon_rating REAL DEFAULT 0.0,
    meta_session_evening_rating REAL DEFAULT 0.0,
    meta_session_night_rating REAL DEFAULT 0.0,
    
    -- ========================================================================
    -- TIER 5: COMPOSITE (11 columns)
    -- Weighted composite scores (0-100)
    -- ========================================================================
    score_aim REAL DEFAULT 0.0,
    score_clutch REAL DEFAULT 0.0,
    score_pistol REAL DEFAULT 0.0,
    score_defense REAL DEFAULT 0.0,
    score_utility REAL DEFAULT 0.0,
    score_stability REAL DEFAULT 0.0,
    score_economy REAL DEFAULT 0.0,
    score_pace REAL DEFAULT 0.0,
    score_overall REAL DEFAULT 0.0,
    tier_classification TEXT,
    tier_percentile REAL DEFAULT 0.0,
    
    -- Foreign key constraint
    FOREIGN KEY (steam_id_64) REFERENCES dim_players(steam_id_64)
);

-- Indexes for query performance
CREATE INDEX IF NOT EXISTS idx_dm_player_features_rating ON dm_player_features(core_avg_rating DESC);
CREATE INDEX IF NOT EXISTS idx_dm_player_features_matches ON dm_player_features(total_matches DESC);
CREATE INDEX IF NOT EXISTS idx_dm_player_features_tier ON dm_player_features(tier_classification);
CREATE INDEX IF NOT EXISTS idx_dm_player_features_updated ON dm_player_features(last_updated DESC);

-- ============================================================================
-- Auxiliary Table: dm_player_match_history
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_player_match_history (
    steam_id_64 TEXT,
    match_id TEXT,
    match_date INTEGER,  -- Unix timestamp
    match_sequence INTEGER,  -- Player's N-th match
    
    -- Core performance snapshot
    rating REAL,
    kd_ratio REAL,
    adr REAL,
    kast REAL,
    is_win BOOLEAN,
    
    -- Match context
    map_name TEXT,
    opponent_avg_elo REAL,
    teammate_avg_rating REAL,
    
    -- Cumulative stats
    cumulative_rating REAL,
    rolling_10_rating REAL,
    
    PRIMARY KEY (steam_id_64, match_id),
    FOREIGN KEY (steam_id_64) REFERENCES dm_player_features(steam_id_64) ON DELETE CASCADE,
    FOREIGN KEY (match_id) REFERENCES fact_matches(match_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_player_history_player_date ON dm_player_match_history(steam_id_64, match_date DESC);
CREATE INDEX IF NOT EXISTS idx_player_history_match ON dm_player_match_history(match_id);

-- ============================================================================
-- Auxiliary Table: dm_player_map_stats
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_player_map_stats (
    steam_id_64 TEXT,
    map_name TEXT,
    
    matches INTEGER DEFAULT 0,
    wins INTEGER DEFAULT 0,
    win_rate REAL DEFAULT 0.0,
    
    avg_rating REAL DEFAULT 0.0,
    avg_kd REAL DEFAULT 0.0,
    avg_adr REAL DEFAULT 0.0,
    avg_kast REAL DEFAULT 0.0,
    
    best_rating REAL DEFAULT 0.0,
    worst_rating REAL DEFAULT 0.0,
    
    PRIMARY KEY (steam_id_64, map_name),
    FOREIGN KEY (steam_id_64) REFERENCES dm_player_features(steam_id_64) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_player_map_stats_player ON dm_player_map_stats(steam_id_64);
CREATE INDEX IF NOT EXISTS idx_player_map_stats_map ON dm_player_map_stats(map_name);

-- ============================================================================
-- Auxiliary Table: dm_player_weapon_stats
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_player_weapon_stats (
    steam_id_64 TEXT,
    weapon_name TEXT,
    
    total_kills INTEGER DEFAULT 0,
    total_headshots INTEGER DEFAULT 0,
    hs_rate REAL DEFAULT 0.0,
    
    usage_rounds INTEGER DEFAULT 0,
    usage_rate REAL DEFAULT 0.0,
    
    avg_kills_per_round REAL DEFAULT 0.0,
    effectiveness_score REAL DEFAULT 0.0,
    
    PRIMARY KEY (steam_id_64, weapon_name),
    FOREIGN KEY (steam_id_64) REFERENCES dm_player_features(steam_id_64) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_player_weapon_stats_player ON dm_player_weapon_stats(steam_id_64);
CREATE INDEX IF NOT EXISTS idx_player_weapon_stats_weapon ON dm_player_weapon_stats(weapon_name);

-- ============================================================================
-- Profile Mart: Time-window statistics
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_player_period_stats (
    steam_id_64 TEXT NOT NULL,
    period_key TEXT NOT NULL,
    period_label TEXT NOT NULL,
    period_start INTEGER,
    period_end INTEGER,
    matches INTEGER NOT NULL DEFAULT 0,
    wins INTEGER NOT NULL DEFAULT 0,
    win_rate REAL,
    avg_rating REAL,
    avg_kd REAL,
    avg_adr REAL,
    avg_kast REAL,
    total_kills INTEGER NOT NULL DEFAULT 0,
    total_deaths INTEGER NOT NULL DEFAULT 0,
    sample_reliable BOOLEAN NOT NULL DEFAULT 0,
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (steam_id_64, period_key),
    FOREIGN KEY (steam_id_64)
        REFERENCES dm_player_features(steam_id_64) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_player_period_player
ON dm_player_period_stats(steam_id_64, period_key);

-- ============================================================================
-- Profile Mart: Career records linked to the source match
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_player_records (
    steam_id_64 TEXT NOT NULL,
    record_key TEXT NOT NULL,
    record_label TEXT NOT NULL,
    record_value REAL,
    match_id TEXT,
    map_name TEXT,
    match_date INTEGER,
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (steam_id_64, record_key),
    FOREIGN KEY (steam_id_64)
        REFERENCES dm_player_features(steam_id_64) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_player_records_player
ON dm_player_records(steam_id_64, record_key);

-- ============================================================================
-- Team Mart: Duo performance
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_duo_stats (
    steam_id_a TEXT NOT NULL,
    steam_id_b TEXT NOT NULL,
    matches INTEGER NOT NULL DEFAULT 0,
    wins INTEGER NOT NULL DEFAULT 0,
    win_rate REAL,
    avg_rating_a REAL,
    avg_rating_b REAL,
    avg_combined_rating REAL,
    first_match_date INTEGER,
    last_match_date INTEGER,
    sample_reliable BOOLEAN NOT NULL DEFAULT 0,
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (steam_id_a, steam_id_b)
);

CREATE INDEX IF NOT EXISTS idx_duo_stats_matches
ON dm_duo_stats(matches DESC, win_rate DESC);

-- ============================================================================
-- Team Mart: Actual 2-5 player combinations
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_lineup_stats (
    lineup_key TEXT PRIMARY KEY,
    player_ids_json TEXT NOT NULL,
    player_count INTEGER NOT NULL,
    matches INTEGER NOT NULL DEFAULT 0,
    wins INTEGER NOT NULL DEFAULT 0,
    win_rate REAL,
    avg_team_rating REAL,
    first_match_date INTEGER,
    last_match_date INTEGER,
    sample_reliable BOOLEAN NOT NULL DEFAULT 0,
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_lineup_stats_size_matches
ON dm_lineup_stats(player_count, matches DESC, win_rate DESC);

-- ============================================================================
-- Narrative Mart: Post-match team report
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_match_reports (
    match_id TEXT PRIMARY KEY,
    match_date INTEGER NOT NULL,
    map_name TEXT,
    roster_count INTEGER NOT NULL DEFAULT 0,
    team_key INTEGER,
    is_win BOOLEAN,
    team_avg_rating REAL,
    team_avg_adr REAL,
    mvp_steam_id TEXT,
    mvp_rating REAL,
    improver_steam_id TEXT,
    improver_delta REAL,
    strongest_duo_json TEXT,
    record_break_count INTEGER NOT NULL DEFAULT 0,
    summary_text TEXT,
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_match_reports_date
ON dm_match_reports(match_date DESC);

-- ============================================================================
-- Narrative Mart: Player performance vs prior form
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_match_player_reports (
    match_id TEXT NOT NULL,
    steam_id_64 TEXT NOT NULL,
    match_date INTEGER NOT NULL,
    rating REAL,
    kd_ratio REAL,
    adr REAL,
    kills INTEGER,
    deaths INTEGER,
    baseline_matches INTEGER NOT NULL DEFAULT 0,
    prior_20_rating REAL,
    prior_20_kd REAL,
    prior_20_adr REAL,
    rating_delta REAL,
    kd_delta REAL,
    adr_delta REAL,
    performance_label TEXT,
    record_keys_json TEXT NOT NULL DEFAULT '[]',
    PRIMARY KEY (match_id, steam_id_64)
);

CREATE INDEX IF NOT EXISTS idx_match_player_reports_player_date
ON dm_match_player_reports(steam_id_64, match_date DESC);

-- ============================================================================
-- Narrative Mart: Every career-record breaking event
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_player_record_events (
    steam_id_64 TEXT NOT NULL,
    record_key TEXT NOT NULL,
    match_id TEXT NOT NULL,
    match_date INTEGER NOT NULL,
    map_name TEXT,
    previous_value REAL,
    record_value REAL NOT NULL,
    is_current_record BOOLEAN NOT NULL DEFAULT 0,
    PRIMARY KEY (steam_id_64, record_key, match_id)
);

CREATE INDEX IF NOT EXISTS idx_record_events_player_date
ON dm_player_record_events(steam_id_64, match_date DESC);

-- ============================================================================
-- Team Mart: Calendar season performance
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_team_season_stats (
    season_key TEXT PRIMARY KEY,
    season_label TEXT NOT NULL,
    period_start INTEGER NOT NULL,
    period_end INTEGER NOT NULL,
    matches INTEGER NOT NULL DEFAULT 0,
    wins INTEGER NOT NULL DEFAULT 0,
    losses INTEGER NOT NULL DEFAULT 0,
    win_rate REAL,
    avg_team_rating REAL,
    best_map TEXT,
    best_map_win_rate REAL,
    top_player_steam_id TEXT,
    top_player_rating REAL,
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================================
-- Award Mart: Best player by calendar period
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_player_awards (
    award_type TEXT NOT NULL,
    period_key TEXT NOT NULL,
    period_label TEXT NOT NULL,
    period_start INTEGER NOT NULL,
    period_end INTEGER NOT NULL,
    steam_id_64 TEXT NOT NULL,
    matches INTEGER NOT NULL,
    wins INTEGER NOT NULL,
    win_rate REAL,
    avg_rating REAL,
    avg_kd REAL,
    avg_adr REAL,
    performance_score REAL,
    sample_reliable BOOLEAN NOT NULL DEFAULT 0,
    PRIMARY KEY (award_type, period_key)
);

CREATE INDEX IF NOT EXISTS idx_player_awards_player
ON dm_player_awards(steam_id_64, period_start DESC);

CREATE INDEX IF NOT EXISTS idx_player_awards_period
ON dm_player_awards(award_type, period_start DESC);

-- ============================================================================
-- Discovery Mart: Data-driven fun facts, both positive and negative
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_discovery_insights (
    insight_key TEXT PRIMARY KEY,
    steam_id_64 TEXT,
    tone TEXT NOT NULL
        CHECK (tone IN ('positive', 'negative', 'fun')),
    category TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    metric_label TEXT,
    metric_value REAL,
    metric_unit TEXT,
    match_id TEXT,
    evidence_json TEXT NOT NULL DEFAULT '{}',
    display_order INTEGER NOT NULL DEFAULT 0,
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_discovery_tone_order
ON dm_discovery_insights(tone, display_order, insight_key);

CREATE INDEX IF NOT EXISTS idx_discovery_player
ON dm_discovery_insights(steam_id_64, tone);

-- ============================================================================
-- Medal Mart: Gold/silver/bronze by map and ELO segment
-- ============================================================================
CREATE TABLE IF NOT EXISTS dm_performance_medals (
    dimension_type TEXT NOT NULL
        CHECK (dimension_type IN ('map', 'elo')),
    dimension_key TEXT NOT NULL,
    dimension_label TEXT NOT NULL,
    medal_rank INTEGER NOT NULL CHECK (medal_rank BETWEEN 1 AND 3),
    medal_tier TEXT NOT NULL
        CHECK (medal_tier IN ('gold', 'silver', 'bronze')),
    steam_id_64 TEXT NOT NULL,
    matches INTEGER NOT NULL,
    wins INTEGER NOT NULL,
    win_rate REAL,
    avg_rating REAL,
    avg_kd REAL,
    avg_adr REAL,
    sample_reliable BOOLEAN NOT NULL DEFAULT 0,
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (dimension_type, dimension_key, medal_rank)
);

CREATE INDEX IF NOT EXISTS idx_performance_medals_player
ON dm_performance_medals(steam_id_64, dimension_type, medal_rank);

CREATE INDEX IF NOT EXISTS idx_performance_medals_dimension
ON dm_performance_medals(dimension_type, dimension_key, medal_rank);

-- ============================================================================
-- Schema Summary
-- ============================================================================
-- dm_player_features: 213 columns (6 metadata + 207 features)
--   - Tier 1 CORE: 41 columns
--   - Tier 2 TACTICAL: 44 columns
--   - Tier 3 INTELLIGENCE: 53 columns
--   - Tier 4 META: 52 columns
--   - Tier 5 COMPOSITE: 11 columns
--
-- dm_player_match_history: Per-match snapshots for trend analysis
-- dm_player_map_stats: Map-level aggregations
-- dm_player_weapon_stats: Weapon usage statistics
-- dm_player_period_stats: Career/recent time-window aggregations
-- dm_player_records: Career record values and source matches
-- dm_duo_stats: Same-team pair performance
-- dm_lineup_stats: Actual same-team 2-5 player combinations
-- dm_match_reports: Post-match team narrative
-- dm_match_player_reports: Player form deltas per match
-- dm_player_record_events: Historical record-breaking moments
-- dm_team_season_stats: Calendar season team summaries
-- dm_player_awards: Daily/weekly/monthly/quarterly/yearly awards
-- dm_discovery_insights: Positive, negative and quirky data discoveries
-- dm_performance_medals: Map and ELO-segment gold/silver/bronze medals
-- ============================================================================