-- Migration: Player Transaction Tracking System
-- Created: 2026-03-08
-- Adds PlayerTransaction and PlayerTeamHistory tables

-- Create transaction type enum
DO $$ BEGIN
    CREATE TYPE transaction_type AS ENUM ('trade', 'waiver', 'signing', 'release', 'retire', 'assignment');
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

-- PlayerTransaction: records every transaction event
CREATE TABLE IF NOT EXISTS "PlayerTransaction" (
    id              BIGSERIAL PRIMARY KEY,
    league          TEXT NOT NULL,
    "playerExternalId" TEXT,
    "playerName"    TEXT NOT NULL,
    "canonicalPlayerId" BIGINT REFERENCES "CanonicalPlayer"(id) ON DELETE SET NULL,
    "fromTeam"      TEXT,
    "toTeam"        TEXT,
    "transactionType" transaction_type NOT NULL,
    "transactionDate" TIMESTAMP(3) WITHOUT TIME ZONE NOT NULL,
    source          TEXT NOT NULL DEFAULT 'espn',
    raw             JSONB,
    "createdAt"     TIMESTAMP(3) WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- PlayerTeamHistory: tracks which team a player is on over time
CREATE TABLE IF NOT EXISTS "PlayerTeamHistory" (
    id              BIGSERIAL PRIMARY KEY,
    league          TEXT NOT NULL,
    "playerExternalId" TEXT,
    "playerName"    TEXT NOT NULL,
    "canonicalPlayerId" BIGINT REFERENCES "CanonicalPlayer"(id) ON DELETE SET NULL,
    team            TEXT NOT NULL,
    "startDate"     TIMESTAMP(3) WITHOUT TIME ZONE NOT NULL,
    "endDate"       TIMESTAMP(3) WITHOUT TIME ZONE,  -- NULL = current team
    source          TEXT NOT NULL DEFAULT 'espn',
    "createdAt"     TIMESTAMP(3) WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for PlayerTransaction
CREATE INDEX IF NOT EXISTS "PlayerTransaction_league_playerExternalId_idx"
    ON "PlayerTransaction" (league, "playerExternalId");

CREATE INDEX IF NOT EXISTS "PlayerTransaction_league_transactionDate_idx"
    ON "PlayerTransaction" (league, "transactionDate");

CREATE INDEX IF NOT EXISTS "PlayerTransaction_league_team_idx"
    ON "PlayerTransaction" (league, "toTeam");

CREATE INDEX IF NOT EXISTS "PlayerTransaction_canonicalPlayerId_idx"
    ON "PlayerTransaction" ("canonicalPlayerId");

-- Unique constraint to prevent duplicate transaction ingestion
-- Uses COALESCE(playerExternalId, playerName) so NULLs don't bypass uniqueness
CREATE UNIQUE INDEX IF NOT EXISTS "PlayerTransaction_unique_event_idx"
    ON "PlayerTransaction" (league, COALESCE("playerExternalId", "playerName"), "transactionType", "transactionDate", COALESCE("toTeam", ''), COALESCE("fromTeam", ''));

-- Indexes for PlayerTeamHistory
CREATE INDEX IF NOT EXISTS "PlayerTeamHistory_league_playerExternalId_idx"
    ON "PlayerTeamHistory" (league, "playerExternalId");

CREATE INDEX IF NOT EXISTS "PlayerTeamHistory_league_team_idx"
    ON "PlayerTeamHistory" (league, team);

CREATE INDEX IF NOT EXISTS "PlayerTeamHistory_canonicalPlayerId_idx"
    ON "PlayerTeamHistory" ("canonicalPlayerId");

-- Unique constraint: a player can only have one open (endDate IS NULL) row per league
-- Uses COALESCE(playerExternalId, playerName) so NULLs don't bypass uniqueness
CREATE UNIQUE INDEX IF NOT EXISTS "PlayerTeamHistory_active_unique_idx"
    ON "PlayerTeamHistory" (league, COALESCE("playerExternalId", "playerName"))
    WHERE "endDate" IS NULL;

COMMENT ON TABLE "PlayerTransaction" IS 'Tracks all player transactions (trades, signings, waivers, etc.) from ESPN and other sources';
COMMENT ON TABLE "PlayerTeamHistory" IS 'Tracks which team a player belongs to over time, derived from transactions';
