-- Phase 3: AI assistant + translation, career & recognition, learning & CPE, paid membership readiness
SET NAMES utf8mb4;

CREATE TABLE search_index (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kind ENUM('post','regulation','vault') NOT NULL,
  ref_id INT NOT NULL,
  lang VARCHAR(8) NOT NULL DEFAULT 'en',
  title VARCHAR(255) NOT NULL DEFAULT '',
  body MEDIUMTEXT NOT NULL,
  visibility ENUM('public','members','circle') NOT NULL DEFAULT 'members',
  circle_id INT NULL,
  author_label VARCHAR(200) NOT NULL DEFAULT '',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ref (kind, ref_id),
  FULLTEXT KEY ft_search (title, body)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE post_translations (
  post_id INT NOT NULL,
  lang VARCHAR(8) NOT NULL,
  title VARCHAR(255) NOT NULL DEFAULT '',
  body_html MEDIUMTEXT NOT NULL,
  source_hash CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (post_id, lang),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  feature ENUM('assistant','translate','ui_translate') NOT NULL,
  tokens_in INT NOT NULL DEFAULT 0,
  tokens_out INT NOT NULL DEFAULT 0,
  sources VARCHAR(400) NOT NULL DEFAULT '',
  status ENUM('ok','error','limited') NOT NULL DEFAULT 'ok',
  error VARCHAR(300) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ai_user (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE translations ADD COLUMN is_machine TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE vault_items ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Career & recognition
ALTER TABLE users ADD COLUMN open_to VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'board,advisory,speaking,mentoring,new_role';

CREATE TABLE mentor_profiles (
  user_id INT PRIMARY KEY,
  topics VARCHAR(300) NOT NULL,
  pitch VARCHAR(600) NOT NULL DEFAULT '',
  capacity TINYINT NOT NULL DEFAULT 2,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE mentor_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mentor_id INT NOT NULL,
  mentee_id INT NOT NULL,
  goal TEXT NOT NULL,
  status ENUM('pending','accepted','declined','closed') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  responded_at DATETIME NULL,
  FOREIGN KEY (mentor_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (mentee_id) REFERENCES users(id) ON DELETE CASCADE,
  KEY idx_mr (mentor_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE opportunities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  posted_by INT NOT NULL,
  kind ENUM('speaking','jury','board','advisory','job') NOT NULL,
  title VARCHAR(200) NOT NULL,
  organisation VARCHAR(160) NOT NULL,
  country_iso2 CHAR(2) NULL,
  description TEXT NOT NULL,
  apply_by DATE NULL,
  is_confidential TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('open','closed','removed') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (posted_by) REFERENCES users(id) ON DELETE CASCADE,
  KEY idx_opp (status, kind)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE opportunity_interests (
  opportunity_id INT NOT NULL,
  user_id INT NOT NULL,
  note VARCHAR(1000) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (opportunity_id, user_id),
  FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE award_cycles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  year SMALLINT NOT NULL,
  title VARCHAR(200) NOT NULL,
  nominations_close DATE NOT NULL,
  status ENUM('draft','nominations','judging','announced') NOT NULL DEFAULT 'draft',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE award_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cycle_id INT NOT NULL,
  name VARCHAR(160) NOT NULL,
  description VARCHAR(500) NOT NULL DEFAULT '',
  sort_order INT NOT NULL DEFAULT 0,
  FOREIGN KEY (cycle_id) REFERENCES award_cycles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE award_nominations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  nominee_id INT NOT NULL,
  nominator_id INT NOT NULL,
  reason TEXT NOT NULL,
  status ENUM('submitted','shortlisted','winner','removed') NOT NULL DEFAULT 'submitted',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_nom (category_id, nominee_id, nominator_id),
  FOREIGN KEY (category_id) REFERENCES award_categories(id) ON DELETE CASCADE,
  FOREIGN KEY (nominee_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (nominator_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Learning & CPE
CREATE TABLE courses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(180) NOT NULL UNIQUE,
  title VARCHAR(200) NOT NULL,
  summary VARCHAR(400) NOT NULL DEFAULT '',
  description TEXT NOT NULL,
  level ENUM('foundation','practitioner','leader') NOT NULL DEFAULT 'practitioner',
  lang VARCHAR(8) NOT NULL DEFAULT 'en',
  cpe_hours DECIMAL(4,1) NOT NULL DEFAULT 1.0,
  price_minor BIGINT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  status ENUM('draft','published') NOT NULL DEFAULT 'draft',
  created_by INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE course_lessons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  course_id INT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  title VARCHAR(200) NOT NULL,
  video_url VARCHAR(400) NOT NULL DEFAULT '',
  body_html MEDIUMTEXT NOT NULL,
  minutes INT NOT NULL DEFAULT 10,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE enrollments (
  course_id INT NOT NULL,
  user_id INT NOT NULL,
  status ENUM('active','completed') NOT NULL DEFAULT 'active',
  payment_id INT NULL,
  enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  certificate_code CHAR(12) NULL UNIQUE,
  PRIMARY KEY (course_id, user_id),
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lesson_progress (
  lesson_id INT NOT NULL,
  user_id INT NOT NULL,
  completed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (lesson_id, user_id),
  FOREIGN KEY (lesson_id) REFERENCES course_lessons(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cpe_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  source ENUM('course','event','external') NOT NULL,
  ref_id INT NOT NULL DEFAULT 0,
  title VARCHAR(200) NOT NULL,
  provider VARCHAR(160) NOT NULL DEFAULT '',
  hours DECIMAL(5,1) NOT NULL,
  activity_date DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  KEY idx_cpe (user_id, activity_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE events ADD COLUMN cpe_hours DECIMAL(4,1) NOT NULL DEFAULT 0;

-- Paid membership (switched off during the free founding year)
CREATE TABLE membership_plans (
  code VARCHAR(30) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price_usd_minor BIGINT NOT NULL,
  months TINYINT NOT NULL DEFAULT 12,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO membership_plans (code, name, price_usd_minor, months) VALUES ('annual', 'Annual membership', 24900, 12);

CREATE TABLE subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  plan_code VARCHAR(30) NOT NULL,
  status ENUM('active','expired','cancelled') NOT NULL DEFAULT 'active',
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  payment_id INT NULL,
  reminded VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'reminders sent: 30,7,0',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_code) REFERENCES membership_plans(code),
  KEY idx_sub (user_id, status, ends_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE payments MODIFY kind ENUM('ad','event','course','membership') NOT NULL;

INSERT INTO settings (k, v) VALUES ('membership_paid','0'), ('founding_discount_pct','50'), ('ai_daily_limit','30'), ('ai_enabled','1');

INSERT INTO comm_templates (code, channel, lang, subject, body) VALUES
('mentor_request','email','en','New mentoring request on {{site}}','Hello {{name}},\n\n{{message}}\n\nRespond here: {{link}}\n\n{{site}}'),
('opportunity_match','email','en','New opportunity: {{title}}','Hello {{name}},\n\nA new opportunity that matches what you are open to was posted:\n{{title}}\n{{link}}\n\n{{site}}'),
('certificate','email','en','Your certificate: {{title}}','Hello {{name}},\n\nCongratulations on completing {{title}}.\nCertificate: {{link}}\n\n{{site}}'),
('membership_renewal','email','en','Your {{site}} membership renews on {{when}}','Hello {{name}},\n\nYour membership ends on {{when}}. Renew here to keep access:\n{{link}}\n\n{{site}}');