-- Phase 4: subscription billing & usage metering (central DB)
ALTER TABLE clients ADD COLUMN gstin VARCHAR(15) NULL, ADD COLUMN billing_address VARCHAR(400) NULL, ADD COLUMN billing_email VARCHAR(160) NULL,
  ADD COLUMN billing_cycle ENUM('monthly','quarterly','yearly') NOT NULL DEFAULT 'monthly', ADD COLUMN unit_price DECIMAL(10,2) NULL COMMENT 'negotiated price per unit; NULL = plan price',
  ADD COLUMN min_units INT NOT NULL DEFAULT 0, ADD COLUMN billing_start DATE NULL;
ALTER TABLE plans ADD COLUMN included_whatsapp INT NOT NULL DEFAULT 500, ADD COLUMN included_voice INT NOT NULL DEFAULT 50,
  ADD COLUMN addon_waveform_bed DECIMAL(10,2) NOT NULL DEFAULT 800, ADD COLUMN addon_rtls_tag DECIMAL(10,2) NOT NULL DEFAULT 150,
  ADD COLUMN price_whatsapp DECIMAL(6,2) NOT NULL DEFAULT 0.90, ADD COLUMN price_voice DECIMAL(6,2) NOT NULL DEFAULT 3.00;
ALTER TABLE usage_snapshots ADD COLUMN beds_occupied INT NOT NULL DEFAULT 0, ADD COLUMN waveform_beds INT NOT NULL DEFAULT 0, ADD COLUMN tags_active INT NOT NULL DEFAULT 0,
  ADD COLUMN whatsapp_sent INT NOT NULL DEFAULT 0, ADD COLUMN sms_sent INT NOT NULL DEFAULT 0, ADD COLUMN email_sent INT NOT NULL DEFAULT 0,
  ADD COLUMN voice_calls INT NOT NULL DEFAULT 0, ADD COLUMN consults INT NOT NULL DEFAULT 0;
CREATE TABLE IF NOT EXISTS invoices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  number VARCHAR(30) NULL,
  period_start DATE NOT NULL, period_end DATE NOT NULL,
  items TEXT NOT NULL COMMENT 'JSON [{desc, sac, qty, unit, rate, amount}]',
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  tax_type ENUM('igst','cgst_sgst','none') NOT NULL DEFAULT 'igst',
  tax_rate DECIMAL(5,2) NOT NULL DEFAULT 18,
  tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'INR',
  status ENUM('draft','issued','paid','void') NOT NULL DEFAULT 'draft',
  issued_at DATETIME NULL, due_date DATE NULL, paid_at DATETIME NULL, payment_ref VARCHAR(120) NULL,
  notes VARCHAR(500) NULL, created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY (number), UNIQUE KEY (client_id, period_start), KEY (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
UPDATE central_meta SET v = '2' WHERE k = 'schema_version';
