-- =====================================================================
-- Caresoft Imaging — migration 008
-- P10: teleradiology — partners, rate cards, allocation rules,
--      study forwarding, SLA tracking and monthly reconciliation.
-- Safe to run on a database created from schema.sql + migrations 002-007.
-- =====================================================================
USE `caresoft_imaging`;

-- Who reads for this hospital: its own staff, empanelled individuals, or a
-- teleradiology company.
CREATE TABLE IF NOT EXISTS img_telerad_partner (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  name          VARCHAR(160) NOT NULL,
  partner_type  ENUM('internal','empanelled','outsourced') NOT NULL DEFAULT 'empanelled',
  contact_person VARCHAR(140) NULL,
  email         VARCHAR(160) NULL,
  phone         VARCHAR(40)  NULL,
  agreement_ref VARCHAR(80)  NULL,
  currency      VARCHAR(8)   NOT NULL DEFAULT 'INR',
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  notes         TEXT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_tp (tenant_id, is_active),
  CONSTRAINT fk_tp_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- What each study is worth, and how quickly it must come back.
-- Rates are dated: last quarter's invoice must not change when a new rate
-- is agreed.
CREATE TABLE IF NOT EXISTS img_telerad_rate (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  partner_id     INT UNSIGNED NOT NULL,
  modality_type  VARCHAR(8) NULL COMMENT 'NULL = any modality',
  priority       ENUM('any','routine','urgent','stat') NOT NULL DEFAULT 'any',
  rate           DECIMAL(10,2) NOT NULL,
  sla_minutes    INT NOT NULL DEFAULT 1440,
  effective_from DATE NOT NULL,
  effective_to   DATE NULL,
  KEY ix_tr (partner_id, modality_type, priority, effective_from),
  CONSTRAINT fk_tr_partner FOREIGN KEY (partner_id) REFERENCES img_telerad_partner(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Who gets what, and when. Matched in priority order, first hit wins.
CREATE TABLE IF NOT EXISTS img_allocation_rule (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  name          VARCHAR(140) NOT NULL,
  modality_type VARCHAR(8) NULL,
  priority      ENUM('any','routine','urgent','stat') NOT NULL DEFAULT 'any',
  visit_type    VARCHAR(20) NULL,
  hour_from     TINYINT NULL COMMENT 'NULL = any hour; 22 with hour_to 8 means overnight',
  hour_to       TINYINT NULL,
  partner_id    INT UNSIGNED NULL COMMENT 'send to this partner',
  radiologist_id INT UNSIGNED NULL COMMENT 'or to this named reader',
  forward_study TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'push images to the central node too',
  sort_order    INT NOT NULL DEFAULT 100,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  KEY ix_ar (tenant_id, is_active, sort_order),
  CONSTRAINT fk_ar_ten FOREIGN KEY (tenant_id) REFERENCES tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- An outsourced reader's account is tied to their partner, and sees only
-- what has been allocated to them.
ALTER TABLE app_user
  ADD COLUMN partner_id INT UNSIGNED NULL COMMENT 'set for external readers' AFTER branch_id,
  ADD KEY ix_user_partner (partner_id);

ALTER TABLE img_allocation
  ADD COLUMN partner_id    INT UNSIGNED NULL AFTER radiologist_id,
  ADD COLUMN rule_id       INT UNSIGNED NULL AFTER partner_id,
  ADD COLUMN currency      VARCHAR(8) NULL AFTER rate,
  ADD COLUMN accepted_at   DATETIME NULL AFTER allocated_at,
  ADD COLUMN declined_at   DATETIME NULL AFTER accepted_at,
  ADD COLUMN decline_reason VARCHAR(255) NULL AFTER declined_at,
  ADD COLUMN reassigned_from INT UNSIGNED NULL AFTER decline_reason,
  ADD COLUMN invoice_id    BIGINT UNSIGNED NULL AFTER breached,
  ADD KEY ix_alloc_partner (tenant_id, partner_id, completed_at),
  ADD KEY ix_alloc_invoice (invoice_id);

-- Images pushed to a central node for remote reading. A queue rather than a
-- flag, so a failed forward is retried rather than lost.
CREATE TABLE IF NOT EXISTS img_study_forward (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id    INT UNSIGNED NOT NULL,
  study_id     BIGINT UNSIGNED NOT NULL,
  peer         VARCHAR(80) NOT NULL COMMENT 'Orthanc peer name on the site node',
  status       ENUM('queued','sending','sent','failed') NOT NULL DEFAULT 'queued',
  attempts     INT NOT NULL DEFAULT 0,
  queued_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at      DATETIME NULL,
  last_error   VARCHAR(255) NULL,
  KEY ix_sf (tenant_id, status, queued_at),
  KEY ix_sf_study (study_id),
  CONSTRAINT fk_sf_study FOREIGN KEY (study_id) REFERENCES img_study(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Monthly reconciliation. The hospital's own count, not the partner's.
CREATE TABLE IF NOT EXISTS img_telerad_invoice (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  partner_id    INT UNSIGNED NOT NULL,
  period_from   DATE NOT NULL,
  period_to     DATE NOT NULL,
  study_count   INT NOT NULL DEFAULT 0,
  within_sla    INT NOT NULL DEFAULT 0,
  breached      INT NOT NULL DEFAULT 0,
  amount        DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency      VARCHAR(8) NOT NULL DEFAULT 'INR',
  status        ENUM('draft','sent','agreed','disputed','paid') NOT NULL DEFAULT 'draft',
  partner_claim DECIMAL(12,2) NULL COMMENT 'what the partner invoiced, for comparison',
  notes         TEXT NULL,
  generated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  generated_by  INT UNSIGNED NULL,
  UNIQUE KEY uq_period (tenant_id, partner_id, period_from, period_to),
  KEY ix_ti (tenant_id, status),
  CONSTRAINT fk_ti_partner FOREIGN KEY (partner_id) REFERENCES img_telerad_partner(id) ON DELETE CASCADE
) ENGINE=InnoDB;