-- CreateTable
CREATE TABLE "problems" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "group_id" TEXT NOT NULL,
    "problem_text" TEXT NOT NULL,
    "answer" TEXT NOT NULL,
    "answer_description" TEXT NOT NULL,
    "sequence_in_group" INTEGER NOT NULL,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "problems_group_id_fkey" FOREIGN KEY ("group_id") REFERENCES "problem_groups" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "problem_groups" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "name" TEXT NOT NULL,
    "description" TEXT,
    "sequence" INTEGER NOT NULL DEFAULT 0,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- CreateTable
CREATE TABLE "users" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "email" TEXT,
    "name" TEXT,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- CreateTable
CREATE TABLE "user_problem_states" (
    "user_id" TEXT NOT NULL,
    "problem_id" TEXT NOT NULL,
    "status" TEXT NOT NULL DEFAULT 'new',
    "introduced_at" DATETIME,
    "last_seen_at" DATETIME,
    "last_answered_at" DATETIME,
    "due_at" DATETIME,
    "memory_strength" REAL NOT NULL DEFAULT 0,
    "difficulty" REAL NOT NULL DEFAULT 0.5,
    "confidence" REAL NOT NULL DEFAULT 0,
    "success_count" INTEGER NOT NULL DEFAULT 0,
    "failure_count" INTEGER NOT NULL DEFAULT 0,
    "consecutive_correct" INTEGER NOT NULL DEFAULT 0,
    "consecutive_wrong" INTEGER NOT NULL DEFAULT 0,
    "recent_avg_response_ms" REAL,
    "last_response_ms" REAL,
    "last_result" TEXT,
    "last_grade" TEXT,
    "seen_count_today" INTEGER NOT NULL DEFAULT 0,

    PRIMARY KEY ("user_id", "problem_id"),
    CONSTRAINT "user_problem_states_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "user_problem_states_problem_id_fkey" FOREIGN KEY ("problem_id") REFERENCES "problems" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "user_group_states" (
    "user_id" TEXT NOT NULL,
    "group_id" TEXT NOT NULL,
    "status" TEXT NOT NULL DEFAULT 'locked',
    "unlocked_at" DATETIME,
    "completed_at" DATETIME,
    "introduced_count" INTEGER NOT NULL DEFAULT 0,
    "stable_count" INTEGER NOT NULL DEFAULT 0,
    "mastery_score" REAL NOT NULL DEFAULT 0,

    PRIMARY KEY ("user_id", "group_id"),
    CONSTRAINT "user_group_states_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "user_group_states_group_id_fkey" FOREIGN KEY ("group_id") REFERENCES "problem_groups" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "review_logs" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "user_id" TEXT NOT NULL,
    "problem_id" TEXT NOT NULL,
    "session_id" TEXT NOT NULL,
    "timestamp" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "presented_type" TEXT NOT NULL,
    "user_input" TEXT NOT NULL,
    "was_correct" BOOLEAN NOT NULL,
    "grade" TEXT NOT NULL,
    "response_ms" INTEGER NOT NULL,
    CONSTRAINT "review_logs_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "review_logs_problem_id_fkey" FOREIGN KEY ("problem_id") REFERENCES "problems" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "study_sessions" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "user_id" TEXT NOT NULL,
    "mode" TEXT NOT NULL,
    "started_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "ended_at" DATETIME,
    "planned_size" INTEGER NOT NULL DEFAULT 20,
    "completed_count" INTEGER NOT NULL DEFAULT 0,
    "correct_count" INTEGER NOT NULL DEFAULT 0,
    "expected_accuracy" REAL NOT NULL DEFAULT 0,
    CONSTRAINT "study_sessions_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateIndex
CREATE INDEX "problems_group_id_idx" ON "problems"("group_id");

-- CreateIndex
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");

-- CreateIndex
CREATE INDEX "user_problem_states_user_id_due_at_idx" ON "user_problem_states"("user_id", "due_at");

-- CreateIndex
CREATE INDEX "user_problem_states_user_id_status_idx" ON "user_problem_states"("user_id", "status");

-- CreateIndex
CREATE INDEX "user_group_states_user_id_status_idx" ON "user_group_states"("user_id", "status");

-- CreateIndex
CREATE INDEX "review_logs_user_id_timestamp_idx" ON "review_logs"("user_id", "timestamp");

-- CreateIndex
CREATE INDEX "review_logs_session_id_idx" ON "review_logs"("session_id");

-- CreateIndex
CREATE INDEX "study_sessions_user_id_started_at_idx" ON "study_sessions"("user_id", "started_at");
