-- =====================================================================
-- Prosensia Gamified Internship Platform - MySQL Schema
-- =====================================================================
-- Charset: utf8mb4 for full unicode (including emoji in challenges)
-- Engine: InnoDB for foreign keys + transactions
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- USERS & AUTH
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email           VARCHAR(190) NOT NULL UNIQUE,
  password_hash   VARCHAR(255) NOT NULL,
  full_name       VARCHAR(120) NOT NULL,
  university      VARCHAR(160) NULL,
  city            VARCHAR(80)  NULL,
  avatar_url      VARCHAR(500) NULL,
  bio             TEXT NULL,
  role            ENUM('student','admin','superadmin') NOT NULL DEFAULT 'student',
  status          ENUM('active','suspended','banned') NOT NULL DEFAULT 'active',
  email_verified  TINYINT(1) NOT NULL DEFAULT 0,
  verify_token    VARCHAR(120) NULL,
  reset_token     VARCHAR(120) NULL,
  reset_expires   DATETIME NULL,
  last_login_at   DATETIME NULL,
  last_login_ip   VARCHAR(45) NULL,
  failed_logins   INT UNSIGNED NOT NULL DEFAULT 0,
  locked_until    DATETIME NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_users_role (role),
  INDEX idx_users_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sessions (JWT refresh tokens / opaque session ids)
CREATE TABLE IF NOT EXISTS sessions (
  id              CHAR(36) PRIMARY KEY,
  user_id         BIGINT UNSIGNED NOT NULL,
  ip              VARCHAR(45) NULL,
  user_agent      VARCHAR(255) NULL,
  expires_at      DATETIME NOT NULL,
  revoked_at      DATETIME NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_sessions_user (user_id),
  CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- DOMAINS & CHALLENGES
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS domains (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug            VARCHAR(60) NOT NULL UNIQUE,
  name            VARCHAR(120) NOT NULL,
  emoji           VARCHAR(10) NULL,
  description     TEXT NULL,
  active          TINYINT(1) NOT NULL DEFAULT 1,
  sort_order      INT NOT NULL DEFAULT 0,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_domains (
  user_id         BIGINT UNSIGNED NOT NULL,
  domain_id       INT UNSIGNED NOT NULL,
  joined_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, domain_id),
  CONSTRAINT fk_ud_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ud_domain FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS challenges (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  domain_id       INT UNSIGNED NOT NULL,
  title           VARCHAR(200) NOT NULL,
  description     TEXT NULL,
  difficulty      ENUM('easy','medium','hard') NOT NULL DEFAULT 'easy',
  type            ENUM('mcq','code','short_answer') NOT NULL DEFAULT 'mcq',
  points          INT UNSIGNED NOT NULL DEFAULT 10,
  time_limit_sec  INT UNSIGNED NOT NULL DEFAULT 60,
  active          TINYINT(1) NOT NULL DEFAULT 1,
  created_by      BIGINT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_challenges_domain (domain_id, active),
  CONSTRAINT fk_ch_domain FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE,
  CONSTRAINT fk_ch_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Choices for MCQs. correct flag is server-side only (never sent to client).
CREATE TABLE IF NOT EXISTS challenge_choices (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  challenge_id    BIGINT UNSIGNED NOT NULL,
  body            TEXT NOT NULL,
  is_correct      TINYINT(1) NOT NULL DEFAULT 0,
  sort_order      INT NOT NULL DEFAULT 0,
  INDEX idx_choices_challenge (challenge_id),
  CONSTRAINT fk_cc_challenge FOREIGN KEY (challenge_id) REFERENCES challenges(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- ATTEMPTS & SCORING
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS attempts (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         BIGINT UNSIGNED NOT NULL,
  challenge_id    BIGINT UNSIGNED NOT NULL,
  domain_id       INT UNSIGNED NOT NULL,
  selected_choice_id BIGINT UNSIGNED NULL,
  answer_text     TEXT NULL,
  is_correct      TINYINT(1) NOT NULL DEFAULT 0,
  points_awarded  INT NOT NULL DEFAULT 0,
  time_taken_ms   INT UNSIGNED NULL,
  ip              VARCHAR(45) NULL,
  user_agent      VARCHAR(255) NULL,
  suspicious      TINYINT(1) NOT NULL DEFAULT 0,
  suspicious_reason VARCHAR(160) NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_attempts_user (user_id, created_at),
  INDEX idx_attempts_domain (domain_id, created_at),
  INDEX idx_attempts_user_challenge (user_id, challenge_id),
  CONSTRAINT fk_att_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_challenge FOREIGN KEY (challenge_id) REFERENCES challenges(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_domain FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Aggregated per-domain score (kept in sync via triggers or app code).
CREATE TABLE IF NOT EXISTS user_domain_scores (
  user_id         BIGINT UNSIGNED NOT NULL,
  domain_id       INT UNSIGNED NOT NULL,
  total_points    INT NOT NULL DEFAULT 0,
  correct_count   INT NOT NULL DEFAULT 0,
  attempt_count   INT NOT NULL DEFAULT 0,
  last_played_at  DATETIME NULL,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, domain_id),
  INDEX idx_uds_leader (domain_id, total_points DESC),
  CONSTRAINT fk_uds_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_uds_domain FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- BADGES / ACHIEVEMENTS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS badges (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code            VARCHAR(60) NOT NULL UNIQUE,
  name            VARCHAR(120) NOT NULL,
  description     VARCHAR(255) NULL,
  icon            VARCHAR(60) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_badges (
  user_id         BIGINT UNSIGNED NOT NULL,
  badge_id        INT UNSIGNED NOT NULL,
  awarded_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, badge_id),
  CONSTRAINT fk_ub_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ub_badge FOREIGN KEY (badge_id) REFERENCES badges(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- AUDIT / ACTIVITY LOGS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS activity_logs (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         BIGINT UNSIGNED NULL,
  action          VARCHAR(80) NOT NULL,         -- login, signup, page_view, attempt, score_change, admin_action, failed_login
  entity_type     VARCHAR(40) NULL,
  entity_id       BIGINT UNSIGNED NULL,
  meta            JSON NULL,
  ip              VARCHAR(45) NULL,
  user_agent      VARCHAR(255) NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_logs_user (user_id, created_at),
  INDEX idx_logs_action (action, created_at),
  CONSTRAINT fk_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Failed logins kept separately for rate-limit / lockout queries.
CREATE TABLE IF NOT EXISTS failed_logins (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email           VARCHAR(190) NOT NULL,
  ip              VARCHAR(45) NULL,
  user_agent      VARCHAR(255) NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_fl_email (email, created_at),
  INDEX idx_fl_ip (ip, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- INTERNSHIP OFFERS (top-10 per domain)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS internship_offers (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id         BIGINT UNSIGNED NOT NULL,
  domain_id       INT UNSIGNED NOT NULL,
  rank_at_offer   INT NOT NULL,
  status          ENUM('pending','accepted','declined','expired') NOT NULL DEFAULT 'pending',
  offered_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  responded_at    DATETIME NULL,
  notes           TEXT NULL,
  CONSTRAINT fk_off_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_off_domain FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE,
  INDEX idx_offers_domain (domain_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;