-- Prepu EAD — schema alinhado com o frontend (AOA / Angola)
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS prepu_ead
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE prepu_ead;

DROP TABLE IF EXISTS live_session_messages;
DROP TABLE IF EXISTS live_sessions;
DROP TABLE IF EXISTS community_members;
DROP TABLE IF EXISTS communities;
DROP TABLE IF EXISTS lesson_comments;
DROP TABLE IF EXISTS lesson_notes;
DROP TABLE IF EXISTS lesson_progress;
DROP TABLE IF EXISTS job_applications;
DROP TABLE IF EXISTS job_skills;
DROP TABLE IF EXISTS career_path_courses;
DROP TABLE IF EXISTS xp_transactions;
DROP TABLE IF EXISTS commissions;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS certificates;
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS lessons;
DROP TABLE IF EXISTS chapters;
DROP TABLE IF EXISTS modules;
DROP TABLE IF EXISTS course_tags;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS career_paths;
DROP TABLE IF EXISTS jobs;
DROP TABLE IF EXISTS companies;
DROP TABLE IF EXISTS gamification_profiles;
DROP TABLE IF EXISTS wallets;
DROP TABLE IF EXISTS profiles;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS platform_faqs;
DROP TABLE IF EXISTS platform_testimonials;
DROP TABLE IF EXISTS platform_partners;

