USE db_abos_v0.1;

-- Links a Lead to a Contact (2026-07-25). Mirrors the existing fact_opportunities.contact_id
-- FK-to-dim_contacts pattern. Purely additive — nullable column, 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 = 'contact_id'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE fact_leads ADD COLUMN contact_id INT NULL AFTER company_name',
    '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_contact'
);
SET @sql = IF(@fk_exists = 0,
    'ALTER TABLE fact_leads ADD CONSTRAINT fk_fact_leads_contact FOREIGN KEY (contact_id) REFERENCES dim_contacts(id)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;