USE nirvaan;

/* Notification queue gains retry state */
ALTER TABLE notifications
  ADD COLUMN attempts        TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER status,
  ADD COLUMN next_attempt_at DATETIME NULL AFTER attempts,
  ADD COLUMN provider_ref    VARCHAR(120) NULL AFTER next_attempt_at,
  ADD COLUMN last_error      VARCHAR(250) NULL AFTER provider_ref,
  ADD COLUMN sent_at         DATETIME NULL AFTER last_error,
  ADD INDEX idx_notif_due (status, next_attempt_at);

/* Per-leg call record. Numbers are deliberately absent — the whole point
   of bridging is that neither side's number is stored against the call. */
CREATE TABLE IF NOT EXISTS call_legs (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  consultation_id BIGINT UNSIGNED NOT NULL,
  call_sid        VARCHAR(120) NOT NULL,
  leg             ENUM('doctor','patient') NOT NULL,
  status          VARCHAR(40)  NOT NULL DEFAULT 'initiated',
  duration_secs   INT UNSIGNED NOT NULL DEFAULT 0,
  started_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ended_at        DATETIME NULL,
  INDEX idx_leg_sid (call_sid),
  INDEX idx_leg_consult (consultation_id),
  FOREIGN KEY (consultation_id) REFERENCES consultations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

/* Webhook replay protection and an audit trail of what providers sent */
CREATE TABLE IF NOT EXISTS webhook_events (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider     VARCHAR(30)  NOT NULL,
  event_key    VARCHAR(160) NOT NULL,
  signature_ok TINYINT(1)   NOT NULL DEFAULT 0,
  payload      JSON         NULL,
  processed    TINYINT(1)   NOT NULL DEFAULT 0,
  result       VARCHAR(250) NULL,
  received_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_event (provider, event_key),
  INDEX idx_wh_recv (received_at)
) ENGINE=InnoDB;

ALTER TABLE consultations
  ADD COLUMN call_started_at DATETIME NULL AFTER call_sid;

/* Phase 5 settings */
INSERT INTO settings (skey,sval,stype,sgroup,label) VALUES
 ('msg91_sender_id','NIRVAN','string','messaging','MSG91 sender ID'),
 ('msg91_template_id','','string','messaging','MSG91 flow/template ID'),
 ('whatsapp_phone_id','','string','messaging','WhatsApp Cloud API phone number ID'),
 ('call_max_seconds','3600','int','consult','Hard cap on a single bridged call'),
 ('rx_followup_window_days','180','int','prescribing','Days a prior consultation counts as continuity of care'),
 ('rx_prohibited','[]','json','prescribing','Extra molecules never prescribable by telemedicine'),
 ('rx_list_a','[]','json','prescribing','Extra List A molecules (first consult needs video)'),
 ('rx_list_b','[]','json','prescribing','Extra List B molecules (follow-up only)')
ON DUPLICATE KEY UPDATE label = VALUES(label);