-- ============================================================================================
-- PRODUCTION MIGRATION — Contact -> Business (multi-select), replaces Contact -> Company
-- ============================================================================================
-- Context: the Contact form's "Company" dropdown edited dim_contacts.company_id, a link to the
-- CRM Client entity (dim_companies) — unrelated to the tenant "Business" concept used everywhere
-- else (top-nav business switcher, Lead.business_id). Replaced with a genuine multi-select
-- Business association so a Contact can apply to specific businesses or all of them.
--
-- Semantics: no contact_businesses rows for a given contact_id = "applies to all businesses"
-- (mirrors the existing business_id IS NULL = "All Businesses" convention used by Leads).
--
-- Changes:
--   1. CREATE contact_businesses — new join table (same shape/style as user_business_access).
--   2. DROP dim_contacts.company_id (+ its FK to dim_companies) — Contact no longer links to a
--      CRM Client. No data migration: no rows existed with a populated business_id before this
--      change shipped, and company_id values are not carried over to the new join table (the
--      Client and Business concepts are different entities, not a renaming).
--   3. DROP dim_contacts.business_id — was an unused single-value column (never populated by the
--      app; ContactService never wrote to it), fully superseded by contact_businesses.
--
-- Risk: dropping company_id removes the ability to look up "all Contacts for a given CRM
-- Client" (the GET_CONTACTS_BY_COMPANY endpoint was removed from the app in the same change —
-- deploy this SQL together with that backend build, not before). If any external tooling/report
-- queries dim_contacts.company_id or dim_contacts.business_id directly, it will break.
--
-- Idempotent throughout — safe to re-run if a deployment is interrupted partway through.
-- Recommended: take a full mysqldump of db_abos_v0.1 before running this.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/contact_businesses.sql
-- ============================================================================================

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS contact_businesses (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id   INT NOT NULL,
    contact_id  INT NOT NULL,
    business_id INT NOT NULL,
    is_deleted  TINYINT(1) NOT NULL DEFAULT 0,
    created_at  TIMESTAMP  DEFAULT CURRENT_TIMESTAMP,
    created_by  INT NULL,
    updated_at  TIMESTAMP  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_cb_contact_business (contact_id, business_id),
    KEY idx_cb_contact (contact_id),
    KEY idx_cb_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Drop the FK on company_id first (if present), then the column itself.
SET @fk_exists = (
    SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_contacts'
      AND CONSTRAINT_NAME = 'dim_contacts_ibfk_2' AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);
SET @sql = IF(@fk_exists > 0,
    'ALTER TABLE dim_contacts DROP FOREIGN KEY dim_contacts_ibfk_2',
    '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 = 'dim_contacts' AND COLUMN_NAME = 'company_id'
);
SET @sql = IF(@col_exists > 0,
    'ALTER TABLE dim_contacts DROP COLUMN company_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 = 'dim_contacts' AND COLUMN_NAME = 'business_id'
);
SET @sql = IF(@col_exists > 0,
    'ALTER TABLE dim_contacts DROP COLUMN business_id',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;