-- ============================================================================================
-- CONSOLIDATED PRODUCTION DEPLOYMENT SCRIPT
-- Combines: contact_businesses.sql, add_contact_linkedin_url.sql, fact_activities.sql,
--           dim_document_templates.sql, dim_workitem_templates.sql, add_task_start_date.sql
--
-- Covers: Contact -> multi-Business scoping (replaces Company/Client link), Contact LinkedIn
-- URL, Activity timeline (Leads/Deals) + Fireflies meeting linking, Proposal/Invoice document
-- templates, Workitem/Task item-type templates, and Task startDate (for the new Gantt view).
--
-- Idempotent throughout — every CREATE TABLE uses IF NOT EXISTS, every ALTER/DROP is guarded by
-- an information_schema check. Safe to re-run in full if a deployment is interrupted partway
-- through. Deploy this at the same time as the matching application build — the new Java code
-- (ContactService, ActivityService, DocumentTemplateService, WorkitemTemplateService, etc.)
-- queries these tables/columns directly and will 500 on every affected endpoint until this runs.
--
-- NOTE on the database name: `stacie_Aggie_v1.0` contains a literal dot, which breaks an
-- unquoted `USE stacie_Aggie_v1.0;` (MySQL parses it as schema `stacie_Aggie_v1`.`0`). The
-- backtick-quoted form below avoids that.
--
-- Recommended: take a full mysqldump of stacie_Aggie_v1.0 before running this.
-- Deploy: mysql -u <user> -p `stacie_Aggie_v1.0` < db/production_deploy_contact_activity_documents.sql
-- ============================================================================================

USE `stacie_Aggie_v1.0`;


-- ============================================================================================
-- SECTION 1 — contact_businesses (Contact -> multi-Business; replaces Contact -> Company/Client)
-- ============================================================================================

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)
);

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;

-- Production has an extra FK on business_id (not present in the local dev schema this script
-- was originally written against) that must be dropped before the column itself can go —
-- otherwise DROP COLUMN business_id fails with error 1553 (index needed in a FK constraint).
-- Looked up dynamically by column rather than hardcoding a constraint name, since naming drifts
-- across environments.
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 = '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;


-- ============================================================================================
-- 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;