CREATE TABLE "achievements" (
	"key" varchar(48) PRIMARY KEY NOT NULL,
	"name" varchar(80) NOT NULL,
	"description" text NOT NULL,
	"category" varchar(24) NOT NULL,
	"metric" varchar(32) NOT NULL,
	"target" bigint NOT NULL,
	"reward_credits" integer DEFAULT 0 NOT NULL,
	"reward_xp" integer DEFAULT 0 NOT NULL,
	"icon" varchar(32) DEFAULT 'star' NOT NULL,
	"sort_order" integer DEFAULT 0 NOT NULL,
	"enabled" boolean DEFAULT true NOT NULL
);
--> statement-breakpoint
CREATE TABLE "admin_sessions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"admin_id" uuid NOT NULL,
	"token_hash" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"ip" varchar(64)
);
--> statement-breakpoint
CREATE TABLE "admin_users" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"username" varchar(32) NOT NULL,
	"password_hash" text NOT NULL,
	"totp_secret" text NOT NULL,
	"role" varchar(16) DEFAULT 'admin' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"last_login_at" timestamp with time zone,
	"disabled" boolean DEFAULT false NOT NULL
);
--> statement-breakpoint
CREATE TABLE "credit_transactions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid NOT NULL,
	"type" varchar(24) NOT NULL,
	"amount" bigint NOT NULL,
	"balance_after" bigint NOT NULL,
	"reference" text,
	"meta" jsonb,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "daily_rewards" (
	"user_id" uuid PRIMARY KEY NOT NULL,
	"streak_day" integer DEFAULT 0 NOT NULL,
	"last_claimed_at" timestamp with time zone,
	"next_available_at" timestamp with time zone,
	"total_claimed" bigint DEFAULT 0 NOT NULL,
	"claims" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "favorites" (
	"user_id" uuid NOT NULL,
	"game_id" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "favorites_user_id_game_id_pk" PRIMARY KEY("user_id","game_id")
);
--> statement-breakpoint
CREATE TABLE "feature_flags" (
	"key" varchar(64) PRIMARY KEY NOT NULL,
	"enabled" boolean DEFAULT true NOT NULL,
	"description" text,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "game_rounds" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"round_id" varchar(32) NOT NULL,
	"user_id" uuid NOT NULL,
	"game_id" uuid NOT NULL,
	"game_slug" varchar(64) NOT NULL,
	"game_version" varchar(16) NOT NULL,
	"client_round_id" uuid NOT NULL,
	"bet" bigint NOT NULL,
	"win" bigint NOT NULL,
	"multiplier" numeric(12, 4) NOT NULL,
	"balance_after" bigint NOT NULL,
	"result" jsonb NOT NULL,
	"features" text[] DEFAULT '{}'::text[] NOT NULL,
	"free_spins" boolean DEFAULT false NOT NULL,
	"bonus" boolean DEFAULT false NOT NULL,
	"jackpot_tier" varchar(8),
	"rng_reference" text NOT NULL,
	"duration_ms" integer,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "game_states" (
	"user_id" uuid NOT NULL,
	"game_id" uuid NOT NULL,
	"state" jsonb NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "game_states_user_id_game_id_pk" PRIMARY KEY("user_id","game_id")
);
--> statement-breakpoint
CREATE TABLE "game_statistics" (
	"game_id" uuid PRIMARY KEY NOT NULL,
	"launches" bigint DEFAULT 0 NOT NULL,
	"spins" bigint DEFAULT 0 NOT NULL,
	"wagered" bigint DEFAULT 0 NOT NULL,
	"won" bigint DEFAULT 0 NOT NULL,
	"wins" bigint DEFAULT 0 NOT NULL,
	"bonuses" bigint DEFAULT 0 NOT NULL,
	"free_spins" bigint DEFAULT 0 NOT NULL,
	"big_wins" bigint DEFAULT 0 NOT NULL,
	"max_win" bigint DEFAULT 0 NOT NULL,
	"max_multiplier" numeric(12, 2) DEFAULT '0' NOT NULL,
	"favorites" integer DEFAULT 0 NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "game_versions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"game_id" uuid NOT NULL,
	"version" varchar(16) NOT NULL,
	"definition" jsonb NOT NULL,
	"definition_hash" varchar(64) NOT NULL,
	"certification" jsonb,
	"status" varchar(16) DEFAULT 'draft' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "games" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" varchar(64) NOT NULL,
	"name" varchar(80) NOT NULL,
	"version" varchar(16) NOT NULL,
	"lifecycle" varchar(16) DEFAULT 'draft' NOT NULL,
	"sort_order" integer DEFAULT 0 NOT NULL,
	"is_featured" boolean DEFAULT false NOT NULL,
	"is_new" boolean DEFAULT true NOT NULL,
	"summary" jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"published_at" timestamp with time zone,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "leaderboards" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"category" varchar(32) NOT NULL,
	"period_key" varchar(16) NOT NULL,
	"user_id" uuid NOT NULL,
	"value" numeric(18, 2) NOT NULL,
	"game_slug" varchar(64),
	"round_id" varchar(32),
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "missions" (
	"key" varchar(48) PRIMARY KEY NOT NULL,
	"name" varchar(80) NOT NULL,
	"description" text NOT NULL,
	"period" varchar(8) NOT NULL,
	"metric" varchar(32) NOT NULL,
	"target" bigint NOT NULL,
	"reward_credits" integer DEFAULT 0 NOT NULL,
	"reward_xp" integer DEFAULT 0 NOT NULL,
	"enabled" boolean DEFAULT true NOT NULL,
	"sort_order" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "platform_settings" (
	"key" varchar(64) PRIMARY KEY NOT NULL,
	"value" jsonb NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "player_levels" (
	"level" integer PRIMARY KEY NOT NULL,
	"xp_required" bigint NOT NULL,
	"xp_cumulative" bigint NOT NULL,
	"reward_credits" integer NOT NULL,
	"title" varchar(48) NOT NULL
);
--> statement-breakpoint
CREATE TABLE "recovery_codes" (
	"user_id" uuid PRIMARY KEY NOT NULL,
	"code_hash" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"rotated_at" timestamp with time zone,
	"used_at" timestamp with time zone,
	"use_count" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "security_events" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid,
	"admin_id" uuid,
	"type" varchar(48) NOT NULL,
	"severity" varchar(8) DEFAULT 'info' NOT NULL,
	"ip" varchar(64),
	"user_agent" text,
	"meta" jsonb,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "sessions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid NOT NULL,
	"token_hash" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"last_seen_at" timestamp with time zone DEFAULT now() NOT NULL,
	"user_agent" text,
	"ip" varchar(64)
);
--> statement-breakpoint
CREATE TABLE "simulation_runs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"game_slug" varchar(64) NOT NULL,
	"game_version" varchar(16) NOT NULL,
	"spins" bigint NOT NULL,
	"status" varchar(16) DEFAULT 'running' NOT NULL,
	"progress" bigint DEFAULT 0 NOT NULL,
	"result" jsonb,
	"error" text,
	"requested_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"finished_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "user_achievements" (
	"user_id" uuid NOT NULL,
	"achievement_key" varchar(48) NOT NULL,
	"unlocked_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "user_achievements_user_id_achievement_key_pk" PRIMARY KEY("user_id","achievement_key")
);
--> statement-breakpoint
CREATE TABLE "user_game_stats" (
	"user_id" uuid NOT NULL,
	"game_id" uuid NOT NULL,
	"spins" bigint DEFAULT 0 NOT NULL,
	"wagered" bigint DEFAULT 0 NOT NULL,
	"won" bigint DEFAULT 0 NOT NULL,
	"bonuses" integer DEFAULT 0 NOT NULL,
	"biggest_win" bigint DEFAULT 0 NOT NULL,
	"biggest_multiplier" numeric(12, 2) DEFAULT '0' NOT NULL,
	"last_played_at" timestamp with time zone DEFAULT now() NOT NULL,
	"first_played_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "user_game_stats_user_id_game_id_pk" PRIMARY KEY("user_id","game_id")
);
--> statement-breakpoint
CREATE TABLE "user_missions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid NOT NULL,
	"mission_key" varchar(48) NOT NULL,
	"period_key" varchar(16) NOT NULL,
	"progress" bigint DEFAULT 0 NOT NULL,
	"progress_set" text[] DEFAULT '{}'::text[] NOT NULL,
	"completed_at" timestamp with time zone,
	"claimed_at" timestamp with time zone,
	"expires_at" timestamp with time zone NOT NULL
);
--> statement-breakpoint
CREATE TABLE "user_settings" (
	"user_id" uuid PRIMARY KEY NOT NULL,
	"sound_enabled" boolean DEFAULT true NOT NULL,
	"music_volume" numeric(3, 2) DEFAULT '0.6' NOT NULL,
	"effects_volume" numeric(3, 2) DEFAULT '0.8' NOT NULL,
	"master_volume" numeric(3, 2) DEFAULT '0.8' NOT NULL,
	"reduce_motion" boolean DEFAULT false NOT NULL,
	"animation_intensity" varchar(8) DEFAULT 'high' NOT NULL,
	"session_reminder_minutes" integer DEFAULT 60 NOT NULL,
	"break_reminder" boolean DEFAULT true NOT NULL,
	"leaderboard_opt_in" boolean DEFAULT true NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "users" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"username" varchar(24) NOT NULL,
	"username_normalized" varchar(24) NOT NULL,
	"password_hash" text NOT NULL,
	"level" integer DEFAULT 1 NOT NULL,
	"xp" bigint DEFAULT 0 NOT NULL,
	"status" varchar(16) DEFAULT 'active' NOT NULL,
	"age_confirmed_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"last_login_at" timestamp with time zone,
	"last_rescue_at" timestamp with time zone,
	"total_spins" bigint DEFAULT 0 NOT NULL,
	"games_played" integer DEFAULT 0 NOT NULL,
	"biggest_win" bigint DEFAULT 0 NOT NULL,
	"biggest_multiplier" numeric(12, 2) DEFAULT '0' NOT NULL
);
--> statement-breakpoint
CREATE TABLE "wallets" (
	"user_id" uuid PRIMARY KEY NOT NULL,
	"balance" bigint DEFAULT 0 NOT NULL,
	"lifetime_wagered" bigint DEFAULT 0 NOT NULL,
	"lifetime_won" bigint DEFAULT 0 NOT NULL,
	"lifetime_granted" bigint DEFAULT 0 NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
CREATE UNIQUE INDEX "admin_sessions_token_idx" ON "admin_sessions" USING btree ("token_hash");--> statement-breakpoint
CREATE UNIQUE INDEX "admin_users_username_idx" ON "admin_users" USING btree ("username");--> statement-breakpoint
CREATE INDEX "ctx_user_created_idx" ON "credit_transactions" USING btree ("user_id","created_at");--> statement-breakpoint
CREATE INDEX "ctx_type_idx" ON "credit_transactions" USING btree ("type");--> statement-breakpoint
CREATE INDEX "ctx_reference_idx" ON "credit_transactions" USING btree ("reference");--> statement-breakpoint
CREATE UNIQUE INDEX "game_rounds_round_id_idx" ON "game_rounds" USING btree ("round_id");--> statement-breakpoint
CREATE UNIQUE INDEX "game_rounds_user_client_idx" ON "game_rounds" USING btree ("user_id","client_round_id");--> statement-breakpoint
CREATE INDEX "game_rounds_user_created_idx" ON "game_rounds" USING btree ("user_id","created_at");--> statement-breakpoint
CREATE INDEX "game_rounds_game_created_idx" ON "game_rounds" USING btree ("game_id","created_at");--> statement-breakpoint
CREATE INDEX "game_rounds_created_idx" ON "game_rounds" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX "game_rounds_win_idx" ON "game_rounds" USING btree ("win");--> statement-breakpoint
CREATE UNIQUE INDEX "game_versions_game_version_idx" ON "game_versions" USING btree ("game_id","version");--> statement-breakpoint
CREATE UNIQUE INDEX "games_slug_idx" ON "games" USING btree ("slug");--> statement-breakpoint
CREATE UNIQUE INDEX "leaderboards_unique_idx" ON "leaderboards" USING btree ("category","period_key","user_id");--> statement-breakpoint
CREATE INDEX "leaderboards_rank_idx" ON "leaderboards" USING btree ("category","period_key","value");--> statement-breakpoint
CREATE INDEX "security_events_created_idx" ON "security_events" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX "security_events_type_idx" ON "security_events" USING btree ("type");--> statement-breakpoint
CREATE INDEX "security_events_user_idx" ON "security_events" USING btree ("user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "sessions_token_hash_idx" ON "sessions" USING btree ("token_hash");--> statement-breakpoint
CREATE INDEX "sessions_user_idx" ON "sessions" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "simulation_runs_game_idx" ON "simulation_runs" USING btree ("game_slug","created_at");--> statement-breakpoint
CREATE INDEX "ugs_user_last_idx" ON "user_game_stats" USING btree ("user_id","last_played_at");--> statement-breakpoint
CREATE UNIQUE INDEX "user_missions_unique_idx" ON "user_missions" USING btree ("user_id","mission_key","period_key");--> statement-breakpoint
CREATE INDEX "user_missions_user_idx" ON "user_missions" USING btree ("user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "users_username_normalized_idx" ON "users" USING btree ("username_normalized");--> statement-breakpoint
CREATE UNIQUE INDEX "users_username_idx" ON "users" USING btree ("username");