-- =====================================================================
-- CareMeet — Caresoft Telemedicine / Video Platform
-- Phase 1 database schema  |  MySQL 8.0+  |  InnoDB / utf8mb4
-- =====================================================================
-- DESIGN NOTES (read before altering)
--  * Multi-tenant by tenant_id on every tenant-owned row. Platform-level
--    rows (super admins, plans, media nodes) have no tenant_id.
--  * Media path is NOT in this DB. Signaling + SFU keep live room state
--    in Redis; MySQL holds durable record only. Never make a call block
--    on a MySQL write.
--  * Raw per-second WebRTC quality samples DO NOT belong here. At 10k
--    concurrent that is ~240k rows/min. Raw samples -> ClickHouse.
--    MySQL keeps the per-session aggregate only (session_quality_summary).
--  * All timestamps UTC. Render in tenant timezone at the app layer.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- LEVEL 1 : PLATFORM (Caresoft)
-- =====================================================================

CREATE TABLE platform_admins (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name              VARCHAR(120)  NOT NULL,
  email             VARCHAR(190)  NOT NULL,
  phone             VARCHAR(20)   NULL,
  password_hash     VARCHAR(255)  NOT NULL,
  role              ENUM('super_admin','ops','support','billing','readonly') NOT NULL DEFAULT 'support',
  totp_secret       VARCHAR(64)   NULL,          -- 2FA mandatory for super_admin
  is_2fa_enabled    TINYINT(1)    NOT NULL DEFAULT 0,
  status            ENUM('active','suspended') NOT NULL DEFAULT 'active',
  last_login_at     DATETIME      NULL,
  last_login_ip     VARBINARY(16) NULL,
  failed_attempts   SMALLINT UNSIGNED 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,
  UNIQUE KEY uq_padmin_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE plans (
  id                     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plan_code              VARCHAR(40)  NOT NULL,
  name                   VARCHAR(120) NOT NULL,
  max_concurrent_calls   INT UNSIGNED NOT NULL DEFAULT 10,
  max_participants_room  SMALLINT UNSIGNED NOT NULL DEFAULT 2,
  monthly_minutes        BIGINT UNSIGNED NOT NULL DEFAULT 0,     -- 0 = unmetered
  overage_paise_per_min  INT UNSIGNED NOT NULL DEFAULT 0,
  recording_enabled      TINYINT(1)   NOT NULL DEFAULT 0,
  recording_retention_days INT UNSIGNED NOT NULL DEFAULT 90,
  storage_gb_included    INT UNSIGNED NOT NULL DEFAULT 0,
  screen_share_enabled   TINYINT(1)   NOT NULL DEFAULT 1,
  custom_domain_enabled  TINYINT(1)   NOT NULL DEFAULT 0,
  sfu_pool               VARCHAR(40)  NOT NULL DEFAULT 'shared', -- shared | dedicated
  monthly_price_paise    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  is_public              TINYINT(1)   NOT NULL DEFAULT 1,
  status                 ENUM('active','retired') 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_plan_code (plan_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Media infrastructure inventory. The scheduler reads this to place rooms.
-- Adding capacity later = INSERT here. This is what makes 500 -> 10,000
-- a config change instead of a rewrite.
CREATE TABLE media_nodes (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  node_type         ENUM('sfu','turn','recorder','signaling') NOT NULL,
  hostname          VARCHAR(190) NOT NULL,
  public_ip         VARCHAR(45)  NOT NULL,
  private_ip        VARCHAR(45)  NULL,
  region            VARCHAR(40)  NOT NULL DEFAULT 'in-mum',  -- in-mum | in-blr | af-nbo ...
  pool              VARCHAR(40)  NOT NULL DEFAULT 'shared',
  capacity_units    INT UNSIGNED NOT NULL DEFAULT 1000,      -- SFU: consumers; recorder: streams
  current_load      INT UNSIGNED NOT NULL DEFAULT 0,
  cpu_pct           DECIMAL(5,2) NOT NULL DEFAULT 0,
  status            ENUM('active','draining','maintenance','down') NOT NULL DEFAULT 'active',
  last_heartbeat_at DATETIME NULL,
  meta              JSON NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_node_host (node_type, hostname),
  KEY ix_node_place (node_type, region, pool, status, current_load)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- LEVEL 2 : TENANT (hospital / lab / pharmacy chain / clinic group)
-- =====================================================================

CREATE TABLE tenants (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_code         VARCHAR(40)  NOT NULL,          -- CARE-0001
  name                VARCHAR(190) NOT NULL,
  legal_name          VARCHAR(190) NULL,
  subdomain           VARCHAR(63)  NOT NULL,          -- apollo.caremeet.in
  custom_domain       VARCHAR(190) NULL,
  contact_person      VARCHAR(120) NULL,
  contact_email       VARCHAR(190) NULL,
  contact_phone       VARCHAR(20)  NULL,
  address_line        VARCHAR(255) NULL,
  city                VARCHAR(80)  NULL,
  state               VARCHAR(80)  NULL,
  country_code        CHAR(2)      NOT NULL DEFAULT 'IN',
  timezone            VARCHAR(64)  NOT NULL DEFAULT 'Asia/Kolkata',
  abdm_hfr_id         VARCHAR(60)  NULL,              -- Health Facility Registry ID
  his_client_code     VARCHAR(60)  NULL,              -- maps to Caresoft HIS install
  preferred_region    VARCHAR(40)  NOT NULL DEFAULT 'in-mum',
  status              ENUM('onboarding','active','suspended','churned') NOT NULL DEFAULT 'onboarding',
  onboarded_by        BIGINT UNSIGNED NULL,
  go_live_date        DATE NULL,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_tenant_code (tenant_code),
  UNIQUE KEY uq_tenant_subdomain (subdomain),
  UNIQUE KEY uq_tenant_domain (custom_domain),
  KEY ix_tenant_status (status),
  CONSTRAINT fk_tenant_onboarder FOREIGN KEY (onboarded_by) REFERENCES platform_admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE tenant_subscriptions (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id           BIGINT UNSIGNED NOT NULL,
  plan_id             BIGINT UNSIGNED NOT NULL,
  starts_on           DATE NOT NULL,
  ends_on             DATE NULL,
  minutes_allotted    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  minutes_consumed    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  concurrent_override INT UNSIGNED NULL,              -- overrides plan cap
  status              ENUM('active','expired','cancelled') NOT NULL DEFAULT 'active',
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY ix_sub_tenant (tenant_id, status),
  CONSTRAINT fk_sub_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_sub_plan   FOREIGN KEY (plan_id)   REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Branding + call policy. One row per tenant.
CREATE TABLE tenant_settings (
  tenant_id                BIGINT UNSIGNED PRIMARY KEY,
  logo_url                 VARCHAR(255) NULL,
  brand_primary            CHAR(7) NOT NULL DEFAULT '#0B5FFF',
  brand_secondary          CHAR(7) NOT NULL DEFAULT '#0A2540',
  waiting_room_message     VARCHAR(500) NULL,
  waiting_room_media_url   VARCHAR(255) NULL,
  recording_policy         ENUM('disabled','optional','always') NOT NULL DEFAULT 'optional',
  recording_consent_text   TEXT NULL,
  recording_consent_version VARCHAR(20) NOT NULL DEFAULT 'v1',
  audio_only_fallback_kbps INT UNSIGNED NOT NULL DEFAULT 150,  -- degrade below this
  max_call_minutes         INT UNSIGNED NOT NULL DEFAULT 60,
  allow_guest_join         TINYINT(1) NOT NULL DEFAULT 1,
  require_patient_otp      TINYINT(1) NOT NULL DEFAULT 1,
  chat_enabled             TINYINT(1) NOT NULL DEFAULT 1,
  file_share_enabled       TINYINT(1) NOT NULL DEFAULT 1,
  data_retention_days      INT UNSIGNED NOT NULL DEFAULT 365,  -- DPDP
  updated_at               DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_settings_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE departments (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  name        VARCHAR(120) NOT NULL,
  code        VARCHAR(40)  NULL,
  status      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_dept (tenant_id, name),
  CONSTRAINT fk_dept_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- LEVEL 3 : USERS (client admin, doctor, staff, patient, lab, pharmacy)
-- =====================================================================

CREATE TABLE users (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id         BIGINT UNSIGNED NOT NULL,
  department_id     BIGINT UNSIGNED NULL,
  role              ENUM('client_admin','doctor','nurse','staff','lab_tech','pharmacist','patient','observer')
                    NOT NULL DEFAULT 'staff',
  name              VARCHAR(120) NOT NULL,
  email             VARCHAR(190) NULL,
  phone             VARCHAR(20)  NULL,
  password_hash     VARCHAR(255) NULL,               -- NULL for OTP-only patients
  external_ref      VARCHAR(80)  NULL,               -- HIS doctor id / UHID
  registration_no   VARCHAR(80)  NULL,               -- NMC reg no — required for doctors
  qualification     VARCHAR(190) NULL,
  photo_url         VARCHAR(255) NULL,
  locale            VARCHAR(10)  NOT NULL DEFAULT 'en',
  status            ENUM('active','inactive','suspended') 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_tenant_email (tenant_id, email),
  UNIQUE KEY uq_user_tenant_phone (tenant_id, phone),
  KEY ix_user_role (tenant_id, role, status),
  KEY ix_user_extref (tenant_id, external_ref),
  CONSTRAINT fk_user_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_dept   FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_sessions (
  id            CHAR(36) PRIMARY KEY,
  actor_type    ENUM('platform_admin','user') NOT NULL,
  actor_id      BIGINT UNSIGNED NOT NULL,
  tenant_id     BIGINT UNSIGNED NULL,
  ip            VARBINARY(16) NULL,
  user_agent    VARCHAR(255) NULL,
  issued_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at    DATETIME NOT NULL,
  revoked_at    DATETIME NULL,
  KEY ix_sess_actor (actor_type, actor_id, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE otp_verifications (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     BIGINT UNSIGNED NULL,
  channel       ENUM('sms','whatsapp','email') NOT NULL DEFAULT 'sms',
  destination   VARCHAR(190) NOT NULL,
  purpose       ENUM('login','room_join','consent') NOT NULL DEFAULT 'room_join',
  code_hash     VARCHAR(255) NOT NULL,
  attempts      TINYINT UNSIGNED NOT NULL DEFAULT 0,
  expires_at    DATETIME NOT NULL,
  consumed_at   DATETIME NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_otp_lookup (destination, purpose, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- PRODUCT INTEGRATION — how MyOPD / Digital IPD / Lab / Pharma connect
-- Consuming products never touch WebRTC. They call POST /v1/rooms with
-- these credentials and receive a short-lived join token.
-- =====================================================================

CREATE TABLE api_clients (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id         BIGINT UNSIGNED NOT NULL,
  product_code      VARCHAR(40)  NOT NULL,   -- myopd | digital_ipd | digital_opd | labsuite | pharmacy | his | eicu
  name              VARCHAR(120) NOT NULL,
  api_key           VARCHAR(64)  NOT NULL,
  api_secret_hash   VARCHAR(255) NOT NULL,
  allowed_origins   TEXT NULL,               -- CSV of origins for browser SDK
  allowed_ips       TEXT NULL,               -- CSV/CIDR for server-to-server
  scopes            VARCHAR(255) NOT NULL DEFAULT 'room:create,room:join,recording:read',
  webhook_url       VARCHAR(255) NULL,
  webhook_secret    VARCHAR(120) NULL,
  rate_limit_per_min INT UNSIGNED NOT NULL DEFAULT 600,
  status            ENUM('active','revoked') NOT NULL DEFAULT 'active',
  last_used_at      DATETIME NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_apikey (api_key),
  KEY ix_apiclient_tenant (tenant_id, product_code, status),
  CONSTRAINT fk_apiclient_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE webhook_deliveries (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  api_client_id BIGINT UNSIGNED NOT NULL,
  event_type    VARCHAR(60) NOT NULL,        -- room.ended, recording.ready, participant.joined
  payload       JSON NOT NULL,
  attempts      TINYINT UNSIGNED NOT NULL DEFAULT 0,
  last_status   SMALLINT NULL,
  next_retry_at DATETIME NULL,
  delivered_at  DATETIME NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_wh_pending (delivered_at, next_retry_at),
  CONSTRAINT fk_wh_client FOREIGN KEY (api_client_id) REFERENCES api_clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- ROOMS & CALLS
-- =====================================================================

CREATE TABLE rooms (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_uuid         CHAR(36) NOT NULL,
  tenant_id         BIGINT UNSIGNED NOT NULL,
  api_client_id     BIGINT UNSIGNED NULL,
  purpose           ENUM('opd_consult','ipd_round','followup','lab_review','pharmacy','tele_icu','second_opinion','internal')
                    NOT NULL DEFAULT 'opd_consult',
  external_ref      VARCHAR(80) NULL,        -- appointment id / admission no in source product
  title             VARCHAR(190) NULL,
  mode              ENUM('p2p','sfu') NOT NULL DEFAULT 'p2p',
  max_participants  SMALLINT UNSIGNED NOT NULL DEFAULT 2,
  recording_mode    ENUM('off','on_demand','auto') NOT NULL DEFAULT 'off',
  scheduled_at      DATETIME NULL,
  expires_at        DATETIME NOT NULL,
  started_at        DATETIME NULL,
  ended_at          DATETIME NULL,
  end_reason        ENUM('host_ended','all_left','expired','timeout','admin_terminated','error') NULL,
  sfu_node_id       BIGINT UNSIGNED NULL,
  turn_node_id      BIGINT UNSIGNED NULL,
  region            VARCHAR(40) NOT NULL DEFAULT 'in-mum',
  status            ENUM('scheduled','waiting','live','ended','cancelled') NOT NULL DEFAULT 'scheduled',
  peak_participants SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  total_minutes     DECIMAL(10,2) NOT NULL DEFAULT 0,
  created_by        BIGINT UNSIGNED NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_room_uuid (room_uuid),
  KEY ix_room_tenant_status (tenant_id, status, scheduled_at),
  KEY ix_room_extref (tenant_id, external_ref),
  KEY ix_room_live (status, started_at),
  CONSTRAINT fk_room_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_room_client FOREIGN KEY (api_client_id) REFERENCES api_clients(id) ON DELETE SET NULL,
  CONSTRAINT fk_room_sfu    FOREIGN KEY (sfu_node_id) REFERENCES media_nodes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE room_participants (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id           BIGINT UNSIGNED NOT NULL,
  tenant_id         BIGINT UNSIGNED NOT NULL,
  user_id           BIGINT UNSIGNED NULL,          -- NULL = guest patient
  display_name      VARCHAR(120) NOT NULL,
  call_role         ENUM('host','doctor','patient','attendant','observer','interpreter') NOT NULL DEFAULT 'patient',
  invite_phone      VARCHAR(20)  NULL,
  invite_email      VARCHAR(190) NULL,
  token_jti         CHAR(36) NULL,                 -- for revocation
  token_expires_at  DATETIME NULL,
  invited_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  first_joined_at   DATETIME NULL,
  last_left_at      DATETIME NULL,
  total_seconds     INT UNSIGNED NOT NULL DEFAULT 0,
  attendance        ENUM('invited','joined','no_show','declined') NOT NULL DEFAULT 'invited',
  KEY ix_part_room (room_id),
  KEY ix_part_user (tenant_id, user_id),
  UNIQUE KEY uq_part_jti (token_jti),
  CONSTRAINT fk_part_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_part_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per media connection. A reconnect creates a NEW row.
CREATE TABLE call_sessions (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id           BIGINT UNSIGNED NOT NULL,
  participant_id    BIGINT UNSIGNED NOT NULL,
  tenant_id         BIGINT UNSIGNED NOT NULL,
  joined_at         DATETIME NOT NULL,
  left_at           DATETIME NULL,
  duration_seconds  INT UNSIGNED NOT NULL DEFAULT 0,
  ip                VARBINARY(16) NULL,
  device_type       ENUM('desktop','android','ios','tablet','unknown') NOT NULL DEFAULT 'unknown',
  browser           VARCHAR(60) NULL,
  os                VARCHAR(60) NULL,
  network_type      VARCHAR(20) NULL,              -- wifi | 4g | 5g | ethernet
  ice_candidate_type ENUM('host','srflx','prflx','relay') NULL,  -- 'relay' = TURN = costs money
  relayed           TINYINT(1) NOT NULL DEFAULT 0,
  leave_reason      ENUM('normal','network_drop','browser_closed','kicked','error','timeout') NULL,
  KEY ix_cs_room (room_id),
  KEY ix_cs_tenant_day (tenant_id, joined_at),
  KEY ix_cs_relay (tenant_id, relayed, joined_at),
  CONSTRAINT fk_cs_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_cs_part FOREIGN KEY (participant_id) REFERENCES room_participants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Aggregate only. Raw 5-second samples go to ClickHouse, never here.
CREATE TABLE session_quality_summary (
  session_id        BIGINT UNSIGNED PRIMARY KEY,
  room_id           BIGINT UNSIGNED NOT NULL,
  tenant_id         BIGINT UNSIGNED NOT NULL,
  avg_rtt_ms        SMALLINT UNSIGNED NULL,
  p95_rtt_ms        SMALLINT UNSIGNED NULL,
  avg_jitter_ms     SMALLINT UNSIGNED NULL,
  avg_packet_loss   DECIMAL(5,2) NULL,
  max_packet_loss   DECIMAL(5,2) NULL,
  avg_bitrate_kbps  INT UNSIGNED NULL,
  avg_fps           TINYINT UNSIGNED NULL,
  resolution_mode   VARCHAR(20) NULL,
  audio_fallback_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  reconnect_count   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  mos_estimate      DECIMAL(3,2) NULL,             -- 1.00 - 5.00, for SLA defence
  computed_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_sq_tenant (tenant_id, computed_at),
  KEY ix_sq_bad (tenant_id, mos_estimate),
  CONSTRAINT fk_sq_session FOREIGN KEY (session_id) REFERENCES call_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE call_events (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id       BIGINT UNSIGNED NOT NULL,
  session_id    BIGINT UNSIGNED NULL,
  tenant_id     BIGINT UNSIGNED NOT NULL,
  event_type    VARCHAR(50) NOT NULL,  -- join,leave,mute_audio,video_off,screen_share_start,
                                       -- recording_start,recording_stop,consent_given,
                                       -- degraded_to_audio,reconnect,waiting_admitted,kicked
  payload       JSON NULL,
  occurred_at   DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  KEY ix_ev_room (room_id, occurred_at),
  KEY ix_ev_tenant (tenant_id, event_type, occurred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE room_chat (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id       BIGINT UNSIGNED NOT NULL,
  tenant_id     BIGINT UNSIGNED NOT NULL,
  participant_id BIGINT UNSIGNED NULL,
  message_type  ENUM('text','file','image','system') NOT NULL DEFAULT 'text',
  body          TEXT NULL,
  file_url      VARCHAR(255) NULL,
  file_name     VARCHAR(190) NULL,
  file_size     INT UNSIGNED NULL,
  mime_type     VARCHAR(100) NULL,
  sent_at       DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  KEY ix_chat_room (room_id, sent_at),
  CONSTRAINT fk_chat_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- RECORDING  (Phase 1)
-- Pipeline: mediasoup PlainTransport -> ffmpeg (codec copy, no transcode)
--           -> raw file -> async worker transcodes to MP4 -> object store
-- =====================================================================

CREATE TABLE recordings (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  recording_uuid      CHAR(36) NOT NULL,
  room_id             BIGINT UNSIGNED NOT NULL,
  tenant_id           BIGINT UNSIGNED NOT NULL,
  recorder_node_id    BIGINT UNSIGNED NULL,
  trigger_type        ENUM('auto','manual','api') NOT NULL DEFAULT 'manual',
  started_by          BIGINT UNSIGNED NULL,
  started_at          DATETIME NOT NULL,
  ended_at            DATETIME NULL,
  duration_seconds    INT UNSIGNED NOT NULL DEFAULT 0,
  status              ENUM('capturing','queued','processing','ready','failed','deleted','expired')
                      NOT NULL DEFAULT 'capturing',
  raw_path            VARCHAR(500) NULL,
  output_path         VARCHAR(500) NULL,
  output_format       VARCHAR(20) NOT NULL DEFAULT 'mp4',
  size_bytes          BIGINT UNSIGNED NOT NULL DEFAULT 0,
  is_encrypted        TINYINT(1) NOT NULL DEFAULT 1,
  encryption_key_ref  VARCHAR(190) NULL,          -- KMS key id, never the key itself
  checksum_sha256     CHAR(64) NULL,
  processing_error    VARCHAR(500) NULL,
  retention_until     DATE NULL,
  deleted_at          DATETIME NULL,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_rec_uuid (recording_uuid),
  KEY ix_rec_room (room_id),
  KEY ix_rec_tenant_status (tenant_id, status),
  KEY ix_rec_queue (status, created_at),
  KEY ix_rec_retention (retention_until, status),
  CONSTRAINT fk_rec_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Telemedicine Practice Guidelines 2020 + DPDP 2023: consent is mandatory
-- and must be provable per participant.
CREATE TABLE recording_consents (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id           BIGINT UNSIGNED NOT NULL,
  participant_id    BIGINT UNSIGNED NOT NULL,
  tenant_id         BIGINT UNSIGNED NOT NULL,
  consented         TINYINT(1) NOT NULL DEFAULT 0,
  consent_text_hash CHAR(64) NOT NULL,
  consent_version   VARCHAR(20) NOT NULL,
  method            ENUM('click','otp','verbal_logged') NOT NULL DEFAULT 'click',
  ip                VARBINARY(16) NULL,
  user_agent        VARCHAR(255) NULL,
  recorded_at       DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY uq_consent (room_id, participant_id),
  KEY ix_consent_tenant (tenant_id, recorded_at),
  CONSTRAINT fk_consent_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE recording_access_log (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  recording_id  BIGINT UNSIGNED NOT NULL,
  tenant_id     BIGINT UNSIGNED NOT NULL,
  actor_type    ENUM('platform_admin','user','api') NOT NULL,
  actor_id      BIGINT UNSIGNED NULL,
  action        ENUM('view','download','share_link','delete','restore') NOT NULL,
  ip            VARBINARY(16) NULL,
  occurred_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_ral_rec (recording_id, occurred_at),
  KEY ix_ral_tenant (tenant_id, occurred_at),
  CONSTRAINT fk_ral_rec FOREIGN KEY (recording_id) REFERENCES recordings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- CLINICAL CONTEXT  (light — prescriptions stay in Caresoft HIS)
-- =====================================================================

CREATE TABLE consultation_notes (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id           BIGINT UNSIGNED NOT NULL,
  tenant_id         BIGINT UNSIGNED NOT NULL,
  doctor_user_id    BIGINT UNSIGNED NULL,
  patient_ref       VARCHAR(80) NULL,              -- UHID in HIS
  chief_complaint   VARCHAR(500) NULL,
  notes             TEXT NULL,
  advice            TEXT NULL,
  followup_date     DATE NULL,
  prescription_ref  VARCHAR(80) NULL,              -- id of Rx written back into HIS
  abdm_linked       TINYINT(1) NOT NULL DEFAULT 0,
  abdm_care_context VARCHAR(120) NULL,
  signed_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_note_room (room_id),
  KEY ix_note_patient (tenant_id, patient_ref),
  CONSTRAINT fk_note_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- USAGE, BILLING, AUDIT
-- =====================================================================

CREATE TABLE usage_daily (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id             BIGINT UNSIGNED NOT NULL,
  usage_date            DATE NOT NULL,
  total_rooms           INT UNSIGNED NOT NULL DEFAULT 0,
  completed_rooms       INT UNSIGNED NOT NULL DEFAULT 0,
  no_show_rooms         INT UNSIGNED NOT NULL DEFAULT 0,
  participant_minutes   DECIMAL(12,2) NOT NULL DEFAULT 0,
  relayed_minutes       DECIMAL(12,2) NOT NULL DEFAULT 0,   -- the expensive ones
  recorded_minutes      DECIMAL(12,2) NOT NULL DEFAULT 0,
  egress_gb             DECIMAL(12,3) NOT NULL DEFAULT 0,
  storage_gb            DECIMAL(12,3) NOT NULL DEFAULT 0,
  peak_concurrent       INT UNSIGNED NOT NULL DEFAULT 0,
  avg_mos               DECIMAL(3,2) NULL,
  computed_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_usage (tenant_id, usage_date),
  KEY ix_usage_date (usage_date),
  CONSTRAINT fk_usage_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_log (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     BIGINT UNSIGNED NULL,
  actor_type    ENUM('platform_admin','user','api','system') NOT NULL,
  actor_id      BIGINT UNSIGNED NULL,
  action        VARCHAR(80) NOT NULL,
  entity_type   VARCHAR(60) NULL,
  entity_id     VARCHAR(60) NULL,
  before_json   JSON NULL,
  after_json    JSON NULL,
  ip            VARBINARY(16) NULL,
  occurred_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_audit_tenant (tenant_id, occurred_at),
  KEY ix_audit_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- SEED
-- =====================================================================

INSERT INTO plans (plan_code, name, max_concurrent_calls, max_participants_room, monthly_minutes,
                   recording_enabled, recording_retention_days, storage_gb_included,
                   custom_domain_enabled, sfu_pool, monthly_price_paise) VALUES
 ('starter',    'Starter',     5,   2,   3000,  0,  30,   0, 0, 'shared',    0),
 ('clinic',     'Clinic',      15,  4,  15000,  1,  90,  25, 0, 'shared',    0),
 ('hospital',   'Hospital',    60,  8,  75000,  1, 180, 150, 1, 'shared',    0),
 ('enterprise', 'Enterprise', 500,  25,     0,  1, 365, 1000, 1, 'dedicated', 0);

-- Replace with real hosts at deploy time.
INSERT INTO media_nodes (node_type, hostname, public_ip, region, pool, capacity_units) VALUES
 ('signaling','sig-01.caremeet.internal','10.0.1.11','in-mum','shared',20000),
 ('sfu',      'sfu-01.caremeet.internal','10.0.2.11','in-mum','shared', 1500),
 ('turn',     'turn-01.caremeet.internal','10.0.3.11','in-mum','shared', 2000),
 ('recorder', 'rec-01.caremeet.internal','10.0.4.11','in-mum','shared',  200);