-- ============================================================
--  CareBill — client portal
--  Run once against an existing database:  mysql carebill < sql/portal.sql
--  (schema.sql already contains all of this for fresh installs)
-- ============================================================

ALTER TABLE clients          ADD COLUMN portal_enabled TINYINT(1) DEFAULT 1 AFTER status;
ALTER TABLE client_contacts  ADD COLUMN portal_access  TINYINT(1) DEFAULT 1 AFTER rx_escalation;

-- one-time sign-in codes. No client passwords are ever stored: a hospital's
-- accounts desk changes hands often and a forgotten password becomes our support call.
CREATE TABLE portal_logins (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NOT NULL,
  contact_id      INT NULL,
  email           VARCHAR(160) NOT NULL,
  code_hash       VARCHAR(255) NOT NULL,
  expires_at      DATETIME NOT NULL,
  used_at         DATETIME NULL,
  attempts        INT DEFAULT 0,
  ip              VARCHAR(45),
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX ix_pl_email (email, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE portal_sessions (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NOT NULL,
  contact_id      INT NULL,
  token_hash      VARCHAR(255) NOT NULL,
  expires_at      DATETIME NOT NULL,
  last_seen       DATETIME NULL,
  ip              VARCHAR(45),
  user_agent      VARCHAR(255),
  revoked         TINYINT(1) DEFAULT 0,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX ix_ps_token (token_hash), INDEX ix_ps_client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- "we have paid, here is the UTR" — an intimation, never a receipt.
-- Money only enters the ledger when accounts sees it in the bank.
CREATE TABLE payment_advices (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NOT NULL,
  invoice_id      INT NULL,
  contact_id      INT NULL,
  amount          DECIMAL(14,2) NOT NULL,
  pay_date        DATE NULL,
  mode            VARCHAR(20) DEFAULT 'neft',
  reference       VARCHAR(120),
  note            TEXT,
  attachment      VARCHAR(255) NULL,
  status          ENUM('new','matched','rejected') DEFAULT 'new',
  payment_id      BIGINT NULL,
  bank_txn_id     BIGINT NULL,
  reviewed_by     INT NULL,
  reviewed_at     DATETIME NULL,
  review_note     VARCHAR(255),
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX ix_pa_status (status, created_at), INDEX ix_pa_client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- questions, disputes and document requests raised from the portal
CREATE TABLE client_queries (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NOT NULL,
  invoice_id      INT NULL,
  contact_id      INT NULL,
  type            ENUM('dispute','question','copy_request','contact_change') DEFAULT 'question',
  subject         VARCHAR(200),
  body            TEXT,
  status          ENUM('open','answered','closed') DEFAULT 'open',
  reply           TEXT,
  replied_by      INT NULL,
  replied_at      DATETIME NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX ix_cq_status (status, created_at), INDEX ix_cq_client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- what the client did in the portal, so a later argument has a record
CREATE TABLE portal_log (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  client_id       INT NULL,
  contact_id      INT NULL,
  email           VARCHAR(160),
  action          VARCHAR(60),
  detail          VARCHAR(255),
  ip              VARCHAR(45),
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX ix_plog_client (client_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;