-- ============================================================================================
-- RESUME SCRIPT — continues production_deploy_contact_activity_documents.sql from the point it
-- failed (error 1553 dropping dim_contacts.business_id — a FK constraint blocked it). Everything
-- before this point (contact_businesses table, company_id FK + column drop) already succeeded on
-- your last run and is NOT repeated here. Idempotent/guarded throughout — safe to re-run.
-- Deploy: mysql -u <user> -p `stacie_Aggie_v1.0` < db/manual-migration-resume-from-business-fk-fix.sql
-- ============================================================================================

USE `stacie_Aggie_v1.0`;


-- ── remainder of Section 1: drop business_id's FK, then the column ──

SET @fk_name = (
    SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_contacts'
      AND COLUMN_NAME = 'business_id' AND REFERENCED_TABLE_NAME IS NOT NULL
    LIMIT 1
);
SET @sql = IF(@fk_name IS NOT NULL,
    CONCAT('ALTER TABLE dim_contacts DROP FOREIGN KEY `', @fk_name, '`'),
    '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;


-- ============================================================================================
-- SECTION 2 — Contact LinkedIn URL
-- ============================================================================================

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_contacts' AND COLUMN_NAME = 'linkedin_url'
);
SET @sql = IF(@col_exists = 0, 'ALTER TABLE dim_contacts ADD COLUMN linkedin_url VARCHAR(500) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ============================================================================================
-- SECTION 3 — fact_activities (Activity timeline for Leads/Deals) + meeting linking
-- ============================================================================================

CREATE TABLE IF NOT EXISTS fact_activities (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT NOT NULL,
    business_id   INT NULL,
    entity_type   VARCHAR(30) NOT NULL,
    entity_id     BIGINT NOT NULL,
    activity_type VARCHAR(20) NOT NULL,
    subject       VARCHAR(255) NULL,
    body          TEXT NULL,
    occurred_at   TIMESTAMP(6) NOT NULL,
    created_at    TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by    INT NULL,
    updated_at    TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by    INT NULL,
    is_deleted    TINYINT NOT NULL DEFAULT 0,
    INDEX idx_fact_activities_entity (tenant_id, entity_type, entity_id),
    INDEX idx_fact_activities_tenant (tenant_id)
);

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_meetings' AND COLUMN_NAME = 'linked_entity_type'
);
SET @sql = IF(@col_exists = 0, 'ALTER TABLE fact_meetings ADD COLUMN linked_entity_type VARCHAR(20) NULL', '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_meetings' AND COLUMN_NAME = 'linked_entity_id'
);
SET @sql = IF(@col_exists = 0, 'ALTER TABLE fact_meetings ADD COLUMN linked_entity_id BIGINT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ============================================================================================
-- SECTION 4 — dim_document_templates (Proposal/Invoice templates + PDF generation)
-- ============================================================================================

CREATE TABLE IF NOT EXISTS dim_document_templates (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id   INT NOT NULL,
    business_id INT NULL,
    type        VARCHAR(20) NOT NULL,
    name        VARCHAR(255) NOT NULL,
    is_default  TINYINT(1) NOT NULL DEFAULT 0,
    body_html   LONGTEXT NULL,
    created_at  TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by  INT NULL,
    updated_at  TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by  INT NULL,
    is_deleted  TINYINT(1) NOT NULL DEFAULT 0,
    INDEX idx_doc_templates_tenant_type (tenant_id, type),
    INDEX idx_doc_templates_tenant (tenant_id)
);


-- ============================================================================================
-- SECTION 5 — dim_workitem_templates (Task/Backlog item-type templates)
-- ============================================================================================

CREATE TABLE IF NOT EXISTS dim_workitem_templates (
    id                      BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id               INT NOT NULL,
    business_id             INT NULL,
    type                    VARCHAR(50) NOT NULL,
    name                    VARCHAR(255) NOT NULL,
    default_title           VARCHAR(255) NULL,
    default_description     TEXT NULL,
    default_priority        VARCHAR(20) NULL,
    default_estimate_hours  DECIMAL(8,2) NULL,
    created_at              TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_by              INT NULL,
    updated_at              TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by              INT NULL,
    is_deleted              TINYINT(1) DEFAULT 0,
    KEY idx_dwt_tenant_type (tenant_id, type),
    KEY idx_dwt_tenant (tenant_id)
);


-- ============================================================================================
-- SECTION 6 — fact_project_tasks.start_date (Gantt view date range)
-- ============================================================================================

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