-- =============================================================================
-- FH Scheduler — Complete Database Schema
-- Single schema: public (no dbo schema)
-- Run once against a fresh Neon database to create all tables + seed reference data.
-- =============================================================================

-- REFERENCE TABLES (no FK dependencies)

CREATE TABLE IF NOT EXISTS tblgender (
    genderid   TEXT PRIMARY KEY,
    gendername TEXT
);

CREATE TABLE IF NOT EXISTS tblsport (
    sportid    TEXT PRIMARY KEY,
    sportname  TEXT,
    seasontype TEXT
);

CREATE TABLE IF NOT EXISTS tblseason (
    seasonid   TEXT PRIMARY KEY,
    seasonname TEXT
);

CREATE TABLE IF NOT EXISTS tblconference (
    conferenceid   TEXT PRIMARY KEY,
    conferencename TEXT
);

CREATE TABLE IF NOT EXISTS tbllevel (
    levelid   SMALLINT PRIMARY KEY,
    levelname TEXT
);

-- =============================================================================
-- SCHOOLS (central table — single public schema, no dbo)
-- =============================================================================

CREATE TABLE IF NOT EXISTS tblschool (
    schoolid                              TEXT PRIMARY KEY,
    schoolname                            TEXT,
    schooladdress                         TEXT,
    schoolcity                            TEXT,
    schoolstate                           TEXT,
    schoolzip                             TEXT,
    schooldistrict                        TEXT,
    schoolphonenumber                     TEXT,
    schoolphonenumberextension            TEXT,
    schoolactivitydirectorname            TEXT,
    schoolactivitydirectoremailaddress    TEXT,
    schoolactivitydirectorcellphonenumber TEXT,
    schoolsite                            TEXT,
    islocal        BOOLEAN DEFAULT TRUE,
    isclub         BOOLEAN DEFAULT FALSE,
    isactive       BOOLEAN DEFAULT TRUE,
    fh_local_teams BOOLEAN DEFAULT FALSE
);

-- =============================================================================
-- TEAM CONFERENCE ASSIGNMENTS
-- =============================================================================

CREATE TABLE IF NOT EXISTS tblschoolsportconference (
    genderid       TEXT NOT NULL,
    seasonid       TEXT NOT NULL,
    sportid        TEXT NOT NULL,
    conferenceid   TEXT NOT NULL,
    schoolid       TEXT NOT NULL,
    schoolname     TEXT,
    sportname      TEXT,
    conferencename TEXT,
    PRIMARY KEY (genderid, seasonid, sportid, conferenceid, schoolid)
);

-- =============================================================================
-- SCHEDULE (composite PK — legacy structure from lacrosse app)
-- =============================================================================

CREATE TABLE IF NOT EXISTS tblschedule (
    genderid              TEXT NOT NULL,
    seasonid              TEXT NOT NULL,
    sportid               TEXT NOT NULL,
    levelname             TEXT NOT NULL,
    team1                 TEXT NOT NULL,
    team2                 TEXT NOT NULL,
    gamedate              TIMESTAMP,
    gametime              TEXT,
    keep                  BOOLEAN DEFAULT FALSE,
    round                 TEXT,
    roundorder            FLOAT,
    calendardateorder     FLOAT,
    team1name             TEXT,
    schoolidteam1         TEXT,
    team2name             TEXT,
    schoolidteam2         TEXT,
    hometeamid            TEXT,
    hometeamname          TEXT,
    hometeamnamenorank    TEXT,
    awayteamid            TEXT,
    awayteamname          TEXT,
    homearbiterschoolname TEXT,
    awayarbiterschoolname TEXT,
    arbitersportname      TEXT,
    sitename              TEXT,
    subsitename           TEXT,
    billto                TEXT,
    scheduleadjustment    BOOLEAN DEFAULT FALSE,
    previoushometeamid    TEXT,
    hasprevioushometeamid BOOLEAN DEFAULT FALSE,
    team1score            INTEGER,
    team2score            INTEGER,
    isfinal               BOOLEAN DEFAULT FALSE,
    ispostseason          BOOLEAN DEFAULT FALSE,
    istournament          BOOLEAN DEFAULT FALSE,
    ssma_timestamp        TEXT NOT NULL DEFAULT NOW()::TEXT,
    PRIMARY KEY (genderid, seasonid, sportid, levelname, team1, team2)
);

-- =============================================================================
-- ASSETS & RESERVATIONS
-- =============================================================================

