-- NAC website — initial schema. MySQL 5.7+ / MariaDB 10.3+, InnoDB, utf8mb4.
-- Times are stored in UTC; the app displays them in Africa/Addis_Ababa.

-- ── Admin accounts & security ───────────────────────────────────────────────
CREATE TABLE roles (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  is_super    TINYINT(1)   NOT NULL DEFAULT 0,  -- super admin: every permission, cannot be edited
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_roles_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE role_permissions (
  role_id    INT UNSIGNED NOT NULL,
  permission VARCHAR(64)  NOT NULL,
  PRIMARY KEY (role_id, permission),
  CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE admin_users (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name            VARCHAR(150) NOT NULL,
  username             VARCHAR(64)  NOT NULL,
  email                VARCHAR(191) NULL,
  phone                VARCHAR(32)  NULL,
  password_hash        VARCHAR(255) NOT NULL,
  role_id              INT UNSIGNED NOT NULL,
  is_active            TINYINT(1)   NOT NULL DEFAULT 1,
  must_change_password TINYINT(1)   NOT NULL DEFAULT 1,
  failed_logins        INT UNSIGNED NOT NULL DEFAULT 0,
  locked_until         DATETIME     NULL,
  last_login_at        DATETIME     NULL,
  last_login_ip        VARCHAR(45)  NULL,
  password_changed_at  DATETIME     NULL,
  created_by           INT UNSIGNED NULL,
  created_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_admin_users_username (username),
  UNIQUE KEY uq_admin_users_email (email),
  CONSTRAINT fk_admin_users_role FOREIGN KEY (role_id) REFERENCES roles (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The cookie holds a random token; only its SHA-256 is stored here.
CREATE TABLE admin_sessions (
  id           CHAR(64)     NOT NULL PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at   DATETIME     NOT NULL,
  ip           VARCHAR(45)  NULL,
  user_agent   VARCHAR(255) NULL,
  KEY idx_admin_sessions_user (user_id),
  KEY idx_admin_sessions_expires (expires_at),
  CONSTRAINT fk_admin_sessions_user FOREIGN KEY (user_id) REFERENCES admin_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE login_attempts (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username   VARCHAR(64) NULL,
  ip         VARCHAR(45) NOT NULL,
  success    TINYINT(1)  NOT NULL DEFAULT 0,
  created_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_login_attempts_ip (ip, created_at),
  KEY idx_login_attempts_username (username, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_log (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NULL,
  user_name   VARCHAR(150) NULL,
  action      VARCHAR(64)  NOT NULL,
  entity_type VARCHAR(64)  NULL,
  entity_id   VARCHAR(64)  NULL,
  summary     VARCHAR(255) NULL,
  details     TEXT         NULL,
  ip          VARCHAR(45)  NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_log_created (created_at),
  KEY idx_audit_log_entity (entity_type, entity_id),
  KEY idx_audit_log_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Site-wide settings & uploaded files ─────────────────────────────────────
CREATE TABLE settings (
  `key`      VARCHAR(64) NOT NULL PRIMARY KEY,
  value      TEXT        NULL,
  updated_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE media (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  path          VARCHAR(255) NOT NULL,          -- relative to STORAGE_DIR/media
  original_name VARCHAR(255) NULL,
  mime_type     VARCHAR(100) NOT NULL,
  size_bytes    INT UNSIGNED NOT NULL DEFAULT 0,
  width         INT UNSIGNED NULL,
  height        INT UNSIGNED NULL,
  alt_text      VARCHAR(255) NULL,
  uploaded_by   INT UNSIGNED NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_media_path (path)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Programs, study modes, fees, intakes ────────────────────────────────────
CREATE TABLE program_categories (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(120) NOT NULL,
  slug         VARCHAR(140) NOT NULL,
  description  TEXT         NULL,
  sort_order   INT          NOT NULL DEFAULT 0,
  is_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,
  UNIQUE KEY uq_program_categories_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE programs (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id          INT UNSIGNED NOT NULL,
  name                 VARCHAR(191) NOT NULL,
  slug                 VARCHAR(191) NOT NULL,
  level                VARCHAR(64)  NOT NULL,       -- Certificate, Level IV, B.A, B.Sc, MA, M.Sc …
  short_description    VARCHAR(500) NULL,
  description          MEDIUMTEXT   NULL,
  career_opportunities TEXT         NULL,
  entry_requirements   TEXT         NULL,
  accreditation        VARCHAR(255) NULL,
  cover_media_id       INT UNSIGNED NULL,
  is_published         TINYINT(1)   NOT NULL DEFAULT 1,
  is_featured          TINYINT(1)   NOT NULL DEFAULT 0,
  sort_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,
  UNIQUE KEY uq_programs_slug (slug),
  KEY idx_programs_category (category_id, sort_order),
  CONSTRAINT fk_programs_category FOREIGN KEY (category_id) REFERENCES program_categories (id),
  CONSTRAINT fk_programs_cover FOREIGN KEY (cover_media_id) REFERENCES media (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One row per way of studying a program (Regular, Extension, Distance …).
CREATE TABLE program_offerings (
  id                        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  program_id                INT UNSIGNED NOT NULL,
  study_mode                VARCHAR(32)  NOT NULL,
  schedule                  VARCHAR(191) NULL,       -- e.g. "Mon–Fri, 8:00–12:00" or "Weekends"
  duration_value            DECIMAL(6,2) NULL,
  duration_unit             VARCHAR(16)  NULL,       -- months, years, terms, semesters, weeks
  duration_months           DECIMAL(6,2) NULL,       -- total length in months, used for payment per month
  credit_hours              DECIMAL(6,2) NULL,
  entry_requirements        TEXT         NULL,       -- overrides the program's requirements when set
  is_accepting_applications TINYINT(1)   NOT NULL DEFAULT 1,
  is_published              TINYINT(1)   NOT NULL DEFAULT 1,
  sort_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,
  UNIQUE KEY uq_program_offerings_mode (program_id, study_mode),
  CONSTRAINT fk_program_offerings_program FOREIGN KEY (program_id) REFERENCES programs (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- basis: per_month, per_credit_hour, per_term, per_semester, per_year, one_time
-- units: how many basis units the whole program has (e.g. 150 credit hours, 8 terms).
CREATE TABLE offering_fees (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  offering_id     INT UNSIGNED  NOT NULL,
  fee_type        VARCHAR(32)   NOT NULL DEFAULT 'tuition',  -- tuition, registration, application, graduation, other
  label           VARCHAR(120)  NOT NULL,
  basis           VARCHAR(32)   NOT NULL,
  amount          DECIMAL(12,2) NOT NULL,
  units           DECIMAL(10,2) NULL,
  monthly_payment DECIMAL(12,2) NULL,                         -- set by admin; otherwise calculated
  currency        CHAR(3)       NOT NULL DEFAULT 'ETB',
  effective_from  DATE          NULL,
  note            VARCHAR(255)  NULL,
  is_published    TINYINT(1)    NOT NULL DEFAULT 1,
  sort_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,
  KEY idx_offering_fees_offering (offering_id, sort_order),
  CONSTRAINT fk_offering_fees_offering FOREIGN KEY (offering_id) REFERENCES program_offerings (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE intakes (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name                 VARCHAR(120) NOT NULL,
  starts_on            DATE         NULL,
  application_deadline DATE         NULL,
  is_open              TINYINT(1)   NOT NULL DEFAULT 1,
  note                 VARCHAR(255) NULL,
  sort_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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Online applications ─────────────────────────────────────────────────────
-- status: new, contacted, no_answer, follow_up, registered, not_qualified, not_interested, cancelled
CREATE TABLE applications (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference_no     VARCHAR(32)  NULL,
  status           VARCHAR(32)  NOT NULL DEFAULT 'new',
  program_id       INT UNSIGNED NOT NULL,
  offering_id      INT UNSIGNED NULL,
  intake_id        INT UNSIGNED NULL,
  program_name     VARCHAR(191) NOT NULL,   -- snapshot at time of applying
  program_level    VARCHAR(64)  NOT NULL,
  study_mode       VARCHAR(32)  NULL,
  first_name       VARCHAR(80)  NOT NULL,
  father_name      VARCHAR(80)  NOT NULL,
  grandfather_name VARCHAR(80)  NULL,
  gender           VARCHAR(10)  NULL,
  date_of_birth    DATE         NULL,
  phone            VARCHAR(32)  NOT NULL,
  alt_phone        VARCHAR(32)  NULL,
  email            VARCHAR(191) NULL,
  city             VARCHAR(120) NULL,
  education_level  VARCHAR(64)  NULL,
  previous_school  VARCHAR(191) NULL,
  completion_year  SMALLINT     NULL,
  exam_result      VARCHAR(64)  NULL,
  how_heard        VARCHAR(64)  NULL,
  message          TEXT         NULL,
  assigned_to      INT UNSIGNED NULL,
  follow_up_on     DATE         NULL,
  last_contacted_at DATETIME    NULL,
  is_possible_duplicate TINYINT(1) NOT NULL DEFAULT 0,
  submitted_ip     VARCHAR(45)  NULL,
  user_agent       VARCHAR(255) NULL,
  deleted_at       DATETIME     NULL,
  created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_applications_reference (reference_no),
  KEY idx_applications_status (status, created_at),
  KEY idx_applications_program (program_id, created_at),
  KEY idx_applications_created (created_at),
  KEY idx_applications_phone (phone),
  KEY idx_applications_assigned (assigned_to),
  KEY idx_applications_ip (submitted_ip, created_at),
  CONSTRAINT fk_applications_program FOREIGN KEY (program_id) REFERENCES programs (id),
  CONSTRAINT fk_applications_offering FOREIGN KEY (offering_id) REFERENCES program_offerings (id) ON DELETE SET NULL,
  CONSTRAINT fk_applications_intake FOREIGN KEY (intake_id) REFERENCES intakes (id) ON DELETE SET NULL,
  CONSTRAINT fk_applications_assigned FOREIGN KEY (assigned_to) REFERENCES admin_users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Files live outside the web root and are only served to signed-in staff.
CREATE TABLE application_documents (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  application_id INT UNSIGNED NOT NULL,
  doc_type       VARCHAR(40)  NOT NULL,
  original_name  VARCHAR(255) NULL,
  stored_path    VARCHAR(255) NOT NULL,
  mime_type      VARCHAR(100) NOT NULL,
  size_bytes     INT UNSIGNED NOT NULL,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_application_documents_app (application_id),
  CONSTRAINT fk_application_documents_app FOREIGN KEY (application_id) REFERENCES applications (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- kind: note, call, status, assign
CREATE TABLE application_activity (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  application_id INT UNSIGNED NOT NULL,
  user_id        INT UNSIGNED NULL,
  user_name      VARCHAR(150) NULL,
  kind           VARCHAR(16)  NOT NULL,
  from_status    VARCHAR(32)  NULL,
  to_status      VARCHAR(32)  NULL,
  note           TEXT         NULL,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_application_activity_app (application_id, created_at),
  CONSTRAINT fk_application_activity_app FOREIGN KEY (application_id) REFERENCES applications (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── News, events, gallery ───────────────────────────────────────────────────
CREATE TABLE news_posts (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title          VARCHAR(255) NOT NULL,
  slug           VARCHAR(191) NOT NULL,
  category       VARCHAR(32)  NOT NULL DEFAULT 'news',   -- news, announcement
  excerpt        VARCHAR(500) NULL,
  body           MEDIUMTEXT   NULL,
  cover_media_id INT UNSIGNED NULL,
  status         VARCHAR(16)  NOT NULL DEFAULT 'draft',  -- draft, published
  is_pinned      TINYINT(1)   NOT NULL DEFAULT 0,
  published_at   DATETIME     NULL,
  created_by     INT UNSIGNED NULL,
  updated_by     INT UNSIGNED NULL,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_news_posts_slug (slug),
  KEY idx_news_posts_published (status, published_at),
  CONSTRAINT fk_news_posts_cover FOREIGN KEY (cover_media_id) REFERENCES media (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE events (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title          VARCHAR(255) NOT NULL,
  slug           VARCHAR(191) NOT NULL,
  category       VARCHAR(40)  NULL,
  starts_at      DATETIME     NOT NULL,
  ends_at        DATETIME     NULL,
  location       VARCHAR(191) NULL,
  description    MEDIUMTEXT   NULL,
  cover_media_id INT UNSIGNED NULL,
  is_published   TINYINT(1)   NOT NULL DEFAULT 1,
  is_featured    TINYINT(1)   NOT NULL DEFAULT 0,
  created_by     INT UNSIGNED NULL,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_events_slug (slug),
  KEY idx_events_starts (is_published, starts_at),
  CONSTRAINT fk_events_cover FOREIGN KEY (cover_media_id) REFERENCES media (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE gallery_albums (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title          VARCHAR(191) NOT NULL,
  slug           VARCHAR(191) NOT NULL,
  description    TEXT         NULL,
  year           SMALLINT     NULL,
  cover_media_id INT UNSIGNED NULL,
  is_published   TINYINT(1)   NOT NULL DEFAULT 1,
  sort_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,
  UNIQUE KEY uq_gallery_albums_slug (slug),
  CONSTRAINT fk_gallery_albums_cover FOREIGN KEY (cover_media_id) REFERENCES media (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE gallery_photos (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  album_id   INT UNSIGNED NOT NULL,
  media_id   INT UNSIGNED NOT NULL,
  caption    VARCHAR(255) NULL,
  sort_order INT          NOT NULL DEFAULT 0,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_gallery_photos_album (album_id, sort_order),
  CONSTRAINT fk_gallery_photos_album FOREIGN KEY (album_id) REFERENCES gallery_albums (id) ON DELETE CASCADE,
  CONSTRAINT fk_gallery_photos_media FOREIGN KEY (media_id) REFERENCES media (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Homepage & page content ─────────────────────────────────────────────────
CREATE TABLE hero_slides (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title        VARCHAR(191) NOT NULL,
  subtitle     VARCHAR(500) NULL,
  button_label VARCHAR(60)  NULL,
  button_link  VARCHAR(255) NULL,
  media_id     INT UNSIGNED NULL,
  is_active    TINYINT(1)   NOT NULL DEFAULT 1,
  sort_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,
  CONSTRAINT fk_hero_slides_media FOREIGN KEY (media_id) REFERENCES media (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fixed blocks of text such as mission, vision, history, CEO message.
CREATE TABLE page_sections (
  `key`      VARCHAR(64)  NOT NULL PRIMARY KEY,
  title      VARCHAR(191) NULL,
  body       MEDIUMTEXT   NULL,
  media_id   INT UNSIGNED NULL,
  updated_by INT UNSIGNED NULL,
  updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_page_sections_media FOREIGN KEY (media_id) REFERENCES media (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE testimonials (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(150) NOT NULL,
  program_label   VARCHAR(191) NULL,
  graduation_year SMALLINT     NULL,
  quote           TEXT         NOT NULL,
  media_id        INT UNSIGNED NULL,
  is_published    TINYINT(1)   NOT NULL DEFAULT 1,
  sort_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,
  CONSTRAINT fk_testimonials_media FOREIGN KEY (media_id) REFERENCES media (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE partners (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(150) NOT NULL,
  description   VARCHAR(500) NULL,
  website_url   VARCHAR(255) NULL,
  logo_media_id INT UNSIGNED NULL,
  is_published  TINYINT(1)   NOT NULL DEFAULT 1,
  sort_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,
  CONSTRAINT fk_partners_logo FOREIGN KEY (logo_media_id) REFERENCES media (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE faqs (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  question     VARCHAR(255) NOT NULL,
  answer       TEXT         NOT NULL,
  category     VARCHAR(64)  NULL,
  is_published TINYINT(1)   NOT NULL DEFAULT 1,
  sort_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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- status: new, read, replied, archived
CREATE TABLE contact_messages (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(150) NOT NULL,
  phone        VARCHAR(32)  NULL,
  email        VARCHAR(191) NULL,
  subject      VARCHAR(191) NULL,
  message      TEXT         NOT NULL,
  status       VARCHAR(16)  NOT NULL DEFAULT 'new',
  handled_by   INT UNSIGNED NULL,
  handled_at   DATETIME     NULL,
  submitted_ip VARCHAR(45)  NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_contact_messages_status (status, created_at),
  KEY idx_contact_messages_ip (submitted_ip, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;