-- Structured Province/unit file manager. Existing unit documents and versions remain in place.
CREATE TABLE document_folders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, scope_type ENUM('province','unit') NOT NULL, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL,
 stable_key VARCHAR(60) NOT NULL, display_label VARCHAR(100) NOT NULL, built_in TINYINT(1) NOT NULL DEFAULT 0, active TINYINT(1) NOT NULL DEFAULT 1,
 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,
 scope_key VARCHAR(90) GENERATED ALWAYS AS (CONCAT(scope_type,':',province_id,':',IFNULL(unit_id,0))) STORED,
 UNIQUE KEY uq_document_folder(scope_key,stable_key), KEY idx_document_folder_scope(scope_type,province_id,unit_id,active),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT chk_document_folder_scope CHECK((scope_type='province' AND unit_id IS NULL) OR (scope_type='unit' AND unit_id IS NOT NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE tenant_documents
 ADD COLUMN folder_id BIGINT UNSIGNED NULL AFTER custom_category_id,
 ADD COLUMN publication_state ENUM('private_officer','member','public_event') NOT NULL DEFAULT 'member' AFTER audience,
 ADD COLUMN redacted_from_document_id BIGINT UNSIGNED NULL AFTER publication_state,
 ADD COLUMN retention_review_at DATE NULL AFTER published_at,
 ADD COLUMN revoked_at DATETIME NULL AFTER archived_at,
 ADD KEY idx_tenant_document_public(unit_id,event_id,publication_state,published_at,revoked_at),
 ADD KEY fk_tenant_document_folder(folder_id), ADD KEY fk_tenant_document_redacted(redacted_from_document_id),
 ADD FOREIGN KEY(folder_id) REFERENCES document_folders(id) ON DELETE SET NULL,
 ADD FOREIGN KEY(redacted_from_document_id) REFERENCES tenant_documents(id) ON DELETE SET NULL;
UPDATE tenant_documents SET publication_state=CASE audience WHEN 'members' THEN 'member' ELSE 'private_officer' END;

CREATE TABLE province_documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, event_id BIGINT UNSIGNED NULL, folder_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(200) NOT NULL, description VARCHAR(500) NULL, publication_state ENUM('private_officer','member','public_event') NOT NULL DEFAULT 'private_officer',
 redacted_from_document_id BIGINT UNSIGNED NULL, published_at DATETIME NULL, retention_review_at DATE NULL, archived_at DATETIME NULL, revoked_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,
 UNIQUE KEY uq_province_document_id(province_id,id), KEY idx_province_document_scope(province_id,folder_id,event_id,publication_state,archived_at,revoked_at),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(event_id) REFERENCES meetings(id) ON DELETE SET NULL,
 FOREIGN KEY(folder_id) REFERENCES document_folders(id), FOREIGN KEY(redacted_from_document_id) REFERENCES province_documents(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE province_document_versions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_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_province_document_version(document_id,version_number), UNIQUE KEY uq_province_document_storage(storage_key),
 FOREIGN KEY(province_id,document_id) REFERENCES province_documents(province_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;

CREATE TABLE document_storage_quotas (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, scope_type ENUM('province','unit') NOT NULL, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL,
 quota_bytes BIGINT UNSIGNED NOT NULL DEFAULT 536870912, updated_by BIGINT UNSIGNED NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 scope_key VARCHAR(90) GENERATED ALWAYS AS (CONCAT(scope_type,':',province_id,':',IFNULL(unit_id,0))) STORED, UNIQUE KEY uq_document_quota(scope_key),
 FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE, FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT chk_document_quota_scope CHECK((scope_type='province' AND unit_id IS NULL) OR (scope_type='unit' AND unit_id IS NOT NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO document_folders(scope_type,province_id,unit_id,stable_key,display_label,built_in)
SELECT 'unit',u.province_id,u.id,x.stable_key,x.display_label,1 FROM units u JOIN (
 SELECT 'images' stable_key,'Images' display_label UNION ALL SELECT 'summons_invitations','Summons & Invitations' UNION ALL SELECT 'bylaws','Bylaws' UNION ALL
 SELECT 'minutes','Minutes' UNION ALL SELECT 'almoner_reports','Almoner Reports' UNION ALL SELECT 'treasurer_reports','Treasurer Reports' UNION ALL
 SELECT 'charity_steward_reports','Charity Steward Reports' UNION ALL SELECT 'general_documents','General Documents') x WHERE u.province_id IS NOT NULL;
INSERT INTO document_folders(scope_type,province_id,unit_id,stable_key,display_label,built_in)
SELECT 'province',p.id,NULL,x.stable_key,x.display_label,1 FROM provinces p JOIN (
 SELECT 'images' stable_key,'Images' display_label UNION ALL SELECT 'summons_invitations','Summons & Invitations' UNION ALL SELECT 'bylaws','Bylaws' UNION ALL
 SELECT 'minutes','Minutes' UNION ALL SELECT 'almoner_reports','Almoner Reports' UNION ALL SELECT 'treasurer_reports','Treasurer Reports' UNION ALL
 SELECT 'charity_steward_reports','Charity Steward Reports' UNION ALL SELECT 'general_documents','General Documents') x;
UPDATE tenant_documents d JOIN document_folders f ON f.scope_type='unit' AND f.unit_id=d.unit_id AND f.stable_key=CASE d.category_key WHEN 'summons' THEN 'summons_invitations' WHEN 'invitations' THEN 'summons_invitations' WHEN 'bylaws' THEN 'bylaws' WHEN 'minutes' THEN 'minutes' ELSE 'general_documents' END SET d.folder_id=f.id;

INSERT INTO migration_history(version,name,checksum,execution_ms) VALUES('202609280030','Structured Province and unit file manager',SHA2('202609280030_structured_file_manager_v1',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);