migrations/007_add_detailed_stats.sql

-- Migration 007: Add detailed FIBA stats to match_player_stats
ALTER TABLE match_player_stats ADD COLUMN IF NOT EXISTS blkr INTEGER DEFAULT 0;
ALTER TABLE match_player_stats ADD COLUMN IF NOT EXISTS fls_on INTEGER DEFAULT 0;
ALTER TABLE match_player_stats ADD COLUMN IF NOT EXISTS is_starter BOOLEAN DEFAULT FALSE;

-- Create table for team-level advanced statistics if not exists (using match_id for consistency with existing)
CREATE TABLE IF NOT EXISTS match_team_stats (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    match_id UUID NOT NULL REFERENCES matches(id) ON DELETE CASCADE,
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    points_from_turnovers INTEGER DEFAULT 0,
    points_in_paint INTEGER DEFAULT 0,
    second_chance_points INTEGER DEFAULT 0,
    fast_break_points INTEGER DEFAULT 0,
    bench_points INTEGER DEFAULT 0,
    biggest_lead INTEGER DEFAULT 0,
    biggest_scoring_run INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(match_id)
);

ALTER TABLE match_team_stats ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view match team stats" ON match_team_stats
    FOR SELECT USING (
        organization_id IN (
            SELECT id FROM organizations WHERE owner_id::text = (SELECT auth.uid())::text
            UNION
            SELECT organization_id FROM users WHERE id::text = (SELECT auth.uid())::text
            UNION
            SELECT organization_id FROM players WHERE user_id::text = (SELECT auth.uid())::text
        )
    );

CREATE POLICY "Owners and Staff can manage match team stats" ON match_team_stats
    FOR ALL WITH CHECK (
        organization_id IN (
            SELECT id FROM organizations WHERE owner_id::text = (SELECT auth.uid())::text
            UNION
            SELECT organization_id FROM users WHERE id::text = (SELECT auth.uid())::text AND (account_type = 'team' OR account_type = 'coach')
        )
    );