CREATE DATABASE IF NOT EXISTS wordly_db
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE wordly_db;

CREATE TABLE IF NOT EXISTS languages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(8) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL,
  native_name VARCHAR(100) NOT NULL,
  category VARCHAR(40) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
);

INSERT IGNORE INTO languages (code,name,native_name,category,sort_order) VALUES
('en','English','English','Europe',1),
('ko','Korean','한국어','Asia',2),
('ja','Japanese','日本語','Asia',3),
('zh','Mandarin Chinese','中文','Asia',4),
('es','Spanish','Español','Europe',5),
('fr','French','Français','Europe',6),
('de','German','Deutsch','Europe',7),
('it','Italian','Italiano','Europe',8),
('pt','Portuguese','Português','Europe',9),
('ar','Arabic','العربية','Middle East',10),
('th','Thai','ไทย','Asia',11),
('vi','Vietnamese','Tiếng Việt','Asia',12),
('ru','Russian','Русский','Europe',13),
('nl','Dutch','Nederlands','Europe',14),
('tr','Turkish','Türkçe','Middle East',15),
('el','Greek','Ελληνικά','Europe',16),
('pl','Polish','Polski','Europe',17),
('hi','Hindi','हिन्दी','South Asia',18),
('fa','Persian','فارسی','Middle East',19),
('id','Indonesian','Bahasa Indonesia','Asia',20);

CREATE TABLE IF NOT EXISTS users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  display_name VARCHAR(100) NOT NULL,
  username VARCHAR(30) NOT NULL UNIQUE,
  username_changed_at DATETIME NULL DEFAULT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  nim VARCHAR(40) NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  email_verified TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS user_settings (
  user_id BIGINT PRIMARY KEY,
  app_language VARCHAR(8) NOT NULL DEFAULT 'id',
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (app_language) REFERENCES languages(code)
);

CREATE TABLE IF NOT EXISTS user_languages (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  language_code VARCHAR(8) NOT NULL,
  current_level VARCHAR(30) NOT NULL DEFAULT 'Beginner',
  started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_language (user_id, language_code),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (language_code) REFERENCES languages(code)
);

CREATE TABLE IF NOT EXISTS lessons (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  language_code VARCHAR(8) NOT NULL,
  title VARCHAR(180) NOT NULL,
  description TEXT,
  level VARCHAR(30) NOT NULL DEFAULT 'Beginner',
  unit_number INT NOT NULL DEFAULT 1,
  lesson_number INT NOT NULL DEFAULT 1,
  FOREIGN KEY (language_code) REFERENCES languages(code)
);

CREATE TABLE IF NOT EXISTS vocabularies (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  lesson_id BIGINT NOT NULL,
  word VARCHAR(180) NOT NULL,
  translation VARCHAR(180) NOT NULL,
  pronunciation VARCHAR(180),
  example_sentence TEXT,
  FOREIGN KEY (lesson_id) REFERENCES lessons(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS quiz_questions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  lesson_id BIGINT NOT NULL,
  question TEXT NOT NULL,
  options_json JSON NOT NULL,
  correct_answer VARCHAR(255) NOT NULL,
  explanation TEXT,
  FOREIGN KEY (lesson_id) REFERENCES lessons(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS user_progress (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  language_code VARCHAR(8) NOT NULL,
  xp INT NOT NULL DEFAULT 0,
  coins INT NOT NULL DEFAULT 0,
  streak_days INT NOT NULL DEFAULT 0,
  current_level INT NOT NULL DEFAULT 1,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_progress_user_language (user_id, language_code),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (language_code) REFERENCES languages(code)
);

CREATE TABLE IF NOT EXISTS game_scores (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  language_code VARCHAR(8) NOT NULL,
  game_type VARCHAR(40) NOT NULL,
  score INT NOT NULL DEFAULT 0,
  xp_earned INT NOT NULL DEFAULT 0,
  coins_earned INT NOT NULL DEFAULT 0,
  played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (language_code) REFERENCES languages(code)
);

CREATE TABLE IF NOT EXISTS music_playlists (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  playlist_name VARCHAR(120) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- WORDLY authentication and account security schema
ALTER TABLE users MODIFY password_hash VARCHAR(255) NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS nim VARCHAR(40) NULL UNIQUE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE users ADD COLUMN IF NOT EXISTS status ENUM('active','suspended') NOT NULL DEFAULT 'active';

CREATE TABLE IF NOT EXISTS auth_identities (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  provider ENUM('google','microsoft') NOT NULL,
  provider_user_id VARCHAR(191) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_provider_user (provider, provider_user_id),
  UNIQUE KEY uq_user_provider (user_id, provider),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS user_sessions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_session_user (user_id), INDEX idx_session_expiry (expires_at),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS email_verifications (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS password_resets (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS oauth_states (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  state_hash CHAR(64) NOT NULL UNIQUE,
  provider ENUM('google','microsoft') NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS oauth_handoffs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  state_hash CHAR(64) NOT NULL UNIQUE,
  user_id BIGINT NOT NULL,
  token VARCHAR(128) NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS user_activity (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  activity_type VARCHAR(50) NOT NULL,
  details_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_activity_user_date (user_id, created_at),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS login_attempts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  identity_key CHAR(64) NOT NULL,
  ip_hash CHAR(64) NOT NULL,
  attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_login_attempt_window (identity_key, ip_hash, attempted_at)
);


CREATE TABLE IF NOT EXISTS user_dashboard_state (
  user_id BIGINT PRIMARY KEY,
  state_json JSON NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_dashboard_state_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
