-- Phase 18: bill all completed clinical work, including approved assessments.
-- 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='assessments' AND column_name='service_id')=0,
  'ALTER TABLE assessments ADD COLUMN service_id BIGINT UNSIGNED NULL AFTER assignment_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='assessments' AND index_name='idx_assessments_service')=0,
  'ALTER TABLE assessments ADD KEY idx_assessments_service (service_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='assessments' AND constraint_name='fk_assessments_service')=0,
  'ALTER TABLE assessments ADD CONSTRAINT fk_assessments_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE SET NULL',
  '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='assessment_id')=0,
  'ALTER TABLE invoice_items ADD COLUMN assessment_id BIGINT UNSIGNED NULL AFTER session_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_assessment')=0,
  'ALTER TABLE invoice_items ADD KEY idx_invoice_items_assessment (assessment_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_assessment')=0,
  'ALTER TABLE invoice_items ADD CONSTRAINT fk_invoice_items_assessment FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE RESTRICT',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Existing completed sessions that already have a service become billable automatically.
UPDATE therapy_sessions SET billable=1 WHERE service_id IS NOT NULL AND (status='completed' OR status IS NULL);