CREATE TABLE users (
  id                 CHAR(36)     NOT NULL PRIMARY KEY,
  email              VARCHAR(255) NOT NULL UNIQUE,
  password_hash      VARCHAR(255) NOT NULL,
  role               ENUM('admin','instructor','student','company','institution','center') NOT NULL DEFAULT 'student',
  is_active          TINYINT(1)   NOT NULL DEFAULT 1,
  email_verified     TINYINT(1)   NOT NULL DEFAULT 0,
  two_factor_enabled TINYINT(1)   NOT NULL DEFAULT 0,
  locale             ENUM('pt','en','fr') NOT NULL DEFAULT 'pt',
  refresh_token_hash VARCHAR(255) NULL,
  password_reset_token_hash VARCHAR(255) NULL,
  password_reset_expires_at DATETIME NULL,
  last_login_at      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_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE profiles (
  id               CHAR(36)     NOT NULL PRIMARY KEY,
  user_id          CHAR(36)     NOT NULL UNIQUE,
  first_name       VARCHAR(120) NOT NULL,
  last_name        VARCHAR(120) NOT NULL,
  headline         VARCHAR(255) NULL,
  phone            VARCHAR(20)  NULL,
  avatar_url       VARCHAR(500) NULL,
  bio              TEXT         NULL,
  city             VARCHAR(100) NULL,
  province         VARCHAR(100) NULL,
  country          CHAR(2)      NOT NULL DEFAULT 'AO',
  organization     VARCHAR(255) NULL,
  employability_score INT       NOT NULL DEFAULT 0,
  created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE companies (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  owner_user_id CHAR(36)     NOT NULL,
  name          VARCHAR(255) NOT NULL,
  slug          VARCHAR(255) NOT NULL UNIQUE,
  nif           VARCHAR(20)  NULL,
  description   TEXT         NULL,
  logo_url      VARCHAR(500) NULL,
  website       VARCHAR(255) NULL,
  city          VARCHAR(100) NULL,
  province      VARCHAR(100) NULL,
  is_verified   TINYINT(1)   NOT NULL DEFAULT 0,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_companies_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE categories (
  id         CHAR(36)     NOT NULL PRIMARY KEY,
  slug       VARCHAR(80)  NOT NULL UNIQUE,
  name       VARCHAR(120) NOT NULL,
  icon       VARCHAR(40)  NOT NULL DEFAULT 'BookOpen',
  sort_order INT          NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE courses (
  id                   CHAR(36)       NOT NULL PRIMARY KEY,
  instructor_id        CHAR(36)       NOT NULL,
  category_id          CHAR(36)       NOT NULL,
  title                VARCHAR(255)   NOT NULL,
  slug                 VARCHAR(255)   NOT NULL UNIQUE,
  subtitle             VARCHAR(500)   NULL,
  description          TEXT           NULL,
  thumbnail_url        VARCHAR(500)   NULL,
  thumbnail_gradient   VARCHAR(120)   NOT NULL DEFAULT 'from-brand-700 via-brand-500 to-gold-500',
  trailer_url          VARCHAR(500)   NULL,
  level                ENUM('beginner','intermediate','advanced') NOT NULL DEFAULT 'beginner',
  language             VARCHAR(40)    NOT NULL DEFAULT 'Português',
  locale               ENUM('pt','en','fr') NOT NULL DEFAULT 'pt',
  price_aoa            DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
  original_price_aoa   DECIMAL(12,2)  NULL,
  currency             CHAR(3)        NOT NULL DEFAULT 'AOA',
  duration_hours       INT            NOT NULL DEFAULT 0,
  lesson_count         INT            NOT NULL DEFAULT 0,
  has_certificate      TINYINT(1)     NOT NULL DEFAULT 1,
  has_subtitles        TINYINT(1)     NOT NULL DEFAULT 1,
  status               ENUM('draft','pending','published','rejected','archived') NOT NULL DEFAULT 'draft',
  is_featured          TINYINT(1)     NOT NULL DEFAULT 0,
  enrollment_count     INT            NOT NULL DEFAULT 0,
  rating_avg           DECIMAL(3,2)   NOT NULL DEFAULT 0.00,
  rating_count         INT            NOT NULL DEFAULT 0,
  objectives           JSON           NULL,
  prerequisites        JSON           NULL,
  faqs                 JSON           NULL,
  created_at           DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_courses_instructor FOREIGN KEY (instructor_id) REFERENCES users(id),
  CONSTRAINT fk_courses_category FOREIGN KEY (category_id) REFERENCES categories(id),
  INDEX idx_courses_status (status),
  INDEX idx_courses_featured (is_featured),
  INDEX idx_courses_slug (slug),
  FULLTEXT idx_courses_search (title, subtitle, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE course_tags (
  course_id CHAR(36)    NOT NULL,
  tag       VARCHAR(60) NOT NULL,
  PRIMARY KEY (course_id, tag),
  CONSTRAINT fk_tags_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE modules (
  id         CHAR(36)     NOT NULL PRIMARY KEY,
  course_id  CHAR(36)     NOT NULL,
  title      VARCHAR(255) NOT NULL,
  sort_order INT          NOT NULL DEFAULT 0,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_modules_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  INDEX idx_modules_course (course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE chapters (
  id         CHAR(36)     NOT NULL PRIMARY KEY,
  module_id  CHAR(36)     NOT NULL,
  title      VARCHAR(255) NOT NULL,
  sort_order INT          NOT NULL DEFAULT 0,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_chapters_module FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE,
  INDEX idx_chapters_module (module_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lessons (
  id           CHAR(36)     NOT NULL PRIMARY KEY,
  chapter_id   CHAR(36)     NOT NULL,
  title        VARCHAR(255) NOT NULL,
  content_type ENUM('video','quiz','exercise','project','assessment','text') NOT NULL DEFAULT 'video',
  content_url  VARCHAR(500) NULL,
  content_body MEDIUMTEXT   NULL,
  transcript   MEDIUMTEXT   NULL,
  ai_summary   TEXT         NULL,
  duration_min INT          NOT NULL DEFAULT 0,
  is_preview   TINYINT(1)   NOT NULL DEFAULT 0,
  sort_order   INT          NOT NULL DEFAULT 0,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_lessons_chapter FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE CASCADE,
  INDEX idx_lessons_chapter (chapter_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE enrollments (
  id           CHAR(36)     NOT NULL PRIMARY KEY,
  user_id      CHAR(36)     NOT NULL,
  course_id    CHAR(36)     NOT NULL,
  status       ENUM('active','completed','cancelled','expired') NOT NULL DEFAULT 'active',
  progress_pct DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  hours_studied DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  enrolled_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME     NULL,
  UNIQUE KEY uq_enrollment (user_id, course_id),
  CONSTRAINT fk_enrollments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_enrollments_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lesson_progress (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  user_id       CHAR(36)     NOT NULL,
  lesson_id     CHAR(36)     NOT NULL,
  enrollment_id CHAR(36)     NOT NULL,
  progress_pct  DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  completed     TINYINT(1)   NOT NULL DEFAULT 0,
  last_second   INT          NOT NULL DEFAULT 0,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_lesson_progress (user_id, lesson_id),
  CONSTRAINT fk_lp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_lp_lesson FOREIGN KEY (lesson_id) REFERENCES lessons(id) ON DELETE CASCADE,
  CONSTRAINT fk_lp_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lesson_notes (
  id         CHAR(36)     NOT NULL PRIMARY KEY,
  user_id    CHAR(36)     NOT NULL,
  lesson_id  CHAR(36)     NOT NULL,
  timestamp_label VARCHAR(12) NOT NULL DEFAULT '00:00',
  body       TEXT         NOT NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notes_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_notes_lesson FOREIGN KEY (lesson_id) REFERENCES lessons(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lesson_comments (
  id         CHAR(36)     NOT NULL PRIMARY KEY,
  user_id    CHAR(36)     NOT NULL,
  lesson_id  CHAR(36)     NOT NULL,
  timestamp_label VARCHAR(12) NOT NULL DEFAULT '00:00',
  body       TEXT         NOT NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_comments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_comments_lesson FOREIGN KEY (lesson_id) REFERENCES lessons(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE certificates (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  user_id       CHAR(36)     NOT NULL,
  course_id     CHAR(36)     NOT NULL,
  enrollment_id CHAR(36)     NOT NULL,
  code          VARCHAR(40)  NOT NULL UNIQUE,
  issued_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at    DATETIME     NULL,
  pdf_url       VARCHAR(500) NULL,
  UNIQUE KEY uq_cert_user_course (user_id, course_id),
  CONSTRAINT fk_certificates_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_certificates_course FOREIGN KEY (course_id) REFERENCES courses(id),
  CONSTRAINT fk_certificates_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wallets (
  id          CHAR(36)      NOT NULL PRIMARY KEY,
  user_id     CHAR(36)      NOT NULL UNIQUE,
  balance_aoa DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  coins       INT           NOT NULL DEFAULT 0,
  currency    CHAR(3)       NOT NULL DEFAULT 'AOA',
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_wallets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
  id         CHAR(36)      NOT NULL PRIMARY KEY,
  user_id    CHAR(36)      NOT NULL,
  course_id  CHAR(36)      NULL,
  amount_aoa DECIMAL(12,2) NOT NULL,
  currency   CHAR(3)       NOT NULL DEFAULT 'AOA',
  method     ENUM('multicaixa_express','referencia_bancaria','appypay','unitel_money','afrimoney','visa','mastercard','paypal','presencial','wallet') NOT NULL DEFAULT 'multicaixa_express',
  status     ENUM('pending','processing','paid','failed','refunded','cancelled') NOT NULL DEFAULT 'pending',
  reference  VARCHAR(100)  NULL UNIQUE,
  paid_at    DATETIME      NULL,
  created_at DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_payments_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_payments_course FOREIGN KEY (course_id) REFERENCES courses(id),
  INDEX idx_payments_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE commissions (
  id               CHAR(36)      NOT NULL PRIMARY KEY,
  payment_id       CHAR(36)      NOT NULL,
  beneficiary_id   CHAR(36)      NOT NULL,
  beneficiary_type ENUM('instructor','center','institution','platform') NOT NULL,
  amount_aoa       DECIMAL(12,2) NOT NULL,
  rate_pct         DECIMAL(5,2)  NOT NULL,
  status           ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
  paid_at          DATETIME      NULL,
  created_at       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_commissions_payment FOREIGN KEY (payment_id) REFERENCES payments(id),
  CONSTRAINT fk_commissions_beneficiary FOREIGN KEY (beneficiary_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE career_paths (
  id              CHAR(36)     NOT NULL PRIMARY KEY,
  slug            VARCHAR(255) NOT NULL UNIQUE,
  title           VARCHAR(255) NOT NULL,
  profession      VARCHAR(120) NOT NULL,
  description     TEXT         NULL,
  course_count    INT          NOT NULL DEFAULT 0,
  estimated_months INT         NOT NULL DEFAULT 6,
  salary_min_aoa  DECIMAL(12,2) NOT NULL DEFAULT 0,
  salary_max_aoa  DECIMAL(12,2) NOT NULL DEFAULT 0,
  demand          ENUM('alta','média','emergente') NOT NULL DEFAULT 'alta',
  color           ENUM('brand','gold') NOT NULL DEFAULT 'brand',
  is_active       TINYINT(1)   NOT NULL DEFAULT 1,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE career_path_courses (
  career_path_id CHAR(36) NOT NULL,
  course_id      CHAR(36) NOT NULL,
  sort_order     INT      NOT NULL DEFAULT 0,
  PRIMARY KEY (career_path_id, course_id),
  CONSTRAINT fk_cpc_path FOREIGN KEY (career_path_id) REFERENCES career_paths(id) ON DELETE CASCADE,
  CONSTRAINT fk_cpc_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE jobs (
  id              CHAR(36)       NOT NULL PRIMARY KEY,
  company_id      CHAR(36)       NOT NULL,
  title           VARCHAR(255)   NOT NULL,
  slug            VARCHAR(255)   NOT NULL UNIQUE,
  description     TEXT           NOT NULL,
  location        VARCHAR(120)   NULL,
  province        VARCHAR(100)   NULL,
  employment_type ENUM('full_time','part_time','contract','internship','remote') NOT NULL DEFAULT 'full_time',
  salary_min_aoa  DECIMAL(12,2)  NULL,
  salary_max_aoa  DECIMAL(12,2)  NULL,
  currency        CHAR(3)        NOT NULL DEFAULT 'AOA',
  is_active       TINYINT(1)     NOT NULL DEFAULT 1,
  applicants_count INT           NOT NULL DEFAULT 0,
  expires_at      DATETIME       NULL,
  created_at      DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_jobs_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE job_skills (
  job_id CHAR(36)    NOT NULL,
  skill  VARCHAR(80) NOT NULL,
  PRIMARY KEY (job_id, skill),
  CONSTRAINT fk_job_skills FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE job_applications (
  id         CHAR(36) NOT NULL PRIMARY KEY,
  job_id     CHAR(36) NOT NULL,
  user_id    CHAR(36) NOT NULL,
  status     ENUM('submitted','reviewing','interview','rejected','hired') NOT NULL DEFAULT 'submitted',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_application (job_id, user_id),
  CONSTRAINT fk_app_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_app_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE gamification_profiles (
  id          CHAR(36) NOT NULL PRIMARY KEY,
  user_id     CHAR(36) NOT NULL UNIQUE,
  xp          INT      NOT NULL DEFAULT 0,
  coins       INT      NOT NULL DEFAULT 0,
  level       INT      NOT NULL DEFAULT 1,
  league      ENUM('bronze','silver','gold','diamond','legendary') NOT NULL DEFAULT 'bronze',
  streak_days INT      NOT NULL DEFAULT 0,
  weekly_goal_hours DECIMAL(5,2) NOT NULL DEFAULT 8.00,
  weekly_done_hours DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  badges      JSON     NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_gamification_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE xp_transactions (
  id           CHAR(36)     NOT NULL PRIMARY KEY,
  user_id      CHAR(36)     NOT NULL,
  amount       INT          NOT NULL,
  reason       VARCHAR(255) NOT NULL,
  reference_id CHAR(36)     NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_xp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_xp_reason_ref (user_id, reason, reference_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE communities (
  id           CHAR(36)     NOT NULL PRIMARY KEY,
  course_id    CHAR(36)     NULL,
  name         VARCHAR(255) NOT NULL,
  slug         VARCHAR(255) NOT NULL UNIQUE,
  description  TEXT         NULL,
  is_public    TINYINT(1)   NOT NULL DEFAULT 1,
  member_count INT          NOT NULL DEFAULT 0,
  created_by   CHAR(36)     NOT NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_communities_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE SET NULL,
  CONSTRAINT fk_communities_creator FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE community_members (
  community_id CHAR(36) NOT NULL,
  user_id      CHAR(36) NOT NULL,
  role         ENUM('member','moderator','admin') NOT NULL DEFAULT 'member',
  joined_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (community_id, user_id),
  CONSTRAINT fk_cm_community FOREIGN KEY (community_id) REFERENCES communities(id) ON DELETE CASCADE,
  CONSTRAINT fk_cm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE live_sessions (
  id               CHAR(36)     NOT NULL PRIMARY KEY,
  course_id        CHAR(36)     NOT NULL,
  instructor_id    CHAR(36)     NOT NULL,
  title            VARCHAR(255) NOT NULL,
  description      TEXT         NULL,
  scheduled_at     DATETIME     NOT NULL,
  duration_min     INT          NOT NULL DEFAULT 60,
  status           ENUM('scheduled','live','ended','cancelled') NOT NULL DEFAULT 'scheduled',
  meeting_url      VARCHAR(500) NULL,
  city_label       VARCHAR(120) NULL,
  created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_live_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  CONSTRAINT fk_live_instructor FOREIGN KEY (instructor_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE live_session_messages (
  id         CHAR(36) NOT NULL PRIMARY KEY,
  session_id CHAR(36) NOT NULL,
  user_id    CHAR(36) NOT NULL,
  message    TEXT     NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_lsm_session FOREIGN KEY (session_id) REFERENCES live_sessions(id) ON DELETE CASCADE,
  CONSTRAINT fk_lsm_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE platform_partners (
  id         CHAR(36)     NOT NULL PRIMARY KEY,
  name       VARCHAR(120) NOT NULL,
  sort_order INT          NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE platform_testimonials (
  id         CHAR(36)     NOT NULL PRIMARY KEY,
  name       VARCHAR(120) NOT NULL,
  role_label VARCHAR(180) NOT NULL,
  quote      TEXT         NOT NULL,
  city       VARCHAR(80)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE platform_faqs (
  id         CHAR(36) NOT NULL PRIMARY KEY,
  question   VARCHAR(255) NOT NULL,
  answer     TEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;