-- ============================================================
-- Classic Catering Services — MySQL Schema
-- Compatible with MySQL 5.7+ / MariaDB 10.3+
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

-- Drop existing tables (for fresh installs / re-imports)
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS certificates;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS payment_receipts;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS training_applications;
DROP TABLE IF EXISTS training_courses;
DROP TABLE IF EXISTS testimonials;
DROP TABLE IF EXISTS videos;
DROP TABLE IF EXISTS gallery_images;
DROP TABLE IF EXISTS catering_requests;
DROP TABLE IF EXISTS catering_services;
DROP TABLE IF EXISTS social_links;
DROP TABLE IF EXISTS business_settings;
DROP TABLE IF EXISTS landing_pages;
DROP TABLE IF EXISTS password_resets;
DROP TABLE IF EXISTS admin_profiles;
DROP TABLE IF EXISTS student_profiles;
DROP TABLE IF EXISTS users;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- USERS, ROLES, PROFILES
-- ============================================================

CREATE TABLE users (
  id              VARCHAR(40) NOT NULL PRIMARY KEY,
  email           VARCHAR(191) NOT NULL UNIQUE,
  phone           VARCHAR(50) NULL,
  password_hash   VARCHAR(255) NOT NULL,
  role            ENUM('SUPER_ADMIN','STAFF_ADMIN','STUDENT') NOT NULL DEFAULT 'STUDENT',
  first_name      VARCHAR(100) NULL,
  last_name       VARCHAR(100) NULL,
  status          VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  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_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE admin_profiles (
  id           VARCHAR(40) NOT NULL PRIMARY KEY,
  user_id      VARCHAR(40) NOT NULL UNIQUE,
  display_name VARCHAR(150) NOT NULL,
  modules      TEXT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_admin_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE student_profiles (
  id                  VARCHAR(40) NOT NULL PRIMARY KEY,
  user_id             VARCHAR(40) NOT NULL UNIQUE,
  application_number  VARCHAR(50) NOT NULL UNIQUE,
  full_name           VARCHAR(150) NOT NULL,
  date_of_birth       DATE NULL,
  gender              VARCHAR(20) NULL,
  residential_address TEXT NULL,
  state_of_residence  VARCHAR(100) NULL,
  education           VARCHAR(100) NULL,
  previous_experience TEXT NULL,
  photo_url           VARCHAR(255) NULL,
  emergency_contact   VARCHAR(50) NULL,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_student_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE password_resets (
  id         VARCHAR(40) NOT NULL PRIMARY KEY,
  user_id    VARCHAR(40) NOT NULL,
  token      VARCHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  used_at    DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pwreset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_pwreset_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- BUSINESS SETTINGS & BRANDING
-- ============================================================

CREATE TABLE business_settings (
  id         VARCHAR(40) NOT NULL PRIMARY KEY,
  `key`      VARCHAR(100) NOT NULL UNIQUE,
  value      TEXT NOT NULL,
  category   VARCHAR(50) NOT NULL DEFAULT 'general',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_settings_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE social_links (
  id         VARCHAR(40) NOT NULL PRIMARY KEY,
  platform   VARCHAR(50) NOT NULL,
  url        VARCHAR(500) NOT NULL,
  icon       VARCHAR(50) NULL,
  `order`    INT NOT NULL DEFAULT 0,
  active     TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_social_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LANDING PAGES (multiple switchable variants)
-- ============================================================

CREATE TABLE landing_pages (
  id                   VARCHAR(40) NOT NULL PRIMARY KEY,
  name                 VARCHAR(150) NOT NULL,
  slug                 VARCHAR(150) NOT NULL UNIQUE,
  is_active            TINYINT(1) NOT NULL DEFAULT 0,
  is_default           TINYINT(1) NOT NULL DEFAULT 0,
  hero_title           VARCHAR(255) NOT NULL,
  hero_subtitle        TEXT NOT NULL,
  hero_image           VARCHAR(500) NOT NULL,
  hero_badge           VARCHAR(100) NULL,
  cta1_text            VARCHAR(100) NOT NULL DEFAULT 'Request Catering Services',
  cta1_link            VARCHAR(255) NOT NULL DEFAULT '/catering/request',
  cta2_text            VARCHAR(100) NOT NULL DEFAULT 'Explore Training Courses',
  cta2_link            VARCHAR(255) NOT NULL DEFAULT '/courses',
  show_stats           TINYINT(1) NOT NULL DEFAULT 1,
  show_services        TINYINT(1) NOT NULL DEFAULT 1,
  show_why_choose_us   TINYINT(1) NOT NULL DEFAULT 1,
  show_video           TINYINT(1) NOT NULL DEFAULT 1,
  show_gallery         TINYINT(1) NOT NULL DEFAULT 1,
  show_training        TINYINT(1) NOT NULL DEFAULT 1,
  show_testimonials    TINYINT(1) NOT NULL DEFAULT 1,
  show_final_cta       TINYINT(1) NOT NULL DEFAULT 1,
  final_cta_headline  VARCHAR(255) NULL,
  final_cta_button_text VARCHAR(100) NULL,
  final_cta_button_link VARCHAR(255) NULL,
  created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_landing_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CATERING SERVICES & REQUESTS
-- ============================================================

CREATE TABLE catering_services (
  id              VARCHAR(40) NOT NULL PRIMARY KEY,
  title           VARCHAR(200) NOT NULL,
  slug            VARCHAR(200) NOT NULL UNIQUE,
  description     TEXT NOT NULL,
  category        VARCHAR(150) NOT NULL,
  image_url       VARCHAR(500) NULL,
  starting_price  DECIMAL(12,2) NULL,
  featured        TINYINT(1) NOT NULL DEFAULT 0,
  published       TINYINT(1) NOT NULL DEFAULT 1,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_services_published (published),
  INDEX idx_services_featured (featured),
  INDEX idx_services_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE catering_requests (
  id                  VARCHAR(40) NOT NULL PRIMARY KEY,
  reference_no        VARCHAR(50) NOT NULL UNIQUE,
  full_name           VARCHAR(150) NOT NULL,
  email               VARCHAR(191) NOT NULL,
  phone               VARCHAR(50) NOT NULL,
  event_type          VARCHAR(50) NOT NULL,
  event_date          DATE NULL,
  event_venue         VARCHAR(255) NULL,
  guest_count         INT NULL,
  service_id          VARCHAR(40) NULL,
  food_preferences    TEXT NULL,
  special_requirements TEXT NULL,
  budget              DECIMAL(12,2) NULL,
  message             TEXT NULL,
  status              VARCHAR(30) NOT NULL DEFAULT 'PENDING',
  internal_notes      TEXT NULL,
  quotation           DECIMAL(12,2) NULL,
  assigned_to         VARCHAR(150) NULL,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_request_service FOREIGN KEY (service_id) REFERENCES catering_services(id) ON DELETE SET NULL,
  INDEX idx_request_status (status),
  INDEX idx_request_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- GALLERY & VIDEOS
-- ============================================================

CREATE TABLE gallery_images (
  id            VARCHAR(40) NOT NULL PRIMARY KEY,
  title         VARCHAR(200) NOT NULL,
  caption       VARCHAR(255) NULL,
  category      VARCHAR(50) NOT NULL,
  image_url     VARCHAR(500) NOT NULL,
  thumbnail_url VARCHAR(500) NULL,
  published     TINYINT(1) NOT NULL DEFAULT 1,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_gallery_published (published),
  INDEX idx_gallery_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE videos (
  id          VARCHAR(40) NOT NULL PRIMARY KEY,
  title       VARCHAR(200) NOT NULL,
  description TEXT NULL,
  youtube_id  VARCHAR(50) NOT NULL,
  category    VARCHAR(50) NULL,
  featured    TINYINT(1) NOT NULL DEFAULT 0,
  published   TINYINT(1) NOT NULL DEFAULT 1,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_videos_published (published)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE testimonials (
  id          VARCHAR(40) NOT NULL PRIMARY KEY,
  client_name VARCHAR(150) NOT NULL,
  client_role VARCHAR(150) NULL,
  content     TEXT NOT NULL,
  rating      INT NOT NULL DEFAULT 5,
  avatar_url  VARCHAR(500) NULL,
  published   TINYINT(1) NOT NULL DEFAULT 1,
  `order`     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,
  INDEX idx_testimonials_published (published)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TRAINING COURSES & APPLICATIONS
-- ============================================================

CREATE TABLE training_courses (
  id                   VARCHAR(40) NOT NULL PRIMARY KEY,
  title                VARCHAR(200) NOT NULL,
  slug                 VARCHAR(200) NOT NULL UNIQUE,
  description          TEXT NOT NULL,
  outline              TEXT NULL,
  category             VARCHAR(50) NOT NULL,
  duration             VARCHAR(50) NULL,
  training_mode        VARCHAR(20) NOT NULL DEFAULT 'PHYSICAL',
  venue                VARCHAR(255) NULL,
  start_date           DATE NULL,
  end_date             DATE NULL,
  application_deadline DATE NULL,
  certificate_type     VARCHAR(100) NULL,
  fee                  DECIMAL(12,2) NOT NULL,
  image_url            VARCHAR(500) NULL,
  status               VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
  accept_applications  TINYINT(1) NOT NULL DEFAULT 1,
  created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_courses_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE training_applications (
  id                  VARCHAR(40) NOT NULL PRIMARY KEY,
  application_no      VARCHAR(50) NOT NULL UNIQUE,
  student_id          VARCHAR(40) NOT NULL,
  course_id           VARCHAR(40) NOT NULL,
  full_name           VARCHAR(150) NOT NULL,
  date_of_birth       DATE NULL,
  gender              VARCHAR(20) NULL,
  phone               VARCHAR(50) NOT NULL,
  email               VARCHAR(191) NOT NULL,
  address             TEXT NULL,
  state_of_residence  VARCHAR(100) NULL,
  education           VARCHAR(100) NULL,
  previous_experience TEXT NULL,
  photo_url           VARCHAR(500) NULL,
  emergency_contact   VARCHAR(50) NULL,
  declaration         TINYINT(1) NOT NULL DEFAULT 0,
  status              VARCHAR(30) NOT NULL DEFAULT 'SUBMITTED',
  admin_notes         TEXT NULL,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_app_student FOREIGN KEY (student_id) REFERENCES student_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_app_course FOREIGN KEY (course_id) REFERENCES training_courses(id),
  INDEX idx_app_status (status),
  INDEX idx_app_student (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PAYMENTS & RECEIPTS
-- ============================================================

CREATE TABLE payments (
  id               VARCHAR(40) NOT NULL PRIMARY KEY,
  reference_no     VARCHAR(50) NOT NULL UNIQUE,
  student_id       VARCHAR(40) NOT NULL,
  application_id   VARCHAR(40) NULL,
  course_id        VARCHAR(40) NULL,
  amount           DECIMAL(12,2) NOT NULL,
  deposit_date     DATE NULL,
  bank_name        VARCHAR(100) NULL,
  teller_ref       VARCHAR(100) NULL,
  note             TEXT NULL,
  status           VARCHAR(30) NOT NULL DEFAULT 'PENDING_VERIFICATION',
  rejection_reason TEXT NULL,
  verifier_id      VARCHAR(40) NULL,
  verified_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_pay_student FOREIGN KEY (student_id) REFERENCES student_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_pay_app FOREIGN KEY (application_id) REFERENCES training_applications(id) ON DELETE SET NULL,
  CONSTRAINT fk_pay_verifier FOREIGN KEY (verifier_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_pay_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payment_receipts (
  id           VARCHAR(40) NOT NULL PRIMARY KEY,
  payment_id   VARCHAR(40) NOT NULL,
  uploader_id  VARCHAR(40) NOT NULL,
  verifier_id  VARCHAR(40) NULL,
  file_path    VARCHAR(500) NOT NULL,
  file_type    VARCHAR(100) NOT NULL,
  file_size    INT NOT NULL,
  status       VARCHAR(20) NOT NULL DEFAULT 'UPLOADED',
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_receipt_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
  CONSTRAINT fk_receipt_uploader FOREIGN KEY (uploader_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_receipt_verifier FOREIGN KEY (verifier_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_receipts_payment (payment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- NOTIFICATIONS, CERTIFICATES, AUDIT
-- ============================================================

CREATE TABLE notifications (
  id         VARCHAR(40) NOT NULL PRIMARY KEY,
  user_id    VARCHAR(40) NOT NULL,
  type       VARCHAR(50) NOT NULL,
  title      VARCHAR(255) NOT NULL,
  body       TEXT NOT NULL,
  link       VARCHAR(255) NULL,
  is_read    TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_notif_user_read (user_id, is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE certificates (
  id             VARCHAR(40) NOT NULL PRIMARY KEY,
  student_id     VARCHAR(40) NOT NULL,
  course_id      VARCHAR(40) NOT NULL,
  application_id VARCHAR(40) NULL,
  certificate_no VARCHAR(50) NOT NULL UNIQUE,
  issue_date     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  file_url       VARCHAR(500) NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_cert_student FOREIGN KEY (student_id) REFERENCES student_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_cert_course FOREIGN KEY (course_id) REFERENCES training_courses(id),
  CONSTRAINT fk_cert_app FOREIGN KEY (application_id) REFERENCES training_applications(id) ON DELETE SET NULL,
  INDEX idx_cert_student (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
  id          VARCHAR(40) NOT NULL PRIMARY KEY,
  actor_id    VARCHAR(40) NULL,
  action      VARCHAR(100) NOT NULL,
  target_type VARCHAR(50) NULL,
  target_id   VARCHAR(40) NULL,
  detail      TEXT NULL,
  ip_address  VARCHAR(50) NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_actor FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_audit_action (action),
  INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;