-- Migration 004: Full schema reset
-- Drops legacy flat tables (questions, vocab, user_progress)
-- Creates new hierarchical schema from supabase/create/ files
-- Adapted for Supabase: users(id) → auth.users(id) UUID
-- Tables kept intact: writing_submissions, user_gamification, xu_transactions, weekly_leaderboard

-- ============================================================
-- DROP LEGACY TABLES
-- ============================================================
DROP TABLE IF EXISTS user_progress CASCADE;
DROP TABLE IF EXISTS vocab CASCADE;
DROP TABLE IF EXISTS questions CASCADE;

-- ============================================================
-- TEST STRUCTURE
-- (from create/test.sql + create/update.sql merged)
-- ============================================================

CREATE TABLE test_category (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  slug  VARCHAR(100) UNIQUE
);

CREATE TABLE test (
  id                SERIAL PRIMARY KEY,
  title             VARCHAR(255) NOT NULL,
  description       TEXT,
  total_questions   INT DEFAULT 0,
  duration_minutes  INT DEFAULT 120,
  category_id       INT REFERENCES test_category(id),
  created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE part (
  id              SERIAL PRIMARY KEY,
  test_id         INT NOT NULL REFERENCES test(id) ON DELETE CASCADE,
  part_number     INT NOT NULL,
  title           VARCHAR(100) NOT NULL,
  question_count  INT DEFAULT 0,
  display_order   INT DEFAULT 0,
  UNIQUE (test_id, part_number)
);

CREATE TABLE tag (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE part_tag (
  part_id  INT NOT NULL REFERENCES part(id) ON DELETE CASCADE,
  tag_id   INT NOT NULL REFERENCES tag(id) ON DELETE CASCADE,
  PRIMARY KEY (part_id, tag_id)
);

CREATE TABLE question_group (
  id             SERIAL PRIMARY KEY,
  part_id        INT NOT NULL REFERENCES part(id) ON DELETE CASCADE,
  audio_url      VARCHAR(500),
  image_url      VARCHAR(500),
  passage_text   TEXT,
  display_order  INT DEFAULT 0
);

CREATE TABLE question (
  id               SERIAL PRIMARY KEY,
  group_id         INT NOT NULL REFERENCES question_group(id) ON DELETE CASCADE,
  question_number  INT NOT NULL,
  question_text    TEXT,
  explanation      TEXT,
  display_order    INT DEFAULT 0
);

CREATE TABLE answer_choice (
  id           SERIAL PRIMARY KEY,
  question_id  INT NOT NULL REFERENCES question(id) ON DELETE CASCADE,
  value        CHAR(1) NOT NULL CHECK (value IN ('A', 'B', 'C', 'D')),
  label_text   TEXT,
  is_correct   BOOLEAN NOT NULL DEFAULT FALSE
);

-- ============================================================
-- FLASHCARD SYSTEM
-- (from create/flash_card.sql)
-- adapted: user_id / created_by → auth.users(id) UUID
-- ============================================================

CREATE TABLE flashcard_list (
  id           SERIAL PRIMARY KEY,
  title        VARCHAR(255) NOT NULL,
  description  TEXT,
  total_words  INT DEFAULT 0,
  is_public    BOOLEAN DEFAULT TRUE,
  created_by   UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE flashcard_term (
  id              SERIAL PRIMARY KEY,
  list_id         INT NOT NULL REFERENCES flashcard_list(id) ON DELETE CASCADE,
  word            VARCHAR(255) NOT NULL,
  part_of_speech  VARCHAR(50),
  phonetic        VARCHAR(100),
  definition      TEXT,
  example         TEXT,
  image_url       VARCHAR(500),
  audio_tts_text  VARCHAR(255),
  audio_lang      VARCHAR(10) DEFAULT 'en-US',
  display_order   INT DEFAULT 0,
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_flashcard_list (
  user_id      UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  list_id      INT NOT NULL REFERENCES flashcard_list(id) ON DELETE CASCADE,
  enrolled_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, list_id)
);

CREATE TABLE user_flashcard_progress (
  id                SERIAL PRIMARY KEY,
  user_id           UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  term_id           INT NOT NULL REFERENCES flashcard_term(id) ON DELETE CASCADE,
  list_id           INT NOT NULL REFERENCES flashcard_list(id) ON DELETE CASCADE,
  status            VARCHAR(20) NOT NULL DEFAULT 'new',  -- new | learning | known | ignored
  ease_factor       DECIMAL(4,2) DEFAULT 1.0,            -- 1.0=easy | 0.65=medium | 0.1=hard | -1=known/ignored
  review_count      INT DEFAULT 0,
  last_reviewed_at  TIMESTAMP,
  next_review_at    TIMESTAMP,
  UNIQUE (user_id, term_id, list_id)
);

CREATE TABLE user_flashcard_session (
  id              SERIAL PRIMARY KEY,
  user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  list_id         INT NOT NULL REFERENCES flashcard_list(id) ON DELETE CASCADE,
  started_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ended_at        TIMESTAMP,
  terms_reviewed  INT DEFAULT 0,
  terms_new       INT DEFAULT 0
);

CREATE TABLE user_flashcard_review_log (
  id            SERIAL PRIMARY KEY,
  session_id    INT NOT NULL REFERENCES user_flashcard_session(id) ON DELETE CASCADE,
  term_id       INT NOT NULL REFERENCES flashcard_term(id) ON DELETE CASCADE,
  user_id       UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  action_value  DECIMAL(4,2) NOT NULL,  -- 1 | 0.65 | 0.1 | -1
  reviewed_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_flashcard_settings (
  user_id          UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  list_id          INT NOT NULL REFERENCES flashcard_list(id) ON DELETE CASCADE,
  daily_new_limit  INT DEFAULT 20,
  shuffle          BOOLEAN DEFAULT TRUE,
  front_side       VARCHAR(10) DEFAULT 'word',  -- 'word' | 'definition'
  show_all_terms   BOOLEAN DEFAULT FALSE,
  PRIMARY KEY (user_id, list_id)
);

-- ============================================================
-- USER TEST HISTORY
-- (from create/user_test_history.sql)
-- adapted: user_id → auth.users(id) UUID
-- ============================================================

CREATE TABLE user_test_attempt (
  id                  SERIAL PRIMARY KEY,
  user_id             UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  test_id             INT NOT NULL REFERENCES test(id) ON DELETE CASCADE,
  selected_parts      INT[],
  time_limit_minutes  INT,
  started_at          TIMESTAMP,
  submitted_at        TIMESTAMP,
  time_spent_seconds  INT,
  total_correct       INT DEFAULT 0,
  total_questions     INT DEFAULT 0,
  score               DECIMAL(5,2),
  created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_answer (
  id              SERIAL PRIMARY KEY,
  attempt_id      INT NOT NULL REFERENCES user_test_attempt(id) ON DELETE CASCADE,
  question_id     INT NOT NULL REFERENCES question(id),
  selected_value  CHAR(1),
  is_correct      BOOLEAN,
  UNIQUE (attempt_id, question_id)
);

-- ============================================================
-- INDEXES
-- ============================================================

-- test structure
CREATE INDEX idx_part_test_id        ON part(test_id);
CREATE INDEX idx_qgroup_part_id      ON question_group(part_id);
CREATE INDEX idx_question_group_id   ON question(group_id);
CREATE INDEX idx_answer_question_id  ON answer_choice(question_id);
CREATE INDEX idx_test_category_id    ON test(category_id);

-- flashcard
CREATE INDEX idx_term_list_id           ON flashcard_term(list_id);
CREATE INDEX idx_term_display_order     ON flashcard_term(list_id, display_order);
CREATE INDEX idx_progress_user          ON user_flashcard_progress(user_id);
CREATE INDEX idx_progress_next_review   ON user_flashcard_progress(user_id, next_review_at);
CREATE INDEX idx_progress_status        ON user_flashcard_progress(user_id, list_id, status);
CREATE INDEX idx_review_log_session     ON user_flashcard_review_log(session_id);
CREATE INDEX idx_enrolled_user          ON user_flashcard_list(user_id);

-- test history
CREATE INDEX idx_attempt_user_id   ON user_test_attempt(user_id);
CREATE INDEX idx_attempt_test_id   ON user_test_attempt(test_id);
CREATE INDEX idx_answer_attempt_id ON user_answer(attempt_id);

-- ============================================================
-- ROW LEVEL SECURITY
-- ============================================================

-- test content: public read, no direct user writes
ALTER TABLE test_category   ENABLE ROW LEVEL SECURITY;
ALTER TABLE test             ENABLE ROW LEVEL SECURITY;
ALTER TABLE part             ENABLE ROW LEVEL SECURITY;
ALTER TABLE tag              ENABLE ROW LEVEL SECURITY;
ALTER TABLE part_tag         ENABLE ROW LEVEL SECURITY;
ALTER TABLE question_group   ENABLE ROW LEVEL SECURITY;
ALTER TABLE question         ENABLE ROW LEVEL SECURITY;
ALTER TABLE answer_choice    ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Public read test_category"  ON test_category  FOR SELECT USING (true);
CREATE POLICY "Public read test"           ON test           FOR SELECT USING (true);
CREATE POLICY "Public read part"           ON part           FOR SELECT USING (true);
CREATE POLICY "Public read tag"            ON tag            FOR SELECT USING (true);
CREATE POLICY "Public read part_tag"       ON part_tag       FOR SELECT USING (true);
CREATE POLICY "Public read question_group" ON question_group FOR SELECT USING (true);
CREATE POLICY "Public read question"       ON question       FOR SELECT USING (true);
CREATE POLICY "Public read answer_choice"  ON answer_choice  FOR SELECT USING (true);

-- flashcard lists/terms: public lists readable by all, private by owner only
ALTER TABLE flashcard_list ENABLE ROW LEVEL SECURITY;
ALTER TABLE flashcard_term ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Public read public lists"
  ON flashcard_list FOR SELECT
  USING (is_public = true OR auth.uid() = created_by);

CREATE POLICY "Owners can insert lists"
  ON flashcard_list FOR INSERT
  WITH CHECK (auth.uid() = created_by);

CREATE POLICY "Public read terms of public lists"
  ON flashcard_term FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM flashcard_list fl
      WHERE fl.id = flashcard_term.list_id
        AND (fl.is_public = true OR fl.created_by = auth.uid())
    )
  );

-- flashcard user data: users own their rows
ALTER TABLE user_flashcard_list        ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_flashcard_progress    ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_flashcard_session     ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_flashcard_review_log  ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_flashcard_settings    ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users own flashcard_list enrollment"   ON user_flashcard_list       FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users own flashcard progress"          ON user_flashcard_progress   FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users own flashcard sessions"          ON user_flashcard_session    FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users own review logs"                 ON user_flashcard_review_log FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users own flashcard settings"          ON user_flashcard_settings   FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

-- test attempt history: users own their rows
ALTER TABLE user_test_attempt  ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_answer        ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users own test attempts"
  ON user_test_attempt FOR ALL
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can read own answers"
  ON user_answer FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM user_test_attempt uta
      WHERE uta.id = user_answer.attempt_id
        AND uta.user_id = auth.uid()
    )
  );

CREATE POLICY "Users can insert own answers"
  ON user_answer FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM user_test_attempt uta
      WHERE uta.id = user_answer.attempt_id
        AND uta.user_id = auth.uid()
    )
  );