-- Document registry — see FactDocument / FactDocumentAccess / DocumentService.
-- fact_documents aggregates Aggie-hosted uploads and linked Google Drive files into one
-- registry row per document; fact_document_access is a grant table for RESTRICTED-visibility
-- documents (mirrors fact_mail_account_access's shape). Also adds drive_access_enabled to
-- dim_mail_account — a tenant-admin-controlled per-connected-account toggle for whether that
-- account's Drive contents can be browsed into the registry at all.
-- Idempotent — safe to re-run.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/add_documents.sql

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS fact_documents (
    id                BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT NOT NULL,
    business_id       INT NULL,
    title             VARCHAR(255) NOT NULL,
    description       TEXT NULL,
    source_type       VARCHAR(20) NOT NULL,
    entity_type       VARCHAR(30) NULL,
    entity_id         BIGINT NULL,
    visibility        VARCHAR(15) NOT NULL DEFAULT 'TENANT',
    mime_type         VARCHAR(255) NULL,
    size_bytes        BIGINT NULL,
    storage_path      VARCHAR(500) NULL,
    original_filename VARCHAR(255) NULL,
    mail_account_id   BIGINT NULL,
    drive_file_id     VARCHAR(255) NULL,
    drive_file_name   VARCHAR(500) NULL,
    web_view_link     VARCHAR(1000) 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(1) NOT NULL DEFAULT 0,
    KEY idx_documents_tenant (tenant_id, is_deleted),
    KEY idx_documents_entity (tenant_id, entity_type, entity_id, is_deleted)
);

CREATE TABLE IF NOT EXISTS fact_document_access (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id   INT NOT NULL,
    document_id BIGINT NOT NULL,
    user_id     INT NOT NULL,
    created_at  TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by  INT NULL,
    is_deleted  TINYINT(1) NOT NULL DEFAULT 0,
    KEY idx_document_access_doc (tenant_id, document_id, is_deleted),
    KEY idx_document_access_user (tenant_id, user_id, is_deleted)
);

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_mail_account' AND COLUMN_NAME = 'drive_access_enabled'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_mail_account ADD COLUMN drive_access_enabled TINYINT(1) NOT NULL DEFAULT 1',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;