CREATE TABLE IF NOT EXISTS "arithmetic_sessions" ( "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, "user_id" uuid NOT NULL REFERENCES "users"("id") ON DELETE cascade, "mode" varchar(20) NOT NULL, "category" varchar(40) NOT NULL, "submode" varchar(20), "star" integer, "custom_config" jsonb, "seed" integer NOT NULL, "duration_seconds" integer, "score" integer DEFAULT 0 NOT NULL, "total_attempts" integer DEFAULT 0 NOT NULL, "correct_attempts" integer DEFAULT 0 NOT NULL, "started_at" timestamp with time zone DEFAULT now() NOT NULL, "ended_at" timestamp with time zone, "created_at" timestamp with time zone DEFAULT now() NOT NULL);CREATE TABLE IF NOT EXISTS "arithmetic_attempts" ( "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, "session_id" uuid NOT NULL REFERENCES "arithmetic_sessions"("id") ON DELETE cascade, "generated_prompt" jsonb NOT NULL, "display_expression" text NOT NULL, "canonical_answer" varchar(120) NOT NULL, "user_answer" varchar(120) NOT NULL, "correct" boolean NOT NULL, "elapsed_ms" integer NOT NULL, "score_delta" integer DEFAULT 0 NOT NULL, "category" varchar(40) NOT NULL, "submode" varchar(20), "star" integer, "metadata" jsonb, "created_at" timestamp with time zone DEFAULT now() NOT NULL);CREATE INDEX IF NOT EXISTS "arithmetic_sessions_user_idx" ON "arithmetic_sessions" ("user_id");CREATE INDEX IF NOT EXISTS "arithmetic_sessions_category_idx" ON "arithmetic_sessions" ("category");CREATE INDEX IF NOT EXISTS "arithmetic_sessions_started_idx" ON "arithmetic_sessions" ("started_at");CREATE INDEX IF NOT EXISTS "arithmetic_attempts_session_idx" ON "arithmetic_attempts" ("session_id");CREATE INDEX IF NOT EXISTS "arithmetic_attempts_category_idx" ON "arithmetic_attempts" ("category");CREATE INDEX IF NOT EXISTS "arithmetic_attempts_created_idx" ON "arithmetic_attempts" ("created_at");
CREATE TABLE IF NOT EXISTS "arithmetic_sessions" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL REFERENCES "users"("id") ON DELETE cascade,
"mode" varchar(20) NOT NULL,
"category" varchar(40) NOT NULL,
"submode" varchar(20),
"star" integer,
"custom_config" jsonb,
"seed" integer NOT NULL,
"duration_seconds" integer,
"score" integer DEFAULT 0 NOT NULL,
"total_attempts" integer DEFAULT 0 NOT NULL,
"correct_attempts" integer DEFAULT 0 NOT NULL,
"started_at" timestamp with time zone DEFAULT now() NOT NULL,
"ended_at" timestamp with time zone,
"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE TABLE IF NOT EXISTS "arithmetic_attempts" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"session_id" uuid NOT NULL REFERENCES "arithmetic_sessions"("id") ON DELETE cascade,
"generated_prompt" jsonb NOT NULL,
"display_expression" text NOT NULL,
"canonical_answer" varchar(120) NOT NULL,
"user_answer" varchar(120) NOT NULL,
"correct" boolean NOT NULL,
"elapsed_ms" integer NOT NULL,
"score_delta" integer DEFAULT 0 NOT NULL,
"category" varchar(40) NOT NULL,
"submode" varchar(20),
"star" integer,
"metadata" jsonb,
"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
CREATE INDEX IF NOT EXISTS "arithmetic_sessions_user_idx" ON "arithmetic_sessions" ("user_id");
CREATE INDEX IF NOT EXISTS "arithmetic_sessions_category_idx" ON "arithmetic_sessions" ("category");
CREATE INDEX IF NOT EXISTS "arithmetic_sessions_started_idx" ON "arithmetic_sessions" ("started_at");
CREATE INDEX IF NOT EXISTS "arithmetic_attempts_session_idx" ON "arithmetic_attempts" ("session_id");
CREATE INDEX IF NOT EXISTS "arithmetic_attempts_category_idx" ON "arithmetic_attempts" ("category");
CREATE INDEX IF NOT EXISTS "arithmetic_attempts_created_idx" ON "arithmetic_attempts" ("created_at");