-- Apply once after migration 016. Additive: legacy URL documents remain available.
CREATE TABLE tenant_document_categories (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 label VARCHAR(100) NOT NULL,
 name_key VARCHAR(100) GENERATED ALWAYS AS (LOWER(TRIM(label))) STORED,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_doc_category_unit_name (unit_id,name_key),
 UNIQUE KEY uq_doc_category_unit_id (unit_id,id),
 FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tenant_documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 event_id BIGINT UNSIGNED NULL,
 category_key VARCHAR(32) NOT NULL,
 custom_category_id BIGINT UNSIGNED NULL,
 title VARCHAR(200) NOT NULL,
 description VARCHAR(500) NULL,
 audience ENUM('members','officers','administrators') NOT NULL DEFAULT 'members',
 published_at DATETIME NULL,
 archived_at DATETIME NULL,
 deleted_at DATETIME NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_tenant_documents(unit_id,event_id,deleted_at,archived_at),
 UNIQUE KEY uq_tenant_documents_unit_id(unit_id,id),
 CONSTRAINT fk_tenant_document_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE CASCADE,
 CONSTRAINT fk_tenant_document_event FOREIGN KEY (event_id) REFERENCES meetings(id) ON DELETE SET NULL,
 CONSTRAINT fk_tenant_document_custom FOREIGN KEY (unit_id,custom_category_id) REFERENCES tenant_document_categories(unit_id,id),
 FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tenant_document_versions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 document_id BIGINT UNSIGNED NOT NULL,
 version_number INT UNSIGNED NOT NULL,
 storage_key CHAR(64) NOT NULL,
 original_filename VARCHAR(200) NOT NULL,
 mime_type VARCHAR(100) NOT NULL,
 byte_size INT UNSIGNED NOT NULL,
 sha256 CHAR(64) NOT NULL,
 uploaded_by BIGINT UNSIGNED NULL,
 uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_document_version(document_id,version_number),
 UNIQUE KEY uq_document_file(storage_key),
 KEY idx_document_versions(unit_id,document_id),
 FOREIGN KEY (unit_id,document_id) REFERENCES tenant_documents(unit_id,id) ON DELETE CASCADE,
 FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;