-- Google Drive file reference links — see FactDriveFileLink / DriveFileLinkService. Stores only
-- a reference (Drive file id, name, link) never the file itself; unlinking never touches Drive.
-- Idempotent — safe to re-run.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/add_drive_file_links.sql

USE db_abos_v0.1;

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