-- ============================================================================
-- Lacrosse Scheduler - Seed SQL (bundle)
-- ============================================================================
-- This file is intended for download/reference and bootstrapping environments
-- that already include the legacy baseline tables used by the app.
--
-- It bundles the project's SQL migrations into a single file in roughly
-- chronological order so an admin can review/apply them as needed.
-- ============================================================================


-- ----------------------------------------------------------------------------
-- 004_add_schedule_version_lock_fields.sql
-- ----------------------------------------------------------------------------
-- Migration: Add lock fields to tblscheduleversion
-- Purpose: Enable locking of schedule versions to prevent modifications while allowing reports/PDFs
-- Date: 2025-01-XX

-- Add lock fields to tblscheduleversion (public schema - default)
ALTER TABLE IF EXISTS public.tblscheduleversion
    ADD COLUMN IF NOT EXISTS is_locked BOOLEAN NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS locked_at TIMESTAMP NULL,
    ADD COLUMN IF NOT EXISTS locked_by TEXT NULL;

-- Add comment to explain the lock feature (public schema)
COMMENT ON COLUMN public.tblscheduleversion.is_locked IS 'When true, version cannot be modified or deleted but can be used for reports/PDFs';
COMMENT ON COLUMN public.tblscheduleversion.locked_at IS 'Timestamp when version was locked';
COMMENT ON COLUMN public.tblscheduleversion.locked_by IS 'User who locked the version (optional)';

-- Create index on is_locked for faster queries (public schema)
CREATE INDEX IF NOT EXISTS idx_scheduleversion_is_locked ON public.tblscheduleversion(is_locked);


-- ----------------------------------------------------------------------------
-- 005_add_rockwood_away_game_time_rule.sql
-- ----------------------------------------------------------------------------
-- Migration: Add Rockwood Schools Away Game Time Restriction Rule
-- Description: Rockwood Schools (Marquette, Lafayette, Eureka, Summit) cannot play away games before 4:30 PM
-- Date: 2025-01-XX

-- Insert the scheduling rule
INSERT INTO public.tblschedulingrule (
    rule_type,
    category,
    title,
    description,
    priority,
    is_active,
    applies_to_sport,
    applies_to_season,
    config_value,
    notes,
    created_at,
    updated_at
) VALUES (
    'constraint',  -- Hard rule that must be satisfied
    'home_away',  -- Category for home/away related rules
    'Rockwood Schools Away Game Time Restriction',
    'Rockwood Schools (Marquette, Lafayette, Eureka, and Summit) cannot play an away game before 4:30 PM. This rule ensures adequate travel time for Rockwood district schools.',
    75,  -- High priority constraint
    true,  -- Active
    NULL,  -- Applies to all sports
    NULL,  -- Applies to all seasons
    '16:30',  -- Minimum away game time (4:30 PM in 24-hour format)
    'Rockwood Schools: Marquette High School, Lafayette High School, Eureka High School, Rockwood Summit High School. This rule is enforced during manual game configuration and game updates.',
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
);


-- ----------------------------------------------------------------------------
-- 006_create_school_site_mapping.sql
-- ----------------------------------------------------------------------------
-- Migration: Create school site mapping table
-- This table maps HomeTeams to their SiteName and SubSiteName combinations
-- Generated from ExampleArbiterLacrosse.csv analysis

CREATE TABLE IF NOT EXISTS public.tblschoolsitemapping (
    id SERIAL PRIMARY KEY,
    schoolid TEXT NOT NULL REFERENCES public.tblschool(schoolid),
    home_team_name TEXT NOT NULL,  -- Original name from CSV
    sitename TEXT NOT NULL,
    subsitename TEXT NOT NULL DEFAULT '',
    is_default BOOLEAN DEFAULT FALSE,  -- Default SubSiteName for this school
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(schoolid, sitename, subsitename)
);

CREATE INDEX IF NOT EXISTS idx_schoolsitemapping_schoolid ON public.tblschoolsitemapping(schoolid);
CREATE INDEX IF NOT EXISTS idx_schoolsitemapping_sitename ON public.tblschoolsitemapping(sitename);


