-- CarePulse IoT — CENTRAL PLATFORM DATABASE
-- Holds platform-level data only: clients, domains, plans, device library, leads, Level 1 users.
-- NO patient data is ever stored here. Each client gets its own database (see client.sql).

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS 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('owner','admin','support') NOT NULL DEFAULT 'admin',
  totp_secret VARCHAR(64) NULL,
  totp_enabled TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  failed_logins INT NOT NULL DEFAULT 0,
  locked_until DATETIME NULL,
  last_login DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  billing_basis ENUM('per_bed','per_device','per_patient','flat') NOT NULL DEFAULT 'per_patient',
  price_month DECIMAL(10,2) NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'INR',
  device_quota INT NOT NULL DEFAULT 50,
  patient_quota INT NOT NULL DEFAULT 200,
  user_quota INT NOT NULL DEFAULT 25,
  features TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS clients (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  org_type ENUM('hospital','clinic','diagnostic','homecare','corporate','other') NOT NULL DEFAULT 'hospital',
  city VARCHAR(80) NULL,
  state VARCHAR(80) NULL,
  country VARCHAR(80) NOT NULL DEFAULT 'India',
  beds INT NULL,
  contact_name VARCHAR(120) NULL,
  contact_email VARCHAR(160) NULL,
  contact_mobile VARCHAR(20) NULL,
  db_name VARCHAR(64) NOT NULL UNIQUE,
  db_host VARCHAR(120) NULL COMMENT 'NULL = default platform DB server; set for dedicated/on-prem DB',
  db_user VARCHAR(64) NULL,
  db_pass VARCHAR(255) NULL COMMENT 'encrypted with APP_KEY',
  plan_id INT UNSIGNED NULL,
  device_quota INT NOT NULL DEFAULT 50,
  patient_quota INT NOT NULL DEFAULT 200,
  user_quota INT NOT NULL DEFAULT 25,
  his_type ENUM('none','caresoft','other') NOT NULL DEFAULT 'none',
  status ENUM('trial','active','suspended','closed') NOT NULL DEFAULT 'trial',
  trial_ends DATE NULL,
  schema_version INT NOT NULL DEFAULT 0,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS client_domains (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  domain VARCHAR(190) NOT NULL UNIQUE,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Global, Caresoft-managed device model library. Clients pick from this.
CREATE TABLE IF NOT EXISTS device_models (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category VARCHAR(40) NOT NULL,
  manufacturer VARCHAR(80) NOT NULL,
  model VARCHAR(120) NOT NULL,
  connection ENUM('ble_standard','ble_vendor','hl7','serial','mqtt','cloud_api','file','manual','photo') NOT NULL,
  ble_service VARCHAR(10) NULL COMMENT '16-bit GATT service UUID, e.g. 1810',
  name_prefix VARCHAR(60) NULL COMMENT 'BLE advertised name prefix for auto-detect',
  parameters VARCHAR(255) NOT NULL COMMENT 'comma separated parameter codes',
  protocol_notes TEXT NULL,
  certified TINYINT(1) NOT NULL DEFAULT 0,
  phase TINYINT NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leads (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  organisation VARCHAR(160) NULL,
  designation VARCHAR(80) NULL,
  email VARCHAR(160) NULL,
  mobile VARCHAR(20) NULL,
  city VARCHAR(80) NULL,
  interest VARCHAR(80) NULL,
  beds VARCHAR(20) NULL,
  message TEXT NULL,
  form_type ENUM('enquiry','demo','trial','partner') NOT NULL DEFAULT 'enquiry',
  source_page VARCHAR(255) NULL,
  utm_source VARCHAR(80) NULL,
  utm_medium VARCHAR(80) NULL,
  utm_campaign VARCHAR(120) NULL,
  status ENUM('new','contacted','demo_scheduled','proposal','won','lost') NOT NULL DEFAULT 'new',
  notes TEXT NULL,
  ip VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (status), KEY (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS platform_settings (
  k VARCHAR(80) PRIMARY KEY,
  v TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS platform_audit (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  detail TEXT NULL,
  ip VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-client usage counts only (never patient-level data)
CREATE TABLE IF NOT EXISTS usage_snapshots (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  snap_date DATE NOT NULL,
  patients_active INT NOT NULL DEFAULT 0,
  devices_active INT NOT NULL DEFAULT 0,
  gateways_online INT NOT NULL DEFAULT 0,
  readings_day INT NOT NULL DEFAULT 0,
  alerts_day INT NOT NULL DEFAULT 0,
  users_active INT NOT NULL DEFAULT 0,
  UNIQUE KEY (client_id, snap_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS central_meta (
  k VARCHAR(40) PRIMARY KEY,
  v VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO central_meta (k, v) VALUES ('schema_version', '1');

-- Default plans
INSERT INTO plans (name, billing_basis, price_month, device_quota, patient_quota, user_quota, features) VALUES
('RPM Starter', 'per_patient', 499, 100, 100, 10, 'Home RPM, WhatsApp alerts, patient app'),
('Hospital Connect', 'per_bed', 1500, 300, 1000, 60, 'RPM + ward monitoring, HIS link, early warning'),
('Enterprise', 'flat', 0, 5000, 20000, 500, 'Multi-branch, dedicated DB, on-prem option');
