-- Aggie-server counterpart of add_drive_file_links.sql.
-- Idempotent — safe to re-run.
-- Deploy: mysql -u <user> -p 'stacie_Aggie_v1.0' < db/aggie_add_drive_file_links.sql

USE `stacie_Aggie_v1.0`;

CREATE TABLE IF NOT EXISTS fact_drive_file_links (
    id             BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id      INT NOT NULL,
    entity_type    VARCHAR(30) NOT NULL,
    entity_id      BIGINT NOT NULL,
    drive_file_id  VARCHAR(255) NOT NULL,
    drive_file_name VARCHAR(500) NULL,
    web_view_link  VARCHAR(1000) NULL,
    mime_type      VARCHAR(255) NULL,
    linked_by      INT NULL,
    linked_at      TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    is_deleted     TINYINT(1) NOT NULL DEFAULT 0,
    KEY idx_drive_file_links_entity (tenant_id, entity_type, entity_id, is_deleted)
);