-- Phase 17.5: Session -> Billing integration
-- Run once against grow_tool_therapies_crm. Safe to run more than once.

SET @db = DATABASE();

SET @sql = IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='therapy_sessions' AND column_name='billable')=0,
  'ALTER TABLE therapy_sessions ADD COLUMN billable TINYINT(1) NOT NULL DEFAULT 0 AFTER service_id',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='invoice_items' AND column_name='session_id')=0,
  'ALTER TABLE invoice_items ADD COLUMN session_id BIGINT UNSIGNED NULL AFTER service_id',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db AND table_name='invoice_items' AND index_name='idx_invoice_items_session')=0,
  'ALTER TABLE invoice_items ADD KEY idx_invoice_items_session (session_id)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.table_constraints WHERE constraint_schema=@db AND table_name='invoice_items' AND constraint_name='fk_invoice_items_session')=0,
  'ALTER TABLE invoice_items ADD CONSTRAINT fk_invoice_items_session FOREIGN KEY (session_id) REFERENCES therapy_sessions(id) ON DELETE RESTRICT',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
