-- =====================================================================
-- NIRVAAN — Sexual Health Consultation Platform
-- Schema v1.0  |  MySQL 8.0+  |  utf8mb4
-- =====================================================================
SET NAMES utf8mb4;
SET time_zone = '+05:30';

CREATE DATABASE IF NOT EXISTS nirvaan DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE nirvaan;

-- ---------------------------------------------------------------------
-- 1. IDENTITY & ACCESS  (3 levels: admin / doctor / user)
-- ---------------------------------------------------------------------

CREATE TABLE admin_users (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name       VARCHAR(120)  NOT NULL,
  email           VARCHAR(160)  NOT NULL UNIQUE,
  mobile          VARCHAR(20)   NULL,
  password_hash   VARCHAR(255)  NOT NULL,
  role            ENUM('superadmin','ops','finance','content','support') NOT NULL DEFAULT 'ops',
  is_active       TINYINT(1)    NOT NULL DEFAULT 1,
  totp_secret     VARCHAR(64)   NULL,
  last_login_at   DATETIME      NULL,
  last_login_ip   VARBINARY(16) NULL,
  failed_attempts SMALLINT      NOT NULL DEFAULT 0,
  locked_until    DATETIME      NULL,
  created_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Patients. Real identity is stored here and NEVER exposed to doctors.
CREATE TABLE users (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_ref        CHAR(12)      NOT NULL UNIQUE,      -- what the doctor sees: NV-8K3QF2
  display_alias     VARCHAR(40)   NOT NULL,             -- self-chosen handle, e.g. "Blue Heron"
  full_name         VARCHAR(120)  NULL,                 -- optional, masked on doctor side
  mobile_e164       VARCHAR(20)   NOT NULL UNIQUE,
  mobile_last4      CHAR(4)       NOT NULL,
  email             VARCHAR(160)  NULL,
  password_hash     VARCHAR(255)  NULL,                 -- NULL = OTP-only account
  gender            ENUM('male','female','other','undisclosed') NOT NULL DEFAULT 'undisclosed',
  dob_year          SMALLINT      NULL,                 -- year only; never full DOB
  city              VARCHAR(80)   NULL,
  state             VARCHAR(80)   NULL,
  pref_language     VARCHAR(10)   NOT NULL DEFAULT 'en',
  pref_doctor_gender ENUM('any','male','female') NOT NULL DEFAULT 'any',
  is_active         TINYINT(1)    NOT NULL DEFAULT 1,
  is_blocked        TINYINT(1)    NOT NULL DEFAULT 0,
  incognito_mode    TINYINT(1)    NOT NULL DEFAULT 1,   -- hides history on device
  kyc_verified      TINYINT(1)    NOT NULL DEFAULT 0,
  signup_source     VARCHAR(60)   NULL,
  utm_json          JSON          NULL,
  last_login_at     DATETIME      NULL,
  created_at        DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_users_city (city),
  INDEX idx_users_created (created_at)
) ENGINE=InnoDB;

-- Doctors / therapists / counsellors
CREATE TABLE doctors (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_slug         VARCHAR(90)   NOT NULL UNIQUE,     -- dr-a-sharma-sexologist-mumbai
  display_name        VARCHAR(120)  NOT NULL,
  legal_name          VARCHAR(120)  NOT NULL,
  email               VARCHAR(160)  NOT NULL UNIQUE,
  mobile_e164         VARCHAR(20)   NOT NULL UNIQUE,
  password_hash       VARCHAR(255)  NOT NULL,
  gender              ENUM('male','female','other') NOT NULL,
  photo_path          VARCHAR(255)  NULL,
  tier                ENUM('counsellor','rmp') NOT NULL DEFAULT 'counsellor',
  -- 'rmp' = Registered Medical Practitioner, may prescribe under TPG 2020
  -- 'counsellor' = advisory/therapy only, no prescription rights
  qualification       VARCHAR(200)  NOT NULL,            -- MBBS, MD (Psychiatry)
  specialities        VARCHAR(255)  NOT NULL,            -- comma list of speciality slugs
  nmc_reg_no          VARCHAR(60)   NULL,                -- state medical council reg no
  nmc_state_council   VARCHAR(120)  NULL,
  nmc_reg_year        SMALLINT      NULL,
  reg_verified        TINYINT(1)    NOT NULL DEFAULT 0,
  reg_verified_at     DATETIME      NULL,
  reg_verified_by     INT UNSIGNED  NULL,
  experience_years    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  languages           VARCHAR(160)  NOT NULL DEFAULT 'en,hi',
  bio                 TEXT          NULL,
  city                VARCHAR(80)   NULL,
  state               VARCHAR(80)   NULL,
  -- per-minute pricing (paise, to avoid float)
  rate_chat_paise     INT UNSIGNED  NOT NULL DEFAULT 1500,
  rate_call_paise     INT UNSIGNED  NOT NULL DEFAULT 2500,
  rate_video_paise    INT UNSIGNED  NOT NULL DEFAULT 4000,
  commission_pct      DECIMAL(5,2)  NOT NULL DEFAULT 30.00,
  accepts_chat        TINYINT(1)    NOT NULL DEFAULT 1,
  accepts_call        TINYINT(1)    NOT NULL DEFAULT 1,
  accepts_video       TINYINT(1)    NOT NULL DEFAULT 0,
  status              ENUM('pending','under_review','approved','suspended','rejected') NOT NULL DEFAULT 'pending',
  rejection_reason    VARCHAR(255)  NULL,
  presence            ENUM('offline','online','busy','away') NOT NULL DEFAULT 'offline',
  presence_updated_at DATETIME      NULL,
  rating_avg          DECIMAL(3,2)  NOT NULL DEFAULT 0.00,
  rating_count        INT UNSIGNED  NOT NULL DEFAULT 0,
  consults_completed  INT UNSIGNED  NOT NULL DEFAULT 0,
  response_secs_avg   INT UNSIGNED  NOT NULL DEFAULT 0,
  is_featured         TINYINT(1)    NOT NULL DEFAULT 0,
  sort_weight         INT           NOT NULL DEFAULT 0,
  onboarded_at        DATETIME      NULL,
  created_at          DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_doc_status_presence (status, presence),
  INDEX idx_doc_city (city),
  INDEX idx_doc_rating (rating_avg DESC)
) ENGINE=InnoDB;

CREATE TABLE doctor_documents (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  doctor_id    INT UNSIGNED NOT NULL,
  doc_type     ENUM('degree','nmc_certificate','govt_id','pan','cancelled_cheque','experience','other') NOT NULL,
  file_path    VARCHAR(255) NOT NULL,
  original_name VARCHAR(200) NOT NULL,
  status       ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  remarks      VARCHAR(255) NULL,
  reviewed_by  INT UNSIGNED NULL,
  reviewed_at  DATETIME     NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE doctor_bank_details (
  doctor_id     INT UNSIGNED PRIMARY KEY,
  account_name  VARCHAR(120) NOT NULL,
  account_no_enc VARBINARY(255) NOT NULL,   -- AES encrypted at app layer
  ifsc          VARCHAR(15)  NOT NULL,
  bank_name     VARCHAR(120) NULL,
  pan_enc       VARBINARY(255) NULL,
  gstin         VARCHAR(20)  NULL,
  verified      TINYINT(1)   NOT NULL DEFAULT 0,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE doctor_availability (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  doctor_id  INT UNSIGNED NOT NULL,
  day_of_week TINYINT     NOT NULL,          -- 0=Sun
  start_time TIME         NOT NULL,
  end_time   TIME         NOT NULL,
  FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE,
  INDEX idx_avail (doctor_id, day_of_week)
) ENGINE=InnoDB;

CREATE TABLE specialities (
  id          SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug        VARCHAR(60)  NOT NULL UNIQUE,
  name        VARCHAR(120) NOT NULL,
  for_gender  ENUM('all','male','female') NOT NULL DEFAULT 'all',
  icon        VARCHAR(60)  NULL,
  seo_title   VARCHAR(180) NULL,
  seo_desc    VARCHAR(320) NULL,
  intro_html  TEXT         NULL,
  sort_order  SMALLINT     NOT NULL DEFAULT 0,
  is_active   TINYINT(1)   NOT NULL DEFAULT 1
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 2. AUTH SUPPORT
-- ---------------------------------------------------------------------

CREATE TABLE otp_tokens (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  channel     ENUM('sms','email','whatsapp') NOT NULL,
  destination VARCHAR(160) NOT NULL,
  purpose     ENUM('login','signup','verify','withdraw','reset') NOT NULL,
  code_hash   CHAR(64)     NOT NULL,
  attempts    TINYINT      NOT NULL DEFAULT 0,
  expires_at  DATETIME     NOT NULL,
  consumed_at DATETIME     NULL,
  ip          VARBINARY(16) NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_otp_lookup (destination, purpose, expires_at)
) ENGINE=InnoDB;

CREATE TABLE sessions (
  id           CHAR(64) PRIMARY KEY,
  actor_type   ENUM('admin','doctor','user') NOT NULL,
  actor_id     INT UNSIGNED NOT NULL,
  ip           VARBINARY(16) NULL,
  user_agent   VARCHAR(255) NULL,
  csrf_token   CHAR(64)     NOT NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at   DATETIME     NOT NULL,
  INDEX idx_sess_actor (actor_type, actor_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 3. WALLET, PRICING, PAYMENTS
-- ---------------------------------------------------------------------

CREATE TABLE wallets (
  user_id        INT UNSIGNED PRIMARY KEY,
  balance_paise  BIGINT NOT NULL DEFAULT 0,
  bonus_paise    BIGINT NOT NULL DEFAULT 0,
  lifetime_added BIGINT NOT NULL DEFAULT 0,
  lifetime_spent BIGINT NOT NULL DEFAULT 0,
  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE wallet_ledger (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  direction    ENUM('credit','debit') NOT NULL,
  amount_paise BIGINT NOT NULL,
  balance_after BIGINT NOT NULL,
  source       ENUM('recharge','consult','refund','bonus','cashback','adjustment','order') NOT NULL,
  ref_table    VARCHAR(40)  NULL,
  ref_id       BIGINT UNSIGNED NULL,
  narration    VARCHAR(200) NULL,
  created_by   VARCHAR(40)  NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_ledger_user (user_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE recharge_packs (
  id            SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  amount_paise  INT UNSIGNED NOT NULL,
  bonus_paise   INT UNSIGNED NOT NULL DEFAULT 0,
  label         VARCHAR(60)  NULL,
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  sort_order    SMALLINT     NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE payments (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id        INT UNSIGNED NOT NULL,
  gateway        VARCHAR(30)  NOT NULL DEFAULT 'cashfree',
  order_ref      VARCHAR(80)  NOT NULL UNIQUE,
  gateway_txn_id VARCHAR(120) NULL,
  amount_paise   INT UNSIGNED NOT NULL,
  bonus_paise    INT UNSIGNED NOT NULL DEFAULT 0,
  status         ENUM('created','pending','success','failed','refunded') NOT NULL DEFAULT 'created',
  method         VARCHAR(40)  NULL,
  statement_desc VARCHAR(40)  NULL,        -- discreet billing descriptor
  raw_response   JSON         NULL,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at   DATETIME     NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_pay_status (status, created_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 4. CONSULTATIONS  (chat / call / video, per-minute metered)
-- ---------------------------------------------------------------------

CREATE TABLE consult_requests (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  doctor_id    INT UNSIGNED NOT NULL,
  mode         ENUM('chat','call','video') NOT NULL,
  status       ENUM('queued','accepted','rejected','expired','cancelled','converted') NOT NULL DEFAULT 'queued',
  intake_json  JSON         NULL,          -- pre-consult questionnaire answers
  requested_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  responded_at DATETIME     NULL,
  expires_at   DATETIME     NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (doctor_id) REFERENCES doctors(id),
  INDEX idx_req_doc (doctor_id, status)
) ENGINE=InnoDB;

CREATE TABLE consultations (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  consult_ref       VARCHAR(24)  NOT NULL UNIQUE,
  request_id        BIGINT UNSIGNED NULL,
  user_id           INT UNSIGNED NOT NULL,
  doctor_id         INT UNSIGNED NOT NULL,
  mode              ENUM('chat','call','video') NOT NULL,
  is_free_session   TINYINT(1)   NOT NULL DEFAULT 0,
  free_minutes      SMALLINT     NOT NULL DEFAULT 0,
  rate_paise_min    INT UNSIGNED NOT NULL,
  started_at        DATETIME     NULL,
  ended_at          DATETIME     NULL,
  billed_seconds    INT UNSIGNED NOT NULL DEFAULT 0,
  billed_minutes    INT UNSIGNED NOT NULL DEFAULT 0,
  gross_paise       BIGINT       NOT NULL DEFAULT 0,
  commission_paise  BIGINT       NOT NULL DEFAULT 0,
  doctor_paise      BIGINT       NOT NULL DEFAULT 0,
  gst_paise         BIGINT       NOT NULL DEFAULT 0,
  status            ENUM('pending','live','completed','aborted','refunded') NOT NULL DEFAULT 'pending',
  end_reason        ENUM('user_ended','doctor_ended','balance_exhausted','timeout','network','admin') NULL,
  -- masking snapshot: exactly what the doctor was shown
  masked_name       VARCHAR(60)  NOT NULL,
  masked_mobile     VARCHAR(20)  NOT NULL,
  call_provider     VARCHAR(30)  NULL,      -- exotel/knowlarity/twilio
  call_sid          VARCHAR(120) NULL,
  recording_path    VARCHAR(255) NULL,
  recording_consent TINYINT(1)   NOT NULL DEFAULT 0,
  created_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (doctor_id) REFERENCES doctors(id),
  INDEX idx_con_user (user_id, created_at),
  INDEX idx_con_doc (doctor_id, created_at),
  INDEX idx_con_status (status)
) ENGINE=InnoDB;

CREATE TABLE consult_messages (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  consultation_id BIGINT UNSIGNED NOT NULL,
  sender_type    ENUM('user','doctor','system') NOT NULL,
  body_enc       BLOB         NULL,          -- AES-256 encrypted at app layer
  attachment_path VARCHAR(255) NULL,
  attachment_type VARCHAR(40)  NULL,
  is_read        TINYINT(1)   NOT NULL DEFAULT 0,
  sent_at        DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  FOREIGN KEY (consultation_id) REFERENCES consultations(id) ON DELETE CASCADE,
  INDEX idx_msg_consult (consultation_id, sent_at)
) ENGINE=InnoDB;

-- per-minute deduction trail; one row per billed minute
CREATE TABLE consult_billing_ticks (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  consultation_id BIGINT UNSIGNED NOT NULL,
  minute_index   SMALLINT UNSIGNED NOT NULL,
  amount_paise   INT UNSIGNED NOT NULL,
  balance_after  BIGINT NOT NULL,
  ticked_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (consultation_id) REFERENCES consultations(id) ON DELETE CASCADE,
  UNIQUE KEY uq_tick (consultation_id, minute_index)
) ENGINE=InnoDB;

CREATE TABLE consult_notes (
  consultation_id BIGINT UNSIGNED PRIMARY KEY,
  doctor_id       INT UNSIGNED NOT NULL,
  chief_complaint TEXT NULL,
  advice_enc      BLOB NULL,
  followup_days   SMALLINT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (consultation_id) REFERENCES consultations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- TPG 2020: prescriptions only by tier='rmp'
CREATE TABLE prescriptions (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  consultation_id BIGINT UNSIGNED NOT NULL,
  doctor_id       INT UNSIGNED NOT NULL,
  user_id         INT UNSIGNED NOT NULL,
  rx_no           VARCHAR(30)  NOT NULL UNIQUE,
  diagnosis_enc   BLOB         NULL,
  drugs_json      JSON         NOT NULL,     -- [{name,strength,dose,freq,days,notes}]
  advice_enc      BLOB         NULL,
  pdf_path        VARCHAR(255) NULL,
  signed_hash     CHAR(64)     NULL,
  issued_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (consultation_id) REFERENCES consultations(id),
  INDEX idx_rx_user (user_id, issued_at)
) ENGINE=InnoDB;

CREATE TABLE consent_log (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  consultation_id BIGINT UNSIGNED NULL,
  consent_type ENUM('teleconsult','recording','data_processing','marketing','terms') NOT NULL,
  consent_text_version VARCHAR(20) NOT NULL,
  granted      TINYINT(1)   NOT NULL,
  ip           VARBINARY(16) NULL,
  user_agent   VARCHAR(255) NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_consent_user (user_id, consent_type)
) ENGINE=InnoDB;

CREATE TABLE reviews (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  consultation_id BIGINT UNSIGNED NOT NULL UNIQUE,
  user_id         INT UNSIGNED NOT NULL,
  doctor_id       INT UNSIGNED NOT NULL,
  rating          TINYINT UNSIGNED NOT NULL,
  comment         VARCHAR(600) NULL,
  status          ENUM('pending','published','hidden') NOT NULL DEFAULT 'pending',
  moderated_by    INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE,
  INDEX idx_rev_doc (doctor_id, status)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 5. FREE LAYER  (the funnel: free tools, free first minutes)
-- ---------------------------------------------------------------------

CREATE TABLE free_tools (
  id          SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug        VARCHAR(60)  NOT NULL UNIQUE,
  name        VARCHAR(120) NOT NULL,
  tool_type   ENUM('assessment','calculator','guide','quiz') NOT NULL,
  for_gender  ENUM('all','male','female') NOT NULL DEFAULT 'all',
  intro_html  TEXT NULL,
  config_json JSON NOT NULL,               -- questions, scoring bands, result copy
  seo_title   VARCHAR(180) NULL,
  seo_desc    VARCHAR(320) NULL,
  is_active   TINYINT(1) NOT NULL DEFAULT 1,
  sort_order  SMALLINT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE free_tool_results (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tool_id     SMALLINT UNSIGNED NOT NULL,
  user_id     INT UNSIGNED NULL,           -- NULL = anonymous, pre-signup
  anon_token  CHAR(32) NULL,
  answers_json JSON NOT NULL,
  score       INT NULL,
  band        VARCHAR(60) NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ftr_user (user_id),
  INDEX idx_ftr_anon (anon_token)
) ENGINE=InnoDB;

CREATE TABLE free_minute_grants (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  minutes    SMALLINT NOT NULL,
  reason     ENUM('first_consult','campaign','goodwill','referral') NOT NULL,
  consumed   TINYINT(1) NOT NULL DEFAULT 0,
  consumed_on BIGINT UNSIGNED NULL,
  expires_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 6. CMS  (landing page, pages, blog, FAQ, city pages, menus)
-- ---------------------------------------------------------------------

CREATE TABLE cms_blocks (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  block_key    VARCHAR(80)  NOT NULL UNIQUE,   -- hero.headline, trust.strip
  block_group  VARCHAR(60)  NOT NULL,          -- home, footer, doctor_page
  label        VARCHAR(140) NOT NULL,
  content_type ENUM('text','html','image','json','number') NOT NULL DEFAULT 'text',
  content      MEDIUMTEXT   NULL,
  is_active    TINYINT(1)   NOT NULL DEFAULT 1,
  sort_order   SMALLINT     NOT NULL DEFAULT 0,
  updated_by   INT UNSIGNED NULL,
  updated_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_block_group (block_group, sort_order)
) ENGINE=InnoDB;

CREATE TABLE cms_pages (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug          VARCHAR(140) NOT NULL UNIQUE,
  title         VARCHAR(200) NOT NULL,
  body_html     MEDIUMTEXT   NULL,
  page_type     ENUM('static','legal','landing','city','speciality') NOT NULL DEFAULT 'static',
  parent_id     INT UNSIGNED NULL,
  hero_image    VARCHAR(255) NULL,
  is_published  TINYINT(1)   NOT NULL DEFAULT 0,
  published_at  DATETIME     NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE cms_posts (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug          VARCHAR(160) NOT NULL UNIQUE,
  title         VARCHAR(220) NOT NULL,
  excerpt       VARCHAR(400) NULL,
  body_html     MEDIUMTEXT   NOT NULL,
  cover_image   VARCHAR(255) NULL,
  category_id   SMALLINT UNSIGNED NULL,
  author_doctor_id INT UNSIGNED NULL,        -- medically reviewed by
  reviewed_by_doctor_id INT UNSIGNED NULL,
  reviewed_at   DATE         NULL,
  reading_mins  TINYINT      NULL,
  is_published  TINYINT(1)   NOT NULL DEFAULT 0,
  published_at  DATETIME     NULL,
  views         INT 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,
  INDEX idx_post_pub (is_published, published_at)
) ENGINE=InnoDB;

CREATE TABLE cms_categories (
  id         SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug       VARCHAR(80)  NOT NULL UNIQUE,
  name       VARCHAR(120) NOT NULL,
  seo_title  VARCHAR(180) NULL,
  seo_desc   VARCHAR(320) NULL,
  sort_order SMALLINT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE cms_faqs (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  scope       VARCHAR(80)  NOT NULL DEFAULT 'home',  -- home | speciality:<slug> | city:<slug>
  question    VARCHAR(300) NOT NULL,
  answer_html TEXT         NOT NULL,
  is_active   TINYINT(1)   NOT NULL DEFAULT 1,
  sort_order  SMALLINT     NOT NULL DEFAULT 0,
  INDEX idx_faq_scope (scope, sort_order)
) ENGINE=InnoDB;

CREATE TABLE cms_menu (
  id         SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  location   ENUM('header','footer_1','footer_2','footer_3') NOT NULL,
  label      VARCHAR(80) NOT NULL,
  url        VARCHAR(200) NOT NULL,
  rel_attr   VARCHAR(40) NULL,
  sort_order SMALLINT NOT NULL DEFAULT 0,
  is_active  TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE cms_media (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  file_path   VARCHAR(255) NOT NULL,
  alt_text    VARCHAR(200) NULL,
  mime        VARCHAR(60)  NULL,
  bytes       INT UNSIGNED NULL,
  uploaded_by INT UNSIGNED NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 7. SEO / AEO / GEO LAYER
-- ---------------------------------------------------------------------

CREATE TABLE seo_meta (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entity_type    ENUM('page','post','doctor','speciality','city','tool','home') NOT NULL,
  entity_id      INT UNSIGNED NOT NULL DEFAULT 0,
  route          VARCHAR(220) NOT NULL,
  meta_title     VARCHAR(200) NULL,
  meta_desc      VARCHAR(340) NULL,
  meta_keywords  VARCHAR(320) NULL,
  canonical_url  VARCHAR(250) NULL,
  og_title       VARCHAR(200) NULL,
  og_desc        VARCHAR(340) NULL,
  og_image       VARCHAR(255) NULL,
  twitter_card   VARCHAR(30)  NOT NULL DEFAULT 'summary_large_image',
  robots         VARCHAR(60)  NOT NULL DEFAULT 'index,follow',
  schema_json    JSON         NULL,          -- extra JSON-LD merged into page
  -- AEO: the short, quotable answer engines lift
  answer_summary VARCHAR(500) NULL,
  answer_bullets JSON         NULL,
  -- GEO
  geo_city       VARCHAR(80)  NULL,
  geo_state      VARCHAR(80)  NULL,
  geo_lat        DECIMAL(10,7) NULL,
  geo_lng        DECIMAL(10,7) NULL,
  hreflang_json  JSON         NULL,
  sitemap_include TINYINT(1)  NOT NULL DEFAULT 1,
  sitemap_priority DECIMAL(2,1) NOT NULL DEFAULT 0.5,
  sitemap_freq   ENUM('always','hourly','daily','weekly','monthly','yearly','never') NOT NULL DEFAULT 'weekly',
  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_seo_route (route),
  INDEX idx_seo_entity (entity_type, entity_id)
) ENGINE=InnoDB;

CREATE TABLE geo_cities (
  id          SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug        VARCHAR(80)  NOT NULL UNIQUE,
  name        VARCHAR(80)  NOT NULL,
  state       VARCHAR(80)  NOT NULL,
  lat         DECIMAL(10,7) NULL,
  lng         DECIMAL(10,7) NULL,
  population  INT UNSIGNED NULL,
  intro_html  TEXT NULL,
  is_active   TINYINT(1) NOT NULL DEFAULT 1,
  sort_order  SMALLINT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE seo_redirects (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  from_path  VARCHAR(250) NOT NULL UNIQUE,
  to_path    VARCHAR(250) NOT NULL,
  http_code  SMALLINT NOT NULL DEFAULT 301,
  hits       INT UNSIGNED NOT NULL DEFAULT 0,
  is_active  TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 8. STORE  (optional, Astromall-equivalent — wellness products only)
-- ---------------------------------------------------------------------

CREATE TABLE products (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug         VARCHAR(140) NOT NULL UNIQUE,
  name         VARCHAR(200) NOT NULL,
  category     VARCHAR(80)  NULL,
  short_desc   VARCHAR(400) NULL,
  body_html    MEDIUMTEXT   NULL,
  image_path   VARCHAR(255) NULL,
  mrp_paise    INT UNSIGNED NOT NULL,
  price_paise  INT UNSIGNED NOT NULL,
  gst_pct      DECIMAL(5,2) NOT NULL DEFAULT 18.00,
  hsn          VARCHAR(12)  NULL,
  stock_qty    INT NOT NULL DEFAULT 0,
  rx_required  TINYINT(1) NOT NULL DEFAULT 0,
  is_active    TINYINT(1) NOT NULL DEFAULT 1,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE orders (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_no      VARCHAR(24) NOT NULL UNIQUE,
  user_id       INT UNSIGNED NOT NULL,
  subtotal_paise INT UNSIGNED NOT NULL,
  gst_paise     INT UNSIGNED NOT NULL DEFAULT 0,
  shipping_paise INT UNSIGNED NOT NULL DEFAULT 0,
  total_paise   INT UNSIGNED NOT NULL,
  status        ENUM('created','paid','packed','shipped','delivered','cancelled','refunded') NOT NULL DEFAULT 'created',
  ship_name_enc VARBINARY(255) NULL,
  ship_addr_enc VARBINARY(600) NULL,
  ship_pin      VARCHAR(10) NULL,
  awb           VARCHAR(60) NULL,
  discreet_packaging TINYINT(1) NOT NULL DEFAULT 1,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id   BIGINT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  qty        SMALLINT NOT NULL,
  price_paise INT UNSIGNED NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 9. PAYOUTS
-- ---------------------------------------------------------------------

CREATE TABLE doctor_earnings (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  doctor_id       INT UNSIGNED NOT NULL,
  consultation_id BIGINT UNSIGNED NOT NULL,
  gross_paise     BIGINT NOT NULL,
  commission_paise BIGINT NOT NULL,
  tds_paise       BIGINT NOT NULL DEFAULT 0,
  net_paise       BIGINT NOT NULL,
  payout_id       BIGINT UNSIGNED NULL,
  status          ENUM('accrued','on_hold','paid','reversed') NOT NULL DEFAULT 'accrued',
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (doctor_id) REFERENCES doctors(id),
  INDEX idx_earn_doc (doctor_id, status)
) ENGINE=InnoDB;

CREATE TABLE payouts (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  doctor_id    INT UNSIGNED NOT NULL,
  payout_ref   VARCHAR(30) NOT NULL UNIQUE,
  period_from  DATE NOT NULL,
  period_to    DATE NOT NULL,
  gross_paise  BIGINT NOT NULL,
  tds_paise    BIGINT NOT NULL DEFAULT 0,
  net_paise    BIGINT NOT NULL,
  status       ENUM('pending','processing','paid','failed') NOT NULL DEFAULT 'pending',
  utr          VARCHAR(60) NULL,
  paid_at      DATETIME NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (doctor_id) REFERENCES doctors(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 10. SUPPORT, AUDIT, SETTINGS
-- ---------------------------------------------------------------------

CREATE TABLE tickets (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_no   VARCHAR(20) NOT NULL UNIQUE,
  raiser_type ENUM('user','doctor') NOT NULL,
  raiser_id   INT UNSIGNED NOT NULL,
  subject     VARCHAR(200) NOT NULL,
  category    VARCHAR(60) NULL,
  priority    ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
  status      ENUM('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
  assigned_to INT UNSIGNED NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE ticket_replies (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id  BIGINT UNSIGNED NOT NULL,
  author_type ENUM('user','doctor','admin') NOT NULL,
  author_id  INT UNSIGNED NOT NULL,
  body       TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- every unmasking / PII view is logged. Non-negotiable.
CREATE TABLE audit_log (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_type  ENUM('admin','doctor','user','system','cron') NOT NULL,
  actor_id    INT UNSIGNED NULL,
  action      VARCHAR(80) NOT NULL,
  entity      VARCHAR(60) NULL,
  entity_id   BIGINT UNSIGNED NULL,
  detail_json JSON NULL,
  ip          VARBINARY(16) NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_actor (actor_type, actor_id, created_at),
  INDEX idx_audit_action (action, created_at)
) ENGINE=InnoDB;

CREATE TABLE settings (
  skey       VARCHAR(80) PRIMARY KEY,
  sval       TEXT NULL,
  stype      ENUM('string','int','bool','json') NOT NULL DEFAULT 'string',
  sgroup     VARCHAR(40) NOT NULL DEFAULT 'general',
  label      VARCHAR(160) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE notifications (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_type  ENUM('user','doctor','admin') NOT NULL,
  actor_id    INT UNSIGNED NOT NULL,
  channel     ENUM('inapp','sms','email','whatsapp','push') NOT NULL,
  title       VARCHAR(160) NULL,
  body        TEXT NULL,
  status      ENUM('queued','sent','failed','read') NOT NULL DEFAULT 'queued',
  ref_table   VARCHAR(40) NULL,
  ref_id      BIGINT UNSIGNED NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_notif (actor_type, actor_id, status)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- SEED
-- ---------------------------------------------------------------------

INSERT INTO settings (skey,sval,stype,sgroup,label) VALUES
 ('site_name','Nirvaan','string','general','Site name'),
 ('site_url','https://nirvaan.in','string','general','Base URL'),
 ('support_email','care@nirvaan.in','string','general','Support email'),
 ('billing_descriptor','NVN WELLNESS','string','payments','Card statement descriptor'),
 ('mask_names_for_doctors','1','bool','privacy','Mask patient names from doctors'),
 ('mask_mobile_for_doctors','1','bool','privacy','Mask patient mobile from doctors'),
 ('allow_doctor_unmask_request','1','bool','privacy','Doctors may request unmasking with reason'),
 ('free_first_minutes','3','int','pricing','Free minutes on first consult'),
 ('min_balance_minutes','2','int','pricing','Minimum balance in minutes to start a consult'),
 ('gst_pct','18','int','pricing','GST percent on platform fee'),
 ('default_commission_pct','30','int','pricing','Default platform commission'),
 ('consult_request_ttl_secs','120','int','consult','Seconds before a request expires'),
 ('chat_idle_timeout_secs','180','int','consult','Idle seconds before auto-end'),
 ('dmr_compliance_mode','1','bool','legal','Block treatment/cure claims in CMS copy');

INSERT INTO specialities (slug,name,for_gender,sort_order) VALUES
 ('mens-sexual-health','Men''s sexual health','male',10),
 ('womens-sexual-health','Women''s sexual health','female',20),
 ('relationship-intimacy','Relationship and intimacy','all',30),
 ('sexual-anxiety','Performance anxiety and stress','all',40),
 ('reproductive-health','Reproductive health','all',50),
 ('sti-concerns','STI concerns and testing','all',60),
 ('adolescent-queries','Young adult queries','all',70),
 ('menopause-andropause','Menopause and andropause','all',80);

INSERT INTO recharge_packs (amount_paise,bonus_paise,label,sort_order) VALUES
 (20000,0,'Starter',10),(50000,5000,'Popular',20),
 (100000,15000,'Value',30),(200000,40000,'Best value',40);

INSERT INTO geo_cities (slug,name,state,lat,lng,sort_order) VALUES
 ('mumbai','Mumbai','Maharashtra',19.0760,72.8777,10),
 ('delhi','Delhi','Delhi',28.6139,77.2090,20),
 ('bengaluru','Bengaluru','Karnataka',12.9716,77.5946,30),
 ('hyderabad','Hyderabad','Telangana',17.3850,78.4867,40),
 ('chennai','Chennai','Tamil Nadu',13.0827,80.2707,50),
 ('pune','Pune','Maharashtra',18.5204,73.8567,60),
 ('kolkata','Kolkata','West Bengal',22.5726,88.3639,70),
 ('ahmedabad','Ahmedabad','Gujarat',23.0225,72.5714,80);