-- ============================================================================================
-- PRODUCTION MIGRATION — Activity timeline for Leads and Deals
-- ============================================================================================
-- Adds a manually-logged interaction feed (meetings, notes, emails, calls, other) attached to a
-- Lead or Deal. entity_id is a plain BIGINT with no FK — same no-FK polymorphic approach as
-- fact_entity_labels, since Deals are stored-procedure-backed, not a JPA entity.
--
-- Also adds linked_entity_type/linked_entity_id to fact_meetings, mirroring the existing manual
-- linking columns already on fact_mail_message/fact_whatsapp_messages, so a synced Fireflies
-- meeting can be linked to a Lead/Deal/Contact/Client and surfaced in the activity feed.
--
-- Idempotent throughout — safe to re-run if a deployment is interrupted partway through.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/fact_activities.sql
-- ============================================================================================

USE db_abos_v0.1;

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;