File size: 5,404 Bytes
c6abe34 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 | -- 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';
|