-- ----------------------------------------------------------------------------
-- 006_insert_school_site_mappings.sql
-- ----------------------------------------------------------------------------
-- Insert school site mappings
-- Generated from site_mapping_analysis.md
-- Matches HomeTeam names to schoolid values from schoolid_mapping_report.md

INSERT INTO public.tblschoolsitemapping (schoolid, home_team_name, sitename, subsitename, is_default)
VALUES
    ('9003', 'Belleville West High School', 'Belleville West HS', 'BW Stadium', FALSE),
    ('9003', 'Belleville West High School', 'Belleville West HS', 'Stadium', TRUE),
    ('7014', 'Clayton High School', 'Clayton High School', 'Field House @ Gay Field', FALSE),
    ('7014', 'Clayton High School', 'Clayton High School', 'Gay Field', TRUE),
    ('7018', 'Cor Jesu Academy', 'Cor Jesu Academy', 'Soccer Field - Front', TRUE),
    ('7018', 'Cor Jesu Academy', 'Cor Jesu Academy', 'Soccer Field Back (Varsity)', FALSE),
    ('9009', 'Edwardsville High School', 'Edwardsville High School', 'EHS Tiger Stadium', TRUE),
    ('7022', 'Eureka High School', 'Eureka High School', 'Lacrosse Field', FALSE),
    ('7022', 'Eureka High School', 'Eureka High School', 'Stadium', TRUE),
    ('1006', 'Fox High School', 'Fox High School', '', FALSE),
    ('1006', 'Fox High School', 'Fox High School', 'Gerald O''Connor Stadium', TRUE),
    ('1006', 'Fox Warrior Lacrosse Club', 'Fox High School', 'Gerald O''Connor Stadium', TRUE),
    ('7024', 'Francis Howell Central High School', 'Francis Howell Central High School', 'FHC Stadium', TRUE),
    ('7024', 'Francis Howell Central High School', 'Francis Howell Central High School', 'FHC Stadium 2', FALSE),
    ('7024', 'Francis Howell Central High School', 'Francis Howell Central High School', 'Francis Howell Central High School', FALSE),
    ('7023', 'Francis Howell High School', 'Francis Howell High School', '', FALSE),
    ('7023', 'Francis Howell High School', 'Francis Howell High School', 'FrH Stadium', TRUE),
    ('7023', 'Francis Howell High School', 'Francis Howell High School', 'FrH Stadium 2', FALSE),
    ('7023', 'Francis Howell High School', 'Francis Howell High School', 'Stadium', FALSE),
    ('7025', 'Francis Howell North High School', 'Francis Howell North High School', 'FHN Stadium', TRUE),
    ('7037', 'Hazelwood West High School', 'Hazelwood West High School', 'Lacrosse Field', FALSE),
    ('7037', 'Hazelwood West High School', 'Hazelwood West High School', 'Stadium 1', TRUE),
    ('7040', 'Incarnate Word Academy', 'Incarnate Word Academy', 'Soccer Field', TRUE),
    ('7042', 'John Burroughs High School', 'John Burroughs High School', 'Field Hockey Field', FALSE),
    ('7042', 'John Burroughs High School', 'John Burroughs High School', 'Leland Field - TURF', TRUE),
    ('7044', 'Kirkwood High School', 'Kirkwood High School', 'KHS Upper Soccer Field', FALSE),
    ('7044', 'Kirkwood High School', 'Kirkwood High School', 'KHS- Upper Lacrosse Field', TRUE),
    ('7045', 'Ladue Horton Watkins High School', 'Ladue Horton Watkins High School', 'Ladue West Campus - 10900 Ladue Rd.', FALSE),
    ('7045', 'Ladue Horton Watkins High School', 'Ladue Horton Watkins High School', 'Ladue West Campus - Lower Turf Field - 10900 Ladue Rd', FALSE),
    ('7045', 'Ladue Horton Watkins High School', 'Ladue Horton Watkins High School', 'Stadium - 1201 S. Warson Rd.', TRUE),
    ('7046', 'Lafayette High School', 'Lafayette High School', '', FALSE),
    ('7046', 'Lafayette High School', 'Lafayette High School', 'Lacrosse Field Grass', TRUE),
    ('7046', 'Lafayette High School', 'Lafayette High School', 'Stadium 1', FALSE),
    ('7046', 'Lafayette High School', 'Lafayette High School', 'Stadium Turf', FALSE),
    ('7049', 'Lindbergh High School', 'Lindbergh High School', 'LHS Field 4 (Lighted Stadium)', TRUE),
    ('7049', 'Lindbergh High School', 'Lindbergh High School', 'LHS Field 9 (Track Stadium Field)', FALSE),
    ('7055', 'Marquette High School', 'Marquette High School', 'Practice Lacrosse Field', FALSE),
    ('7055', 'Marquette High School', 'Marquette High School', 'Stadium 1', TRUE),
    ('7055', 'Marquette High School', 'Marquette High School', 'Stadium Field', FALSE),
    ('7062', 'Mary Institute Country Day School', 'Mary Institute Country Day School', '', FALSE),
    ('7062', 'Mary Institute Country Day School', 'Mary Institute Country Day School', 'Ellis Field - Field #9', FALSE),
    ('7062', 'Mary Institute Country Day School', 'Mary Institute Country Day School', 'McCreery Field #3', FALSE),
    ('7062', 'Mary Institute Country Day School', 'Mary Institute Country Day School', 'O''Hara Field #6 - STADIUM/VAR', TRUE),
    ('7062', 'Mary Institute Country Day School', 'Mary Institute Country Day School', 'Taylor Field #2', FALSE),
    ('7065', 'Nerinx Hall High School', 'Nerinx Hall High School', 'Field Hockey Field', TRUE),
    ('7065', 'Nerinx Hall High School', 'Nerinx Hall High School', 'Soccer Field', FALSE),
    ('1010', 'Northwest R-1 High School', 'Northwest High School', 'Stadium', TRUE),
    ('7068', 'Notre Dame High School (Saint Louis)', 'Notre Dame High School (Saint Louis)', 'Soccer Field', TRUE),
    ('7068', 'Notre Dame High School (Saint Louis)', 'Notre Dame High School (Saint Louis)', 'Softball Field', FALSE),
    ('7069', 'Oakville Senior High School', 'Oakville Senior High School', 'Rodger Estes Stadium', TRUE),
    ('7069', 'Oakville Senior High School', 'Oakville Senior High School', 'Turf', FALSE),
    ('7071', 'Parkway Central High School', 'Parkway Central High School', 'Stadium', TRUE),
    ('7071', 'Parkway Central High School', 'Parkway Central High School', 'Stadium - Turf', FALSE),
    ('7071', 'Parkway Central High School', 'Parkway Central High School', 'Upper Field', FALSE),
    ('7072', 'Parkway North High School', 'Parkway North High School', 'Stadium', TRUE),
    ('7072', 'Parkway North High School', 'Parkway North High School', 'Upper Field', FALSE),
    ('7073', 'Parkway South High School', 'Parkway South High School', 'Stadium', TRUE),
    ('7073', 'Parkway South High School', 'Parkway South High School', 'Stadium Field', FALSE),
    ('7074', 'Parkway West High School', 'Parkway West High School', 'Stadium', TRUE),
    ('7075', 'Pattonville High School', 'Pattonville High School', 'Stadium 1', TRUE),
    ('7046', 'Rockwood Summit High School', 'Rockwood Summit High School', 'Field A', FALSE),
    ('7046', 'Rockwood Summit High School', 'Rockwood Summit High School', 'Field B', FALSE),
    ('7046', 'Rockwood Summit High School', 'Rockwood Summit High School', 'Field D', FALSE),
    ('7046', 'Rockwood Summit High School', 'Rockwood Summit High School', 'Soccer Upper Field', FALSE),
    ('7046', 'Rockwood Summit High School', 'Rockwood Summit High School', 'Stadium', TRUE),
    ('7046', 'Rockwood Summit High School - Rockwood Summit', 'Rockwood Summit High School', 'Field A', FALSE),
    ('7046', 'Rockwood Summit High School - Rockwood Summit', 'Rockwood Summit High School', 'Stadium', TRUE),
    ('7088', 'St. Dominic High School', 'St. Dominic High School', 'St Dominic Stadium', TRUE),
    ('7088', 'St. Dominic High School', 'St. Dominic High School', 'Stadium - Turf', FALSE),
    ('9019', 'Ursuline Academy', 'Ursuline Academy', 'FH/LAX/Soc Game Field', TRUE),
    ('9019', 'Ursuline Academy', 'Ursuline Academy', 'Soccer Field', FALSE),
    ('7051', 'Villa Duchesne', 'Villa Duchesne High School', 'Condie Water Turf Stadium - No cleats allowed.', TRUE),
    ('7051', 'Villa Duchesne', 'Villa Duchesne High School', 'Holthause Field and Track-Lower Campus', FALSE),
    ('7104', 'Visitation Academy', 'Visitation Academy', 'Soccer Field', TRUE),
    ('7105', 'Webster Groves High School', 'Webster Groves High School', 'Moss Field', FALSE),
    ('7105', 'Webster Groves High School', 'Webster Groves High School', 'Stadium 1', TRUE),
    ('7106', 'Westminster Christian Academy', 'Westminster Christian Academy', 'Football Field', FALSE),
    ('7106', 'Westminster Christian Academy', 'Westminster Christian Academy', 'Stadium Turf Field', TRUE),
    ('7106', 'Westminster Christian Academy', 'Westminster Christian Academy', 'Turf Stadium Field', FALSE),
    ('7106', 'Westminster Christian Academy', 'Westminster Christian Academy', 'Upper Lacrosse Field', FALSE),
    ('7107', 'Whitfield School', 'Whitfield School', 'Lacrosse', TRUE)
