-- CareKiosk Phase 1 schema  (MySQL 8 / MariaDB 10.6+)
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS tenants (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code          VARCHAR(40)  NOT NULL UNIQUE,
  name          VARCHAR(160) NOT NULL,
  short_name    VARCHAR(60)  NULL,
  status        ENUM('active','suspended') NOT NULL DEFAULT 'active',
  plan          VARCHAR(40)  NOT NULL DEFAULT 'standard',
  licence_until DATE NULL,
  max_devices   SMALLINT UNSIGNED NOT NULL DEFAULT 2,
  settings      LONGTEXT NULL,                          -- JSON: branding, theme, kiosk behaviour, contact, grievance
  his_type      ENUM('none','caresoft','generic_rest') NOT NULL DEFAULT 'none',
  his_config    TEXT NULL,                              -- encrypted JSON
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tenant_domains (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id   INT UNSIGNED NOT NULL,
  domain      VARCHAR(190) NOT NULL UNIQUE,             -- lower-case host, no port
  is_primary  TINYINT(1) NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_dom_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Level 1 super (tenant_id NULL) / Level 2 hospital admin / Level 3 front-desk operator
CREATE TABLE IF NOT EXISTS users (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id      INT UNSIGNED NULL,
  role           ENUM('super','admin','operator') NOT NULL,
  name           VARCHAR(120) NOT NULL,
  email          VARCHAR(190) NOT NULL,
  password_hash  VARCHAR(255) NOT NULL,
  is_active      TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at  DATETIME NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_tenant_email (tenant_id, email),
  CONSTRAINT fk_user_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attempts (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  scope_key    VARCHAR(190) NOT NULL,
  attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_scope_time (scope_key, attempted_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS departments (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id    INT UNSIGNED NOT NULL,
  name         VARCHAR(120) NOT NULL,
  names_i18n   TEXT NULL,                               -- JSON {"hi":"..","mr":".."}
  token_prefix VARCHAR(6) NOT NULL DEFAULT 'GEN',
  his_code     VARCHAR(40) NULL,
  sort_order   SMALLINT NOT NULL DEFAULT 0,
  is_active    TINYINT(1) NOT NULL DEFAULT 1,
  KEY ix_dept_tenant (tenant_id),
  CONSTRAINT fk_dept_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kiosk_devices (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id       INT UNSIGNED NOT NULL,
  label           VARCHAR(80) NOT NULL,
  location        VARCHAR(120) NULL,
  token_hash      CHAR(64) NULL,                         -- sha256 of the device cookie
  pairing_code    CHAR(6) NULL,
  pairing_expires DATETIME NULL,
  paired_at       DATETIME NULL,
  last_seen_at    DATETIME NULL,
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_device_token (token_hash),
  KEY ix_device_tenant (tenant_id),
  CONSTRAINT fk_dev_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- PII columns encrypted at rest (libsodium secretbox). mobile_hash = keyed HMAC for lookup.
CREATE TABLE IF NOT EXISTS registrations (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id         INT UNSIGNED NOT NULL,
  device_id         INT UNSIGNED NULL,
  ref_no            VARCHAR(24) NOT NULL,
  patient_type      ENUM('new','returning') NOT NULL DEFAULT 'new',
  full_name         VARCHAR(160) NOT NULL,
  gender            ENUM('M','F','O') NOT NULL,
  dob               DATE NULL,
  age_years         TINYINT UNSIGNED NULL,
  mobile_enc        TEXT NOT NULL,
  mobile_hash       CHAR(64) NOT NULL,
  mobile_last4      CHAR(4) NOT NULL,
  email_enc         TEXT NULL,
  address_enc       TEXT NULL,
  city              VARCHAR(80) NULL,
  pincode           CHAR(6) NULL,
  guardian_name     VARCHAR(160) NULL,
  guardian_relation VARCHAR(40) NULL,
  department_id     INT UNSIGNED NULL,
  token_no          VARCHAR(16) NULL,
  photo_file        VARCHAR(80) NULL,
  lang              VARCHAR(5) NOT NULL DEFAULT 'en',
  status            ENUM('waiting','verified','registered','cancelled') NOT NULL DEFAULT 'waiting',
  his_sync          ENUM('na','pending','synced','failed') NOT NULL DEFAULT 'na',
  his_uhid          VARCHAR(40) NULL,
  his_error         VARCHAR(255) NULL,
  withdraw_token    CHAR(32) NOT NULL,
  handled_by        INT UNSIGNED NULL,
  handled_at        DATETIME NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_reg_ref (tenant_id, ref_no),
  UNIQUE KEY uq_reg_withdraw (withdraw_token),
  KEY ix_reg_tenant_date (tenant_id, created_at),
  KEY ix_reg_mobile (tenant_id, mobile_hash),
  CONSTRAINT fk_reg_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS token_counters (
  tenant_id   INT UNSIGNED NOT NULL,
  counter_day DATE NOT NULL,
  prefix      VARCHAR(6) NOT NULL,
  last_no     INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (tenant_id, counter_day, prefix)
) ENGINE=InnoDB;

-- DPDP: purposes, versioned notices, append-only hash-chained consent ledger
CREATE TABLE IF NOT EXISTS consent_purposes (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id   INT UNSIGNED NOT NULL,
  code        VARCHAR(40) NOT NULL,
  title_i18n  TEXT NOT NULL,                            -- JSON {"en":..,"hi":..,"mr":..}
  desc_i18n   TEXT NOT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 0,             -- needed to deliver care itself
  sort_order  SMALLINT NOT NULL DEFAULT 0,
  is_active   TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_purpose (tenant_id, code),
  CONSTRAINT fk_purpose_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consent_notices (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id    INT UNSIGNED NOT NULL,
  version      SMALLINT UNSIGNED NOT NULL,
  body_i18n    LONGTEXT NOT NULL,
  body_hash    CHAR(64) NOT NULL,
  published_by INT UNSIGNED NULL,
  published_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_current   TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_notice_ver (tenant_id, version),
  CONSTRAINT fk_notice_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consent_records (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id       INT UNSIGNED NOT NULL,
  registration_id BIGINT UNSIGNED NOT NULL,
  purpose_code    VARCHAR(40) NOT NULL,
  action          ENUM('granted','denied','withdrawn') NOT NULL,
  notice_version  SMALLINT UNSIGNED NOT NULL,
  notice_hash     CHAR(64) NOT NULL,
  lang            VARCHAR(5) NOT NULL,
  given_by        ENUM('self','guardian') NOT NULL DEFAULT 'self',
  channel         ENUM('kiosk','web','desk') NOT NULL DEFAULT 'kiosk',
  device_id       INT UNSIGNED NULL,
  ip_addr         VARCHAR(45) NULL,
  user_agent      VARCHAR(255) NULL,
  prev_hash       CHAR(64) NOT NULL,
  record_hash     CHAR(64) NOT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_consent_reg (registration_id),
  KEY ix_consent_tenant (tenant_id, id),
  CONSTRAINT fk_consent_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_log (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id  INT UNSIGNED NULL,
  user_id    INT UNSIGNED NULL,
  action     VARCHAR(60) NOT NULL,
  entity     VARCHAR(40) NULL,
  entity_id  VARCHAR(40) NULL,
  detail     TEXT NULL,
  ip_addr    VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_audit_tenant (tenant_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;