DatabaseCreation: contract_generator.sql

BEGIN;

WITH
db_horizon AS (
    SELECT MAX(match_date) AS latest_match FROM "match"
),

appearances AS (
    SELECT
        l.player_id,
        l.team_id,
        m.season_id,
        m.match_date
    FROM Lineup l
    JOIN "match" m ON m.match_id = l.match_id
    JOIN Team t    ON t.team_id  = l.team_id
    WHERE t.country IS DISTINCT FROM 'International'
),

pts AS (
    SELECT
        player_id, team_id, season_id,
        MIN(match_date) AS first_match,
        MAX(match_date) AS last_match,
        COUNT(*)        AS apps
    FROM appearances
    GROUP BY player_id, team_id, season_id
),

ranked AS (
    SELECT
        pts.*,
        ROW_NUMBER() OVER (
            PARTITION BY player_id, season_id
            ORDER BY apps DESC, last_match DESC
        ) AS rn
    FROM pts
),
primary_team_per_season AS (
    SELECT player_id, team_id, season_id, first_match, last_match
    FROM ranked
    WHERE rn = 1
),

ordered AS (
    SELECT
        p.*,
        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY first_match) AS seq,
        LAG(team_id) OVER (PARTITION BY player_id ORDER BY first_match) AS prev_team
    FROM primary_team_per_season p
),
flagged AS (
    SELECT
        o.*,
        CASE WHEN prev_team IS DISTINCT FROM team_id THEN 1 ELSE 0 END AS new_stint
    FROM ordered o
),
grouped AS (
    SELECT
        f.*,
        SUM(new_stint) OVER (
            PARTITION BY player_id
            ORDER BY seq
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS stint_no
    FROM flagged f
),

stints AS (
    SELECT
        player_id,
        team_id,
        stint_no,
        MIN(first_match)                                 AS stint_first_match,
        MAX(last_match)                                  AS stint_last_match,
        (ARRAY_AGG(season_id ORDER BY first_match))[1]   AS anchor_season_id
    FROM grouped
    GROUP BY player_id, team_id, stint_no
),

stints_with_next AS (
    SELECT
        s.*,
        LEAD(stint_first_match) OVER (
            PARTITION BY player_id ORDER BY stint_no
        ) AS next_stint_start
    FROM stints s
),

windowed AS (
    SELECT
        sn.*,
        (sn.stint_first_match
            - ((14 + floor(random() * 43))::int) * INTERVAL '1 day'
        )::date AS start_date_raw,
        (4 + floor(random() * 6))::int AS target_years
    FROM stints_with_next sn
),

with_dates AS (
    SELECT
        w.player_id,
        w.team_id,
        w.anchor_season_id,
        w.stint_first_match,
        w.stint_last_match,
        w.next_stint_start,
        w.start_date_raw  AS start_date,
        (w.start_date_raw + (w.target_years || ' years')::INTERVAL)::date
            AS desired_end_by_length,
        CASE
            WHEN w.next_stint_start IS NULL THEN NULL
            ELSE (w.next_stint_start
                  - ((14 + floor(random() * 57))::int) * INTERVAL '1 day'
                 )::date
        END AS hard_cap_end,
        (w.stint_last_match
            + ((30 + floor(random() * 151))::int) * INTERVAL '1 day'
        )::date AS soft_min_end
    FROM windowed w
),

final AS (
    SELECT
        d.player_id,
        d.team_id,
        d.anchor_season_id AS season_id,
        d.stint_first_match,
        d.stint_last_match,
        d.start_date,
        CASE
            WHEN d.hard_cap_end IS NOT NULL THEN
                CASE
                    WHEN LEAST(
                            GREATEST(d.desired_end_by_length, d.soft_min_end),
                            d.hard_cap_end
                         ) > d.start_date
                    THEN LEAST(
                            GREATEST(d.desired_end_by_length, d.soft_min_end),
                            d.hard_cap_end
                         )
                    WHEN d.hard_cap_end > d.start_date THEN d.hard_cap_end
                    ELSE (d.stint_last_match + INTERVAL '1 day')::date
                END
            WHEN (SELECT latest_match FROM db_horizon)
                 - d.stint_last_match <= 240 THEN NULL
            ELSE
                CASE
                    WHEN GREATEST(d.desired_end_by_length, d.soft_min_end) > d.start_date
                        THEN GREATEST(d.desired_end_by_length, d.soft_min_end)
                    ELSE (d.stint_last_match + INTERVAL '1 day')::date
                END
        END AS end_date
    FROM with_dates d
)

INSERT INTO Player_contract
    (player_id, team_id, season_id, contract_value, start_date, end_date)
SELECT
    f.player_id,
    f.team_id,
    f.season_id,
    ROUND( (50000 + random() * 24950000)::numeric, 2 ) AS contract_value,
    f.start_date,
    f.end_date
FROM final f
WHERE NOT EXISTS (
    SELECT 1 FROM Player_contract pc
    WHERE pc.player_id = f.player_id
      AND pc.team_id   = f.team_id
      AND pc.season_id = f.season_id
)
AND NOT EXISTS (
    SELECT 1 FROM Player_contract pc2
    WHERE pc2.player_id = f.player_id
      AND pc2.team_id  <> f.team_id
      AND pc2.start_date <= COALESCE(f.end_date, DATE '9999-12-31')
      AND COALESCE(pc2.end_date, DATE '9999-12-31') >= f.start_date
);

COMMIT;