CREATE TYPE "public"."match_status" AS ENUM('scheduled', 'in_progress', 'complete', 'canceled', 'postponed');--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "match_sub_games" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"season_match_id" uuid NOT NULL,
	"game_number" integer NOT NULL,
	"game_label" varchar(100),
	"participant1_score" integer,
	"participant2_score" integer,
	"winner_id" uuid,
	"status" "match_status" DEFAULT 'scheduled' NOT NULL,
	"started_at" timestamp,
	"completed_at" timestamp,
	"external_game_id" varchar(255),
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "season_matches" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"sports_season_id" uuid NOT NULL,
	"scoring_event_id" uuid,
	"participant1_id" uuid,
	"participant2_id" uuid,
	"winner_id" uuid,
	"participant1_score" integer,
	"participant2_score" integer,
	"match_stage" integer,
	"match_round" integer,
	"matchday" integer,
	"is_series" boolean DEFAULT false NOT NULL,
	"status" "match_status" DEFAULT 'scheduled' NOT NULL,
	"scheduled_at" timestamp,
	"started_at" timestamp,
	"completed_at" timestamp,
	"external_match_id" varchar(255),
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "sports_seasons" ADD COLUMN "external_season_id" varchar(255);--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "match_sub_games" ADD CONSTRAINT "match_sub_games_season_match_id_season_matches_id_fk" FOREIGN KEY ("season_match_id") REFERENCES "public"."season_matches"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "match_sub_games" ADD CONSTRAINT "match_sub_games_winner_id_season_participants_id_fk" FOREIGN KEY ("winner_id") REFERENCES "public"."season_participants"("id") ON DELETE set null ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "season_matches" ADD CONSTRAINT "season_matches_sports_season_id_sports_seasons_id_fk" FOREIGN KEY ("sports_season_id") REFERENCES "public"."sports_seasons"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "season_matches" ADD CONSTRAINT "season_matches_scoring_event_id_scoring_events_id_fk" FOREIGN KEY ("scoring_event_id") REFERENCES "public"."scoring_events"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "season_matches" ADD CONSTRAINT "season_matches_participant1_id_season_participants_id_fk" FOREIGN KEY ("participant1_id") REFERENCES "public"."season_participants"("id") ON DELETE set null ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "season_matches" ADD CONSTRAINT "season_matches_participant2_id_season_participants_id_fk" FOREIGN KEY ("participant2_id") REFERENCES "public"."season_participants"("id") ON DELETE set null ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "season_matches" ADD CONSTRAINT "season_matches_winner_id_season_participants_id_fk" FOREIGN KEY ("winner_id") REFERENCES "public"."season_participants"("id") ON DELETE set null ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "match_sub_games_match_game_number_unique" ON "match_sub_games" USING btree ("season_match_id","game_number");--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "season_matches_external_id_unique" ON "season_matches" USING btree ("external_match_id") WHERE "season_matches"."external_match_id" IS NOT NULL;--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "season_matches_sports_season_status_idx" ON "season_matches" USING btree ("sports_season_id","status");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "season_matches_sports_season_stage_round_idx" ON "season_matches" USING btree ("sports_season_id","match_stage","match_round");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "season_matches_scoring_event_idx" ON "season_matches" USING btree ("scoring_event_id");