-- =====================================================================
-- LarCare — base schema
-- Source: docs/02-DATABASE-SCHEMA.md §3
-- =====================================================================
SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- ---------- Site configuration (single row, id = 1) ----------
CREATE TABLE IF NOT EXISTS site_settings (
  id            TINYINT UNSIGNED NOT NULL PRIMARY KEY DEFAULT 1,
  company_name  VARCHAR(120)  NOT NULL,
  tagline       VARCHAR(255)  NOT NULL,
  phone         VARCHAR(32)   NOT NULL,
  email         VARCHAR(160)  NOT NULL,
  address       VARCHAR(255)  NOT NULL,
  states_served JSON          NOT NULL,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
                              ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT chk_settings_singleton CHECK (id = 1)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS social_links (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  platform   VARCHAR(40)  NOT NULL,
  url        VARCHAR(500) NOT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  is_active  TINYINT(1)   NOT NULL DEFAULT 1,
  UNIQUE KEY uq_social_platform (platform)
) ENGINE=InnoDB;

-- Navigation: parent_id NULL = top level, else a dropdown child.
-- `location` separates the header mega-menu from footer columns.
CREATE TABLE IF NOT EXISTS nav_items (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  parent_id  BIGINT UNSIGNED NULL,
  location   ENUM('header','footer') NOT NULL DEFAULT 'header',
  label      VARCHAR(120) NOT NULL,
  href       VARCHAR(255) NOT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  is_active  TINYINT(1)   NOT NULL DEFAULT 1,
  CONSTRAINT fk_nav_parent FOREIGN KEY (parent_id)
    REFERENCES nav_items (id) ON DELETE CASCADE,
  KEY idx_nav_lookup (location, parent_id, sort_order)
) ENGINE=InnoDB;

-- ---------- Care services ----------
CREATE TABLE IF NOT EXISTS care_services (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid              CHAR(36)     NOT NULL,
  slug              VARCHAR(160) NOT NULL,
  title             VARCHAR(200) NOT NULL,
  short_description VARCHAR(500) NOT NULL,
  hero_image        VARCHAR(500) NULL,
  body_html         MEDIUMTEXT   NOT NULL,
  sort_order        SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  featured_on_home  TINYINT(1)   NOT NULL DEFAULT 0,
  status            ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  meta_title        VARCHAR(200) NULL,
  meta_description  VARCHAR(320) NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP,
  deleted_at        DATETIME NULL,
  UNIQUE KEY uq_service_uuid (uuid),
  UNIQUE KEY uq_service_slug (slug),
  KEY idx_service_published (status, deleted_at, sort_order)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS care_service_highlights (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  service_id BIGINT UNSIGNED NOT NULL,
  label      VARCHAR(255) NOT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  CONSTRAINT fk_highlight_service FOREIGN KEY (service_id)
    REFERENCES care_services (id) ON DELETE CASCADE,
  KEY idx_highlight_service (service_id, sort_order)
) ENGINE=InnoDB;

-- ---------- Blog ----------
CREATE TABLE IF NOT EXISTS blog_posts (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid             CHAR(36)     NOT NULL,
  slug             VARCHAR(200) NOT NULL,
  title            VARCHAR(255) NOT NULL,
  excerpt          VARCHAR(600) NOT NULL,
  cover_image      VARCHAR(500) NULL,
  body_html        MEDIUMTEXT   NOT NULL,
  author           VARCHAR(160) NOT NULL DEFAULT 'LarCare Services',
  published_at     DATE         NULL,
  status           ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  meta_title       VARCHAR(200) NULL,
  meta_description VARCHAR(320) NULL,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                            ON UPDATE CURRENT_TIMESTAMP,
  deleted_at       DATETIME NULL,
  UNIQUE KEY uq_post_uuid (uuid),
  UNIQUE KEY uq_post_slug (slug),
  KEY idx_post_feed (status, deleted_at, published_at DESC)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tags (
  id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(80)  NOT NULL,
  name VARCHAR(120) NOT NULL,
  UNIQUE KEY uq_tag_slug (slug)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS blog_post_tags (
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  CONSTRAINT fk_bpt_post FOREIGN KEY (post_id)
    REFERENCES blog_posts (id) ON DELETE CASCADE,
  CONSTRAINT fk_bpt_tag FOREIGN KEY (tag_id)
    REFERENCES tags (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- Products (affiliate / referral catalogue) ----------
CREATE TABLE IF NOT EXISTS products (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid         CHAR(36)     NOT NULL,
  slug         VARCHAR(160) NOT NULL,
  name         VARCHAR(200) NOT NULL,
  description  VARCHAR(1000) NOT NULL,
  image        VARCHAR(500) NULL,
  price        DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  currency     CHAR(3)      NOT NULL DEFAULT 'USD',
  external_url VARCHAR(1000) NOT NULL,
  sort_order   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  status       ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                        ON UPDATE CURRENT_TIMESTAMP,
  deleted_at   DATETIME NULL,
  UNIQUE KEY uq_product_uuid (uuid),
  UNIQUE KEY uq_product_slug (slug),
  KEY idx_product_published (status, deleted_at, sort_order)
) ENGINE=InnoDB;

-- ---------- Small content collections ----------
CREATE TABLE IF NOT EXISTS testimonials (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid        CHAR(36)     NOT NULL,
  author_name VARCHAR(160) NOT NULL,
  rating      TINYINT UNSIGNED NOT NULL DEFAULT 5,
  quote       VARCHAR(1000) NOT NULL,
  sort_order  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  status      ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at  DATETIME NULL,
  UNIQUE KEY uq_testimonial_uuid (uuid),
  CONSTRAINT chk_rating CHECK (rating BETWEEN 1 AND 5)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS core_values (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  icon        VARCHAR(60)  NOT NULL,
  title       VARCHAR(160) NOT NULL,
  description VARCHAR(1000) NOT NULL,
  sort_order  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  is_active   TINYINT(1)   NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS about_stats (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  icon       VARCHAR(60)  NOT NULL,
  title      VARCHAR(255) NOT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  is_active  TINYINT(1)   NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS job_openings (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid       CHAR(36)     NOT NULL,
  slug       VARCHAR(160) NOT NULL,
  title      VARCHAR(200) NOT NULL,
  employment_type VARCHAR(80) NOT NULL,
  location   VARCHAR(160) NOT NULL,
  summary    VARCHAR(1000) NOT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  status     ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  UNIQUE KEY uq_job_uuid (uuid),
  UNIQUE KEY uq_job_slug (slug)
) ENGINE=InnoDB;

-- ---------- Inbound submissions ----------
-- PII lives here. See docs/02-DATABASE-SCHEMA.md §6 for retention and access notes.
CREATE TABLE IF NOT EXISTS contact_submissions (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid         CHAR(36)     NOT NULL,
  first_name   VARCHAR(120) NOT NULL,
  last_name    VARCHAR(120) NOT NULL,
  email        VARCHAR(320) NOT NULL,
  phone        VARCHAR(40)  NULL,
  message      TEXT         NOT NULL,
  attachment_path VARCHAR(500) NULL,
  source_page  VARCHAR(255) NULL,
  ip_hash      CHAR(64)     NULL,
  user_agent   VARCHAR(500) NULL,
  status       ENUM('new','in_progress','closed','spam') NOT NULL DEFAULT 'new',
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_contact_uuid (uuid),
  KEY idx_contact_triage (status, created_at DESC),
  KEY idx_contact_throttle (ip_hash, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sms_consents (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid          CHAR(36)     NOT NULL,
  first_name    VARCHAR(120) NOT NULL,
  last_name     VARCHAR(120) NOT NULL,
  email         VARCHAR(320) NOT NULL,
  phone         VARCHAR(40)  NOT NULL,
  consent_given TINYINT(1)   NOT NULL,
  consent_text  TEXT         NOT NULL,
  ip_hash       CHAR(64)     NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at    DATETIME NULL,
  UNIQUE KEY uq_sms_uuid (uuid),
  KEY idx_sms_phone (phone, created_at DESC)
) ENGINE=InnoDB;

-- ---------- Audit ----------
CREATE TABLE IF NOT EXISTS audit_log (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  entity      VARCHAR(80)  NOT NULL,
  entity_uuid CHAR(36)     NULL,
  action      VARCHAR(40)  NOT NULL,
  actor       VARCHAR(160) NOT NULL DEFAULT 'system',
  detail      JSON         NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_entity (entity, entity_uuid, created_at DESC)
) ENGINE=InnoDB;