-- "Assigned To" pickers were hard-wired to `users` only, which also meant any active user
-- without an explicit per-module/per-business access grant was invisible in the picker (only
-- the tenant admin showed up). Fixed in CrmLookupService (now lists every active user, plus
-- every active HRM employee who has no login), and this adds the actual assignment target for
-- that second case: a nullable assigned_employee_id FK, mutually exclusive with the existing
-- assigned_user_id on each entity (enforced in the service layer, not the DB).
-- Idempotent (information_schema-guarded), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/add_assignable_employee.sql

USE db_abos_v0.1;

-- Projects/Tasks
SET @db = DATABASE();
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;