254 lines
9.0 KiB
SQL
254 lines
9.0 KiB
SQL
/*
|
|
Warnings:
|
|
|
|
- A unique constraint covering the columns `[match_id,name]` on the table `odd_categories` will be added. If there are existing duplicate values, this will fail.
|
|
- A unique constraint covering the columns `[odd_category_db_id,name]` on the table `odd_selections` will be added. If there are existing duplicate values, this will fail.
|
|
|
|
*/
|
|
-- CreateEnum
|
|
CREATE TYPE "TotoBulletinStatus" AS ENUM ('UPCOMING', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "TotoMatchResult" AS ENUM ('HOME', 'DRAW', 'AWAY');
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "live_matches" ADD COLUMN "lineups" JSONB,
|
|
ADD COLUMN "odds" JSONB,
|
|
ADD COLUMN "odds_updated_at" TIMESTAMP(3),
|
|
ADD COLUMN "referee_name" TEXT,
|
|
ADD COLUMN "sidelined" JSONB;
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "odd_selections" ADD COLUMN "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "match_ai_features" (
|
|
"match_id" TEXT NOT NULL,
|
|
"home_elo" DOUBLE PRECISION NOT NULL DEFAULT 1500.0,
|
|
"away_elo" DOUBLE PRECISION NOT NULL DEFAULT 1500.0,
|
|
"home_form_score" DOUBLE PRECISION NOT NULL DEFAULT 50.0,
|
|
"away_form_score" DOUBLE PRECISION NOT NULL DEFAULT 50.0,
|
|
"missing_players_impact" DOUBLE PRECISION NOT NULL DEFAULT 0.0,
|
|
"calculator_ver" TEXT NOT NULL DEFAULT 'v1.0',
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "match_ai_features_pkey" PRIMARY KEY ("match_id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "team_elo_ratings" (
|
|
"team_id" TEXT NOT NULL,
|
|
"overall_elo" DOUBLE PRECISION NOT NULL DEFAULT 1500.0,
|
|
"home_elo" DOUBLE PRECISION NOT NULL DEFAULT 1500.0,
|
|
"away_elo" DOUBLE PRECISION NOT NULL DEFAULT 1500.0,
|
|
"form_elo" DOUBLE PRECISION NOT NULL DEFAULT 1500.0,
|
|
"matches_played" INTEGER NOT NULL DEFAULT 0,
|
|
"recent_form" TEXT NOT NULL DEFAULT '',
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "team_elo_ratings_pkey" PRIMARY KEY ("team_id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "odds_history" (
|
|
"id" BIGSERIAL NOT NULL,
|
|
"selection_id" INTEGER NOT NULL,
|
|
"match_id" TEXT NOT NULL,
|
|
"previous_value" DOUBLE PRECISION NOT NULL,
|
|
"new_value" DOUBLE PRECISION NOT NULL,
|
|
"bookmaker" TEXT DEFAULT 'MACKOLIK',
|
|
"change_time" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "odds_history_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "user_coupons" (
|
|
"id" TEXT NOT NULL,
|
|
"user_id" TEXT NOT NULL,
|
|
"strategy" TEXT NOT NULL,
|
|
"total_odds" DOUBLE PRECISION NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'PENDING',
|
|
"isPublic" BOOLEAN NOT NULL DEFAULT false,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "user_coupons_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "user_coupon_items" (
|
|
"id" SERIAL NOT NULL,
|
|
"coupon_id" TEXT NOT NULL,
|
|
"match_id" TEXT NOT NULL,
|
|
"selection" TEXT NOT NULL,
|
|
"odd_at_time" DOUBLE PRECISION NOT NULL,
|
|
"is_correct" BOOLEAN,
|
|
|
|
CONSTRAINT "user_coupon_items_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "toto_bulletins" (
|
|
"id" TEXT NOT NULL,
|
|
"game_cycle_no" INTEGER NOT NULL,
|
|
"program_name" TEXT,
|
|
"season" TEXT,
|
|
"status" "TotoBulletinStatus" NOT NULL DEFAULT 'UPCOMING',
|
|
"payin_begin_date" TIMESTAMP(3),
|
|
"payin_end_date" TIMESTAMP(3),
|
|
"pool_total" DOUBLE PRECISION,
|
|
"rollover_amount" DOUBLE PRECISION,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "toto_bulletins_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "toto_bulletin_matches" (
|
|
"id" SERIAL NOT NULL,
|
|
"bulletin_id" TEXT NOT NULL,
|
|
"match_order" INTEGER NOT NULL,
|
|
"home_team_name" TEXT NOT NULL,
|
|
"away_team_name" TEXT NOT NULL,
|
|
"league_name" TEXT,
|
|
"kickoff_time" TIMESTAMP(3),
|
|
"match_id" TEXT,
|
|
"result" "TotoMatchResult",
|
|
"is_cancelled" BOOLEAN NOT NULL DEFAULT false,
|
|
"draw_result" "TotoMatchResult",
|
|
|
|
CONSTRAINT "toto_bulletin_matches_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "toto_results" (
|
|
"id" TEXT NOT NULL,
|
|
"bulletin_id" TEXT NOT NULL,
|
|
"winners_15" INTEGER NOT NULL DEFAULT 0,
|
|
"prize_15" DOUBLE PRECISION,
|
|
"winners_14" INTEGER NOT NULL DEFAULT 0,
|
|
"prize_14" DOUBLE PRECISION,
|
|
"winners_13" INTEGER NOT NULL DEFAULT 0,
|
|
"prize_13" DOUBLE PRECISION,
|
|
"winners_12" INTEGER NOT NULL DEFAULT 0,
|
|
"prize_12" DOUBLE PRECISION,
|
|
"rollover_next" DOUBLE PRECISION,
|
|
"pool_distributed" DOUBLE PRECISION,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "toto_results_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "toto_coupons" (
|
|
"id" TEXT NOT NULL,
|
|
"user_id" TEXT NOT NULL,
|
|
"bulletin_id" TEXT NOT NULL,
|
|
"strategy" TEXT,
|
|
"column_count" INTEGER NOT NULL,
|
|
"total_cost" DOUBLE PRECISION NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'PENDING',
|
|
"total_prize" DOUBLE PRECISION,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "toto_coupons_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "toto_columns" (
|
|
"id" SERIAL NOT NULL,
|
|
"coupon_id" TEXT NOT NULL,
|
|
"predictions" VARCHAR(15) NOT NULL,
|
|
"correct_count" INTEGER,
|
|
"prize_amount" DOUBLE PRECISION,
|
|
|
|
CONSTRAINT "toto_columns_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "odds_history_match_id_change_time_idx" ON "odds_history"("match_id", "change_time");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "odds_history_selection_id_idx" ON "odds_history"("selection_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "user_coupons_user_id_idx" ON "user_coupons"("user_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "user_coupons_status_idx" ON "user_coupons"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "user_coupon_items_coupon_id_idx" ON "user_coupon_items"("coupon_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "toto_bulletins_game_cycle_no_key" ON "toto_bulletins"("game_cycle_no");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "toto_bulletins_status_idx" ON "toto_bulletins"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "toto_bulletin_matches_bulletin_id_idx" ON "toto_bulletin_matches"("bulletin_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "toto_bulletin_matches_match_id_idx" ON "toto_bulletin_matches"("match_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "toto_bulletin_matches_bulletin_id_match_order_key" ON "toto_bulletin_matches"("bulletin_id", "match_order");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "toto_results_bulletin_id_key" ON "toto_results"("bulletin_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "toto_coupons_user_id_idx" ON "toto_coupons"("user_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "toto_coupons_bulletin_id_idx" ON "toto_coupons"("bulletin_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "toto_coupons_status_idx" ON "toto_coupons"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "toto_columns_coupon_id_idx" ON "toto_columns"("coupon_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "toto_columns_correct_count_idx" ON "toto_columns"("correct_count");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "odd_categories_match_id_name_key" ON "odd_categories"("match_id", "name");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "odd_selections_odd_category_db_id_name_key" ON "odd_selections"("odd_category_db_id", "name");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "match_ai_features" ADD CONSTRAINT "match_ai_features_match_id_fkey" FOREIGN KEY ("match_id") REFERENCES "matches"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "team_elo_ratings" ADD CONSTRAINT "team_elo_ratings_team_id_fkey" FOREIGN KEY ("team_id") REFERENCES "teams"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "odds_history" ADD CONSTRAINT "odds_history_selection_id_fkey" FOREIGN KEY ("selection_id") REFERENCES "odd_selections"("db_id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "user_coupons" ADD CONSTRAINT "user_coupons_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "user_coupon_items" ADD CONSTRAINT "user_coupon_items_coupon_id_fkey" FOREIGN KEY ("coupon_id") REFERENCES "user_coupons"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "user_coupon_items" ADD CONSTRAINT "user_coupon_items_match_id_fkey" FOREIGN KEY ("match_id") REFERENCES "matches"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "toto_bulletin_matches" ADD CONSTRAINT "toto_bulletin_matches_bulletin_id_fkey" FOREIGN KEY ("bulletin_id") REFERENCES "toto_bulletins"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "toto_results" ADD CONSTRAINT "toto_results_bulletin_id_fkey" FOREIGN KEY ("bulletin_id") REFERENCES "toto_bulletins"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "toto_coupons" ADD CONSTRAINT "toto_coupons_bulletin_id_fkey" FOREIGN KEY ("bulletin_id") REFERENCES "toto_bulletins"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "toto_columns" ADD CONSTRAINT "toto_columns_coupon_id_fkey" FOREIGN KEY ("coupon_id") REFERENCES "toto_coupons"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|