-- PostgreSQL additive migration. Apply once before deploying the PHP files.
BEGIN;
CREATE TABLE IF NOT EXISTS managed_folders (
 id BIGSERIAL PRIMARY KEY,
 owner_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 owner_name VARCHAR(255) NOT NULL,
 owner_role VARCHAR(50) NOT NULL CHECK (owner_role IN ('dvc1','dvc2','ceo','cfo','coo','cao')),
 name VARCHAR(150) NOT NULL CHECK (length(trim(name)) > 0),
 description VARCHAR(500) NOT NULL DEFAULT '',
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE(owner_id,name)
);
ALTER TABLE managed_files ADD COLUMN IF NOT EXISTS folder_id BIGINT REFERENCES managed_folders(id) ON DELETE RESTRICT;
ALTER TABLE managed_uploads ADD COLUMN IF NOT EXISTS folder_id BIGINT REFERENCES managed_folders(id) ON DELETE RESTRICT;
CREATE INDEX IF NOT EXISTS managed_files_folder_idx ON managed_files(folder_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS managed_files_campus_idx ON managed_files(campus_key) WHERE deleted_at IS NULL;
COMMIT;
