-- ============================================================================================
-- PRODUCTION MIGRATION — File attachments for Leads, Deals, Orders, Invoices
-- ============================================================================================
-- Adds fact_attachments: a polymorphic file-attachment table. entity_id is a plain BIGINT with
-- no FK — same no-FK approach as fact_activities, since Order has no JPA entity at all
-- (stored-procedure/view-backed only).
--
-- File bytes are NOT stored in this table — they land on local disk under
-- abos.attachments.upload-root and are served back only through the authenticated
-- /api/attachments/{id}/download endpoint. This table just tracks metadata + storage_path.
--
-- Idempotent — safe to re-run.
-- Deploy: mysql -u <user> -p `stacie_Aggie_v1.0` < db/fact_attachments.sql
-- ============================================================================================

USE `stacie_Aggie_v1.0`;

CREATE TABLE IF NOT EXISTS fact_attachments (
    id                BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT NOT NULL,
    entity_type       VARCHAR(30) NOT NULL,
    entity_id         BIGINT NOT NULL,
    storage_path      VARCHAR(500) NOT NULL,
    original_filename VARCHAR(255) NULL,
    mime_type         VARCHAR(100) NULL,
    size_bytes        BIGINT 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_attachments_entity (tenant_id, entity_type, entity_id),
    INDEX idx_fact_attachments_tenant (tenant_id)
);