USE db_abos_v0.1;

-- Adds a Lead -> Service link (dim_services) and a Lead description field (2026-07-25).
-- dim_services/dim_service_types/dim_service_packages already exist in the schema but were
-- never wired to any entity/API/UI — this activates dim_services as a lookup source for
-- leads. Purely additive — nullable columns, no existing column touched.

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_leads' AND COLUMN_NAME = 'service_id'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE fact_leads ADD COLUMN service_id INT NULL AFTER contact_id',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @fk_exists = (
    SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_leads' AND CONSTRAINT_NAME = 'fk_fact_leads_service'
);
SET @sql = IF(@fk_exists = 0,
    'ALTER TABLE fact_leads ADD CONSTRAINT fk_fact_leads_service FOREIGN KEY (service_id) REFERENCES dim_services(id)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_leads' AND COLUMN_NAME = 'description'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE fact_leads ADD COLUMN description TEXT NULL AFTER notes',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;