-- ============================================================================
-- DIABEASY  |  Database Schema  v1.0
-- Stack: MySQL 8.0+ / PHP 8.2+
-- Charset: utf8mb4 (Hindi/Marathi/Gujarati content + emoji in WhatsApp)
-- ============================================================================
-- DESIGN NOTES
--  * WhatsApp is the primary intake. wa_* tables are first-class, not an add-on.
--  * A "user" is the ACCOUNT OWNER (may be the patient, or the son/daughter).
--    A "patient" is who the report belongs to. 1 user -> many patients.
--    This is the family-linking growth loop; it is built in from day one.
--  * report_values is the normalised, per-parameter table. All graphs, trends
--    and care-gap logic read from it. Never chart from extraction JSON.
--  * Report files live OUTSIDE webroot. DB stores relative path only.
--  * consents + audit_log exist for DPDP Act 2023 from day one, not retrofit.
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================================
-- SECTION 1 : ACCESS CONTROL  (2 levels: admin | user)
-- ============================================================================

CREATE TABLE admin_users (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(120)  NOT NULL,
  email           VARCHAR(180)  NOT NULL,
  mobile          VARCHAR(20)   DEFAULT NULL,
  password_hash   VARCHAR(255)  NOT NULL,
  role            ENUM('superadmin','admin','verifier','support','content') NOT NULL DEFAULT 'support',
  -- verifier  = clinical/technician role, approves extracted reports
  -- content   = CMS + blog only
  status          ENUM('active','disabled') NOT NULL DEFAULT 'active',
  last_login_at   DATETIME      DEFAULT NULL,
  last_login_ip   VARCHAR(45)   DEFAULT NULL,
  failed_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until    DATETIME      DEFAULT 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_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid            CHAR(36)      NOT NULL,          -- used in URLs, never expose id
  mobile          VARCHAR(20)   NOT NULL,          -- E.164, e.g. 919820012345
  email           VARCHAR(180)  DEFAULT NULL,
  full_name       VARCHAR(120)  DEFAULT NULL,
  password_hash   VARCHAR(255)  DEFAULT NULL,      -- NULL until they set portal login
  preferred_lang  ENUM('en','hi','mr','gu','ta','bn') NOT NULL DEFAULT 'en',
  signup_source   ENUM('whatsapp','portal','telecall','campaign','referral') NOT NULL DEFAULT 'whatsapp',
  wa_opted_in     TINYINT(1)    NOT NULL DEFAULT 0,
  wa_opt_in_at    DATETIME      DEFAULT NULL,
  mobile_verified TINYINT(1)    NOT NULL DEFAULT 0,
  city            VARCHAR(100)  DEFAULT NULL,
  state           VARCHAR(100)  DEFAULT NULL,
  pincode         VARCHAR(10)   DEFAULT NULL,
  referral_code   VARCHAR(20)   DEFAULT NULL,
  referred_by     INT UNSIGNED  DEFAULT NULL,
  status          ENUM('active','blocked','deleted') NOT NULL DEFAULT 'active',
  deleted_at      DATETIME      DEFAULT NULL,      -- DPDP: soft delete then purge job
  created_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_mobile (mobile),
  UNIQUE KEY uq_users_uuid (uuid),
  KEY idx_users_email (email),
  KEY idx_users_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_sessions (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       INT UNSIGNED NOT NULL,
  token_hash    CHAR(64)     NOT NULL,
  ip            VARCHAR(45)  DEFAULT NULL,
  user_agent    VARCHAR(255) DEFAULT NULL,
  expires_at    DATETIME     NOT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_sess_token (token_hash),
  KEY idx_sess_user (user_id),
  CONSTRAINT fk_sess_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE otp_codes (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  mobile      VARCHAR(20)  NOT NULL,
  purpose     ENUM('login','signup','link_patient','delete_account') NOT NULL DEFAULT 'login',
  code_hash   CHAR(64)     NOT NULL,
  attempts    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  consumed_at DATETIME     DEFAULT NULL,
  expires_at  DATETIME     NOT NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_otp_mobile (mobile, purpose)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- SECTION 2 : PATIENTS  (family linking)
-- ============================================================================

CREATE TABLE patients (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid              CHAR(36)     NOT NULL,        -- also used as storage folder name
  user_id           INT UNSIGNED NOT NULL,        -- account owner / manager
  full_name         VARCHAR(120) NOT NULL,
  relation          ENUM('self','father','mother','spouse','son','daughter','brother','sister','grandparent','other')
                    NOT NULL DEFAULT 'self',
  gender            ENUM('male','female','other') DEFAULT NULL,
  birth_year        SMALLINT UNSIGNED DEFAULT NULL,   -- year only; avoids storing full DOB
  mobile            VARCHAR(20)  DEFAULT NULL,        -- patient's own no. if different
  diabetes_type     ENUM('type1','type2','gestational','prediabetes','unknown') NOT NULL DEFAULT 'unknown',
  diagnosed_year    SMALLINT UNSIGNED DEFAULT NULL,
  height_cm         DECIMAL(5,1) DEFAULT NULL,
  weight_kg         DECIMAL(5,1) DEFAULT NULL,
  on_insulin        TINYINT(1)   DEFAULT NULL,
  known_conditions  JSON         DEFAULT NULL,        -- ["hypertension","cad"] - self-declared
  storage_folder    VARCHAR(255) NOT NULL,            -- e.g. patients/9f2c.../  (relative)
  is_primary        TINYINT(1)   NOT NULL DEFAULT 0,  -- the 'self' record
  status            ENUM('active','archived','deleted') NOT NULL DEFAULT 'active',
  created_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_patients_uuid (uuid),
  KEY idx_patients_user (user_id, status),
  CONSTRAINT fk_patients_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- SECTION 3 : WHATSAPP LAYER  (primary intake channel)
-- ============================================================================

CREATE TABLE wa_contacts (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  wa_id           VARCHAR(20)  NOT NULL,          -- phone as Meta returns it
  user_id         INT UNSIGNED DEFAULT NULL,      -- linked once identified
  profile_name    VARCHAR(120) DEFAULT NULL,
  active_patient_id INT UNSIGNED DEFAULT NULL,    -- who they are currently uploading for
  opted_in        TINYINT(1)   NOT NULL DEFAULT 0,
  opted_out_at    DATETIME     DEFAULT NULL,
  last_inbound_at DATETIME     DEFAULT NULL,      -- drives 24h session-window logic
  last_outbound_at DATETIME    DEFAULT NULL,
  msg_count_in    INT UNSIGNED NOT NULL DEFAULT 0,
  msg_count_out   INT UNSIGNED NOT NULL DEFAULT 0,
  is_blocked      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,
  UNIQUE KEY uq_wa_id (wa_id),
  KEY idx_wa_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Conversation state machine. Keeps the bot simple and testable.
CREATE TABLE wa_sessions (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  wa_id         VARCHAR(20)  NOT NULL,
  state         VARCHAR(60)  NOT NULL DEFAULT 'idle',
  -- idle | awaiting_consent | awaiting_name | awaiting_patient_choice
  -- | awaiting_report | processing | awaiting_report_date | awaiting_feedback
  context       JSON         DEFAULT NULL,        -- scratchpad for the flow
  patient_id    INT UNSIGNED DEFAULT NULL,
  expires_at    DATETIME     NOT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_wa_session (wa_id),
  KEY idx_wa_state (state, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE wa_messages (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  wa_id           VARCHAR(20)  NOT NULL,
  wamid           VARCHAR(160) DEFAULT NULL,      -- Meta message id, for dedupe
  direction       ENUM('in','out') NOT NULL,
  msg_type        ENUM('text','image','document','audio','video','interactive','template','location','sticker','system') NOT NULL,
  body            TEXT         DEFAULT NULL,
  media_id        VARCHAR(160) DEFAULT NULL,      -- Meta media id
  media_mime      VARCHAR(100) DEFAULT NULL,
  media_sha256    CHAR(64)     DEFAULT NULL,
  local_path      VARCHAR(255) DEFAULT NULL,      -- after auto-download
  download_status ENUM('n/a','pending','downloaded','failed') NOT NULL DEFAULT 'n/a',
  template_name   VARCHAR(120) DEFAULT NULL,
  delivery_status ENUM('queued','sent','delivered','read','failed') DEFAULT NULL,
  error_code      VARCHAR(40)  DEFAULT NULL,
  error_detail    VARCHAR(500) DEFAULT NULL,
  report_id       BIGINT UNSIGNED DEFAULT NULL,   -- set once mapped to a report
  raw_payload     JSON         DEFAULT NULL,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_wamid (wamid),
  KEY idx_wamsg_contact (wa_id, created_at),
  KEY idx_wamsg_download (download_status),
  KEY idx_wamsg_report (report_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE wa_templates (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(120) NOT NULL,            -- as approved in Meta BM
  language      VARCHAR(10)  NOT NULL DEFAULT 'en',
  category      ENUM('utility','marketing','authentication') NOT NULL DEFAULT 'utility',
  purpose       VARCHAR(120) DEFAULT NULL,        -- report_ready | recall_due | red_flag | welcome
  body_preview  TEXT         DEFAULT NULL,
  variables     JSON         DEFAULT NULL,
  status        ENUM('approved','pending','rejected','paused') NOT NULL DEFAULT 'pending',
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_tpl (name, language)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- SECTION 4 : REPORTS + EXTRACTION
-- ============================================================================

CREATE TABLE reports (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid              CHAR(36)     NOT NULL,
  patient_id        INT UNSIGNED NOT NULL,
  user_id           INT UNSIGNED NOT NULL,       -- who uploaded
  source            ENUM('whatsapp','portal','telecall','import') NOT NULL DEFAULT 'whatsapp',
  wa_message_id     BIGINT UNSIGNED DEFAULT NULL,
  original_name     VARCHAR(255) DEFAULT NULL,
  stored_path       VARCHAR(255) NOT NULL,       -- relative to STORAGE_ROOT
  file_mime         VARCHAR(100) DEFAULT NULL,
  file_size         INT UNSIGNED DEFAULT NULL,
  file_sha256       CHAR(64)     DEFAULT NULL,   -- duplicate-upload detection
  page_count        SMALLINT UNSIGNED DEFAULT 1,
  report_date       DATE         DEFAULT NULL,   -- date on the report, not upload date
  lab_name          VARCHAR(160) DEFAULT NULL,
  doctor_name       VARCHAR(160) DEFAULT NULL,
  report_category   ENUM('lab','prescription','discharge','imaging','other') NOT NULL DEFAULT 'lab',
  status            ENUM('received','downloading','queued','extracting','pending_verification',
                         'verified','delivered','failed','rejected') NOT NULL DEFAULT 'received',
  extraction_json   JSON         DEFAULT NULL,   -- raw AI output, kept for audit/retrain
  extraction_model  VARCHAR(80)  DEFAULT NULL,
  extraction_conf   DECIMAL(4,3) DEFAULT NULL,   -- 0.000 - 1.000
  extraction_ms     INT UNSIGNED DEFAULT NULL,
  needs_review      TINYINT(1)   NOT NULL DEFAULT 1,
  verified_by       INT UNSIGNED DEFAULT NULL,   -- admin_users.id
  verified_at       DATETIME     DEFAULT NULL,
  verifier_notes    VARCHAR(500) DEFAULT NULL,
  delivered_at      DATETIME     DEFAULT NULL,
  failure_reason    VARCHAR(255) DEFAULT NULL,
  retry_count       TINYINT UNSIGNED 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_reports_uuid (uuid),
  KEY idx_reports_patient (patient_id, report_date),
  KEY idx_reports_status (status, created_at),
  KEY idx_reports_sha (file_sha256),
  CONSTRAINT fk_reports_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Master list of parameters we understand. Seeded below.
CREATE TABLE lab_parameters (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code            VARCHAR(40)  NOT NULL,         -- HBA1C, FBS, CREAT, UACR ...
  display_name    VARCHAR(120) NOT NULL,
  short_name      VARCHAR(40)  DEFAULT NULL,
  category        ENUM('glycemic','renal','lipid','hepatic','cbc','thyroid','cardiac','vitals','other') NOT NULL,
  unit            VARCHAR(30)  DEFAULT NULL,
  ref_low         DECIMAL(12,3) DEFAULT NULL,
  ref_high        DECIMAL(12,3) DEFAULT NULL,
  target_low      DECIMAL(12,3) DEFAULT NULL,    -- diabetes-specific target band
  target_high     DECIMAL(12,3) DEFAULT NULL,
  critical_low    DECIMAL(12,3) DEFAULT NULL,    -- triggers escalation
  critical_high   DECIMAL(12,3) DEFAULT NULL,
  is_key          TINYINT(1)   NOT NULL DEFAULT 0,  -- show on dashboard/trend
  chart_type      ENUM('line','bar','gauge','none') NOT NULL DEFAULT 'line',
  higher_is_worse TINYINT(1)   NOT NULL DEFAULT 1,
  synonyms        JSON         DEFAULT NULL,     -- alt names seen on Indian lab reports
  explain_en      TEXT         DEFAULT NULL,     -- plain-language "what this means"
  explain_hi      TEXT         DEFAULT NULL,
  sort_order      SMALLINT     NOT NULL DEFAULT 100,
  status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
  UNIQUE KEY uq_param_code (code),
  KEY idx_param_cat (category, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Normalised values. EVERY graph and rule reads from here.
CREATE TABLE report_values (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  report_id     BIGINT UNSIGNED NOT NULL,
  patient_id    INT UNSIGNED NOT NULL,          -- denormalised for fast trend queries
  param_code    VARCHAR(40)  DEFAULT NULL,      -- NULL if unmapped/unknown test
  raw_name      VARCHAR(160) NOT NULL,          -- exactly as printed on the report
  value_num     DECIMAL(12,3) DEFAULT NULL,
  value_text    VARCHAR(120) DEFAULT NULL,      -- for non-numeric e.g. "Trace", "Nil"
  unit          VARCHAR(30)  DEFAULT NULL,
  ref_text      VARCHAR(80)  DEFAULT NULL,      -- reference range as printed
  flag          ENUM('low','normal','borderline','high','critical_low','critical_high','unknown')
                NOT NULL DEFAULT 'unknown',
  test_date     DATE         DEFAULT NULL,
  confidence    DECIMAL(4,3) DEFAULT NULL,
  was_corrected TINYINT(1)   NOT NULL DEFAULT 0, -- verifier edited the AI value
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_rv_report (report_id),
  KEY idx_rv_trend (patient_id, param_code, test_date),
  CONSTRAINT fk_rv_report FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- The patient-facing output.
CREATE TABLE report_summaries (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  report_id       BIGINT UNSIGNED NOT NULL,
  patient_id      INT UNSIGNED NOT NULL,
  language        ENUM('en','hi','mr','gu','ta','bn') NOT NULL DEFAULT 'en',
  headline        VARCHAR(255) DEFAULT NULL,     -- one-line verdict
  summary_text    TEXT         NOT NULL,         -- plain-language explanation
  whats_good      JSON         DEFAULT NULL,     -- ["Your BP is well controlled"]
  whats_watch     JSON         DEFAULT NULL,     -- needs attention
  dos             JSON         DEFAULT NULL,     -- lifestyle + screening ONLY
  donts           JSON         DEFAULT NULL,     -- lifestyle + screening ONLY
  ask_your_doctor JSON         DEFAULT NULL,     -- questions to take to next visit
  missing_tests   JSON         DEFAULT NULL,     -- e.g. no kidney test in this report
  comparison_note TEXT         DEFAULT NULL,     -- vs previous report
  card_image_path VARCHAR(255) DEFAULT NULL,     -- shareable PNG
  generated_by    ENUM('ai','human','ai_edited') NOT NULL DEFAULT 'ai',
  model_used      VARCHAR(80)  DEFAULT NULL,
  approved_by     INT UNSIGNED DEFAULT NULL,
  approved_at     DATETIME     DEFAULT NULL,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sum_report (report_id, language),
  KEY idx_sum_patient (patient_id, created_at),
  CONSTRAINT fk_sum_report FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- SECTION 5 : RED FLAGS + CARE GAPS
-- ============================================================================

CREATE TABLE escalations (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id    INT UNSIGNED NOT NULL,
  report_id     BIGINT UNSIGNED DEFAULT NULL,
  severity      ENUM('urgent','high','moderate') NOT NULL DEFAULT 'moderate',
  trigger_code  VARCHAR(60)  NOT NULL,          -- CRITICAL_GLUCOSE_HIGH, HIGH_CREAT ...
  trigger_detail VARCHAR(500) DEFAULT NULL,
  auto_message_sent TINYINT(1) NOT NULL DEFAULT 0,
  called_at     DATETIME     DEFAULT NULL,
  handled_by    INT UNSIGNED DEFAULT NULL,
  outcome       VARCHAR(500) DEFAULT NULL,
  status        ENUM('open','in_progress','closed') NOT NULL DEFAULT 'open',
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_esc_status (status, severity, created_at),
  KEY idx_esc_patient (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE care_gap_rules (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code            VARCHAR(40)  NOT NULL,
  title_en        VARCHAR(160) NOT NULL,
  title_hi        VARCHAR(200) DEFAULT NULL,
  why_it_matters  TEXT         DEFAULT NULL,
  param_code      VARCHAR(40)  DEFAULT NULL,    -- satisfied when this param appears
  frequency_months SMALLINT UNSIGNED NOT NULL DEFAULT 12,
  applies_to      ENUM('all','type1','type2','on_insulin','age_40_plus') NOT NULL DEFAULT 'all',
  linked_service  VARCHAR(60)  DEFAULT NULL,    -- Phase 2: maps to bookable service
  sort_order      SMALLINT     NOT NULL DEFAULT 100,
  status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
  UNIQUE KEY uq_gap_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE patient_care_gaps (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id    INT UNSIGNED NOT NULL,
  rule_code     VARCHAR(40)  NOT NULL,
  last_done_on  DATE         DEFAULT NULL,
  last_report_id BIGINT UNSIGNED DEFAULT NULL,
  next_due_on   DATE         DEFAULT NULL,
  status        ENUM('never_done','due','overdue','done') NOT NULL DEFAULT 'never_done',
  reminded_at   DATETIME     DEFAULT NULL,
  reminder_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
  snoozed_until DATE         DEFAULT NULL,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_gap_patient (patient_id, rule_code),
  KEY idx_gap_due (status, next_due_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- SECTION 6 : CONSENT + AUDIT  (DPDP Act 2023)
-- ============================================================================

CREATE TABLE consents (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       INT UNSIGNED NOT NULL,
  patient_id    INT UNSIGNED DEFAULT NULL,
  consent_type  ENUM('data_processing','whatsapp_comms','partner_share','marketing','research_anon') NOT NULL,
  -- partner_share MUST be captured separately before Phase 2 lab/pharmacy handoff
  policy_version VARCHAR(20) NOT NULL DEFAULT '1.0',
  granted       TINYINT(1)   NOT NULL DEFAULT 1,
  channel       ENUM('whatsapp','portal','telecall') NOT NULL,
  evidence      VARCHAR(255) DEFAULT NULL,      -- wamid or form submission ref
  ip            VARCHAR(45)  DEFAULT NULL,
  granted_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at    DATETIME     DEFAULT NULL,
  KEY idx_consent_user (user_id, consent_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_log (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_type    ENUM('admin','user','system','cron') NOT NULL,
  actor_id      INT UNSIGNED DEFAULT NULL,
  action        VARCHAR(80)  NOT NULL,          -- report.view, report.download, user.delete
  entity_type   VARCHAR(60)  DEFAULT NULL,
  entity_id     BIGINT UNSIGNED DEFAULT NULL,
  detail        JSON         DEFAULT NULL,
  ip            VARCHAR(45)  DEFAULT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_entity (entity_type, entity_id),
  KEY idx_audit_actor (actor_type, actor_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- SECTION 7 : CMS  (landing page, blog, testimonials, SEO / AEO / GEO)
-- ============================================================================

CREATE TABLE cms_pages (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug          VARCHAR(180) NOT NULL,
  title         VARCHAR(200) NOT NULL,
  content       LONGTEXT     DEFAULT NULL,
  page_type     ENUM('landing','static','legal','service') NOT NULL DEFAULT 'static',
  meta_title    VARCHAR(200) DEFAULT NULL,
  meta_desc     VARCHAR(320) DEFAULT NULL,
  canonical_url VARCHAR(255) DEFAULT NULL,
  og_image      VARCHAR(255) DEFAULT NULL,
  schema_json   JSON         DEFAULT NULL,      -- JSON-LD injected in <head>
  robots        VARCHAR(60)  NOT NULL DEFAULT 'index,follow',
  status        ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  sort_order    SMALLINT     NOT NULL DEFAULT 100,
  updated_by    INT UNSIGNED DEFAULT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_page_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Editable homepage sections so marketing can rearrange without a developer.
CREATE TABLE cms_blocks (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  block_key     VARCHAR(60)  NOT NULL,          -- hero, how_it_works, trust_bar, faq_home
  heading       VARCHAR(255) DEFAULT NULL,
  subheading    VARCHAR(500) DEFAULT NULL,
  body          TEXT         DEFAULT NULL,
  items         JSON         DEFAULT NULL,      -- repeatable cards/steps
  image_path    VARCHAR(255) DEFAULT NULL,
  cta_label     VARCHAR(80)  DEFAULT NULL,
  cta_url       VARCHAR(255) DEFAULT NULL,
  sort_order    SMALLINT     NOT NULL DEFAULT 100,
  status        ENUM('active','hidden') NOT NULL DEFAULT 'active',
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_block_key (block_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE blog_categories (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug        VARCHAR(120) NOT NULL,
  name        VARCHAR(120) NOT NULL,
  description VARCHAR(500) DEFAULT NULL,
  meta_title  VARCHAR(200) DEFAULT NULL,
  meta_desc   VARCHAR(320) DEFAULT NULL,
  sort_order  SMALLINT NOT NULL DEFAULT 100,
  status      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  UNIQUE KEY uq_blogcat_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE blog_posts (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug            VARCHAR(200) NOT NULL,
  category_id     INT UNSIGNED DEFAULT NULL,
  title           VARCHAR(255) NOT NULL,
  excerpt         VARCHAR(500) DEFAULT NULL,
  content         LONGTEXT     NOT NULL,
  cover_image     VARCHAR(255) DEFAULT NULL,
  cover_alt       VARCHAR(200) DEFAULT NULL,
  author_name     VARCHAR(120) DEFAULT NULL,
  reviewed_by     VARCHAR(160) DEFAULT NULL,    -- E-E-A-T: medical reviewer name
  reviewer_creds  VARCHAR(160) DEFAULT NULL,
  language        ENUM('en','hi','mr','gu','ta','bn') NOT NULL DEFAULT 'en',
  reading_minutes TINYINT UNSIGNED DEFAULT NULL,
  meta_title      VARCHAR(200) DEFAULT NULL,
  meta_desc       VARCHAR(320) DEFAULT NULL,
  focus_keyword   VARCHAR(120) DEFAULT NULL,
  canonical_url   VARCHAR(255) DEFAULT NULL,
  schema_json     JSON         DEFAULT NULL,    -- Article / MedicalWebPage JSON-LD
  faq_json        JSON         DEFAULT NULL,    -- AEO: Q&A pairs -> FAQPage schema
  key_takeaways   JSON         DEFAULT NULL,    -- AEO: answer-first bullet block
  geo_city        VARCHAR(100) DEFAULT NULL,    -- GEO: city-targeted variants
  geo_state       VARCHAR(100) DEFAULT NULL,
  view_count      INT UNSIGNED NOT NULL DEFAULT 0,
  is_featured     TINYINT(1)   NOT NULL DEFAULT 0,
  status          ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  published_at    DATETIME     DEFAULT NULL,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_post_slug (slug),
  KEY idx_post_status (status, published_at),
  KEY idx_post_cat (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE testimonials (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  person_name   VARCHAR(120) NOT NULL,
  city          VARCHAR(100) DEFAULT NULL,
  age_band      VARCHAR(20)  DEFAULT NULL,      -- "58" or "50-60"
  photo_path    VARCHAR(255) DEFAULT NULL,
  quote         TEXT         NOT NULL,
  rating        TINYINT UNSIGNED DEFAULT NULL,
  consent_ref   VARCHAR(160) DEFAULT NULL,      -- proof of permission to publish
  is_featured   TINYINT(1)   NOT NULL DEFAULT 0,
  sort_order    SMALLINT     NOT NULL DEFAULT 100,
  status        ENUM('pending','published','hidden') NOT NULL DEFAULT 'pending',
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_testi_status (status, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE faqs (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  question    VARCHAR(300) NOT NULL,
  answer      TEXT         NOT NULL,
  context     ENUM('home','pricing','privacy','reports','general') NOT NULL DEFAULT 'general',
  language    ENUM('en','hi','mr','gu','ta','bn') NOT NULL DEFAULT 'en',
  sort_order  SMALLINT     NOT NULL DEFAULT 100,
  status      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  KEY idx_faq_ctx (context, status, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE seo_redirects (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  from_path    VARCHAR(255) NOT NULL,
  to_path      VARCHAR(255) NOT NULL,
  status_code  SMALLINT NOT NULL DEFAULT 301,
  hit_count    INT UNSIGNED NOT NULL DEFAULT 0,
  status       ENUM('active','inactive') NOT NULL DEFAULT 'active',
  UNIQUE KEY uq_redirect_from (from_path)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE media_library (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  file_path     VARCHAR(255) NOT NULL,
  file_name     VARCHAR(200) NOT NULL,
  mime          VARCHAR(100) DEFAULT NULL,
  file_size     INT UNSIGNED DEFAULT NULL,
  width         SMALLINT UNSIGNED DEFAULT NULL,
  height        SMALLINT UNSIGNED DEFAULT NULL,
  alt_text      VARCHAR(200) DEFAULT NULL,
  uploaded_by   INT UNSIGNED DEFAULT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE enquiries (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(120) DEFAULT NULL,
  mobile      VARCHAR(20)  DEFAULT NULL,
  email       VARCHAR(180) DEFAULT NULL,
  city        VARCHAR(100) DEFAULT NULL,
  message     TEXT         DEFAULT NULL,
  source_page VARCHAR(200) DEFAULT NULL,
  utm         JSON         DEFAULT NULL,
  ip          VARCHAR(45)  DEFAULT NULL,
  status      ENUM('new','contacted','converted','junk') NOT NULL DEFAULT 'new',
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_enq_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- SECTION 8 : PHASE-2 COMMERCE  (lab tests first, then food + VAS)
-- Tables created now so Phase 1 can already record demand signals.
-- ============================================================================

CREATE TABLE partners (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(160) NOT NULL,
  partner_type    ENUM('lab','pharmacy','food','clinic','device','other') NOT NULL,
  contact_person  VARCHAR(120) DEFAULT NULL,
  mobile          VARCHAR(20)  DEFAULT NULL,
  email           VARCHAR(180) DEFAULT NULL,
  cities          JSON         DEFAULT NULL,
  commission_pct  DECIMAL(5,2) DEFAULT NULL,
  settlement_days SMALLINT     DEFAULT 30,
  api_config      JSON         DEFAULT NULL,    -- encrypted at app layer
  status          ENUM('active','paused','inactive') NOT NULL DEFAULT 'active',
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE service_categories (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(40)  NOT NULL,            -- lab_test, food, pharmacy, consult, vas
  name        VARCHAR(120) NOT NULL,
  icon        VARCHAR(80)  DEFAULT NULL,
  sort_order  SMALLINT     NOT NULL DEFAULT 100,
  status      ENUM('active','coming_soon','inactive') NOT NULL DEFAULT 'coming_soon',
  UNIQUE KEY uq_svccat_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE services (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id     INT UNSIGNED NOT NULL,
  partner_id      INT UNSIGNED DEFAULT NULL,
  sku             VARCHAR(60)  DEFAULT NULL,
  name            VARCHAR(200) NOT NULL,
  slug            VARCHAR(200) DEFAULT NULL,
  description     TEXT         DEFAULT NULL,
  image_path      VARCHAR(255) DEFAULT NULL,
  mrp             DECIMAL(10,2) DEFAULT NULL,
  sale_price      DECIMAL(10,2) DEFAULT NULL,
  commission_pct  DECIMAL(5,2)  DEFAULT NULL,
  gst_pct         DECIMAL(5,2)  DEFAULT 0.00,
  is_recurring    TINYINT(1)   NOT NULL DEFAULT 0,   -- food subscriptions
  recurrence_days SMALLINT     DEFAULT NULL,
  linked_gap_rule VARCHAR(40)  DEFAULT NULL,         -- surfaced when this gap is due
  home_collection TINYINT(1)   NOT NULL DEFAULT 0,
  cities          JSON         DEFAULT NULL,
  status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_svc_cat (category_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_no        VARCHAR(40)  NOT NULL,
  user_id         INT UNSIGNED NOT NULL,
  patient_id      INT UNSIGNED DEFAULT NULL,
  partner_id      INT UNSIGNED DEFAULT NULL,
  channel         ENUM('whatsapp','portal','telecall') NOT NULL DEFAULT 'whatsapp',
  subtotal        DECIMAL(10,2) NOT NULL DEFAULT 0,
  discount        DECIMAL(10,2) NOT NULL DEFAULT 0,
  gst_amount      DECIMAL(10,2) NOT NULL DEFAULT 0,
  total           DECIMAL(10,2) NOT NULL DEFAULT 0,
  commission_due  DECIMAL(10,2) NOT NULL DEFAULT 0,
  payment_status  ENUM('pending','paid','cod','failed','refunded') NOT NULL DEFAULT 'pending',
  payment_ref     VARCHAR(120) DEFAULT NULL,
  fulfil_status   ENUM('placed','confirmed','scheduled','completed','cancelled') NOT NULL DEFAULT 'placed',
  scheduled_for   DATETIME     DEFAULT NULL,
  address_json    JSON         DEFAULT NULL,
  notes           VARCHAR(500) DEFAULT NULL,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_order_no (order_no),
  KEY idx_order_user (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_items (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id    BIGINT UNSIGNED NOT NULL,
  service_id  INT UNSIGNED DEFAULT NULL,
  item_name   VARCHAR(200) NOT NULL,
  qty         SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  unit_price  DECIMAL(10,2) NOT NULL DEFAULT 0,
  line_total  DECIMAL(10,2) NOT NULL DEFAULT 0,
  KEY idx_oi_order (order_id),
  CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Demand signal captured in Phase 1, before booking exists.
CREATE TABLE service_interest (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       INT UNSIGNED NOT NULL,
  patient_id    INT UNSIGNED DEFAULT NULL,
  category_code VARCHAR(40)  NOT NULL,
  detail        VARCHAR(255) DEFAULT NULL,
  source        ENUM('whatsapp','portal','care_gap') NOT NULL DEFAULT 'care_gap',
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_si_cat (category_code, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- SECTION 9 : SYSTEM
-- ============================================================================

CREATE TABLE settings (
  setting_key   VARCHAR(80)  NOT NULL PRIMARY KEY,
  setting_value TEXT         DEFAULT NULL,
  value_type    ENUM('string','int','bool','json') NOT NULL DEFAULT 'string',
  group_name    VARCHAR(60)  DEFAULT NULL,
  is_secret     TINYINT(1)   NOT NULL DEFAULT 0,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE job_queue (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  job_type      VARCHAR(60)  NOT NULL,          -- download_media, extract_report, gen_summary,
                                                -- render_card, send_wa, recompute_gaps
  payload       JSON         NOT NULL,
  priority      TINYINT UNSIGNED NOT NULL DEFAULT 5,
  attempts      TINYINT UNSIGNED NOT NULL DEFAULT 0,
  max_attempts  TINYINT UNSIGNED NOT NULL DEFAULT 3,
  status        ENUM('pending','running','done','failed','dead') NOT NULL DEFAULT 'pending',
  available_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  started_at    DATETIME     DEFAULT NULL,
  finished_at   DATETIME     DEFAULT NULL,
  last_error    VARCHAR(1000) DEFAULT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_jq_pick (status, available_at, priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE api_log (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  service       VARCHAR(40)  NOT NULL,          -- meta_wa, anthropic, payment
  endpoint      VARCHAR(200) DEFAULT NULL,
  http_status   SMALLINT     DEFAULT NULL,
  duration_ms   INT UNSIGNED DEFAULT NULL,
  tokens_in     INT UNSIGNED DEFAULT NULL,
  tokens_out    INT UNSIGNED DEFAULT NULL,
  cost_estimate DECIMAL(10,4) DEFAULT NULL,
  ref_type      VARCHAR(40)  DEFAULT NULL,
  ref_id        BIGINT UNSIGNED DEFAULT NULL,
  error         VARCHAR(1000) DEFAULT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_apilog (service, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================================
-- SEED : lab parameters (key diabetes panel)
-- ============================================================================

INSERT INTO lab_parameters
(code, display_name, short_name, category, unit, ref_low, ref_high, target_low, target_high,
 critical_low, critical_high, is_key, higher_is_worse, sort_order, synonyms, explain_en) VALUES
('HBA1C','Glycated Haemoglobin (HbA1c)','HbA1c','glycemic','%',4.0,5.6,NULL,7.0,NULL,10.0,1,1,10,
 '["hba1c","glycosylated haemoglobin","glycated hemoglobin","a1c","hb a1c"]',
 'Your average blood sugar over the last 2-3 months. One number that shows the whole quarter, not just one day.'),
('FBS','Fasting Blood Sugar','FBS','glycemic','mg/dL',70,99,80,130,54,300,1,1,20,
 '["fasting blood sugar","fbs","fasting glucose","glucose fasting","blood sugar fasting"]',
 'Sugar level after not eating for 8 hours. Shows what your body does overnight.'),
('PPBS','Post Prandial Blood Sugar','PPBS','glycemic','mg/dL',70,139,NULL,180,54,400,1,1,30,
 '["post prandial","ppbs","pp blood sugar","2 hour glucose","postprandial glucose"]',
 'Sugar level 2 hours after a meal. Shows how well your body handles food.'),
('RBS','Random Blood Sugar','RBS','glycemic','mg/dL',70,139,NULL,180,54,400,0,1,40,
 '["random blood sugar","rbs","random glucose"]',
 'Sugar level at any time of day, without fasting.'),
('CREAT','Serum Creatinine','Creatinine','renal','mg/dL',0.6,1.3,NULL,1.3,NULL,2.0,1,1,50,
 '["creatinine","s. creatinine","serum creatinine","creat"]',
 'A waste product your kidneys clear. If it rises, your kidneys may not be filtering well.'),
('EGFR','eGFR','eGFR','renal','mL/min/1.73m2',90,NULL,60,NULL,15,NULL,1,0,60,
 '["egfr","gfr","estimated gfr","glomerular filtration rate"]',
 'How well your kidneys are filtering. Higher is better. Below 60 needs attention.'),
('UACR','Urine Albumin-Creatinine Ratio','Urine ACR','renal','mg/g',0,30,NULL,30,NULL,300,1,1,70,
 '["urine acr","albumin creatinine ratio","microalbumin","urine microalbumin","uacr"]',
 'The earliest sign of kidney damage from diabetes - years before creatinine changes. The most commonly skipped test.'),
('UREA','Blood Urea','Urea','renal','mg/dL',15,40,NULL,40,NULL,80,0,1,80,
 '["urea","blood urea","bun"]',
 'Another waste product cleared by the kidneys.'),
('LDL','LDL Cholesterol','LDL','lipid','mg/dL',0,100,NULL,100,NULL,190,1,1,90,
 '["ldl","ldl cholesterol","low density lipoprotein"]',
 'The cholesterol that blocks arteries. In diabetes the target is stricter than for others.'),
('HDL','HDL Cholesterol','HDL','lipid','mg/dL',40,NULL,40,NULL,NULL,NULL,1,0,100,
 '["hdl","hdl cholesterol","high density lipoprotein"]',
 'The protective cholesterol. Higher is better.'),
('TG','Triglycerides','TG','lipid','mg/dL',0,150,NULL,150,NULL,500,1,1,110,
 '["triglycerides","tg","trigly"]',
 'A blood fat that rises when sugar is poorly controlled.'),
('TCHOL','Total Cholesterol','Total Chol','lipid','mg/dL',0,200,NULL,200,NULL,300,0,1,120,
 '["total cholesterol","cholesterol total","t. cholesterol"]',
 'All cholesterol added together.'),
('HB','Haemoglobin','Hb','cbc','g/dL',12.0,16.0,NULL,NULL,7.0,NULL,1,0,130,
 '["haemoglobin","hemoglobin","hb","hgb"]',
 'Carries oxygen in your blood. Low levels cause tiredness.'),
('TSH','TSH','TSH','thyroid','µIU/mL',0.4,4.5,NULL,NULL,NULL,10.0,0,1,140,
 '["tsh","thyroid stimulating hormone"]',
 'Thyroid function. Thyroid problems are common alongside diabetes.'),
('SGPT','SGPT / ALT','SGPT','hepatic','U/L',0,45,NULL,45,NULL,150,0,1,150,
 '["sgpt","alt","alanine aminotransferase"]',
 'A liver enzyme. Fatty liver is very common in type 2 diabetes.'),
('VITD','Vitamin D (25-OH)','Vit D','other','ng/mL',30,100,30,NULL,NULL,NULL,0,0,160,
 '["vitamin d","25 oh vitamin d","vit d","25-hydroxy"]',
 'Low vitamin D is very common in India and affects bone and muscle health.'),
('BPSYS','Blood Pressure (Systolic)','BP Sys','vitals','mmHg',90,130,NULL,130,NULL,180,1,1,170,
 '["systolic","bp systolic","blood pressure systolic"]',
 'The upper BP number. In diabetes, keeping this under control protects kidneys and eyes.'),
('BPDIA','Blood Pressure (Diastolic)','BP Dia','vitals','mmHg',60,80,NULL,80,NULL,110,1,1,180,
 '["diastolic","bp diastolic","blood pressure diastolic"]',
 'The lower BP number.');

-- ============================================================================
-- SEED : care gap rules  (drives recalls in Phase 1, bookings in Phase 2)
-- ============================================================================

INSERT INTO care_gap_rules
(code, title_en, why_it_matters, param_code, frequency_months, applies_to, linked_service, sort_order) VALUES
('HBA1C_Q3','HbA1c test','Shows your average sugar for 3 months. The single most important diabetes test.','HBA1C',3,'all','lab_hba1c',10),
('KIDNEY_YR','Kidney check (Urine ACR + Creatinine)','Diabetes damages kidneys silently. Urine ACR catches it years earlier than any symptom.','UACR',12,'all','lab_renal',20),
('EYE_YR','Retina / eye screening','Diabetic retinopathy has no symptoms until vision is already lost. Annual screening prevents blindness.',NULL,12,'all','eye_screening',30),
('FOOT_YR','Foot examination','Loss of sensation in feet leads to ulcers and amputation. A 5-minute check prevents it.',NULL,12,'all','foot_screening',40),
('LIPID_YR','Lipid profile','Heart attack is the most common cause of death in diabetes. Lipids are the warning system.','LDL',12,'all','lab_lipid',50),
('BP_Q3','Blood pressure check','High BP plus diabetes multiplies kidney and eye damage.','BPSYS',3,'all','bp_check',60),
('HB_YR','Haemoglobin / CBC','Anaemia is common and worsens tiredness attributed to diabetes.','HB',12,'all','lab_cbc',70),
('LIVER_YR','Liver function','Fatty liver affects most people with type 2 diabetes.','SGPT',12,'type2','lab_lft',80);

-- ============================================================================
-- SEED : service categories (Phase 2 sequencing is deliberate)
-- ============================================================================

INSERT INTO service_categories (code, name, icon, sort_order, status) VALUES
('lab_test','Lab tests','flask',10,'coming_soon'),
('food','Diabetes-friendly food','leaf',20,'coming_soon'),
('pharmacy','Medicines','pill',30,'coming_soon'),
('consult','Consultation','stethoscope',40,'coming_soon'),
('vas','Other services','plus',50,'coming_soon');

-- ============================================================================
-- SEED : CMS blocks (homepage skeleton, editable without a developer)
-- ============================================================================

INSERT INTO cms_blocks (block_key, heading, subheading, sort_order) VALUES
('hero','Send your lab report. Get it in plain language.','WhatsApp your report to Diabeasy. We tell you what each number means, what to do, and what test you have missed. Free.',10),
('how_it_works','Three steps',NULL,20),
('trust_bar','Built by Caresoft','Healthcare software running in 1,000+ hospitals across 12+ countries.',30),
('why_reports','Your reports, kept together','Every report you send is stored safely, so you can see whether you are improving.',40),
('care_gaps','The tests most people miss',NULL,50),
('testimonials','What people say',NULL,60),
('faq_home','Common questions',NULL,70);

INSERT INTO settings (setting_key, setting_value, value_type, group_name, is_secret) VALUES
('site_name','Diabeasy','string','general',0),
('wa_business_number','','string','whatsapp',0),
('wa_phone_number_id','','string','whatsapp',1),
('wa_waba_id','','string','whatsapp',1),
('wa_access_token','','string','whatsapp',1),
('wa_verify_token','','string','whatsapp',1),
('anthropic_api_key','','string','ai',1),
('extraction_model','claude-sonnet-4-6','string','ai',0),
('require_human_verification','1','bool','ai',0),
('privacy_policy_version','1.0','string','legal',0),
('report_retention_months','60','int','legal',0),
('escalation_mobile','','string','ops',0);