migrations/006_stat_import_v2.sql

-- Migration 006: Production-grade Stat Sheet Import Pipeline
-- Maps "team_id" from spec to "organization_id" for compatibility with existing schema.

-- A. games (Replaces/Augments matches)
CREATE TABLE IF NOT EXISTS games (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    opponent_name TEXT,
    game_date DATE,
    source_file_url TEXT,
    source_file_type TEXT,
    import_job_id UUID,
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_games_org ON games(organization_id);

-- B. stat_import_jobs
CREATE TABLE IF NOT EXISTS stat_import_jobs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    uploaded_by UUID REFERENCES users(id) ON DELETE SET NULL,
    source_file_url TEXT,
    source_file_type TEXT,
    status TEXT DEFAULT 'uploaded',
    document_type TEXT,
    confidence_score NUMERIC,
    review_required BOOLEAN DEFAULT FALSE,
    raw_extraction JSONB,
    normalized_extraction JSONB,
    validation_report JSONB,
    matching_report JSONB,
    failure_reason TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_stat_import_jobs_org ON stat_import_jobs(organization_id);

-- Add foreign key from games to import job now that it's created
ALTER TABLE games ADD CONSTRAINT fk_games_import_job FOREIGN KEY (import_job_id) REFERENCES stat_import_jobs(id) ON DELETE SET NULL;

-- C. game_player_stats
CREATE TABLE IF NOT EXISTS game_player_stats (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    game_id UUID NOT NULL REFERENCES games(id) ON DELETE CASCADE,
    player_id UUID REFERENCES players(id) ON DELETE SET NULL,
    jersey_number INTEGER,
    player_name_raw TEXT NOT NULL,
    starter_status TEXT,
    is_captain BOOLEAN DEFAULT FALSE,
    position TEXT,
    minutes TEXT,
    points INTEGER DEFAULT 0,
    fg_made INTEGER DEFAULT 0,
    fg_attempted INTEGER DEFAULT 0,
    fg_pct NUMERIC,
    two_p_made INTEGER DEFAULT 0,
    two_p_attempted INTEGER DEFAULT 0,
    two_p_pct NUMERIC,
    three_p_made INTEGER DEFAULT 0,
    three_p_attempted INTEGER DEFAULT 0,
    three_p_pct NUMERIC,
    ft_made INTEGER DEFAULT 0,
    ft_attempted INTEGER DEFAULT 0,
    ft_pct NUMERIC,
    off_reb INTEGER DEFAULT 0,
    def_reb INTEGER DEFAULT 0,
    reb INTEGER DEFAULT 0,
    ast INTEGER DEFAULT 0,
    turnovers INTEGER DEFAULT 0,
    raw_row_json JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_game_player_stats_game ON game_player_stats(game_id);
CREATE INDEX IF NOT EXISTS idx_game_player_stats_player ON game_player_stats(player_id);

-- D. game_team_totals
CREATE TABLE IF NOT EXISTS game_team_totals (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    game_id UUID NOT NULL REFERENCES games(id) ON DELETE CASCADE,
    points INTEGER DEFAULT 0,
    fg_made INTEGER DEFAULT 0,
    fg_attempted INTEGER DEFAULT 0,
    fg_pct NUMERIC,
    two_p_made INTEGER DEFAULT 0,
    two_p_attempted INTEGER DEFAULT 0,
    two_p_pct NUMERIC,
    three_p_made INTEGER DEFAULT 0,
    three_p_attempted INTEGER DEFAULT 0,
    three_p_pct NUMERIC,
    ft_made INTEGER DEFAULT 0,
    ft_attempted INTEGER DEFAULT 0,
    ft_pct NUMERIC,
    off_reb INTEGER DEFAULT 0,
    def_reb INTEGER DEFAULT 0,
    reb INTEGER DEFAULT 0,
    ast INTEGER DEFAULT 0,
    turnovers INTEGER DEFAULT 0,
    raw_totals_json JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_game_team_totals_game ON game_team_totals(game_id);

-- E. game_team_advanced_stats
CREATE TABLE IF NOT EXISTS game_team_advanced_stats (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    game_id UUID NOT NULL REFERENCES games(id) ON DELETE CASCADE,
    points_from_turnovers INTEGER,
    points_in_paint INTEGER,
    second_chance_points INTEGER,
    fast_break_points INTEGER,
    bench_points INTEGER,
    biggest_lead INTEGER,
    biggest_scoring_run INTEGER,
    raw_summary_json JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_game_team_advanced_stats_game ON game_team_advanced_stats(game_id);

-- F. stat_import_review_items
CREATE TABLE IF NOT EXISTS stat_import_review_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    import_job_id UUID NOT NULL REFERENCES stat_import_jobs(id) ON DELETE CASCADE,
    issue_type TEXT NOT NULL,
    field_path TEXT,
    raw_value TEXT,
    suggested_value TEXT,
    suggested_player_id UUID REFERENCES players(id) ON DELETE SET NULL,
    severity TEXT NOT NULL,
    resolution_status TEXT DEFAULT 'pending',
    resolution_payload JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_stat_import_review_items_job ON stat_import_review_items(import_job_id);

-- RLS
ALTER TABLE games ENABLE ROW LEVEL SECURITY;
ALTER TABLE stat_import_jobs ENABLE ROW LEVEL SECURITY;
ALTER TABLE game_player_stats ENABLE ROW LEVEL SECURITY;
ALTER TABLE game_team_totals ENABLE ROW LEVEL SECURITY;
ALTER TABLE game_team_advanced_stats ENABLE ROW LEVEL SECURITY;
ALTER TABLE stat_import_review_items ENABLE ROW LEVEL SECURITY;

-- PostgREST reload
NOTIFY pgrst, 'reload schema';