spb/spinza

1CREATE TABLE "achievements" (2	"key" varchar(48) PRIMARY KEY NOT NULL,3	"name" varchar(80) NOT NULL,4	"description" text NOT NULL,5	"category" varchar(24) NOT NULL,6	"metric" varchar(32) NOT NULL,7	"target" bigint NOT NULL,8	"reward_credits" integer DEFAULT 0 NOT NULL,9	"reward_xp" integer DEFAULT 0 NOT NULL,10	"icon" varchar(32) DEFAULT 'star' NOT NULL,11	"sort_order" integer DEFAULT 0 NOT NULL,12	"enabled" boolean DEFAULT true NOT NULL13);14--> statement-breakpoint15CREATE TABLE "admin_sessions" (16	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,17	"admin_id" uuid NOT NULL,18	"token_hash" text NOT NULL,19	"created_at" timestamp with time zone DEFAULT now() NOT NULL,20	"expires_at" timestamp with time zone NOT NULL,21	"ip" varchar(64)22);23--> statement-breakpoint24CREATE TABLE "admin_users" (25	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,26	"username" varchar(32) NOT NULL,27	"password_hash" text NOT NULL,28	"totp_secret" text NOT NULL,29	"role" varchar(16) DEFAULT 'admin' NOT NULL,30	"created_at" timestamp with time zone DEFAULT now() NOT NULL,31	"last_login_at" timestamp with time zone,32	"disabled" boolean DEFAULT false NOT NULL33);34--> statement-breakpoint35CREATE TABLE "credit_transactions" (36	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,37	"user_id" uuid NOT NULL,38	"type" varchar(24) NOT NULL,39	"amount" bigint NOT NULL,40	"balance_after" bigint NOT NULL,41	"reference" text,42	"meta" jsonb,43	"created_at" timestamp with time zone DEFAULT now() NOT NULL44);45--> statement-breakpoint46CREATE TABLE "daily_rewards" (47	"user_id" uuid PRIMARY KEY NOT NULL,48	"streak_day" integer DEFAULT 0 NOT NULL,49	"last_claimed_at" timestamp with time zone,50	"next_available_at" timestamp with time zone,51	"total_claimed" bigint DEFAULT 0 NOT NULL,52	"claims" integer DEFAULT 0 NOT NULL53);54--> statement-breakpoint55CREATE TABLE "favorites" (56	"user_id" uuid NOT NULL,57	"game_id" uuid NOT NULL,58	"created_at" timestamp with time zone DEFAULT now() NOT NULL,59	CONSTRAINT "favorites_user_id_game_id_pk" PRIMARY KEY("user_id","game_id")60);61--> statement-breakpoint62CREATE TABLE "feature_flags" (63	"key" varchar(64) PRIMARY KEY NOT NULL,64	"enabled" boolean DEFAULT true NOT NULL,65	"description" text,66	"updated_at" timestamp with time zone DEFAULT now() NOT NULL67);68--> statement-breakpoint69CREATE TABLE "game_rounds" (70	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,71	"round_id" varchar(32) NOT NULL,72	"user_id" uuid NOT NULL,73	"game_id" uuid NOT NULL,74	"game_slug" varchar(64) NOT NULL,75	"game_version" varchar(16) NOT NULL,76	"client_round_id" uuid NOT NULL,77	"bet" bigint NOT NULL,78	"win" bigint NOT NULL,79	"multiplier" numeric(12, 4) NOT NULL,80	"balance_after" bigint NOT NULL,81	"result" jsonb NOT NULL,82	"features" text[] DEFAULT '{}'::text[] NOT NULL,83	"free_spins" boolean DEFAULT false NOT NULL,84	"bonus" boolean DEFAULT false NOT NULL,85	"jackpot_tier" varchar(8),86	"rng_reference" text NOT NULL,87	"duration_ms" integer,88	"created_at" timestamp with time zone DEFAULT now() NOT NULL89);90--> statement-breakpoint91CREATE TABLE "game_states" (92	"user_id" uuid NOT NULL,93	"game_id" uuid NOT NULL,94	"state" jsonb NOT NULL,95	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,96	CONSTRAINT "game_states_user_id_game_id_pk" PRIMARY KEY("user_id","game_id")97);98--> statement-breakpoint99CREATE TABLE "game_statistics" (100	"game_id" uuid PRIMARY KEY NOT NULL,101	"launches" bigint DEFAULT 0 NOT NULL,102	"spins" bigint DEFAULT 0 NOT NULL,103	"wagered" bigint DEFAULT 0 NOT NULL,104	"won" bigint DEFAULT 0 NOT NULL,105	"wins" bigint DEFAULT 0 NOT NULL,106	"bonuses" bigint DEFAULT 0 NOT NULL,107	"free_spins" bigint DEFAULT 0 NOT NULL,108	"big_wins" bigint DEFAULT 0 NOT NULL,109	"max_win" bigint DEFAULT 0 NOT NULL,110	"max_multiplier" numeric(12, 2) DEFAULT '0' NOT NULL,111	"favorites" integer DEFAULT 0 NOT NULL,112	"updated_at" timestamp with time zone DEFAULT now() NOT NULL113);114--> statement-breakpoint115CREATE TABLE "game_versions" (116	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,117	"game_id" uuid NOT NULL,118	"version" varchar(16) NOT NULL,119	"definition" jsonb NOT NULL,120	"definition_hash" varchar(64) NOT NULL,121	"certification" jsonb,122	"status" varchar(16) DEFAULT 'draft' NOT NULL,123	"created_at" timestamp with time zone DEFAULT now() NOT NULL124);125--> statement-breakpoint126CREATE TABLE "games" (127	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,128	"slug" varchar(64) NOT NULL,129	"name" varchar(80) NOT NULL,130	"version" varchar(16) NOT NULL,131	"lifecycle" varchar(16) DEFAULT 'draft' NOT NULL,132	"sort_order" integer DEFAULT 0 NOT NULL,133	"is_featured" boolean DEFAULT false NOT NULL,134	"is_new" boolean DEFAULT true NOT NULL,135	"summary" jsonb NOT NULL,136	"created_at" timestamp with time zone DEFAULT now() NOT NULL,137	"published_at" timestamp with time zone,138	"updated_at" timestamp with time zone DEFAULT now() NOT NULL139);140--> statement-breakpoint141CREATE TABLE "leaderboards" (142	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,143	"category" varchar(32) NOT NULL,144	"period_key" varchar(16) NOT NULL,145	"user_id" uuid NOT NULL,146	"value" numeric(18, 2) NOT NULL,147	"game_slug" varchar(64),148	"round_id" varchar(32),149	"updated_at" timestamp with time zone DEFAULT now() NOT NULL150);151--> statement-breakpoint152CREATE TABLE "missions" (153	"key" varchar(48) PRIMARY KEY NOT NULL,154	"name" varchar(80) NOT NULL,155	"description" text NOT NULL,156	"period" varchar(8) NOT NULL,157	"metric" varchar(32) NOT NULL,158	"target" bigint NOT NULL,159	"reward_credits" integer DEFAULT 0 NOT NULL,160	"reward_xp" integer DEFAULT 0 NOT NULL,161	"enabled" boolean DEFAULT true NOT NULL,162	"sort_order" integer DEFAULT 0 NOT NULL163);164--> statement-breakpoint165CREATE TABLE "platform_settings" (166	"key" varchar(64) PRIMARY KEY NOT NULL,167	"value" jsonb NOT NULL,168	"updated_at" timestamp with time zone DEFAULT now() NOT NULL169);170--> statement-breakpoint171CREATE TABLE "player_levels" (172	"level" integer PRIMARY KEY NOT NULL,173	"xp_required" bigint NOT NULL,174	"xp_cumulative" bigint NOT NULL,175	"reward_credits" integer NOT NULL,176	"title" varchar(48) NOT NULL177);178--> statement-breakpoint179CREATE TABLE "recovery_codes" (180	"user_id" uuid PRIMARY KEY NOT NULL,181	"code_hash" text NOT NULL,182	"created_at" timestamp with time zone DEFAULT now() NOT NULL,183	"rotated_at" timestamp with time zone,184	"used_at" timestamp with time zone,185	"use_count" integer DEFAULT 0 NOT NULL186);187--> statement-breakpoint188CREATE TABLE "security_events" (189	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,190	"user_id" uuid,191	"admin_id" uuid,192	"type" varchar(48) NOT NULL,193	"severity" varchar(8) DEFAULT 'info' NOT NULL,194	"ip" varchar(64),195	"user_agent" text,196	"meta" jsonb,197	"created_at" timestamp with time zone DEFAULT now() NOT NULL198);199--> statement-breakpoint200CREATE TABLE "sessions" (201	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,202	"user_id" uuid NOT NULL,203	"token_hash" text NOT NULL,204	"created_at" timestamp with time zone DEFAULT now() NOT NULL,205	"expires_at" timestamp with time zone NOT NULL,206	"last_seen_at" timestamp with time zone DEFAULT now() NOT NULL,207	"user_agent" text,208	"ip" varchar(64)209);210--> statement-breakpoint211CREATE TABLE "simulation_runs" (212	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,213	"game_slug" varchar(64) NOT NULL,214	"game_version" varchar(16) NOT NULL,215	"spins" bigint NOT NULL,216	"status" varchar(16) DEFAULT 'running' NOT NULL,217	"progress" bigint DEFAULT 0 NOT NULL,218	"result" jsonb,219	"error" text,220	"requested_by" uuid,221	"created_at" timestamp with time zone DEFAULT now() NOT NULL,222	"finished_at" timestamp with time zone223);224--> statement-breakpoint225CREATE TABLE "user_achievements" (226	"user_id" uuid NOT NULL,227	"achievement_key" varchar(48) NOT NULL,228	"unlocked_at" timestamp with time zone DEFAULT now() NOT NULL,229	CONSTRAINT "user_achievements_user_id_achievement_key_pk" PRIMARY KEY("user_id","achievement_key")230);231--> statement-breakpoint232CREATE TABLE "user_game_stats" (233	"user_id" uuid NOT NULL,234	"game_id" uuid NOT NULL,235	"spins" bigint DEFAULT 0 NOT NULL,236	"wagered" bigint DEFAULT 0 NOT NULL,237	"won" bigint DEFAULT 0 NOT NULL,238	"bonuses" integer DEFAULT 0 NOT NULL,239	"biggest_win" bigint DEFAULT 0 NOT NULL,240	"biggest_multiplier" numeric(12, 2) DEFAULT '0' NOT NULL,241	"last_played_at" timestamp with time zone DEFAULT now() NOT NULL,242	"first_played_at" timestamp with time zone DEFAULT now() NOT NULL,243	CONSTRAINT "user_game_stats_user_id_game_id_pk" PRIMARY KEY("user_id","game_id")244);245--> statement-breakpoint246CREATE TABLE "user_missions" (247	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,248	"user_id" uuid NOT NULL,249	"mission_key" varchar(48) NOT NULL,250	"period_key" varchar(16) NOT NULL,251	"progress" bigint DEFAULT 0 NOT NULL,252	"progress_set" text[] DEFAULT '{}'::text[] NOT NULL,253	"completed_at" timestamp with time zone,254	"claimed_at" timestamp with time zone,255	"expires_at" timestamp with time zone NOT NULL256);257--> statement-breakpoint258CREATE TABLE "user_settings" (259	"user_id" uuid PRIMARY KEY NOT NULL,260	"sound_enabled" boolean DEFAULT true NOT NULL,261	"music_volume" numeric(3, 2) DEFAULT '0.6' NOT NULL,262	"effects_volume" numeric(3, 2) DEFAULT '0.8' NOT NULL,263	"master_volume" numeric(3, 2) DEFAULT '0.8' NOT NULL,264	"reduce_motion" boolean DEFAULT false NOT NULL,265	"animation_intensity" varchar(8) DEFAULT 'high' NOT NULL,266	"session_reminder_minutes" integer DEFAULT 60 NOT NULL,267	"break_reminder" boolean DEFAULT true NOT NULL,268	"leaderboard_opt_in" boolean DEFAULT true NOT NULL,269	"updated_at" timestamp with time zone DEFAULT now() NOT NULL270);271--> statement-breakpoint272CREATE TABLE "users" (273	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,274	"username" varchar(24) NOT NULL,275	"username_normalized" varchar(24) NOT NULL,276	"password_hash" text NOT NULL,277	"level" integer DEFAULT 1 NOT NULL,278	"xp" bigint DEFAULT 0 NOT NULL,279	"status" varchar(16) DEFAULT 'active' NOT NULL,280	"age_confirmed_at" timestamp with time zone,281	"created_at" timestamp with time zone DEFAULT now() NOT NULL,282	"last_login_at" timestamp with time zone,283	"last_rescue_at" timestamp with time zone,284	"total_spins" bigint DEFAULT 0 NOT NULL,285	"games_played" integer DEFAULT 0 NOT NULL,286	"biggest_win" bigint DEFAULT 0 NOT NULL,287	"biggest_multiplier" numeric(12, 2) DEFAULT '0' NOT NULL288);289--> statement-breakpoint290CREATE TABLE "wallets" (291	"user_id" uuid PRIMARY KEY NOT NULL,292	"balance" bigint DEFAULT 0 NOT NULL,293	"lifetime_wagered" bigint DEFAULT 0 NOT NULL,294	"lifetime_won" bigint DEFAULT 0 NOT NULL,295	"lifetime_granted" bigint DEFAULT 0 NOT NULL,296	"updated_at" timestamp with time zone DEFAULT now() NOT NULL297);298--> statement-breakpoint299ALTER TABLE "admin_sessions" ADD CONSTRAINT "admin_sessions_admin_id_admin_users_id_fk" FOREIGN KEY ("admin_id") REFERENCES "public"."admin_users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint300ALTER TABLE "credit_transactions" ADD CONSTRAINT "credit_transactions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint301ALTER TABLE "daily_rewards" ADD CONSTRAINT "daily_rewards_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint302ALTER TABLE "favorites" ADD CONSTRAINT "favorites_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint303ALTER TABLE "favorites" ADD CONSTRAINT "favorites_game_id_games_id_fk" FOREIGN KEY ("game_id") REFERENCES "public"."games"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint304ALTER TABLE "game_rounds" ADD CONSTRAINT "game_rounds_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint305ALTER TABLE "game_rounds" ADD CONSTRAINT "game_rounds_game_id_games_id_fk" FOREIGN KEY ("game_id") REFERENCES "public"."games"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint306ALTER TABLE "game_states" ADD CONSTRAINT "game_states_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint307ALTER TABLE "game_states" ADD CONSTRAINT "game_states_game_id_games_id_fk" FOREIGN KEY ("game_id") REFERENCES "public"."games"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint308ALTER TABLE "game_statistics" ADD CONSTRAINT "game_statistics_game_id_games_id_fk" FOREIGN KEY ("game_id") REFERENCES "public"."games"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint309ALTER TABLE "game_versions" ADD CONSTRAINT "game_versions_game_id_games_id_fk" FOREIGN KEY ("game_id") REFERENCES "public"."games"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint310ALTER TABLE "leaderboards" ADD CONSTRAINT "leaderboards_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint311ALTER TABLE "recovery_codes" ADD CONSTRAINT "recovery_codes_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint312ALTER TABLE "security_events" ADD CONSTRAINT "security_events_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint313ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint314ALTER TABLE "user_achievements" ADD CONSTRAINT "user_achievements_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint315ALTER TABLE "user_achievements" ADD CONSTRAINT "user_achievements_achievement_key_achievements_key_fk" FOREIGN KEY ("achievement_key") REFERENCES "public"."achievements"("key") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint316ALTER TABLE "user_game_stats" ADD CONSTRAINT "user_game_stats_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint317ALTER TABLE "user_game_stats" ADD CONSTRAINT "user_game_stats_game_id_games_id_fk" FOREIGN KEY ("game_id") REFERENCES "public"."games"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint318ALTER TABLE "user_missions" ADD CONSTRAINT "user_missions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint319ALTER TABLE "user_missions" ADD CONSTRAINT "user_missions_mission_key_missions_key_fk" FOREIGN KEY ("mission_key") REFERENCES "public"."missions"("key") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint320ALTER TABLE "user_settings" ADD CONSTRAINT "user_settings_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint321ALTER TABLE "wallets" ADD CONSTRAINT "wallets_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint322CREATE UNIQUE INDEX "admin_sessions_token_idx" ON "admin_sessions" USING btree ("token_hash");--> statement-breakpoint323CREATE UNIQUE INDEX "admin_users_username_idx" ON "admin_users" USING btree ("username");--> statement-breakpoint324CREATE INDEX "ctx_user_created_idx" ON "credit_transactions" USING btree ("user_id","created_at");--> statement-breakpoint325CREATE INDEX "ctx_type_idx" ON "credit_transactions" USING btree ("type");--> statement-breakpoint326CREATE INDEX "ctx_reference_idx" ON "credit_transactions" USING btree ("reference");--> statement-breakpoint327CREATE UNIQUE INDEX "game_rounds_round_id_idx" ON "game_rounds" USING btree ("round_id");--> statement-breakpoint328CREATE UNIQUE INDEX "game_rounds_user_client_idx" ON "game_rounds" USING btree ("user_id","client_round_id");--> statement-breakpoint329CREATE INDEX "game_rounds_user_created_idx" ON "game_rounds" USING btree ("user_id","created_at");--> statement-breakpoint330CREATE INDEX "game_rounds_game_created_idx" ON "game_rounds" USING btree ("game_id","created_at");--> statement-breakpoint331CREATE INDEX "game_rounds_created_idx" ON "game_rounds" USING btree ("created_at");--> statement-breakpoint332CREATE INDEX "game_rounds_win_idx" ON "game_rounds" USING btree ("win");--> statement-breakpoint333CREATE UNIQUE INDEX "game_versions_game_version_idx" ON "game_versions" USING btree ("game_id","version");--> statement-breakpoint334CREATE UNIQUE INDEX "games_slug_idx" ON "games" USING btree ("slug");--> statement-breakpoint335CREATE UNIQUE INDEX "leaderboards_unique_idx" ON "leaderboards" USING btree ("category","period_key","user_id");--> statement-breakpoint336CREATE INDEX "leaderboards_rank_idx" ON "leaderboards" USING btree ("category","period_key","value");--> statement-breakpoint337CREATE INDEX "security_events_created_idx" ON "security_events" USING btree ("created_at");--> statement-breakpoint338CREATE INDEX "security_events_type_idx" ON "security_events" USING btree ("type");--> statement-breakpoint339CREATE INDEX "security_events_user_idx" ON "security_events" USING btree ("user_id");--> statement-breakpoint340CREATE UNIQUE INDEX "sessions_token_hash_idx" ON "sessions" USING btree ("token_hash");--> statement-breakpoint341CREATE INDEX "sessions_user_idx" ON "sessions" USING btree ("user_id");--> statement-breakpoint342CREATE INDEX "simulation_runs_game_idx" ON "simulation_runs" USING btree ("game_slug","created_at");--> statement-breakpoint343CREATE INDEX "ugs_user_last_idx" ON "user_game_stats" USING btree ("user_id","last_played_at");--> statement-breakpoint344CREATE UNIQUE INDEX "user_missions_unique_idx" ON "user_missions" USING btree ("user_id","mission_key","period_key");--> statement-breakpoint345CREATE INDEX "user_missions_user_idx" ON "user_missions" USING btree ("user_id");--> statement-breakpoint346CREATE UNIQUE INDEX "users_username_normalized_idx" ON "users" USING btree ("username_normalized");--> statement-breakpoint347CREATE UNIQUE INDEX "users_username_idx" ON "users" USING btree ("username");