-- Registry folders — see FactDocumentFolder / DocumentService's folder methods. Organizes
-- fact_documents rows only (uploaded/Drive-linked through the Documents module); the
-- attachment/Drive-link read-through rows (fact_attachments/fact_drive_file_links) always show
-- at the registry root regardless of folder structure.
-- Idempotent — safe to re-run.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/add_document_folders.sql

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS fact_document_folders (
    id               BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id        INT NOT NULL,
    name             VARCHAR(255) NOT NULL,
    parent_folder_id 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(1) NOT NULL DEFAULT 0,
    KEY idx_document_folders_parent (tenant_id, parent_folder_id, is_deleted)
);

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_documents' AND COLUMN_NAME = 'folder_id'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE fact_documents ADD COLUMN folder_id BIGINT NULL, ADD KEY idx_documents_folder (tenant_id, folder_id, is_deleted)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;