-- Migration: Add offline payment fee tracking
-- Run against ccserveradmin_forms_staging (and production after testing)

ALTER TABLE clients
  ADD COLUMN stripe_billing_customer_id VARCHAR(255) NULL AFTER stripe_account_id;

ALTER TABLE settings_clinic_payment
  ADD COLUMN allow_offline_payments TINYINT(1) NOT NULL DEFAULT 0 AFTER allow_half_payment,
  ADD COLUMN offline_billing_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER allow_offline_payments;

CREATE TABLE clinic_fee_accruals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_id INT(11) NOT NULL,
  submission_id INT(11) NOT NULL,
  patient_id INT(11) DEFAULT NULL,
  invoice_number VARCHAR(50) DEFAULT NULL,
  offline_payment_amount DECIMAL(10,2) NOT NULL,
  fee_percent DECIMAL(5,2) NOT NULL DEFAULT 2.50,
  fee_amount DECIMAL(10,2) NOT NULL,
  recorded_by_user_id INT(11) NOT NULL,
  recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status ENUM('pending','invoiced','paid','void') NOT NULL DEFAULT 'pending',
  stripe_invoice_id VARCHAR(255) DEFAULT NULL,
  stripe_invoice_line_id VARCHAR(255) DEFAULT NULL,
  invoiced_at DATETIME DEFAULT NULL,
  paid_at DATETIME DEFAULT NULL,
  note VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_client_status (client_id, status),
  KEY idx_submission (submission_id),
  KEY idx_stripe_invoice (stripe_invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
