-- Aggie-server counterpart of add_assignable_employee.sql.
-- Idempotent — safe to re-run.
-- Deploy: mysql -u <user> -p 'stacie_Aggie_v1.0' < db/aggie_add_assignable_employee.sql

USE `stacie_Aggie_v1.0`;

SET @db = DATABASE();

-- Projects/Tasks
SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_project_tasks' AND COLUMN_NAME='assigned_employee_id') = 0,
  'ALTER TABLE fact_project_tasks ADD COLUMN assigned_employee_id INT NULL AFTER assigned_user_id',
  'SELECT 1'));
PREPARE s FROM @stmt; EXECUTE s; DEALLOCATE PREPARE s;

SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_project_tasks' AND INDEX_NAME='idx_fact_project_tasks_assigned_employee') = 0,
  'ALTER TABLE fact_project_tasks ADD INDEX idx_fact_project_tasks_assigned_employee (tenant_id, is_deleted, assigned_employee_id)',
  'SELECT 1'));
PREPARE s FROM @stmt; EXECUTE s; DEALLOCATE PREPARE s;

-- CRM Leads
SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_leads' AND COLUMN_NAME='assigned_employee_id') = 0,
  'ALTER TABLE fact_leads ADD COLUMN assigned_employee_id INT NULL AFTER assigned_to',
  'SELECT 1'));
PREPARE s FROM @stmt; EXECUTE s; DEALLOCATE PREPARE s;

SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_leads' AND INDEX_NAME='idx_fact_leads_assigned_employee') = 0,
  'ALTER TABLE fact_leads ADD INDEX idx_fact_leads_assigned_employee (tenant_id, is_deleted, assigned_employee_id)',
  'SELECT 1'));
PREPARE s FROM @stmt; EXECUTE s; DEALLOCATE PREPARE s;

-- CRM Deals/Opportunities
SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_opportunities' AND COLUMN_NAME='assigned_employee_id') = 0,
  'ALTER TABLE fact_opportunities ADD COLUMN assigned_employee_id INT NULL AFTER assigned_to',
  'SELECT 1'));
PREPARE s FROM @stmt; EXECUTE s; DEALLOCATE PREPARE s;

SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_opportunities' AND INDEX_NAME='idx_fact_opportunities_assigned_employee') = 0,
  'ALTER TABLE fact_opportunities ADD INDEX idx_fact_opportunities_assigned_employee (tenant_id, is_deleted, assigned_employee_id)',
  'SELECT 1'));
PREPARE s FROM @stmt; EXECUTE s; DEALLOCATE PREPARE s;

-- Appointments
SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_appointments' AND COLUMN_NAME='assigned_employee_id') = 0,
  'ALTER TABLE fact_appointments ADD COLUMN assigned_employee_id INT NULL AFTER assigned_to',
  'SELECT 1'));
PREPARE s FROM @stmt; EXECUTE s; DEALLOCATE PREPARE s;

SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_appointments' AND INDEX_NAME='idx_fact_appointments_assigned_employee') = 0,
  'ALTER TABLE fact_appointments ADD INDEX idx_fact_appointments_assigned_employee (tenant_id, is_deleted, assigned_employee_id)',
  'SELECT 1'));
PREPARE s FROM @stmt; EXECUTE s; DEALLOCATE PREPARE s;

-- Mail
SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_mail_message' AND COLUMN_NAME='assigned_employee_id') = 0,
  'ALTER TABLE fact_mail_message ADD COLUMN assigned_employee_id INT NULL AFTER assigned_user_id',
  'SELECT 1'));
PREPARE s FROM @stmt; EXECUTE s; DEALLOCATE PREPARE s;

-- WhatsApp
SET @stmt = (SELECT IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_whatsapp_message' AND COLUMN_NAME='assigned_employee_id') = 0,
  'ALTER TABLE fact_whatsapp_message ADD COLUMN assigned_employee_id INT NULL AFTER assigned_user_id',
  'SELECT 1'));
PREPARE s FROM @stmt; EXECUTE s; DEALLOCATE PREPARE s;