CREATE TABLE IF NOT EXISTS tblasset (
    assetid     SERIAL PRIMARY KEY,
    schoolid    TEXT REFERENCES tblschool(schoolid),
    schoolname  TEXT NOT NULL,
    sitename    TEXT NOT NULL,
    subsitename TEXT NOT NULL,
    assetname   TEXT NOT NULL,
    assettype   TEXT NOT NULL,
    description TEXT,
    capacity    INTEGER,
    isactive    BOOLEAN DEFAULT TRUE,
    createdat   TIMESTAMP DEFAULT NOW(),
    updatedat   TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblassetreservation (
    reservationid SERIAL PRIMARY KEY,
    assetid       INTEGER NOT NULL REFERENCES tblasset(assetid),
    schoolid      TEXT    NOT NULL REFERENCES tblschool(schoolid),
    eventname     TEXT    NOT NULL,
    eventtype     TEXT,
    eventdate     DATE    NOT NULL,
    starttime     TIME    NOT NULL,
    endtime       TIME    NOT NULL,
    contactname   TEXT,
    contactemail  TEXT,
    contactphone  TEXT,
    notes         TEXT,
    isapproved    BOOLEAN DEFAULT TRUE,
    createdat     TIMESTAMP DEFAULT NOW(),
    updatedat     TIMESTAMP DEFAULT NOW()
);

-- =============================================================================
-- FH CONFLICT FORMS (intake from coaches/ADs)
-- =============================================================================

CREATE TABLE IF NOT EXISTS "2025_fh_info_conflicts" (
    id                    SERIAL PRIMARY KEY,
    school_name           TEXT NOT NULL,
    ad_name               TEXT NOT NULL,
    ad_email              TEXT NOT NULL,
    field_name            TEXT,
    varsity_team          TEXT NOT NULL,
    jv_team               TEXT NOT NULL,
    varsity_start_time    TEXT NOT NULL,
    jv_start_time         TEXT NOT NULL,
    first_game_date       DATE NOT NULL,
    field_conflicts       TEXT,
    school_conflicts      TEXT,
    special_requests      TEXT,
    playoff_info          TEXT,
    verification_required BOOLEAN DEFAULT FALSE,
    createdat             TIMESTAMP DEFAULT NOW(),
    updatedat             TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS "2026_fh_info_conflicts" (
    id                    SERIAL PRIMARY KEY,
    season_id             TEXT NOT NULL DEFAULT '2026',
    school_name           TEXT NOT NULL,
    ad_name               TEXT NOT NULL,
    ad_email              TEXT NOT NULL,
    field_name            TEXT,
    varsity_team          TEXT NOT NULL,
    jv_team               TEXT NOT NULL,
    varsity_start_time    TEXT NOT NULL,
    jv_start_time         TEXT NOT NULL,
    first_game_date       DATE NOT NULL,
    field_conflicts       TEXT,
    school_conflicts      TEXT,
    special_requests      TEXT,
    playoff_info          TEXT,
    verification_required BOOLEAN DEFAULT FALSE,
    createdat             TIMESTAMP DEFAULT NOW(),
    updatedat             TIMESTAMP DEFAULT NOW()
);

-- =============================================================================
-- APP TABLES
-- =============================================================================

CREATE TABLE IF NOT EXISTS tblannouncement (
    id           SERIAL PRIMARY KEY,
    title        TEXT NOT NULL,
    content      TEXT NOT NULL,
    is_important BOOLEAN DEFAULT FALSE,
    is_active    BOOLEAN DEFAULT TRUE,
    created_at   TIMESTAMP DEFAULT NOW(),
    updated_at   TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblrivalry (
    id                SERIAL PRIMARY KEY,
    sport             TEXT,
    team1_school_id   TEXT NOT NULL,
    team1_school_name TEXT NOT NULL,
    team2_school_id   TEXT NOT NULL,
    team2_school_name TEXT NOT NULL,
    notes             TEXT,
    created_at        TIMESTAMP DEFAULT NOW(),
    updated_at        TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblbugreport (
    id            SERIAL PRIMARY KEY,
    page_name     TEXT NOT NULL,
    description   TEXT NOT NULL,
    error_message TEXT,
    browser       TEXT,
    user_email    TEXT,
    status        TEXT DEFAULT 'open',
    priority      TEXT DEFAULT 'medium',
    notes         TEXT,
    created_at    TIMESTAMP DEFAULT NOW(),
    updated_at    TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ref_intake_conflicts (
    id            SERIAL PRIMARY KEY,
    sport_id      TEXT,
    season_id     TEXT,
    school_id     TEXT,
    school_name   TEXT,
    conflict_date DATE,
    conflict_type TEXT,
    description   TEXT
);

CREATE TABLE IF NOT EXISTS ref_import_history (
    batch_id      SERIAL PRIMARY KEY,
    sport_id      TEXT NOT NULL,
    season        TEXT NOT NULL,
    filename      TEXT NOT NULL,
    rows_imported INTEGER DEFAULT 0,
    import_status TEXT DEFAULT 'success',
    error_message TEXT,
    imported_at   TIMESTAMP DEFAULT NOW(),
    imported_by   TEXT
);

CREATE TABLE IF NOT EXISTS tblschedulingrule (
    id                SERIAL PRIMARY KEY,
    rule_type         TEXT NOT NULL,
    category          TEXT NOT NULL,
    title             TEXT NOT NULL,
    description       TEXT NOT NULL,
    priority          INTEGER DEFAULT 50,
    is_active         BOOLEAN DEFAULT TRUE,
    applies_to_sport  TEXT,
    applies_to_season TEXT,
    config_value      TEXT,
    notes             TEXT,
    created_at        TIMESTAMP DEFAULT NOW(),
    updated_at        TIMESTAMP DEFAULT NOW()
);

-- =============================================================================
-- RANKINGS
-- =============================================================================

CREATE TABLE IF NOT EXISTS tblteamrank (
    id                     SERIAL PRIMARY KEY,
    schoolid               TEXT    NOT NULL,
    schoolname             TEXT    NOT NULL,
    sportid                TEXT    NOT NULL,
    genderid               TEXT    NOT NULL,
    seasonid               TEXT    NOT NULL,
    rank                   INTEGER NOT NULL,
    rating                 FLOAT,
    winpercentage          FLOAT,
    pointdifferential      FLOAT,
    strengthofschedule     FLOAT,
    experiencerating       FLOAT,
    gamesplayed            INTEGER,
    record                 TEXT,
    notes                  TEXT,
    createdat              TIMESTAMP DEFAULT NOW(),
    updatedat              TIMESTAMP DEFAULT NOW(),
    agd                    FLOAT,
    wp                     FLOAT,
    owp                    FLOAT,
    oowp                   FLOAT,
    sched                  FLOAT,
    laxnumbersrating       FLOAT,
    experienceadjustment   FLOAT,
    returningplayerscount  INTEGER,
    graduatingplayerscount INTEGER,
    tournamentmodifier     FLOAT,
    finalpreseasonrating   FLOAT,
    basedonseasonsid       TEXT,
    calculationtype        TEXT DEFAULT 'preseason'
);

CREATE TABLE IF NOT EXISTS tblpreseasonconfig (
    id               SERIAL PRIMARY KEY,
    sportid          TEXT    NOT NULL UNIQUE,
    agdweight        INTEGER NOT NULL DEFAULT 20,
    wpweight         INTEGER NOT NULL DEFAULT 20,
    owpweight        INTEGER NOT NULL DEFAULT 20,
    oowpweight       INTEGER NOT NULL DEFAULT 15,
    experienceweight INTEGER NOT NULL DEFAULT 20,
    tournamentweight INTEGER NOT NULL DEFAULT 5,
    schedweight      INTEGER NOT NULL DEFAULT 0,
    coachesblendpct  INTEGER NOT NULL DEFAULT 30,
    notes            TEXT,
    createdat        TIMESTAMP DEFAULT NOW(),
    updatedat        TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblrankingjob (
    id               SERIAL PRIMARY KEY,
    sportid          TEXT NOT NULL,
    genderid         TEXT NOT NULL,
    seasonid         TEXT NOT NULL,
    basedonseasonsid TEXT NOT NULL,
    jobtype          TEXT NOT NULL,
    status           TEXT NOT NULL DEFAULT 'pending',
    totalteams       INTEGER,
    processedteams   INTEGER,
    errormessage     TEXT,
    errordetails     TEXT,
    startedat        TIMESTAMP,
    completedat      TIMESTAMP,
    createdat        TIMESTAMP DEFAULT NOW(),
    agdweight        INTEGER,
    wpweight         INTEGER,
    owpweight        INTEGER,
    oowpweight       INTEGER,
    experienceweight INTEGER,
    tournamentweight INTEGER,
    user_label         TEXT,
    coachesblendpct    INTEGER
);

CREATE TABLE IF NOT EXISTS tblcoachpollrank (
    id         SERIAL PRIMARY KEY,
    schoolid   TEXT    NOT NULL,
    sportid    TEXT    NOT NULL,
    genderid   TEXT    NOT NULL,
    seasonid   TEXT    NOT NULL,
    poll_rank  INTEGER NOT NULL,
    updatedat  TIMESTAMP DEFAULT NOW(),
    CONSTRAINT uq_coachpoll_school_ctx UNIQUE (schoolid, sportid, genderid, seasonid)
);

CREATE INDEX IF NOT EXISTS idx_coachpoll_ctx ON tblcoachpollrank (sportid, genderid, seasonid);

CREATE TABLE IF NOT EXISTS tbltournamentstanding (
    id         SERIAL PRIMARY KEY,
    sportid    TEXT    NOT NULL,
    genderid   TEXT    NOT NULL,
    seasonid   TEXT    NOT NULL,
    schoolid   TEXT    NOT NULL,
    schoolname TEXT    NOT NULL,
    finalrank  INTEGER NOT NULL,
    modifier   FLOAT   DEFAULT 0,
    notes      TEXT,
    createdat  TIMESTAMP DEFAULT NOW(),
    updatedat  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblteamperformancerating (
    id                              SERIAL PRIMARY KEY,
    schoolid                        TEXT NOT NULL,
    sportid                         TEXT NOT NULL,
    genderid                        TEXT NOT NULL,
    seasonid                        TEXT NOT NULL,
    schoolname                      TEXT NOT NULL,
    games_played                    INTEGER DEFAULT 0,
    total_goals_for                 INTEGER DEFAULT 0,
    total_goals_against             INTEGER DEFAULT 0,
    goal_differential               INTEGER DEFAULT 0,
    goals_for_per_game              FLOAT   DEFAULT 0,
    goals_against_per_game          FLOAT   DEFAULT 0,
    average_opponent_wp             FLOAT   DEFAULT 0,
    sos_adjusted_goals_for          FLOAT   DEFAULT 0,
    sos_adjusted_goals_against      FLOAT   DEFAULT 0,
    total_goals                     INTEGER DEFAULT 0,
    total_assists                   INTEGER DEFAULT 0,
    total_points                    INTEGER DEFAULT 0,
    goals_per_game                  FLOAT   DEFAULT 0,
    assists_per_game                FLOAT   DEFAULT 0,
    points_per_game                 FLOAT   DEFAULT 0,
    assist_to_goal_ratio            FLOAT   DEFAULT 0,
    offensive_rating                FLOAT   DEFAULT 0,
    offensive_percentile            FLOAT   DEFAULT 0,
    total_ground_balls              INTEGER DEFAULT 0,
    total_caused_turnovers          INTEGER DEFAULT 0,
    total_draw_controls             INTEGER DEFAULT 0,
    ground_balls_per_game           FLOAT   DEFAULT 0,
    caused_turnovers_per_game       FLOAT   DEFAULT 0,
    draw_controls_per_game          FLOAT   DEFAULT 0,
    defensive_rating                FLOAT   DEFAULT 0,
    defensive_percentile            FLOAT   DEFAULT 0,
    goals_vs_ground_balls_ratio     FLOAT   DEFAULT 0,
    goals_vs_caused_turnovers_ratio FLOAT   DEFAULT 0,
    teamwork_score                  FLOAT   DEFAULT 0,
    offensive_efficiency            FLOAT   DEFAULT 0,
    defensive_efficiency            FLOAT   DEFAULT 0,
    overall_rating                  FLOAT   DEFAULT 0,
    calculation_type                TEXT    DEFAULT 'season',
    createdat                       TIMESTAMP DEFAULT NOW(),
    updatedat                       TIMESTAMP DEFAULT NOW()
);

-- =============================================================================
-- CONFLICT TABLES
-- =============================================================================

CREATE TABLE IF NOT EXISTS tblfieldconflict (
    id           SERIAL PRIMARY KEY,
    schoolid     TEXT NOT NULL,
    sportid      TEXT NOT NULL,
    genderid     TEXT NOT NULL,
    seasonid     TEXT NOT NULL,
    conflictdate DATE NOT NULL,
    reason       TEXT,
    notes        TEXT,
    createdat    TIMESTAMP DEFAULT NOW(),
    updatedat    TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblschoolconflict (
    id           SERIAL PRIMARY KEY,
    schoolid     TEXT NOT NULL,
    sportid      TEXT NOT NULL,
    genderid     TEXT NOT NULL,
    seasonid     TEXT NOT NULL,
    conflictdate DATE NOT NULL,
    reason       TEXT,
    notes        TEXT,
    createdat    TIMESTAMP DEFAULT NOW(),
    updatedat    TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblfirstgamedate (
    id            SERIAL PRIMARY KEY,
    schoolid      TEXT NOT NULL,
    sportid       TEXT NOT NULL,
    genderid      TEXT NOT NULL,
    seasonid      TEXT NOT NULL,
    firstgamedate DATE NOT NULL,
    notes         TEXT,
    createdat     TIMESTAMP DEFAULT NOW(),
    updatedat     TIMESTAMP DEFAULT NOW()
);

-- =============================================================================
-- SCHEDULER WORKING TABLES
-- =============================================================================

CREATE TABLE IF NOT EXISTS tblpossiblegamedate (
    id            SERIAL PRIMARY KEY,
    genderid      TEXT NOT NULL,
    sportid       TEXT NOT NULL,
    seasonid      TEXT NOT NULL,
    calendardate  DATE NOT NULL,
    dayoftheweek  TEXT,
    formatteddate TEXT,
    isavailable   BOOLEAN DEFAULT TRUE,
    notes         TEXT
);

CREATE TABLE IF NOT EXISTS tblscheduledate (
    id            SERIAL PRIMARY KEY,
    genderid      TEXT NOT NULL,
    sportid       TEXT NOT NULL,
    seasonid      TEXT NOT NULL,
    levelname     TEXT NOT NULL,
    team1         TEXT NOT NULL,
    team2         TEXT NOT NULL,
    team1name     TEXT,
    schoolidteam1 TEXT,
    team2name     TEXT,
    schoolidteam2 TEXT,
    matchuptype   TEXT,
    priority      INTEGER DEFAULT 50,
    createdat     TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblschedulebuild (
    id                SERIAL PRIMARY KEY,
    genderid          TEXT NOT NULL,
    sportid           TEXT NOT NULL,
    seasonid          TEXT NOT NULL,
    levelname         TEXT NOT NULL,
    team1             TEXT NOT NULL,
    team2             TEXT NOT NULL,
    gamedate          TIMESTAMP,
    gametime          TEXT,
    team1name         TEXT,
    schoolidteam1     TEXT,
    team2name         TEXT,
    schoolidteam2     TEXT,
    hometeamid        TEXT,
    hometeamname      TEXT,
    awayteamid        TEXT,
    awayteamname      TEXT,
    round             TEXT,
    roundorder        FLOAT,
    calendardateorder FLOAT,
    matchuptype       TEXT,
    notes             TEXT
);

CREATE TABLE IF NOT EXISTS tblmatrix (
    id          SERIAL PRIMARY KEY,
    genderid    TEXT,
    sportid     TEXT,
    seasonid    TEXT,
    team        TEXT,
    opponent    TEXT,
    played      BOOLEAN DEFAULT FALSE,
    scheduled   BOOLEAN DEFAULT FALSE,
    gamedate    DATE,
    ishome      BOOLEAN,
    matchuptype TEXT
);

CREATE TABLE IF NOT EXISTS tblgamelist (
    id       SERIAL PRIMARY KEY,
    genderid TEXT,
    sportid  TEXT,
    seasonid TEXT,
    team1    TEXT,
    team2    TEXT,
    priority INTEGER DEFAULT 50,
    assigned BOOLEAN DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS tblschoolexclusion (
    id        SERIAL PRIMARY KEY,
    schoolid  TEXT NOT NULL,
    sportid   TEXT NOT NULL,
    genderid  TEXT NOT NULL,
    seasonid  TEXT NOT NULL,
    excluded  BOOLEAN DEFAULT TRUE,
    reason    TEXT,
    createdat TIMESTAMP DEFAULT NOW(),
    updatedat TIMESTAMP DEFAULT NOW()
);

-- =============================================================================
-- SCHEDULE VERSIONS
-- =============================================================================

CREATE TABLE IF NOT EXISTS tblscheduleversion (
    version_id   SERIAL PRIMARY KEY,
    version_name TEXT NOT NULL,
    description  TEXT,
    sportid      TEXT NOT NULL,
    genderid     TEXT NOT NULL,
    seasonid     TEXT NOT NULL,
    game_count   INTEGER DEFAULT 0,
    created_at   TIMESTAMP DEFAULT NOW(),
    created_by   TEXT,
    is_locked    BOOLEAN NOT NULL DEFAULT FALSE,
    locked_at    TIMESTAMP,
    locked_by    TEXT
);

CREATE TABLE IF NOT EXISTS tblscheduleversiongames (
    id                SERIAL PRIMARY KEY,
    version_id        INTEGER NOT NULL REFERENCES tblscheduleversion(version_id) ON DELETE CASCADE,
    genderid          TEXT NOT NULL,
    sportid           TEXT NOT NULL,
    seasonid          TEXT NOT NULL,
    levelname         TEXT NOT NULL,
    team1             TEXT NOT NULL,
    team2             TEXT NOT NULL,
    gamedate          TIMESTAMP,
    gametime          TEXT,
    team1name         TEXT,
    schoolidteam1     TEXT,
    team2name         TEXT,
    schoolidteam2     TEXT,
    hometeamid        TEXT,
    hometeamname      TEXT,
    awayteamid        TEXT,
    awayteamname      TEXT,
    round             TEXT,
    roundorder        FLOAT,
    calendardateorder FLOAT,
    matchuptype       TEXT,
    notes             TEXT
);

CREATE TABLE IF NOT EXISTS tblscheduleissue (
    id            SERIAL PRIMARY KEY,
    sportid       TEXT NOT NULL,
    genderid      TEXT NOT NULL,
    seasonid      TEXT NOT NULL,
    issue_type    TEXT NOT NULL,
    schoolid      TEXT,
    description   TEXT NOT NULL,
    priority      INTEGER DEFAULT 50,
    status        TEXT DEFAULT 'open',
    notes         TEXT,
    ai_suggestion TEXT,
    createdat     TIMESTAMP DEFAULT NOW(),
    updatedat     TIMESTAMP DEFAULT NOW(),
    resolvedat    TIMESTAMP
);

-- =============================================================================
-- PLAYER / EXPERIENCE TABLES
-- =============================================================================

CREATE TABLE IF NOT EXISTS tblroster (
    id            SERIAL PRIMARY KEY,
    schoolid      TEXT NOT NULL,
    sportid       TEXT NOT NULL,
    genderid      TEXT NOT NULL,
    seasonid      TEXT NOT NULL,
    playername    TEXT NOT NULL,
    gradyear      TEXT NOT NULL,
    position      TEXT,
    jerseynumber  TEXT,
    isunavailable BOOLEAN DEFAULT FALSE,
    isimpact      BOOLEAN DEFAULT FALSE,
    coachrating   INTEGER,
    notes         TEXT,
    createdat     TIMESTAMP DEFAULT NOW(),
    updatedat     TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblplayerstats (
    id              SERIAL PRIMARY KEY,
    schoolid        TEXT NOT NULL,
    sportid         TEXT NOT NULL,
    genderid        TEXT NOT NULL,
    seasonid        TEXT NOT NULL,
    playername      TEXT NOT NULL,
    gamesplayed     INTEGER DEFAULT 0,
    goals           INTEGER DEFAULT 0,
    assists         INTEGER DEFAULT 0,
    points          INTEGER DEFAULT 0,
    groundballs     INTEGER DEFAULT 0,
    causedturnovers INTEGER DEFAULT 0,
    drawcontrols    INTEGER DEFAULT 0,
    createdat       TIMESTAMP DEFAULT NOW(),
    updatedat       TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblgoaliestats (
    id                  SERIAL PRIMARY KEY,
    schoolid            TEXT NOT NULL,
    sportid             TEXT NOT NULL,
    genderid            TEXT NOT NULL,
    seasonid            TEXT NOT NULL,
    goaliename          TEXT NOT NULL,
    gamesplayed         INTEGER DEFAULT 0,
    minutesplayed       INTEGER DEFAULT 0,
    saves               INTEGER DEFAULT 0,
    goalsallowed        INTEGER DEFAULT 0,
    savepercentage      FLOAT,
    goalsagainstaverage FLOAT,
    createdat           TIMESTAMP DEFAULT NOW(),
    updatedat           TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblawards (
    id                   SERIAL PRIMARY KEY,
    schoolid             TEXT NOT NULL,
    sportid              TEXT NOT NULL,
    genderid             TEXT NOT NULL,
    seasonid             TEXT NOT NULL,
    playername           TEXT NOT NULL,
    awardlevel           TEXT NOT NULL,
    awardname            TEXT,
    awardingorganization TEXT,
    awarddate            DATE,
    notes                TEXT,
    createdat            TIMESTAMP DEFAULT NOW()
);

-- =============================================================================
-- FEEDBACK & SCORE REPORTING
-- =============================================================================

CREATE TABLE IF NOT EXISTS tblcoachfeedback (
    id                      SERIAL PRIMARY KEY,
    school_id               TEXT NOT NULL,
    school_name             TEXT NOT NULL,
    strongest_teams         TEXT,
    weakest_teams           TEXT,
    wish_to_play            TEXT,
    teams_like_yours        TEXT,
    deserve_better_schedule TEXT,
    strongest_players       TEXT,
    players_strengths       TEXT,
    promising_freshman      TEXT,
    injuries_departures     TEXT,
    club_players_count      TEXT,
    multi_sport_athletes    TEXT,
    scheduling_factors      TEXT,
    state_tournament_fair   TEXT,
    below_24_preference     TEXT,
    additional_notes        TEXT,
    created_at              TIMESTAMP DEFAULT NOW(),
    updated_at              TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblscorereport (
    id                     SERIAL PRIMARY KEY,
    sport_id               TEXT NOT NULL DEFAULT '3',
    season_id              TEXT NOT NULL DEFAULT '2026',
    game_date              DATE NOT NULL,
    game_time              TIME NOT NULL,
    home_team_id           TEXT NOT NULL,
    home_team_name         TEXT NOT NULL,
    away_team_id           TEXT NOT NULL,
    away_team_name         TEXT NOT NULL,
    home_score             INTEGER NOT NULL,
    away_score             INTEGER NOT NULL,
    location               TEXT,
    level                  TEXT DEFAULT 'Varsity',
    is_overtime            BOOLEAN DEFAULT FALSE,
    is_shootout            BOOLEAN DEFAULT FALSE,
    attendance             INTEGER,
    submitter_email        TEXT,
    submitter_role         TEXT,
    is_verified            BOOLEAN DEFAULT FALSE,
    verified_by            TEXT,
    verified_at            TIMESTAMP,
    game_notes             TEXT,
    highlight_players      TEXT,
    created_at             TIMESTAMP DEFAULT NOW(),
    updated_at             TIMESTAMP DEFAULT NOW(),
    home_cards             INTEGER DEFAULT 0,
    away_cards             INTEGER DEFAULT 0,
    player_of_game_offense TEXT,
    player_of_game_defense TEXT,
    officials_names        TEXT,
    game_stats             TEXT,
    is_final               BOOLEAN DEFAULT TRUE,
    is_tournament          BOOLEAN DEFAULT FALSE,
    is_playoff             BOOLEAN DEFAULT FALSE,
    reported_by            TEXT
);

-- =============================================================================
-- USER / PROGRESS TRACKING
-- =============================================================================

CREATE TABLE IF NOT EXISTS tbluserprofile (
    profile_id   SERIAL PRIMARY KEY,
    profile_name TEXT NOT NULL,
    created_at   TIMESTAMP DEFAULT NOW(),
    updated_at   TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblschedulingprogresstask (
    task_id          SERIAL PRIMARY KEY,
    milestone        TEXT NOT NULL,
    task_name        TEXT NOT NULL,
    task_description TEXT,
    task_url         TEXT,
    display_order    INTEGER DEFAULT 0,
    created_at       TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblusertaskprogress (
    id              SERIAL PRIMARY KEY,
    user_profile_id INTEGER NOT NULL REFERENCES tbluserprofile(profile_id),
    task_id         INTEGER NOT NULL REFERENCES tblschedulingprogresstask(task_id),
    completed       BOOLEAN DEFAULT FALSE,
    completed_at    TIMESTAMP,
    created_at      TIMESTAMP DEFAULT NOW(),
    updated_at      TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblusernote (
    note_id         SERIAL PRIMARY KEY,
    user_profile_id INTEGER NOT NULL REFERENCES tbluserprofile(profile_id),
    note_text       TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    note_tags       JSONB NOT NULL DEFAULT '[]'::jsonb,
    created_at      TIMESTAMP DEFAULT NOW(),
    updated_at      TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblusernotetask (
    id         SERIAL PRIMARY KEY,
    note_id    INTEGER NOT NULL REFERENCES tblusernote(note_id),
    task_id    INTEGER NOT NULL REFERENCES tblschedulingprogresstask(task_id),
    created_at TIMESTAMP DEFAULT NOW()
);

-- =============================================================================
-- SCHOOL ALIASES & SITE MAPPINGS (FK to tblschool — no schema prefix)
-- =============================================================================

CREATE TABLE IF NOT EXISTS tblschoolalias (
    aliasid              SERIAL PRIMARY KEY,
    schoolid             TEXT NOT NULL REFERENCES tblschool(schoolid),
    alias                TEXT NOT NULL,
    alias_type           TEXT,
    source               TEXT,
    is_preferred_display BOOLEAN DEFAULT FALSE,
    created_at           TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblschoolsitemapping (
    id             SERIAL PRIMARY KEY,
    schoolid       TEXT NOT NULL REFERENCES tblschool(schoolid),
    home_team_name TEXT NOT NULL,
    sitename       TEXT NOT NULL,
    subsitename    TEXT NOT NULL DEFAULT '',
    is_default     BOOLEAN DEFAULT FALSE,
    created_at     TIMESTAMP DEFAULT NOW()
);

-- =============================================================================
-- C-TEAM TABLES (legacy support; not primary for FH varsity scheduling)
-- =============================================================================

CREATE TABLE IF NOT EXISTS tblcteaminfo (
    id                         SERIAL PRIMARY KEY,
    season_id                  TEXT NOT NULL DEFAULT '2026',
    school_name                TEXT NOT NULL,
    school_id                  TEXT,
    has_cteam                  BOOLEAN NOT NULL DEFAULT TRUE,
    cu_count                   TEXT NOT NULL,
    weeknight_availability     TEXT,
    saturday_hosting_conflicts TEXT,
    saturday_team_conflicts    TEXT,
    notes                      TEXT,
    createdat                  TIMESTAMP DEFAULT NOW(),
    updatedat                  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblcteamschedule (
    id               SERIAL PRIMARY KEY,
    season_id        TEXT NOT NULL DEFAULT '2026',
    game_date        DATE NOT NULL,
    game_time        TEXT NOT NULL,
    home_team_id     TEXT,
    home_team_name   TEXT NOT NULL,
    away_team_id     TEXT,
    away_team_name   TEXT NOT NULL,
    site             TEXT,
    subsite          TEXT,
    is_saturday_game BOOLEAN DEFAULT TRUE,
    is_manual        BOOLEAN DEFAULT FALSE,
    host_school_id   TEXT,
    game_slot        INTEGER,
    game_id          TEXT,
    custom_game_id   TEXT,
    partner          TEXT,
    sport            TEXT DEFAULT 'Field Hockey',
    level            TEXT DEFAULT 'C-Team',
    bill_to          TEXT,
    createdat        TIMESTAMP DEFAULT NOW(),
    updatedat        TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS tblcteamseasonsettings (
    id                      SERIAL PRIMARY KEY,
    season_id               TEXT NOT NULL UNIQUE,
    first_varsity_game_date DATE,
    season_start_date       DATE,
    season_end_date         DATE,
    blocked_dates           JSONB,
    createdat               TIMESTAMP DEFAULT NOW(),
    updatedat               TIMESTAMP DEFAULT NOW()
);

-- =============================================================================
-- ARBITER 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 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 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 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 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 TABLE IF NOT EXISTS arbiter_schools (
    id                 SERIAL PRIMARY KEY,
    schoolid           TEXT REFERENCES 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 TABLE IF NOT EXISTS arbiter_teams (
    id                 SERIAL PRIMARY KEY,
    external_id        INTEGER UNIQUE,
    school_id          INTEGER NOT NULL REFERENCES arbiter_schools(id) ON DELETE CASCADE,
    sport_id           INTEGER NOT NULL REFERENCES arbiter_sports(id) ON DELETE CASCADE,
    level_id           INTEGER NOT NULL 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()
);

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 TABLE IF NOT EXISTS arbiter_field_mappings (
    id            SERIAL PRIMARY KEY,
    field_name    VARCHAR(100) UNIQUE NOT NULL,
    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()
);

-- =============================================================================
-- SEED DATA — Field Hockey 2026
-- =============================================================================

INSERT INTO tblgender (genderid, gendername) VALUES ('1','Girls'), ('2','Boys')
ON CONFLICT (genderid) DO NOTHING;

INSERT INTO tblsport (sportid, sportname, seasontype) VALUES ('3','Field Hockey','Fall')
ON CONFLICT (sportid) DO UPDATE SET sportname='Field Hockey', seasontype='Fall';

INSERT INTO tblseason (seasonid, seasonname) VALUES ('2026','2026'), ('2025','2025'), ('2024','2024')
ON CONFLICT (seasonid) DO NOTHING;

INSERT INTO tbllevel (levelid, levelname) VALUES (1,'Varsity'), (2,'JV'), (3,'C-Team')
ON CONFLICT (levelid) DO NOTHING;

INSERT INTO tblconference (conferenceid, conferencename) VALUES
    ('GISL',   'GISL'),
    ('03',     'Metro'),
    ('04',     'Yellow'),
    ('05',     'Red'),
    ('06',     'Green'),
    ('HIDDEN', 'Hidden (Removed Teams)')
ON CONFLICT (conferenceid) DO UPDATE SET conferencename = EXCLUDED.conferencename;

-- Scheduling rules — FH day-of-week game caps per FH_Context.md
INSERT INTO tblschedulingrule
    (rule_type, category, title, description, priority, is_active, applies_to_sport, applies_to_season, config_value)
VALUES
    ('constraint',    'games_per_day',  'FH Max Games Monday',      'Maximum games on Monday',                90, TRUE, '3', '2026', '7'),
    ('constraint',    'games_per_day',  'FH Max Games Tuesday',     'Maximum games on Tuesday',               90, TRUE, '3', '2026', '7'),
    ('constraint',    'games_per_day',  'FH Max Games Wednesday',   'Maximum games on Wednesday',             90, TRUE, '3', '2026', '7'),
    ('constraint',    'games_per_day',  'FH Max Games Thursday',    'Maximum games on Thursday',              90, TRUE, '3', '2026', '6'),
    ('constraint',    'games_per_day',  'FH Max Games Friday',      'Maximum games on Friday',                90, TRUE, '3', '2026', '5'),
    ('configuration', 'games_per_team', 'FH Target Games Per Team', 'Target regular-season games per team',   80, TRUE, '3', '2026', '14')
ON CONFLICT DO NOTHING;

INSERT INTO tblpreseasonconfig
    (sportid, agdweight, wpweight, owpweight, oowpweight, experienceweight, tournamentweight, schedweight)
VALUES ('3', 20, 20, 20, 15, 20, 5, 0)
ON CONFLICT (sportid) DO NOTHING;

-- 30 FH Schools (IDs match LACROSSE_DATABASE_URL for cross-reference)
INSERT INTO tblschool (schoolid, schoolname, islocal, isclub, isactive, fh_local_teams) VALUES
    -- GISL
    ('7018', 'Cor Jesu Academy',                  TRUE, FALSE, TRUE, TRUE),
    ('7065', 'Nerinx Hall High School',            TRUE, FALSE, TRUE, TRUE),
    ('7068', 'Notre Dame High School',             TRUE, FALSE, TRUE, TRUE),
    ('7088', 'St. Dominic High School',            TRUE, FALSE, TRUE, TRUE),
    ('7090', 'St. Josephs Academy',                TRUE, FALSE, TRUE, TRUE),
    ('7099', 'Ursuline Academy',                   TRUE, FALSE, TRUE, TRUE),
    ('7103', 'Villa Duchesne and Oak Hill School', TRUE, FALSE, TRUE, TRUE),
    ('7104', 'Visitation Academy',                 TRUE, FALSE, TRUE, TRUE),
    -- Green
    ('7069', 'Oakville High School',               TRUE, FALSE, TRUE, TRUE),
    ('7071', 'Parkway Central High School',        TRUE, FALSE, TRUE, TRUE),
    ('7075', 'Pattonville High School',            TRUE, FALSE, TRUE, TRUE),
    ('FH001','United Field Hockey',                TRUE, TRUE,  TRUE, TRUE),
    ('7105', 'Webster Groves High School',         TRUE, FALSE, TRUE, TRUE),
    -- Metro
    ('7042', 'John Burroughs School',              TRUE, FALSE, TRUE, TRUE),
    ('7052', 'Lutheran South High School',         TRUE, FALSE, TRUE, TRUE),
    ('7062', 'Mary Institute Country Day School',  TRUE, FALSE, TRUE, TRUE),
    ('7076', 'Principia High School',              TRUE, FALSE, TRUE, TRUE),
    ('7106', 'Westminster Christian Academy',      TRUE, FALSE, TRUE, TRUE),
    ('7107', 'Whitfield School',                   TRUE, FALSE, TRUE, TRUE),
    -- Red
    ('9009', 'Edwardsville High School',           TRUE, FALSE, TRUE, TRUE),
    ('7022', 'Eureka High School',                 TRUE, FALSE, TRUE, TRUE),
    ('7049', 'Lindbergh High School',              TRUE, FALSE, TRUE, TRUE),
    ('7073', 'Parkway South High School',          TRUE, FALSE, TRUE, TRUE),
    ('7074', 'Parkway West High School',           TRUE, FALSE, TRUE, TRUE),
    -- Yellow
    ('7044', 'Kirkwood High School',               TRUE, FALSE, TRUE, TRUE),
    ('7045', 'Ladue Horton Watkins High School',   TRUE, FALSE, TRUE, TRUE),
    ('7046', 'Lafayette High School',              TRUE, FALSE, TRUE, TRUE),
    ('7055', 'Marquette High School',              TRUE, FALSE, TRUE, TRUE),
    ('7080', 'Rockwood Summit High School',        TRUE, FALSE, TRUE, TRUE),
    -- No conference
    ('8999', 'Pembroke Hill School',               TRUE, FALSE, TRUE, TRUE)
ON CONFLICT (schoolid) DO UPDATE SET fh_local_teams = TRUE, isactive = TRUE;

-- Display aliases
INSERT INTO tblschoolalias (schoolid, alias, alias_type, source, is_preferred_display) VALUES
    ('7018', 'Cor Jesu',           'display', 'fh_reference', TRUE),
    ('7065', 'Nerinx Hall',        'display', 'fh_reference', TRUE),
    ('7068', 'Notre Dame',         'display', 'fh_reference', TRUE),
    ('7088', 'St. Dominic',        'display', 'fh_reference', TRUE),
    ('7090', 'St. Joseph''s',      'display', 'fh_reference', TRUE),
    ('7099', 'Ursuline',           'display', 'fh_reference', TRUE),
    ('7103', 'Villa Duchesne',     'display', 'fh_reference', TRUE),
    ('7104', 'Visitation',         'display', 'fh_reference', TRUE),
    ('7069', 'Oakville',           'display', 'fh_reference', TRUE),
    ('7071', 'Parkway Central',    'display', 'fh_reference', TRUE),
    ('7075', 'Pattonville',        'display', 'fh_reference', TRUE),
    ('FH001','United Field Hockey','display', 'fh_reference', TRUE),
    ('7105', 'Webster Groves',     'display', 'fh_reference', TRUE),
    ('7042', 'John Burroughs',     'display', 'fh_reference', TRUE),
    ('7052', 'Lutheran South',     'display', 'fh_reference', TRUE),
    ('7062', 'MICDS',              'display', 'fh_reference', TRUE),
    ('7076', 'Principia Panthers', 'display', 'fh_reference', TRUE),
    ('7106', 'Westminster',        'display', 'fh_reference', TRUE),
    ('7107', 'Whitfield',          'display', 'fh_reference', TRUE),
    ('9009', 'Edwardsville',       'display', 'fh_reference', TRUE),
    ('7022', 'Eureka',             'display', 'fh_reference', TRUE),
    ('7049', 'Lindbergh',          'display', 'fh_reference', TRUE),
    ('7073', 'Parkway South',      'display', 'fh_reference', TRUE),
    ('7074', 'Parkway West',       'display', 'fh_reference', TRUE),
    ('7044', 'Kirkwood',           'display', 'fh_reference', TRUE),
    ('7045', 'Ladue',              'display', 'fh_reference', TRUE),
    ('7046', 'Lafayette',          'display', 'fh_reference', TRUE),
    ('7055', 'Marquette',          'display', 'fh_reference', TRUE),
    ('7080', 'Summit',             'display', 'fh_reference', TRUE),
    ('8999', 'Pembroke Hill',      'display', 'fh_reference', TRUE)
ON CONFLICT DO NOTHING;

-- Conference assignments
INSERT INTO tblschoolsportconference
    (genderid, seasonid, sportid, conferenceid, schoolid, schoolname, sportname, conferencename)
VALUES
    ('1','2026','3','GISL','7018','Cor Jesu Chargers',         'Field Hockey','GISL'),
    ('1','2026','3','GISL','7065','Nerinx Hall Markers',       'Field Hockey','GISL'),
    ('1','2026','3','GISL','7068','Notre Dame Rebels',         'Field Hockey','GISL'),
    ('1','2026','3','GISL','7088','St. Dominic Crusaders',     'Field Hockey','GISL'),
    ('1','2026','3','GISL','7090','St. Joseph''s Angels',      'Field Hockey','GISL'),
    ('1','2026','3','GISL','7099','Ursuline Bears',            'Field Hockey','GISL'),
    ('1','2026','3','GISL','7103','Villa Duchesne Saints',     'Field Hockey','GISL'),
    ('1','2026','3','GISL','7104','Visitation Vivettes',       'Field Hockey','GISL'),
    ('1','2026','3','06',  '7069','Oakville Tigers',           'Field Hockey','Green'),
    ('1','2026','3','06',  '7071','Parkway Central Colts',     'Field Hockey','Green'),
    ('1','2026','3','06',  '7075','Pattonville Pirates',       'Field Hockey','Green'),
    ('1','2026','3','06',  'FH001','United Field Hockey',      'Field Hockey','Green'),
    ('1','2026','3','06',  '7105','Webster Groves Statesmen',  'Field Hockey','Green'),
    ('1','2026','3','03',  '7042','John Burroughs Bombers',    'Field Hockey','Metro'),
    ('1','2026','3','03',  '7052','Lutheran South Lancers',    'Field Hockey','Metro'),
    ('1','2026','3','03',  '7062','MICDS Rams',                'Field Hockey','Metro'),
    ('1','2026','3','03',  '7076','Principia Panthers',        'Field Hockey','Metro'),
    ('1','2026','3','03',  '7106','Westminster Wildcats',      'Field Hockey','Metro'),
    ('1','2026','3','03',  '7107','Whitfield Warriors',        'Field Hockey','Metro'),
    ('1','2026','3','05',  '9009','Edwardsville Tigers',       'Field Hockey','Red'),
    ('1','2026','3','05',  '7022','Eureka Wildcats',           'Field Hockey','Red'),
    ('1','2026','3','05',  '7049','Lindbergh Flyers',          'Field Hockey','Red'),
    ('1','2026','3','05',  '7073','Parkway South Patriots',    'Field Hockey','Red'),
    ('1','2026','3','05',  '7074','Parkway West Longhorns',    'Field Hockey','Red'),
    ('1','2026','3','04',  '7044','Kirkwood Pioneers',         'Field Hockey','Yellow'),
    ('1','2026','3','04',  '7045','Ladue Rams',                'Field Hockey','Yellow'),
    ('1','2026','3','04',  '7046','Lafayette Lancers',         'Field Hockey','Yellow'),
    ('1','2026','3','04',  '7055','Marquette Mustangs',        'Field Hockey','Yellow'),
    ('1','2026','3','04',  '7080','Summit Falcons',            'Field Hockey','Yellow'),
    ('1','2026','3','HIDDEN','8999','Pembroke Hill',           'Field Hockey','Hidden (Removed Teams)')
ON CONFLICT (genderid, seasonid, sportid, conferenceid, schoolid) DO UPDATE SET
    schoolname     = EXCLUDED.schoolname,
    conferencename = EXCLUDED.conferencename;
