-- ============================================================
-- Seoul Educamp International - MySQL schema
-- Target: MySQL 8+ (also tested on MariaDB 10.11). Import via phpMyAdmin.
-- Charset: utf8mb4 (Bangla + Korean support)
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET time_zone = '+06:00';

-- ---------- RBAC ----------
CREATE TABLE IF NOT EXISTS roles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL,
  is_staff TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(60) NOT NULL UNIQUE,
  label VARCHAR(120) NOT NULL,
  module VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id INT UNSIGNED NOT NULL,
  permission_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_rp_perm FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Users ----------
CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id INT UNSIGNED NOT NULL,
  full_name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(30) NULL,
  whatsapp VARCHAR(30) NULL,
  password_hash VARCHAR(255) NOT NULL,
  email_verified_at DATETIME NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  must_change_password TINYINT(1) NOT NULL DEFAULT 0,
  last_login_at DATETIME NULL,
  failed_logins INT UNSIGNED NOT NULL DEFAULT 0,
  locked_until DATETIME NULL,
  avatar VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_users_role (role_id),
  INDEX idx_users_phone (phone),
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_tokens (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  type ENUM('verify_email','reset_password') NOT NULL,
  token_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_tok_hash (token_hash),
  CONSTRAINT fk_tok_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rate_limits (
  rkey VARCHAR(190) NOT NULL PRIMARY KEY,
  hits INT UNSIGNED NOT NULL DEFAULT 0,
  window_start INT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS staff_profiles (
  user_id INT UNSIGNED PRIMARY KEY,
  job_title VARCHAR(120) NULL,
  department VARCHAR(80) NULL,
  bio TEXT NULL,
  is_counselor TINYINT(1) NOT NULL DEFAULT 0,
  CONSTRAINT fk_staff_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Student data ----------
CREATE TABLE IF NOT EXISTS student_profiles (
  user_id INT UNSIGNED PRIMARY KEY,
  student_code VARCHAR(20) NOT NULL UNIQUE,
  date_of_birth DATE NULL,
  gender ENUM('male','female','other') NULL,
  nationality VARCHAR(80) NULL DEFAULT 'Bangladeshi',
  address TEXT NULL,
  city VARCHAR(80) NULL,
  pref_university_id INT UNSIGNED NULL,
  pref_program VARCHAR(190) NULL,
  pref_degree_level VARCHAR(40) NULL,
  pref_city VARCHAR(80) NULL,
  pref_intake VARCHAR(60) NULL,
  pref_language VARCHAR(40) NULL,
  assigned_staff_id INT UNSIGNED NULL,
  source ENUM('website','inquiry','admin') NOT NULL DEFAULT 'website',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_sp_staff (assigned_staff_id),
  CONSTRAINT fk_sp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_sp_staff FOREIGN KEY (assigned_staff_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_academic_records (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  level ENUM('ssc','hsc','bachelor','master') NOT NULL,
  institution VARCHAR(190) NULL,
  board_or_university VARCHAR(190) NULL,
  group_or_major VARCHAR(120) NULL,
  passing_year SMALLINT UNSIGNED NULL,
  gpa VARCHAR(20) NULL,
  gpa_scale VARCHAR(10) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_acad (user_id, level),
  CONSTRAINT fk_acad_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_passport_records (
  user_id INT UNSIGNED PRIMARY KEY,
  passport_number VARCHAR(30) NULL,
  issue_date DATE NULL,
  expiry_date DATE NULL,
  place_of_issue VARCHAR(100) NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_pass_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_sponsor_records (
  user_id INT UNSIGNED PRIMARY KEY,
  sponsor_name VARCHAR(150) NULL,
  relationship VARCHAR(60) NULL,
  occupation VARCHAR(120) NULL,
  phone VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  address TEXT NULL,
  annual_income DECIMAL(14,2) NULL,
  bank_balance DECIMAL(14,2) NULL,
  financial_notes TEXT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_spon_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Destination countries ----------
CREATE TABLE IF NOT EXISTS countries (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  name_bn VARCHAR(120) NULL,
  iso2 CHAR(2) NOT NULL COMMENT 'ISO 3166-1 alpha-2, used to draw the flag',
  currency CHAR(3) NOT NULL DEFAULT 'USD' COMMENT 'tuition currency for this country',
  short_description VARCHAR(400) NULL,
  short_description_bn VARCHAR(600) NULL,
  overview TEXT NULL,
  overview_bn TEXT NULL,
  why_study TEXT NULL COMMENT 'one reason per line',
  tuition_info TEXT NULL,
  scholarship_info TEXT NULL,
  admission_requirements TEXT NULL,
  intake_info TEXT NULL,
  visa_guidance TEXT NULL,
  application_process TEXT NULL COMMENT 'one step per line',
  banner VARCHAR(255) NULL,
  data_verified TINYINT(1) NOT NULL DEFAULT 0,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_country_pub (is_published, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Universities / programs ----------
CREATE TABLE IF NOT EXISTS universities (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(190) NOT NULL UNIQUE,
  country_id INT UNSIGNED NULL,
  name VARCHAR(190) NOT NULL,
  name_bn VARCHAR(190) NULL,
  name_ko VARCHAR(190) NULL,
  category ENUM('National','Public','Private','Research') NOT NULL DEFAULT 'Private',
  city VARCHAR(80) NOT NULL,
  province VARCHAR(80) NULL,
  address VARCHAR(255) NULL,
  short_description VARCHAR(400) NULL,
  short_description_bn VARCHAR(600) NULL,
  description MEDIUMTEXT NULL,
  description_bn MEDIUMTEXT NULL,
  campus_info TEXT NULL,
  admission_requirements TEXT NULL,
  language_requirements TEXT NULL,
  required_documents TEXT NULL,
  scholarship_info TEXT NULL,
  application_deadline DATE NULL,
  tuition_min DECIMAL(14,0) NULL,
  tuition_max DECIMAL(14,0) NULL,
  tuition_note VARCHAR(190) NULL,
  website_url VARCHAR(255) NULL,
  established_year SMALLINT UNSIGNED NULL,
  logo VARCHAR(255) NULL,
  banner VARCHAR(255) NULL,
  data_verified TINYINT(1) NOT NULL DEFAULT 0,
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  meta_title VARCHAR(190) NULL,
  meta_description VARCHAR(300) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_uni_city (city),
  INDEX idx_uni_country (country_id),
  CONSTRAINT fk_uni_country FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE SET NULL,
  INDEX idx_uni_pub (is_published, deleted_at),
  FULLTEXT KEY ft_uni (name, short_description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS programs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  university_id INT UNSIGNED NOT NULL,
  slug VARCHAR(190) NOT NULL,
  name VARCHAR(190) NOT NULL,
  name_bn VARCHAR(190) NULL,
  degree_level ENUM('Korean Language','Diploma','Bachelor','Master','PhD') NOT NULL DEFAULT 'Bachelor',
  field VARCHAR(100) NULL,
  duration VARCHAR(60) NULL,
  tuition_fee DECIMAL(14,0) NULL,
  tuition_period VARCHAR(30) NOT NULL DEFAULT 'per semester',
  language VARCHAR(40) NOT NULL DEFAULT 'English',
  intake_months VARCHAR(100) NULL,
  description TEXT NULL,
  description_bn TEXT NULL,
  eligibility TEXT NULL,
  eligibility_bn TEXT NULL,
  data_verified TINYINT(1) NOT NULL DEFAULT 0,
  is_popular TINYINT(1) NOT NULL DEFAULT 0,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  UNIQUE KEY uq_prog_slug (slug),
  INDEX idx_prog_uni (university_id),
  INDEX idx_prog_level (degree_level),
  FULLTEXT KEY ft_prog (name, field),
  CONSTRAINT fk_prog_uni FOREIGN KEY (university_id) REFERENCES universities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS university_intakes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  university_id INT UNSIGNED NOT NULL,
  intake_name VARCHAR(60) NOT NULL,
  intake_year SMALLINT UNSIGNED NOT NULL,
  start_month VARCHAR(20) NULL,
  application_deadline DATE NULL,
  is_open TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_intake_uni (university_id),
  CONSTRAINT fk_intake_uni FOREIGN KEY (university_id) REFERENCES universities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scholarships (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  university_id INT UNSIGNED NULL,
  name VARCHAR(190) NOT NULL,
  name_bn VARCHAR(190) NULL,
  coverage VARCHAR(255) NULL,
  eligibility TEXT NULL,
  description TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_sch_uni (university_id),
  CONSTRAINT fk_sch_uni FOREIGN KEY (university_id) REFERENCES universities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Applications ----------
CREATE TABLE IF NOT EXISTS applications (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference_id VARCHAR(24) NOT NULL UNIQUE,
  tracking_code_hash VARCHAR(255) NOT NULL,
  tracking_code_enc VARCHAR(255) NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  university_id INT UNSIGNED NOT NULL,
  program_id INT UNSIGNED NULL,
  intake_id INT UNSIGNED NULL,
  intake_label VARCHAR(80) NULL,
  status ENUM('draft','submitted','under_review','documents_required','ready_for_university','submitted_to_university','conditional_offer','offer_received','tuition_payment_pending','tuition_paid','visa_documentation','visa_submitted','visa_approved','visa_refused','completed','withdrawn') NOT NULL DEFAULT 'draft',
  public_update VARCHAR(500) NULL,
  statement_of_purpose TEXT NULL,
  study_plan TEXT NULL,
  assigned_staff_id INT UNSIGNED NULL,
  admission_result ENUM('pending','conditional','offered','rejected') NOT NULL DEFAULT 'pending',
  visa_status ENUM('not_applicable','not_started','documentation','submitted','approved','refused') NOT NULL DEFAULT 'not_applicable',
  submitted_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_app_user (user_id),
  INDEX idx_app_status (status),
  INDEX idx_app_uni (university_id),
  INDEX idx_app_staff (assigned_staff_id),
  CONSTRAINT fk_app_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_app_uni FOREIGN KEY (university_id) REFERENCES universities(id),
  CONSTRAINT fk_app_prog FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE SET NULL,
  CONSTRAINT fk_app_intake FOREIGN KEY (intake_id) REFERENCES university_intakes(id) ON DELETE SET NULL,
  CONSTRAINT fk_app_staff FOREIGN KEY (assigned_staff_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS application_status_history (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  application_id INT UNSIGNED NOT NULL,
  previous_status VARCHAR(40) NULL,
  new_status VARCHAR(40) NOT NULL,
  changed_by INT UNSIGNED NULL,
  comment VARCHAR(500) NULL,
  is_public TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ash_app (application_id),
  CONSTRAINT fk_ash_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
  CONSTRAINT fk_ash_user FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS application_notes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  application_id INT UNSIGNED NULL,
  student_id INT UNSIGNED NULL,
  note_type ENUM('general','counselor','admission','visa') NOT NULL DEFAULT 'general',
  note TEXT NOT NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_an_app (application_id),
  INDEX idx_an_student (student_id),
  CONSTRAINT fk_an_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
  CONSTRAINT fk_an_student FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_an_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Documents ----------
CREATE TABLE IF NOT EXISTS document_types (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(60) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  name_bn VARCHAR(160) NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 0,
  allow_multiple TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_documents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_id INT UNSIGNED NOT NULL,
  application_id INT UNSIGNED NULL,
  document_type_id INT UNSIGNED NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(80) NOT NULL UNIQUE,
  mime_type VARCHAR(100) NOT NULL,
  file_size INT UNSIGNED NOT NULL,
  status ENUM('uploaded','pending_review','approved','rejected','resubmission_required') NOT NULL DEFAULT 'pending_review',
  reviewer_id INT UNSIGNED NULL,
  review_comment VARCHAR(500) NULL,
  reviewed_at DATETIME NULL,
  replaced_by INT UNSIGNED NULL,
  uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_doc_student (student_id),
  INDEX idx_doc_app (application_id),
  INDEX idx_doc_status (status),
  CONSTRAINT fk_doc_student FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_doc_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE SET NULL,
  CONSTRAINT fk_doc_type FOREIGN KEY (document_type_id) REFERENCES document_types(id),
  CONSTRAINT fk_doc_reviewer FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS document_review_history (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  document_id INT UNSIGNED NOT NULL,
  previous_status VARCHAR(40) NULL,
  new_status VARCHAR(40) NOT NULL,
  reviewer_id INT UNSIGNED NULL,
  comment VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_drh_doc (document_id),
  CONSTRAINT fk_drh_doc FOREIGN KEY (document_id) REFERENCES student_documents(id) ON DELETE CASCADE,
  CONSTRAINT fk_drh_user FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Appointments ----------
CREATE TABLE IF NOT EXISTS counselor_availability (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  staff_id INT UNSIGNED NOT NULL,
  weekday TINYINT UNSIGNED NOT NULL COMMENT '0=Sunday..6=Saturday',
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  slot_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 30,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  INDEX idx_ca_staff (staff_id),
  CONSTRAINT fk_ca_staff FOREIGN KEY (staff_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS appointments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ref_no VARCHAR(24) NOT NULL UNIQUE,
  user_id INT UNSIGNED NULL,
  guest_name VARCHAR(150) NULL,
  guest_phone VARCHAR(30) NULL,
  guest_email VARCHAR(190) NULL,
  counselor_id INT UNSIGNED NULL,
  start_at DATETIME NOT NULL,
  end_at DATETIME NOT NULL,
  mode ENUM('online','office','phone') NOT NULL DEFAULT 'online',
  topic VARCHAR(190) NULL,
  status ENUM('pending','confirmed','cancelled','completed','rescheduled','no_show') NOT NULL DEFAULT 'pending',
  meeting_link VARCHAR(255) NULL,
  admin_notes TEXT NULL,
  reminder_sent_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_appt_user (user_id),
  INDEX idx_appt_start (start_at),
  INDEX idx_appt_counselor (counselor_id, start_at),
  CONSTRAINT fk_appt_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_appt_counselor FOREIGN KEY (counselor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Inquiry CRM ----------
CREATE TABLE IF NOT EXISTS inquiries (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ref_no VARCHAR(24) NOT NULL UNIQUE,
  full_name VARCHAR(150) NOT NULL,
  phone VARCHAR(30) NOT NULL,
  whatsapp VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  interested_degree VARCHAR(40) NULL,
  preferred_university VARCHAR(190) NULL,
  preferred_university_id INT UNSIGNED NULL,
  intended_intake VARCHAR(60) NULL,
  message TEXT NULL,
  source ENUM('whatsapp_drawer','contact_page','counseling_page','other') NOT NULL DEFAULT 'contact_page',
  status ENUM('new','contacted','counseling_scheduled','interested','follow_up_required','converted','not_interested') NOT NULL DEFAULT 'new',
  assigned_staff_id INT UNSIGNED NULL,
  next_followup_at DATETIME NULL,
  converted_user_id INT UNSIGNED NULL,
  converted_application_id INT UNSIGNED NULL,
  ip_address VARCHAR(45) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_inq_status (status),
  INDEX idx_inq_staff (assigned_staff_id),
  INDEX idx_inq_phone (phone),
  CONSTRAINT fk_inq_staff FOREIGN KEY (assigned_staff_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_inq_user FOREIGN KEY (converted_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inquiry_notes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  inquiry_id INT UNSIGNED NOT NULL,
  note TEXT NOT NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_in_inq (inquiry_id),
  CONSTRAINT fk_in_inq FOREIGN KEY (inquiry_id) REFERENCES inquiries(id) ON DELETE CASCADE,
  CONSTRAINT fk_in_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inquiry_followups (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  inquiry_id INT UNSIGNED NOT NULL,
  due_at DATETIME NOT NULL,
  remarks VARCHAR(255) NULL,
  is_done TINYINT(1) NOT NULL DEFAULT 0,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_if_inq (inquiry_id),
  INDEX idx_if_due (due_at),
  CONSTRAINT fk_if_inq FOREIGN KEY (inquiry_id) REFERENCES inquiries(id) ON DELETE CASCADE,
  CONSTRAINT fk_if_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Notifications ----------
CREATE TABLE IF NOT EXISTS notifications (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  type VARCHAR(40) NOT NULL,
  title VARCHAR(190) NOT NULL,
  body VARCHAR(500) NULL,
  url VARCHAR(255) NULL,
  read_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_notif_user (user_id, read_at),
  CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Finance ----------
CREATE TABLE IF NOT EXISTS payments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_id INT UNSIGNED NOT NULL,
  application_id INT UNSIGNED NULL,
  description VARCHAR(190) NOT NULL DEFAULT 'Consultancy service fee',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  discount DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_pay_student (student_id),
  CONSTRAINT fk_pay_student FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_pay_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE SET NULL,
  CONSTRAINT fk_pay_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_transactions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_id INT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  method ENUM('cash','bank_transfer','bkash','nagad','other') NOT NULL DEFAULT 'cash',
  reference_no VARCHAR(100) NULL,
  paid_at DATETIME NOT NULL,
  notes VARCHAR(255) NULL,
  received_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_pt_pay (payment_id),
  CONSTRAINT fk_pt_pay FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
  CONSTRAINT fk_pt_user FOREIGN KEY (received_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS receipts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  receipt_no VARCHAR(30) NOT NULL UNIQUE,
  transaction_id INT UNSIGNED NOT NULL UNIQUE,
  issued_by INT UNSIGNED NULL,
  issued_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_rc_tx FOREIGN KEY (transaction_id) REFERENCES payment_transactions(id) ON DELETE CASCADE,
  CONSTRAINT fk_rc_user FOREIGN KEY (issued_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS refunds (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_id INT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  reason VARCHAR(255) NULL,
  method ENUM('cash','bank_transfer','bkash','nagad','other') NOT NULL DEFAULT 'cash',
  refunded_at DATETIME NOT NULL,
  processed_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ref_pay (payment_id),
  CONSTRAINT fk_ref_pay FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
  CONSTRAINT fk_ref_user FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Staff tasks ----------
CREATE TABLE IF NOT EXISTS staff_tasks (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  assigned_to INT UNSIGNED NOT NULL,
  student_id INT UNSIGNED NULL,
  application_id INT UNSIGNED NULL,
  title VARCHAR(190) NOT NULL,
  details TEXT NULL,
  due_date DATE NULL,
  priority ENUM('low','normal','high') NOT NULL DEFAULT 'normal',
  status ENUM('open','in_progress','done') NOT NULL DEFAULT 'open',
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_task_assignee (assigned_to, status),
  CONSTRAINT fk_task_to FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_task_student FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_task_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE SET NULL,
  CONSTRAINT fk_task_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- CMS ----------
CREATE TABLE IF NOT EXISTS testimonials (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_name VARCHAR(120) NOT NULL,
  university VARCHAR(190) NULL,
  program VARCHAR(190) NULL,
  quote TEXT NOT NULL,
  quote_bn TEXT NULL,
  photo VARCHAR(255) NULL,
  consent_confirmed TINYINT(1) NOT NULL DEFAULT 0,
  is_published TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS blog_categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  name_bn VARCHAR(160) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS blog_posts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NULL,
  slug VARCHAR(190) NOT NULL UNIQUE,
  title VARCHAR(220) NOT NULL,
  title_bn VARCHAR(260) NULL,
  excerpt VARCHAR(400) NULL,
  excerpt_bn VARCHAR(600) NULL,
  content MEDIUMTEXT NOT NULL,
  content_bn MEDIUMTEXT NULL,
  image VARCHAR(255) NULL,
  status ENUM('draft','published') NOT NULL DEFAULT 'draft',
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  author_id INT UNSIGNED NULL,
  published_at DATETIME NULL,
  meta_title VARCHAR(190) NULL,
  meta_description VARCHAR(300) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_blog_status (status, published_at),
  FULLTEXT KEY ft_blog (title, excerpt),
  CONSTRAINT fk_blog_cat FOREIGN KEY (category_id) REFERENCES blog_categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_blog_author FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS faq_categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  name_bn VARCHAR(160) NULL,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS faqs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NULL,
  question VARCHAR(300) NOT NULL,
  question_bn VARCHAR(400) NULL,
  answer TEXT NOT NULL,
  answer_bn TEXT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  show_on_home TINYINT(1) NOT NULL DEFAULT 0,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_faq_cat FOREIGN KEY (category_id) REFERENCES faq_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS services (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(120) NOT NULL UNIQUE,
  title VARCHAR(160) NOT NULL,
  title_bn VARCHAR(200) NULL,
  icon VARCHAR(60) NOT NULL DEFAULT 'bi-mortarboard',
  summary VARCHAR(300) NULL,
  summary_bn VARCHAR(500) NULL,
  description TEXT NULL,
  description_bn TEXT NULL,
  process_steps TEXT NULL COMMENT 'one step per line',
  process_steps_bn TEXT NULL,
  requirements TEXT NULL COMMENT 'one item per line',
  requirements_bn TEXT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_published TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_members (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  name_bn VARCHAR(160) NULL,
  position VARCHAR(120) NULL,
  position_bn VARCHAR(160) NULL,
  bio TEXT NULL,
  bio_bn TEXT NULL,
  photo VARCHAR(255) NULL,
  whatsapp_contact_id INT UNSIGNED NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_published TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_blocks (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  block_key VARCHAR(60) NOT NULL UNIQUE,
  label VARCHAR(120) NOT NULL,
  title VARCHAR(250) NULL,
  title_bn VARCHAR(300) NULL,
  subtitle VARCHAR(500) NULL,
  subtitle_bn VARCHAR(700) NULL,
  body TEXT NULL,
  body_bn TEXT NULL,
  image VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS website_settings (
  skey VARCHAR(80) NOT NULL PRIMARY KEY,
  svalue TEXT NULL,
  sgroup VARCHAR(40) NOT NULL DEFAULT 'general',
  label VARCHAR(160) NULL,
  input_type VARCHAR(20) NOT NULL DEFAULT 'text',
  is_secret TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- WhatsApp ----------
CREATE TABLE IF NOT EXISTS whatsapp_contacts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  role_label VARCHAR(120) NULL,
  phone VARCHAR(30) NOT NULL COMMENT 'digits with country code, e.g. 8801XXXXXXXXX',
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS whatsapp_messages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  msg_key VARCHAR(40) NOT NULL UNIQUE,
  label VARCHAR(120) NOT NULL,
  message_text VARCHAR(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS whatsapp_click_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contact_id INT UNSIGNED NULL,
  context VARCHAR(60) NULL,
  page_url VARCHAR(255) NULL,
  message_preview VARCHAR(255) NULL,
  user_id INT UNSIGNED NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_wcl_date (created_at),
  CONSTRAINT fk_wcl_contact FOREIGN KEY (contact_id) REFERENCES whatsapp_contacts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Logs ----------
CREATE TABLE IF NOT EXISTS activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  student_id INT UNSIGNED NULL,
  action VARCHAR(60) NOT NULL,
  entity VARCHAR(40) NULL,
  entity_id INT UNSIGNED NULL,
  description VARCHAR(500) NULL,
  ip_address VARCHAR(45) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_al_student (student_id, created_at),
  INDEX idx_al_user (user_id),
  INDEX idx_al_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  to_email VARCHAR(190) NOT NULL,
  subject VARCHAR(250) NOT NULL,
  status ENUM('sent','failed','not_configured') NOT NULL,
  error_message VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_el_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_id INT UNSIGNED NULL,
  inquiry_id INT UNSIGNED NULL,
  channel ENUM('website_inquiry','whatsapp_click','whatsapp_manual','whatsapp_api','phone','email','message','other') NOT NULL DEFAULT 'message',
  direction ENUM('inbound','outbound') NOT NULL DEFAULT 'outbound',
  subject VARCHAR(190) NULL,
  message TEXT NULL,
  visible_to_student TINYINT(1) NOT NULL DEFAULT 0,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_cl_student (student_id),
  INDEX idx_cl_inquiry (inquiry_id),
  CONSTRAINT fk_cl_student FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_cl_inq FOREIGN KEY (inquiry_id) REFERENCES inquiries(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
