-- DET Advent — MySQL / MariaDB schema (utf8mb4)
-- Applied automatically on server start (CREATE TABLE IF NOT EXISTS), or run: npm run db:init

CREATE TABLE IF NOT EXISTS honors (
  id                    VARCHAR(100)  NOT NULL,
  code                  VARCHAR(40)   NOT NULL,
  name                  VARCHAR(200)  NOT NULL,
  category              VARCHAR(60)   NOT NULL,
  level                 TINYINT       NOT NULL DEFAULT 1,
  icon_name             VARCHAR(60)   NOT NULL DEFAULT 'Sparkles',
  badge_color           VARCHAR(20)   NOT NULL DEFAULT '#B45309',
  description           TEXT          NULL,
  gc_url                VARCHAR(500)  NULL,
  gc_website_url        VARCHAR(500)  NULL,
  passing_score         TINYINT       NOT NULL DEFAULT 75,
  time_limit_minutes    SMALLINT      NOT NULL DEFAULT 20,
  requirements_summary  JSON          NULL,
  requirements          JSON          NULL,
  is_custom             TINYINT(1)    NOT NULL DEFAULT 0,
  created_by            VARCHAR(100)  NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_honors_code (code),
  KEY idx_honors_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS questions (
  id                     VARCHAR(120) NOT NULL,
  honor_id               VARCHAR(100) NOT NULL,
  position               SMALLINT     NOT NULL DEFAULT 0,
  type                   ENUM('multiple_choice','one_word','explanatory') NOT NULL,
  question               TEXT         NOT NULL,
  options                JSON         NULL,   -- 4 options (multiple_choice)
  correct_answer_index   TINYINT      NULL,   -- 0..3 (multiple_choice)
  acceptable_answers     JSON         NULL,   -- (one_word)
  sample_answer          VARCHAR(120) NULL,   -- (one_word)
  rubric_points          JSON         NULL,   -- (explanatory)
  model_answer           TEXT         NULL,   -- (explanatory)
  explanation            TEXT         NULL,
  reference_requirement  VARCHAR(255) NULL,
  PRIMARY KEY (id),
  KEY idx_questions_honor (honor_id, type, position),
  CONSTRAINT fk_questions_honor FOREIGN KEY (honor_id) REFERENCES honors (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS clubs (
  id    VARCHAR(100) NOT NULL,
  name  VARCHAR(200) NOT NULL,
  data  JSON         NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id       VARCHAR(100) NOT NULL,
  email    VARCHAR(255) NOT NULL,
  role     VARCHAR(30)  NOT NULL,
  club_id  VARCHAR(100) NULL,
  data     JSON         NOT NULL,
  PRIMARY KEY (id),
  KEY idx_users_email (email),
  KEY idx_users_club (club_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attempts (
  id         VARCHAR(100) NOT NULL,
  user_id    VARCHAR(100) NOT NULL,
  honor_id   VARCHAR(100) NOT NULL,
  club_id    VARCHAR(100) NULL,
  score      DECIMAL(6,2) NOT NULL DEFAULT 0,
  passed     TINYINT(1)   NOT NULL DEFAULT 0,
  created_at VARCHAR(40)  NOT NULL,
  data       JSON         NOT NULL,   -- full attempt incl. per-question answers
  PRIMARY KEY (id),
  KEY idx_attempts_user (user_id),
  KEY idx_attempts_honor (honor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS certificates (
  id                 VARCHAR(100) NOT NULL,
  verification_code  VARCHAR(60)  NOT NULL,
  user_id            VARCHAR(100) NOT NULL,
  honor_id           VARCHAR(100) NOT NULL,
  payment_status     VARCHAR(10)  NOT NULL DEFAULT 'unpaid',
  issued_at          VARCHAR(40)  NOT NULL,
  data               JSON         NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cert_code (verification_code),
  KEY idx_cert_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notifications (
  id   VARCHAR(100) NOT NULL,
  data JSON         NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mentor_reviews (
  id        VARCHAR(100) NOT NULL,
  user_id   VARCHAR(100) NOT NULL,
  honor_id  VARCHAR(100) NOT NULL,
  status    VARCHAR(20)  NOT NULL,
  data      JSON         NOT NULL,
  PRIMARY KEY (id),
  KEY idx_reviews_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS transactions (
  id              VARCHAR(100) NOT NULL,
  certificate_id  VARCHAR(100) NOT NULL,
  status          VARCHAR(20)  NOT NULL,
  reference       VARCHAR(100) NOT NULL,
  data            JSON         NOT NULL,
  PRIMARY KEY (id),
  KEY idx_tx_reference (reference)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  k  VARCHAR(64) NOT NULL,
  v  JSON        NOT NULL,
  PRIMARY KEY (k)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;