-- ============================================================================================
-- Bookmarked URL links attached to a record (Lead, Deal, Contact, Company, Order, Invoice,
-- Project, Workitem, Wiki page).
-- ============================================================================================
-- Adds fact_links: a polymorphic bookmarked-URL table. entity_id is a plain BIGINT with no FK —
-- same no-FK approach as fact_activities/fact_attachments, since Order has no JPA entity at all
-- (stored-procedure/view-backed only).
--
-- Idempotent — safe to re-run.
-- ============================================================================================

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS fact_links (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT NOT NULL,
    entity_type   VARCHAR(30) NOT NULL,
    entity_id     BIGINT NOT NULL,
    url           VARCHAR(1000) NOT NULL,
    label         VARCHAR(255) 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_links_entity (tenant_id, entity_type, entity_id),
    INDEX idx_fact_links_tenant (tenant_id)
);