ON CONFLICT (schoolid, sitename, subsitename) DO NOTHING;


-- ----------------------------------------------------------------------------
-- 007_update_schedule_with_default_sites.sql
-- ----------------------------------------------------------------------------
-- Migration: Update schedule table with default SiteName and SubSiteName values
-- This script updates tblschedule rows with default site information from tblschoolsitemapping

UPDATE public.tblschedule s
SET
    sitename = m.sitename,
    subsitename = m.subsitename
FROM public.tblschoolsitemapping m
WHERE
    s.hometeamid = m.schoolid
    AND m.is_default = TRUE
    AND (s.sitename IS NULL OR s.sitename = '')
    AND (s.subsitename IS NULL OR s.subsitename = '');

UPDATE public.tblschedule s
SET
    subsitename = m.subsitename
FROM public.tblschoolsitemapping m
WHERE
    s.hometeamid = m.schoolid
    AND s.sitename = m.sitename
    AND m.is_default = TRUE
    AND (s.subsitename IS NULL OR s.subsitename = '');

UPDATE public.tblschedule s
SET
    subsitename = m.subsitename
FROM public.tblschoolsitemapping m
WHERE
    s.hometeamid = m.schoolid
    AND s.sitename = m.sitename
    AND m.is_default = TRUE
    AND s.subsitename IS NOT NULL
    AND s.subsitename != ''
    AND s.subsitename != m.subsitename;


