-- Phase 10: therapy session workflow fields.
-- Safe to run against the existing grow_tool_therapies_crm database.

SET @db := DATABASE();

SET @sql := IF(
  EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=@db AND table_name='therapy_sessions' AND column_name='session_type'),
  'SELECT 1',
  'ALTER TABLE therapy_sessions ADD COLUMN session_type VARCHAR(80) NULL AFTER service_id'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=@db AND table_name='therapy_sessions' AND column_name='status'),
  'SELECT 1',
  "ALTER TABLE therapy_sessions ADD COLUMN status VARCHAR(30) NOT NULL DEFAULT 'scheduled' AFTER session_type"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema=@db AND table_name='therapy_sessions' AND index_name='idx_sessions_status_date'),
  'SELECT 1',
  'ALTER TABLE therapy_sessions ADD KEY idx_sessions_status_date (status, session_date)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
