-- =====================================================================
-- Caresoft Visitor Management System (VMS)
-- MySQL 8.x  |  utf8mb4  |  InnoDB
-- 3-tier: L1 platform (super admin)  L2 client admin  L3 gate user
-- =====================================================================

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

SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- LEVEL 1 : PLATFORM
-- ---------------------------------------------------------------------

CREATE TABLE platform_users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(120)  NOT NULL,
  email         VARCHAR(160)  NOT NULL UNIQUE,
  mobile        VARCHAR(20)   NULL,
  password_hash VARCHAR(255)  NOT NULL,
  role          ENUM('super_admin','platform_support') NOT NULL DEFAULT 'platform_support',
  status        ENUM('active','inactive') NOT NULL DEFAULT 'active',
  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
) ENGINE=InnoDB;

CREATE TABLE plans (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code           VARCHAR(40) NOT NULL UNIQUE,
  name           VARCHAR(120) NOT NULL,
  max_gates      INT UNSIGNED NOT NULL DEFAULT 2,
  max_users      INT UNSIGNED NOT NULL DEFAULT 10,
  max_visits_day INT UNSIGNED NOT NULL DEFAULT 500,
  allow_caresoft TINYINT(1) NOT NULL DEFAULT 0,
  allow_photo    TINYINT(1) NOT NULL DEFAULT 1,
  allow_api      TINYINT(1) NOT NULL DEFAULT 0,
  price_year     DECIMAL(12,2) NOT NULL DEFAULT 0,
  status         ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- A client = one hospital / hospital group tenant
CREATE TABLE clients (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_code    VARCHAR(30)  NOT NULL UNIQUE,      -- e.g. VMS-ANANTA
  name           VARCHAR(180) NOT NULL,             -- display / brand name
  legal_name     VARCHAR(200) NULL,
  hospital_type  VARCHAR(60)  NULL,                 -- Multi-speciality / Trust / Nursing home
  bed_count      INT UNSIGNED NULL,
  address1       VARCHAR(200) NULL,
  address2       VARCHAR(200) NULL,
  city           VARCHAR(80)  NULL,
  state          VARCHAR(80)  NULL,
  country        VARCHAR(80)  NOT NULL DEFAULT 'India',
  pincode        VARCHAR(12)  NULL,
  contact_person VARCHAR(120) NULL,
  contact_email  VARCHAR(160) NULL,
  contact_mobile VARCHAR(20)  NULL,
  gstin          VARCHAR(20)  NULL,
  logo_path      VARCHAR(255) NULL,
  theme_color    VARCHAR(10)  NOT NULL DEFAULT '#0E3A42',
  timezone       VARCHAR(60)  NOT NULL DEFAULT 'Asia/Kolkata',

  plan_id        INT UNSIGNED NULL,
  valid_from     DATE NOT NULL,
  valid_to       DATE NOT NULL,
  status         ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',

  onboarded_by   INT UNSIGNED NULL,                 -- platform_users.id
  notes          TEXT NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_clients_status (status, valid_to),
  CONSTRAINT fk_clients_plan FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB;

-- Per-client key/value settings (branding, pass rules, otp, consent text ...)
CREATE TABLE client_settings (
  client_id   INT UNSIGNED NOT NULL,
  skey        VARCHAR(64)  NOT NULL,
  svalue      TEXT NULL,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (client_id, skey),
  CONSTRAINT fk_cs_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---- STEP 1 : CARESOFT HIS INTEGRATION (configured at Level 1) -------
CREATE TABLE caresoft_integration (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id       INT UNSIGNED NOT NULL UNIQUE,
  enabled         TINYINT(1) NOT NULL DEFAULT 0,
  base_url        VARCHAR(255) NULL,      -- https://his.hospital.in/api
  hospital_code   VARCHAR(40)  NULL,      -- Caresoft HIS unit / branch code
  auth_type       ENUM('apikey','basic','bearer') NOT NULL DEFAULT 'apikey',
  api_key         VARCHAR(255) NULL,
  api_secret      VARCHAR(255) NULL,
  ep_patient      VARCHAR(160) NOT NULL DEFAULT '/vms/patient',    -- lookup by UHID / IPD no
  ep_ward         VARCHAR(160) NOT NULL DEFAULT '/vms/wards',
  ep_doctor       VARCHAR(160) NOT NULL DEFAULT '/vms/doctors',
  ep_visit_push   VARCHAR(160) NOT NULL DEFAULT '/vms/visitor-log', -- push gate pass back to HIS
  sync_wards      TINYINT(1) NOT NULL DEFAULT 1,
  sync_doctors    TINYINT(1) NOT NULL DEFAULT 1,
  push_visits     TINYINT(1) NOT NULL DEFAULT 1,
  verify_ssl      TINYINT(1) NOT NULL DEFAULT 1,
  timeout_sec     INT UNSIGNED NOT NULL DEFAULT 15,
  last_sync_at    DATETIME NULL,
  last_status     VARCHAR(255) NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_ci_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE caresoft_sync_log (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id   INT UNSIGNED NOT NULL,
  action      VARCHAR(60) NOT NULL,       -- patient_lookup / ward_sync / visit_push
  ref         VARCHAR(80) NULL,           -- UHID / visit id
  http_code   INT NULL,
  ok          TINYINT(1) NOT NULL DEFAULT 0,
  request     MEDIUMTEXT NULL,
  response    MEDIUMTEXT NULL,
  ms          INT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_csl (client_id, action, created_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- LEVEL 2 / 3 : CLIENT MASTERS
-- ---------------------------------------------------------------------

CREATE TABLE users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id     INT UNSIGNED NOT NULL,
  name          VARCHAR(120) NOT NULL,
  email         VARCHAR(160) NULL,
  mobile        VARCHAR(20)  NULL,
  username      VARCHAR(80)  NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('client_admin','supervisor','gate_user','reports_only') NOT NULL DEFAULT 'gate_user',
  gate_id       INT UNSIGNED NULL,          -- default gate for L3 users
  status        ENUM('active','inactive') NOT NULL DEFAULT 'active',
  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,
  UNIQUE KEY uq_user (client_id, username),
  KEY idx_users_client (client_id, role, status),
  CONSTRAINT fk_users_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Gates / entry points. Each gate has its own QR token.
CREATE TABLE gates (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id    INT UNSIGNED NOT NULL,
  gate_code    VARCHAR(30)  NOT NULL,        -- MAIN, OPD, EMG, ICU
  gate_name    VARCHAR(120) NOT NULL,
  qr_token     CHAR(32)     NOT NULL UNIQUE, -- goes into the printed QR
  block_name   VARCHAR(120) NULL,
  self_service TINYINT(1) NOT NULL DEFAULT 1, -- visitor can self register at this gate
  status       ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_gate (client_id, gate_code),
  CONSTRAINT fk_gates_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Wards / departments (local, or pulled from Caresoft HIS)
CREATE TABLE departments (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id    INT UNSIGNED NOT NULL,
  dept_code    VARCHAR(40)  NOT NULL,
  dept_name    VARCHAR(140) NOT NULL,
  dept_type    ENUM('ward','opd','icu','ot','admin','other') NOT NULL DEFAULT 'ward',
  floor_name   VARCHAR(80) NULL,
  max_visitors INT UNSIGNED NOT NULL DEFAULT 2,   -- per patient, at a time
  source       ENUM('local','caresoft') NOT NULL DEFAULT 'local',
  status       ENUM('active','inactive') NOT NULL DEFAULT 'active',
  UNIQUE KEY uq_dept (client_id, dept_code),
  CONSTRAINT fk_dept_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE doctors (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id  INT UNSIGNED NOT NULL,
  doc_code   VARCHAR(40) NOT NULL,
  doc_name   VARCHAR(160) NOT NULL,
  speciality VARCHAR(120) NULL,
  source     ENUM('local','caresoft') NOT NULL DEFAULT 'local',
  status     ENUM('active','inactive') NOT NULL DEFAULT 'active',
  UNIQUE KEY uq_doc (client_id, doc_code),
  CONSTRAINT fk_doc_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE purposes (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id    INT UNSIGNED NOT NULL,
  purpose_name VARCHAR(120) NOT NULL,
  needs_patient TINYINT(1) NOT NULL DEFAULT 1,   -- ask UHID / patient details
  pass_minutes  INT UNSIGNED NOT NULL DEFAULT 120,
  sort_order    INT NOT NULL DEFAULT 0,
  status        ENUM('active','inactive') NOT NULL DEFAULT 'active',
  UNIQUE KEY uq_purpose (client_id, purpose_name),
  CONSTRAINT fk_purpose_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Marketing / patient-education content shown on the signup screen
CREATE TABLE content_blocks (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id   INT UNSIGNED NOT NULL,
  slot        ENUM('signup_top','signup_bottom','pass_footer','thankyou','consent') NOT NULL,
  title       VARCHAR(180) NULL,
  body_html   MEDIUMTEXT NULL,
  image_path  VARCHAR(255) NULL,
  link_url    VARCHAR(255) NULL,
  lang        VARCHAR(10) NOT NULL DEFAULT 'en',
  sort_order  INT NOT NULL DEFAULT 0,
  status      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_content (client_id, slot, status),
  CONSTRAINT fk_content_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- VISITORS & VISITS
-- ---------------------------------------------------------------------

CREATE TABLE visitors (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id      INT UNSIGNED NOT NULL,
  full_name      VARCHAR(140) NOT NULL,
  mobile         VARCHAR(20)  NOT NULL,
  mobile_verified TINYINT(1)  NOT NULL DEFAULT 0,
  email          VARCHAR(160) NULL,
  gender         ENUM('M','F','O') NULL,
  id_type        VARCHAR(40) NULL,           -- Aadhaar / DL / Voter (last 4 only)
  id_last4       VARCHAR(8)  NULL,
  address        VARCHAR(255) NULL,
  photo_path     VARCHAR(255) NULL,
  opt_in_marketing TINYINT(1) NOT NULL DEFAULT 0,
  total_visits   INT UNSIGNED NOT NULL DEFAULT 0,
  first_seen_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at   DATETIME NULL,
  UNIQUE KEY uq_visitor (client_id, mobile),
  KEY idx_visitor_name (client_id, full_name),
  CONSTRAINT fk_visitor_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE visits (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id     INT UNSIGNED NOT NULL,
  visitor_id    BIGINT UNSIGNED NOT NULL,
  gate_id       INT UNSIGNED NULL,
  pass_no       VARCHAR(30)  NOT NULL,        -- VP/2607/000123
  qr_token      CHAR(40)     NOT NULL UNIQUE, -- printed on pass, scanned at gate
  purpose_id    INT UNSIGNED NULL,
  purpose_text  VARCHAR(120) NULL,

  -- patient context (from Caresoft HIS where integrated)
  patient_uhid    VARCHAR(40)  NULL,
  patient_ipd_no  VARCHAR(40)  NULL,
  patient_name    VARCHAR(160) NULL,
  ward_code       VARCHAR(40)  NULL,
  ward_name       VARCHAR(140) NULL,
  bed_no          VARCHAR(30)  NULL,
  doctor_name     VARCHAR(160) NULL,
  patient_source  ENUM('manual','caresoft') NOT NULL DEFAULT 'manual',
  relation        VARCHAR(60)  NULL,

  whom_to_meet  VARCHAR(160) NULL,            -- for non-patient visits (vendor/staff)
  vehicle_no    VARCHAR(20)  NULL,
  belongings    VARCHAR(255) NULL,

  status        ENUM('pending','issued','in','out','expired','denied','cancelled')
                NOT NULL DEFAULT 'pending',
  issued_at     DATETIME NULL,
  valid_till    DATETIME NULL,
  in_at         DATETIME NULL,
  out_at        DATETIME NULL,
  duration_min  INT UNSIGNED NULL,

  issued_by     INT UNSIGNED NULL,            -- users.id (NULL = visitor self-service)
  in_by         INT UNSIGNED NULL,
  out_by        INT UNSIGNED NULL,
  channel       ENUM('self_qr','gate_desk','pre_approved') NOT NULL DEFAULT 'self_qr',
  deny_reason   VARCHAR(255) NULL,
  remarks       VARCHAR(255) NULL,
  pushed_to_his 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_pass (client_id, pass_no),
  KEY idx_visit_status (client_id, status, created_at),
  KEY idx_visit_day    (client_id, created_at),
  KEY idx_visit_uhid   (client_id, patient_uhid),
  KEY idx_visit_gate   (client_id, gate_id, created_at),
  CONSTRAINT fk_visit_client  FOREIGN KEY (client_id)  REFERENCES clients(id)  ON DELETE CASCADE,
  CONSTRAINT fk_visit_visitor FOREIGN KEY (visitor_id) REFERENCES visitors(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE otp_log (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id   INT UNSIGNED NOT NULL,
  mobile      VARCHAR(20) NOT NULL,
  otp_hash    VARCHAR(255) NOT NULL,
  channel     ENUM('whatsapp','sms') NOT NULL DEFAULT 'whatsapp',
  session_key CHAR(32) NOT NULL,
  attempts    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  verified    TINYINT(1) NOT NULL DEFAULT 0,
  expires_at  DATETIME NOT NULL,
  ip          VARCHAR(45) NULL,
  provider_msg_id VARCHAR(120) NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_otp (client_id, mobile, created_at),
  KEY idx_otp_sess (session_key)
) ENGINE=InnoDB;

CREATE TABLE blacklist (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id  INT UNSIGNED NOT NULL,
  mobile     VARCHAR(20) NULL,
  full_name  VARCHAR(140) NULL,
  reason     VARCHAR(255) NOT NULL,
  added_by   INT UNSIGNED NULL,
  status     ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bl (client_id, mobile, status),
  CONSTRAINT fk_bl_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE audit_log (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id  INT UNSIGNED NULL,
  actor_type ENUM('platform','client','gate','visitor','system') NOT NULL,
  actor_id   INT UNSIGNED NULL,
  action     VARCHAR(80) NOT NULL,
  entity     VARCHAR(60) NULL,
  entity_id  VARCHAR(60) NULL,
  detail     TEXT NULL,
  ip         VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit (client_id, created_at),
  KEY idx_audit_action (action, created_at)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- Reporting helpers
-- ---------------------------------------------------------------------
CREATE OR REPLACE VIEW v_visit_daily AS
SELECT client_id,
       DATE(created_at) AS visit_date,
       COUNT(*)                                        AS total_passes,
       SUM(status='in')                                AS currently_in,
       SUM(status='out')                               AS completed,
       SUM(status='denied')                            AS denied,
       SUM(status='expired')                           AS expired,
       ROUND(AVG(NULLIF(duration_min,0)),1)            AS avg_minutes
FROM visits
GROUP BY client_id, DATE(created_at);

CREATE OR REPLACE VIEW v_ward_load AS
SELECT client_id, ward_name,
       COUNT(*) AS passes,
       SUM(status='in') AS inside_now
FROM visits
WHERE ward_name IS NOT NULL
GROUP BY client_id, ward_name;

-- ---------------------------------------------------------------------
-- Website demo requests (public landing page)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS demo_requests (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(120) NOT NULL,
  hospital   VARCHAR(180) NOT NULL,
  mobile     VARCHAR(20)  NOT NULL,
  email      VARCHAR(160) NULL,
  city       VARCHAR(80)  NULL,
  bed_count  INT UNSIGNED NULL,
  message    TEXT NULL,
  status     ENUM('new','contacted','demo_done','won','lost') NOT NULL DEFAULT 'new',
  ip         VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_dr (status, created_at)
) ENGINE=InnoDB;