-- ----------------------------------------------------------------------------
-- 008_create_arbiter_reference_tables.sql
-- ----------------------------------------------------------------------------
-- Arbiter import/export reference tables

CREATE TABLE IF NOT EXISTS arbiter_partners (
    id SERIAL PRIMARY KEY,
    external_id INTEGER UNIQUE,
    name VARCHAR(255) NOT NULL,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_arbiter_partners_external_id ON arbiter_partners(external_id);
CREATE INDEX IF NOT EXISTS idx_arbiter_partners_name ON arbiter_partners(name);

CREATE TABLE IF NOT EXISTS arbiter_sports (
    id SERIAL PRIMARY KEY,
    external_id INTEGER UNIQUE,
    name VARCHAR(255) NOT NULL,
    display_name VARCHAR(255),
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_arbiter_sports_external_id ON arbiter_sports(external_id);
CREATE INDEX IF NOT EXISTS idx_arbiter_sports_name ON arbiter_sports(name);

CREATE TABLE IF NOT EXISTS arbiter_levels (
    id SERIAL PRIMARY KEY,
    external_id INTEGER UNIQUE,
    sport_id INTEGER REFERENCES arbiter_sports(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_arbiter_levels_external_id ON arbiter_levels(external_id);
CREATE INDEX IF NOT EXISTS idx_arbiter_levels_sport_id ON arbiter_levels(sport_id);

CREATE TABLE IF NOT EXISTS arbiter_sites (
    id SERIAL PRIMARY KEY,
    external_id INTEGER UNIQUE,
    name VARCHAR(255) NOT NULL,
    display_name VARCHAR(255),
    is_default_placeholder BOOLEAN DEFAULT FALSE,
    address TEXT,
    coordinates JSONB,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_arbiter_sites_external_id ON arbiter_sites(external_id);
CREATE INDEX IF NOT EXISTS idx_arbiter_sites_is_default ON arbiter_sites(is_default_placeholder);
CREATE INDEX IF NOT EXISTS idx_arbiter_sites_name ON arbiter_sites(name);

CREATE TABLE IF NOT EXISTS arbiter_subsites (
    id SERIAL PRIMARY KEY,
    external_id INTEGER UNIQUE,
    site_id INTEGER REFERENCES arbiter_sites(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    detail VARCHAR(255),
    is_default_placeholder BOOLEAN DEFAULT FALSE,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_arbiter_subsites_external_id ON arbiter_subsites(external_id);
CREATE INDEX IF NOT EXISTS idx_arbiter_subsites_site_id ON arbiter_subsites(site_id);

CREATE TABLE IF NOT EXISTS arbiter_schools (
    id SERIAL PRIMARY KEY,
    schoolid VARCHAR(10) REFERENCES public.tblschool(schoolid) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    short_name VARCHAR(100),
    default_site_id INTEGER REFERENCES arbiter_sites(id) ON DELETE SET NULL,
    default_subsite_id INTEGER REFERENCES arbiter_subsites(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_arbiter_schools_schoolid ON arbiter_schools(schoolid);
CREATE INDEX IF NOT EXISTS idx_arbiter_schools_name ON arbiter_schools(name);
CREATE INDEX IF NOT EXISTS idx_arbiter_schools_default_site ON arbiter_schools(default_site_id);

CREATE TABLE IF NOT EXISTS arbiter_teams (
    id SERIAL PRIMARY KEY,
    external_id INTEGER UNIQUE,
    school_id INTEGER REFERENCES arbiter_schools(id) ON DELETE CASCADE,
    sport_id INTEGER REFERENCES arbiter_sports(id) ON DELETE CASCADE,
    level_id INTEGER REFERENCES arbiter_levels(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    default_site_id INTEGER REFERENCES arbiter_sites(id) ON DELETE SET NULL,
    default_subsite_id INTEGER REFERENCES arbiter_subsites(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(school_id, sport_id, level_id)
);

CREATE INDEX IF NOT EXISTS idx_arbiter_teams_external_id ON arbiter_teams(external_id);
CREATE INDEX IF NOT EXISTS idx_arbiter_teams_school_id ON arbiter_teams(school_id);
CREATE INDEX IF NOT EXISTS idx_arbiter_teams_sport_level ON arbiter_teams(sport_id, level_id);

CREATE TABLE IF NOT EXISTS arbiter_bill_tos (
    id SERIAL PRIMARY KEY,
    external_id INTEGER UNIQUE,
    name VARCHAR(255) NOT NULL,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_arbiter_billtos_external_id ON arbiter_bill_tos(external_id);
CREATE INDEX IF NOT EXISTS idx_arbiter_billtos_name ON arbiter_bill_tos(name);

CREATE TABLE IF NOT EXISTS arbiter_field_mappings (
    id SERIAL PRIMARY KEY,
    field_name VARCHAR(100) NOT NULL UNIQUE,
    source_type VARCHAR(50) NOT NULL,
    source_value TEXT,
    default_value TEXT,
    is_required BOOLEAN DEFAULT FALSE,
    display_order INTEGER DEFAULT 0,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_arbiter_field_mappings_field_name ON arbiter_field_mappings(field_name);
CREATE INDEX IF NOT EXISTS idx_arbiter_field_mappings_display_order ON arbiter_field_mappings(display_order);

CREATE OR REPLACE VIEW v_arbiter_team_home_venues AS
SELECT
    t.id as team_id,
    t.name as team_name,
    sc.name as school_name,
    sp.name as sport_name,
    l.name as level_name,
    COALESCE(t.default_site_id, sc.default_site_id) as effective_site_id,
    COALESCE(t.default_subsite_id, sc.default_subsite_id) as effective_subsite_id,
    s.name as site_name,
    ss.name as subsite_name
FROM arbiter_teams t
JOIN arbiter_schools sc ON t.school_id = sc.id
JOIN arbiter_sports sp ON t.sport_id = sp.id
JOIN arbiter_levels l ON t.level_id = l.id
LEFT JOIN arbiter_sites s ON COALESCE(t.default_site_id, sc.default_site_id) = s.id
LEFT JOIN arbiter_subsites ss ON COALESCE(t.default_subsite_id, sc.default_subsite_id) = ss.id;

CREATE OR REPLACE FUNCTION resolve_arbiter_home_team_defaults(p_team_id INTEGER)
RETURNS TABLE(resolved_site_id INTEGER, resolved_subsite_id INTEGER) AS $$
BEGIN
    RETURN QUERY
    SELECT
        COALESCE(t.default_site_id, sc.default_site_id) as resolved_site_id,
        COALESCE(t.default_subsite_id, sc.default_subsite_id) as resolved_subsite_id
    FROM arbiter_teams t
    JOIN arbiter_schools sc ON t.school_id = sc.id
    WHERE t.id = p_team_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_arbiter_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_arbiter_partners_updated_at BEFORE UPDATE ON arbiter_partners
    FOR EACH ROW EXECUTE FUNCTION update_arbiter_updated_at_column();
CREATE TRIGGER update_arbiter_sports_updated_at BEFORE UPDATE ON arbiter_sports
    FOR EACH ROW EXECUTE FUNCTION update_arbiter_updated_at_column();
CREATE TRIGGER update_arbiter_levels_updated_at BEFORE UPDATE ON arbiter_levels
    FOR EACH ROW EXECUTE FUNCTION update_arbiter_updated_at_column();
CREATE TRIGGER update_arbiter_sites_updated_at BEFORE UPDATE ON arbiter_sites
    FOR EACH ROW EXECUTE FUNCTION update_arbiter_updated_at_column();
CREATE TRIGGER update_arbiter_subsites_updated_at BEFORE UPDATE ON arbiter_subsites
    FOR EACH ROW EXECUTE FUNCTION update_arbiter_updated_at_column();
CREATE TRIGGER update_arbiter_schools_updated_at BEFORE UPDATE ON arbiter_schools
    FOR EACH ROW EXECUTE FUNCTION update_arbiter_updated_at_column();
CREATE TRIGGER update_arbiter_teams_updated_at BEFORE UPDATE ON arbiter_teams
    FOR EACH ROW EXECUTE FUNCTION update_arbiter_updated_at_column();
CREATE TRIGGER update_arbiter_bill_tos_updated_at BEFORE UPDATE ON arbiter_bill_tos
    FOR EACH ROW EXECUTE FUNCTION update_arbiter_updated_at_column();
CREATE TRIGGER update_arbiter_field_mappings_updated_at BEFORE UPDATE ON arbiter_field_mappings
    FOR EACH ROW EXECUTE FUNCTION update_arbiter_updated_at_column();


-- ----------------------------------------------------------------------------
-- 009_cteam_blocked_dates.sql
-- ----------------------------------------------------------------------------
ALTER TABLE public.tblcteamseasonsettings
ADD COLUMN IF NOT EXISTS blocked_dates JSONB DEFAULT '[]';

COMMENT ON COLUMN public.tblcteamseasonsettings.blocked_dates IS 'Array of date strings (YYYY-MM-DD) when no C-Team games should be scheduled, e.g. Easter';


-- ----------------------------------------------------------------------------
-- 010_cteam_schedule_is_manual.sql
-- ----------------------------------------------------------------------------
ALTER TABLE public.tblcteamschedule
ADD COLUMN IF NOT EXISTS is_manual BOOLEAN DEFAULT FALSE;

COMMENT ON COLUMN public.tblcteamschedule.is_manual IS 'True for user-created manual games; these are not deleted when regenerating the schedule';

