-- Market templates, line identity, and multilingual labels.

ALTER TABLE "markets" ADD COLUMN IF NOT EXISTS "market_key" VARCHAR(64);
ALTER TABLE "markets" ADD COLUMN IF NOT EXISTS "line_key" VARCHAR(180);
ALTER TABLE "markets" ADD COLUMN IF NOT EXISTS "params_json" JSONB;
ALTER TABLE "markets" ADD COLUMN IF NOT EXISTS "promo_label_i18n" JSONB;
ALTER TABLE "markets" ADD COLUMN IF NOT EXISTS "name_i18n" JSONB;
ALTER TABLE "markets" ADD COLUMN IF NOT EXISTS "template_item_id" BIGINT;

UPDATE "markets"
SET
  "market_key" = COALESCE("market_key", "market_type"),
  "line_key" = COALESCE(
    "line_key",
    "market_type" || ':' || COALESCE(to_char("line_value", 'FM999999990.00'), 'none')
  )
WHERE "market_key" IS NULL OR "line_key" IS NULL;

ALTER TABLE "market_selections" ADD COLUMN IF NOT EXISTS "name_i18n" JSONB;
ALTER TABLE "bet_selections" ADD COLUMN IF NOT EXISTS "market_name_snapshot" VARCHAR(255);

CREATE TABLE IF NOT EXISTS "market_templates" (
  "id" BIGSERIAL PRIMARY KEY,
  "sport_type" VARCHAR(20) NOT NULL DEFAULT 'FOOTBALL',
  "name" VARCHAR(128) NOT NULL,
  "name_i18n" JSONB,
  "description" VARCHAR(500),
  "is_default" BOOLEAN NOT NULL DEFAULT false,
  "status" VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  "sort_order" INTEGER NOT NULL DEFAULT 0,
  "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS "market_template_items" (
  "id" BIGSERIAL PRIMARY KEY,
  "template_id" BIGINT NOT NULL,
  "market_type" VARCHAR(64) NOT NULL,
  "market_key" VARCHAR(64),
  "line_key" VARCHAR(180) NOT NULL,
  "period" VARCHAR(16) NOT NULL,
  "line_value" DECIMAL(8,2),
  "params_json" JSONB,
  "status" VARCHAR(20) NOT NULL DEFAULT 'OPEN',
  "allow_single" BOOLEAN NOT NULL DEFAULT true,
  "allow_parlay" BOOLEAN NOT NULL DEFAULT true,
  "show_on_player" BOOLEAN NOT NULL DEFAULT true,
  "sort_order" INTEGER NOT NULL DEFAULT 0,
  "promo_label" VARCHAR(100),
  "promo_label_i18n" JSONB,
  "name_i18n" JSONB,
  "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "market_template_items_template_id_fkey"
    FOREIGN KEY ("template_id") REFERENCES "market_templates"("id") ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS "market_template_selections" (
  "id" BIGSERIAL PRIMARY KEY,
  "template_item_id" BIGINT NOT NULL,
  "selection_code" VARCHAR(64) NOT NULL,
  "selection_name" VARCHAR(255) NOT NULL,
  "name_i18n" JSONB,
  "odds" DECIMAL(18,6) NOT NULL,
  "status" VARCHAR(20) NOT NULL DEFAULT 'OPEN',
  "sort_order" INTEGER NOT NULL DEFAULT 0,
  "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "market_template_selections_template_item_id_fkey"
    FOREIGN KEY ("template_item_id") REFERENCES "market_template_items"("id") ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX IF NOT EXISTS "markets_match_id_line_key_idx" ON "markets"("match_id", "line_key");
CREATE INDEX IF NOT EXISTS "market_templates_sport_type_status_idx" ON "market_templates"("sport_type", "status");
CREATE INDEX IF NOT EXISTS "market_templates_is_default_idx" ON "market_templates"("is_default");
CREATE INDEX IF NOT EXISTS "market_template_items_template_id_idx" ON "market_template_items"("template_id");
CREATE INDEX IF NOT EXISTS "market_template_items_market_type_idx" ON "market_template_items"("market_type");
CREATE UNIQUE INDEX IF NOT EXISTS "market_template_items_template_id_line_key_key" ON "market_template_items"("template_id", "line_key");
CREATE INDEX IF NOT EXISTS "market_template_selections_template_item_id_idx" ON "market_template_selections"("template_item_id");
CREATE UNIQUE INDEX IF NOT EXISTS "market_template_selections_template_item_id_selection_code_key"
  ON "market_template_selections"("template_item_id", "selection_code");
