-- Run once on PostgreSQL before deploying school_files.php.
-- Each school gets an independent schema; global File Management tables remain separate.

CREATE SCHEMA IF NOT EXISTS school_files_agriculture;
SET search_path TO school_files_agriculture, public;
BEGIN;
CREATE TABLE IF NOT EXISTS managed_files (
 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,
 campus VARCHAR(180) NOT NULL,
 campus_key VARCHAR(180) NOT NULL,
 title VARCHAR(200) NOT NULL,
 current_version INTEGER NOT NULL DEFAULT 1,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS managed_file_versions (
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 version INTEGER NOT NULL,
 filename VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL CHECK(size_bytes > 0),
 sha256 CHAR(64) NOT NULL,
 mime VARCHAR(150) NOT NULL,
 storage_key CHAR(64) NOT NULL UNIQUE,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(file_id, version)
);
CREATE TABLE IF NOT EXISTS managed_uploads (
 id CHAR(32) PRIMARY KEY,
 owner_id BIGINT NOT NULL REFERENCES users(id),
 client_key CHAR(32) NOT NULL,
 file_id BIGINT REFERENCES managed_files(id),
 expected_version INTEGER NOT NULL DEFAULT 0,
 filename VARCHAR(200) NOT NULL,
 title VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL,
 sha256 CHAR(64) NOT NULL,
 received_bytes BIGINT NOT NULL DEFAULT 0,
 result_file_id BIGINT REFERENCES managed_files(id),
 result_version INTEGER,
 expires_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '7 days'),
 UNIQUE(owner_id, client_key)
);
CREATE TABLE IF NOT EXISTS managed_file_audit (
 id BIGSERIAL PRIMARY KEY,
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(50) NOT NULL,
 version INTEGER NOT NULL,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS managed_files_owner_idx ON managed_files(owner_id);
CREATE INDEX IF NOT EXISTS managed_files_scope_idx ON managed_files(owner_role, campus_key) WHERE deleted_at IS NULL;
COMMIT;

-- 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;

-- Apply after 001_public_folders.sql. Existing public folders stay public.
BEGIN;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_role_check;
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus_key VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS visibility VARCHAR(20) NOT NULL DEFAULT 'public' CHECK(visibility IN ('public','restricted'));
ALTER TABLE managed_folders ALTER COLUMN visibility SET DEFAULT 'restricted';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_id_name_key;
CREATE UNIQUE INDEX IF NOT EXISTS managed_folders_active_name_idx ON managed_folders(owner_id,name) WHERE deleted_at IS NULL;
-- Existing folders were executive public folders; campus backfill aids display.
UPDATE managed_folders g SET campus=COALESCE(u.campus,''), campus_key=lower(trim(regexp_replace(COALESCE(u.campus,''),'\s+campus\s*$','','i'))) FROM users u WHERE g.owner_id=u.id AND g.campus='';
CREATE TABLE IF NOT EXISTS managed_folder_members (
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id) ON DELETE RESTRICT,
 user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 invited_by BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 state VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK(state IN ('pending','accepted','declined')),
 invited_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 responded_at TIMESTAMPTZ,
 PRIMARY KEY(folder_id,user_id)
);
CREATE INDEX IF NOT EXISTS managed_folder_members_inbox_idx ON managed_folder_members(user_id,state);
CREATE INDEX IF NOT EXISTS managed_folders_campus_idx ON managed_folders(campus_key) WHERE deleted_at IS NULL;
CREATE TABLE IF NOT EXISTS managed_folder_audit (
 id BIGSERIAL PRIMARY KEY,
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(40) NOT NULL,
 target_user_id BIGINT REFERENCES users(id),
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMIT;


CREATE SCHEMA IF NOT EXISTS school_files_business;
SET search_path TO school_files_business, public;
BEGIN;
CREATE TABLE IF NOT EXISTS managed_files (
 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,
 campus VARCHAR(180) NOT NULL,
 campus_key VARCHAR(180) NOT NULL,
 title VARCHAR(200) NOT NULL,
 current_version INTEGER NOT NULL DEFAULT 1,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS managed_file_versions (
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 version INTEGER NOT NULL,
 filename VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL CHECK(size_bytes > 0),
 sha256 CHAR(64) NOT NULL,
 mime VARCHAR(150) NOT NULL,
 storage_key CHAR(64) NOT NULL UNIQUE,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(file_id, version)
);
CREATE TABLE IF NOT EXISTS managed_uploads (
 id CHAR(32) PRIMARY KEY,
 owner_id BIGINT NOT NULL REFERENCES users(id),
 client_key CHAR(32) NOT NULL,
 file_id BIGINT REFERENCES managed_files(id),
 expected_version INTEGER NOT NULL DEFAULT 0,
 filename VARCHAR(200) NOT NULL,
 title VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL,
 sha256 CHAR(64) NOT NULL,
 received_bytes BIGINT NOT NULL DEFAULT 0,
 result_file_id BIGINT REFERENCES managed_files(id),
 result_version INTEGER,
 expires_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '7 days'),
 UNIQUE(owner_id, client_key)
);
CREATE TABLE IF NOT EXISTS managed_file_audit (
 id BIGSERIAL PRIMARY KEY,
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(50) NOT NULL,
 version INTEGER NOT NULL,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS managed_files_owner_idx ON managed_files(owner_id);
CREATE INDEX IF NOT EXISTS managed_files_scope_idx ON managed_files(owner_role, campus_key) WHERE deleted_at IS NULL;
COMMIT;

-- 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;

-- Apply after 001_public_folders.sql. Existing public folders stay public.
BEGIN;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_role_check;
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus_key VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS visibility VARCHAR(20) NOT NULL DEFAULT 'public' CHECK(visibility IN ('public','restricted'));
ALTER TABLE managed_folders ALTER COLUMN visibility SET DEFAULT 'restricted';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_id_name_key;
CREATE UNIQUE INDEX IF NOT EXISTS managed_folders_active_name_idx ON managed_folders(owner_id,name) WHERE deleted_at IS NULL;
-- Existing folders were executive public folders; campus backfill aids display.
UPDATE managed_folders g SET campus=COALESCE(u.campus,''), campus_key=lower(trim(regexp_replace(COALESCE(u.campus,''),'\s+campus\s*$','','i'))) FROM users u WHERE g.owner_id=u.id AND g.campus='';
CREATE TABLE IF NOT EXISTS managed_folder_members (
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id) ON DELETE RESTRICT,
 user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 invited_by BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 state VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK(state IN ('pending','accepted','declined')),
 invited_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 responded_at TIMESTAMPTZ,
 PRIMARY KEY(folder_id,user_id)
);
CREATE INDEX IF NOT EXISTS managed_folder_members_inbox_idx ON managed_folder_members(user_id,state);
CREATE INDEX IF NOT EXISTS managed_folders_campus_idx ON managed_folders(campus_key) WHERE deleted_at IS NULL;
CREATE TABLE IF NOT EXISTS managed_folder_audit (
 id BIGSERIAL PRIMARY KEY,
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(40) NOT NULL,
 target_user_id BIGINT REFERENCES users(id),
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMIT;


CREATE SCHEMA IF NOT EXISTS school_files_education;
SET search_path TO school_files_education, public;
BEGIN;
CREATE TABLE IF NOT EXISTS managed_files (
 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,
 campus VARCHAR(180) NOT NULL,
 campus_key VARCHAR(180) NOT NULL,
 title VARCHAR(200) NOT NULL,
 current_version INTEGER NOT NULL DEFAULT 1,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS managed_file_versions (
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 version INTEGER NOT NULL,
 filename VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL CHECK(size_bytes > 0),
 sha256 CHAR(64) NOT NULL,
 mime VARCHAR(150) NOT NULL,
 storage_key CHAR(64) NOT NULL UNIQUE,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(file_id, version)
);
CREATE TABLE IF NOT EXISTS managed_uploads (
 id CHAR(32) PRIMARY KEY,
 owner_id BIGINT NOT NULL REFERENCES users(id),
 client_key CHAR(32) NOT NULL,
 file_id BIGINT REFERENCES managed_files(id),
 expected_version INTEGER NOT NULL DEFAULT 0,
 filename VARCHAR(200) NOT NULL,
 title VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL,
 sha256 CHAR(64) NOT NULL,
 received_bytes BIGINT NOT NULL DEFAULT 0,
 result_file_id BIGINT REFERENCES managed_files(id),
 result_version INTEGER,
 expires_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '7 days'),
 UNIQUE(owner_id, client_key)
);
CREATE TABLE IF NOT EXISTS managed_file_audit (
 id BIGSERIAL PRIMARY KEY,
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(50) NOT NULL,
 version INTEGER NOT NULL,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS managed_files_owner_idx ON managed_files(owner_id);
CREATE INDEX IF NOT EXISTS managed_files_scope_idx ON managed_files(owner_role, campus_key) WHERE deleted_at IS NULL;
COMMIT;

-- 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;

-- Apply after 001_public_folders.sql. Existing public folders stay public.
BEGIN;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_role_check;
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus_key VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS visibility VARCHAR(20) NOT NULL DEFAULT 'public' CHECK(visibility IN ('public','restricted'));
ALTER TABLE managed_folders ALTER COLUMN visibility SET DEFAULT 'restricted';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_id_name_key;
CREATE UNIQUE INDEX IF NOT EXISTS managed_folders_active_name_idx ON managed_folders(owner_id,name) WHERE deleted_at IS NULL;
-- Existing folders were executive public folders; campus backfill aids display.
UPDATE managed_folders g SET campus=COALESCE(u.campus,''), campus_key=lower(trim(regexp_replace(COALESCE(u.campus,''),'\s+campus\s*$','','i'))) FROM users u WHERE g.owner_id=u.id AND g.campus='';
CREATE TABLE IF NOT EXISTS managed_folder_members (
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id) ON DELETE RESTRICT,
 user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 invited_by BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 state VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK(state IN ('pending','accepted','declined')),
 invited_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 responded_at TIMESTAMPTZ,
 PRIMARY KEY(folder_id,user_id)
);
CREATE INDEX IF NOT EXISTS managed_folder_members_inbox_idx ON managed_folder_members(user_id,state);
CREATE INDEX IF NOT EXISTS managed_folders_campus_idx ON managed_folders(campus_key) WHERE deleted_at IS NULL;
CREATE TABLE IF NOT EXISTS managed_folder_audit (
 id BIGSERIAL PRIMARY KEY,
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(40) NOT NULL,
 target_user_id BIGINT REFERENCES users(id),
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMIT;


CREATE SCHEMA IF NOT EXISTS school_files_ict;
SET search_path TO school_files_ict, public;
BEGIN;
CREATE TABLE IF NOT EXISTS managed_files (
 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,
 campus VARCHAR(180) NOT NULL,
 campus_key VARCHAR(180) NOT NULL,
 title VARCHAR(200) NOT NULL,
 current_version INTEGER NOT NULL DEFAULT 1,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS managed_file_versions (
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 version INTEGER NOT NULL,
 filename VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL CHECK(size_bytes > 0),
 sha256 CHAR(64) NOT NULL,
 mime VARCHAR(150) NOT NULL,
 storage_key CHAR(64) NOT NULL UNIQUE,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(file_id, version)
);
CREATE TABLE IF NOT EXISTS managed_uploads (
 id CHAR(32) PRIMARY KEY,
 owner_id BIGINT NOT NULL REFERENCES users(id),
 client_key CHAR(32) NOT NULL,
 file_id BIGINT REFERENCES managed_files(id),
 expected_version INTEGER NOT NULL DEFAULT 0,
 filename VARCHAR(200) NOT NULL,
 title VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL,
 sha256 CHAR(64) NOT NULL,
 received_bytes BIGINT NOT NULL DEFAULT 0,
 result_file_id BIGINT REFERENCES managed_files(id),
 result_version INTEGER,
 expires_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '7 days'),
 UNIQUE(owner_id, client_key)
);
CREATE TABLE IF NOT EXISTS managed_file_audit (
 id BIGSERIAL PRIMARY KEY,
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(50) NOT NULL,
 version INTEGER NOT NULL,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS managed_files_owner_idx ON managed_files(owner_id);
CREATE INDEX IF NOT EXISTS managed_files_scope_idx ON managed_files(owner_role, campus_key) WHERE deleted_at IS NULL;
COMMIT;

-- 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;

-- Apply after 001_public_folders.sql. Existing public folders stay public.
BEGIN;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_role_check;
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus_key VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS visibility VARCHAR(20) NOT NULL DEFAULT 'public' CHECK(visibility IN ('public','restricted'));
ALTER TABLE managed_folders ALTER COLUMN visibility SET DEFAULT 'restricted';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_id_name_key;
CREATE UNIQUE INDEX IF NOT EXISTS managed_folders_active_name_idx ON managed_folders(owner_id,name) WHERE deleted_at IS NULL;
-- Existing folders were executive public folders; campus backfill aids display.
UPDATE managed_folders g SET campus=COALESCE(u.campus,''), campus_key=lower(trim(regexp_replace(COALESCE(u.campus,''),'\s+campus\s*$','','i'))) FROM users u WHERE g.owner_id=u.id AND g.campus='';
CREATE TABLE IF NOT EXISTS managed_folder_members (
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id) ON DELETE RESTRICT,
 user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 invited_by BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 state VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK(state IN ('pending','accepted','declined')),
 invited_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 responded_at TIMESTAMPTZ,
 PRIMARY KEY(folder_id,user_id)
);
CREATE INDEX IF NOT EXISTS managed_folder_members_inbox_idx ON managed_folder_members(user_id,state);
CREATE INDEX IF NOT EXISTS managed_folders_campus_idx ON managed_folders(campus_key) WHERE deleted_at IS NULL;
CREATE TABLE IF NOT EXISTS managed_folder_audit (
 id BIGSERIAL PRIMARY KEY,
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(40) NOT NULL,
 target_user_id BIGINT REFERENCES users(id),
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMIT;


CREATE SCHEMA IF NOT EXISTS school_files_health_sciences;
SET search_path TO school_files_health_sciences, public;
BEGIN;
CREATE TABLE IF NOT EXISTS managed_files (
 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,
 campus VARCHAR(180) NOT NULL,
 campus_key VARCHAR(180) NOT NULL,
 title VARCHAR(200) NOT NULL,
 current_version INTEGER NOT NULL DEFAULT 1,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS managed_file_versions (
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 version INTEGER NOT NULL,
 filename VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL CHECK(size_bytes > 0),
 sha256 CHAR(64) NOT NULL,
 mime VARCHAR(150) NOT NULL,
 storage_key CHAR(64) NOT NULL UNIQUE,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(file_id, version)
);
CREATE TABLE IF NOT EXISTS managed_uploads (
 id CHAR(32) PRIMARY KEY,
 owner_id BIGINT NOT NULL REFERENCES users(id),
 client_key CHAR(32) NOT NULL,
 file_id BIGINT REFERENCES managed_files(id),
 expected_version INTEGER NOT NULL DEFAULT 0,
 filename VARCHAR(200) NOT NULL,
 title VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL,
 sha256 CHAR(64) NOT NULL,
 received_bytes BIGINT NOT NULL DEFAULT 0,
 result_file_id BIGINT REFERENCES managed_files(id),
 result_version INTEGER,
 expires_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '7 days'),
 UNIQUE(owner_id, client_key)
);
CREATE TABLE IF NOT EXISTS managed_file_audit (
 id BIGSERIAL PRIMARY KEY,
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(50) NOT NULL,
 version INTEGER NOT NULL,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS managed_files_owner_idx ON managed_files(owner_id);
CREATE INDEX IF NOT EXISTS managed_files_scope_idx ON managed_files(owner_role, campus_key) WHERE deleted_at IS NULL;
COMMIT;

-- 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;

-- Apply after 001_public_folders.sql. Existing public folders stay public.
BEGIN;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_role_check;
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus_key VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS visibility VARCHAR(20) NOT NULL DEFAULT 'public' CHECK(visibility IN ('public','restricted'));
ALTER TABLE managed_folders ALTER COLUMN visibility SET DEFAULT 'restricted';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_id_name_key;
CREATE UNIQUE INDEX IF NOT EXISTS managed_folders_active_name_idx ON managed_folders(owner_id,name) WHERE deleted_at IS NULL;
-- Existing folders were executive public folders; campus backfill aids display.
UPDATE managed_folders g SET campus=COALESCE(u.campus,''), campus_key=lower(trim(regexp_replace(COALESCE(u.campus,''),'\s+campus\s*$','','i'))) FROM users u WHERE g.owner_id=u.id AND g.campus='';
CREATE TABLE IF NOT EXISTS managed_folder_members (
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id) ON DELETE RESTRICT,
 user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 invited_by BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 state VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK(state IN ('pending','accepted','declined')),
 invited_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 responded_at TIMESTAMPTZ,
 PRIMARY KEY(folder_id,user_id)
);
CREATE INDEX IF NOT EXISTS managed_folder_members_inbox_idx ON managed_folder_members(user_id,state);
CREATE INDEX IF NOT EXISTS managed_folders_campus_idx ON managed_folders(campus_key) WHERE deleted_at IS NULL;
CREATE TABLE IF NOT EXISTS managed_folder_audit (
 id BIGSERIAL PRIMARY KEY,
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(40) NOT NULL,
 target_user_id BIGINT REFERENCES users(id),
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMIT;


CREATE SCHEMA IF NOT EXISTS school_files_engineering;
SET search_path TO school_files_engineering, public;
BEGIN;
CREATE TABLE IF NOT EXISTS managed_files (
 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,
 campus VARCHAR(180) NOT NULL,
 campus_key VARCHAR(180) NOT NULL,
 title VARCHAR(200) NOT NULL,
 current_version INTEGER NOT NULL DEFAULT 1,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS managed_file_versions (
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 version INTEGER NOT NULL,
 filename VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL CHECK(size_bytes > 0),
 sha256 CHAR(64) NOT NULL,
 mime VARCHAR(150) NOT NULL,
 storage_key CHAR(64) NOT NULL UNIQUE,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(file_id, version)
);
CREATE TABLE IF NOT EXISTS managed_uploads (
 id CHAR(32) PRIMARY KEY,
 owner_id BIGINT NOT NULL REFERENCES users(id),
 client_key CHAR(32) NOT NULL,
 file_id BIGINT REFERENCES managed_files(id),
 expected_version INTEGER NOT NULL DEFAULT 0,
 filename VARCHAR(200) NOT NULL,
 title VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL,
 sha256 CHAR(64) NOT NULL,
 received_bytes BIGINT NOT NULL DEFAULT 0,
 result_file_id BIGINT REFERENCES managed_files(id),
 result_version INTEGER,
 expires_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '7 days'),
 UNIQUE(owner_id, client_key)
);
CREATE TABLE IF NOT EXISTS managed_file_audit (
 id BIGSERIAL PRIMARY KEY,
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(50) NOT NULL,
 version INTEGER NOT NULL,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS managed_files_owner_idx ON managed_files(owner_id);
CREATE INDEX IF NOT EXISTS managed_files_scope_idx ON managed_files(owner_role, campus_key) WHERE deleted_at IS NULL;
COMMIT;

-- 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;

-- Apply after 001_public_folders.sql. Existing public folders stay public.
BEGIN;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_role_check;
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus_key VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS visibility VARCHAR(20) NOT NULL DEFAULT 'public' CHECK(visibility IN ('public','restricted'));
ALTER TABLE managed_folders ALTER COLUMN visibility SET DEFAULT 'restricted';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_id_name_key;
CREATE UNIQUE INDEX IF NOT EXISTS managed_folders_active_name_idx ON managed_folders(owner_id,name) WHERE deleted_at IS NULL;
-- Existing folders were executive public folders; campus backfill aids display.
UPDATE managed_folders g SET campus=COALESCE(u.campus,''), campus_key=lower(trim(regexp_replace(COALESCE(u.campus,''),'\s+campus\s*$','','i'))) FROM users u WHERE g.owner_id=u.id AND g.campus='';
CREATE TABLE IF NOT EXISTS managed_folder_members (
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id) ON DELETE RESTRICT,
 user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 invited_by BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 state VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK(state IN ('pending','accepted','declined')),
 invited_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 responded_at TIMESTAMPTZ,
 PRIMARY KEY(folder_id,user_id)
);
CREATE INDEX IF NOT EXISTS managed_folder_members_inbox_idx ON managed_folder_members(user_id,state);
CREATE INDEX IF NOT EXISTS managed_folders_campus_idx ON managed_folders(campus_key) WHERE deleted_at IS NULL;
CREATE TABLE IF NOT EXISTS managed_folder_audit (
 id BIGSERIAL PRIMARY KEY,
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(40) NOT NULL,
 target_user_id BIGINT REFERENCES users(id),
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMIT;


CREATE SCHEMA IF NOT EXISTS school_files_natural_sciences;
SET search_path TO school_files_natural_sciences, public;
BEGIN;
CREATE TABLE IF NOT EXISTS managed_files (
 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,
 campus VARCHAR(180) NOT NULL,
 campus_key VARCHAR(180) NOT NULL,
 title VARCHAR(200) NOT NULL,
 current_version INTEGER NOT NULL DEFAULT 1,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS managed_file_versions (
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 version INTEGER NOT NULL,
 filename VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL CHECK(size_bytes > 0),
 sha256 CHAR(64) NOT NULL,
 mime VARCHAR(150) NOT NULL,
 storage_key CHAR(64) NOT NULL UNIQUE,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(file_id, version)
);
CREATE TABLE IF NOT EXISTS managed_uploads (
 id CHAR(32) PRIMARY KEY,
 owner_id BIGINT NOT NULL REFERENCES users(id),
 client_key CHAR(32) NOT NULL,
 file_id BIGINT REFERENCES managed_files(id),
 expected_version INTEGER NOT NULL DEFAULT 0,
 filename VARCHAR(200) NOT NULL,
 title VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL,
 sha256 CHAR(64) NOT NULL,
 received_bytes BIGINT NOT NULL DEFAULT 0,
 result_file_id BIGINT REFERENCES managed_files(id),
 result_version INTEGER,
 expires_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '7 days'),
 UNIQUE(owner_id, client_key)
);
CREATE TABLE IF NOT EXISTS managed_file_audit (
 id BIGSERIAL PRIMARY KEY,
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(50) NOT NULL,
 version INTEGER NOT NULL,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS managed_files_owner_idx ON managed_files(owner_id);
CREATE INDEX IF NOT EXISTS managed_files_scope_idx ON managed_files(owner_role, campus_key) WHERE deleted_at IS NULL;
COMMIT;

-- 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;

-- Apply after 001_public_folders.sql. Existing public folders stay public.
BEGIN;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_role_check;
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus_key VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS visibility VARCHAR(20) NOT NULL DEFAULT 'public' CHECK(visibility IN ('public','restricted'));
ALTER TABLE managed_folders ALTER COLUMN visibility SET DEFAULT 'restricted';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_id_name_key;
CREATE UNIQUE INDEX IF NOT EXISTS managed_folders_active_name_idx ON managed_folders(owner_id,name) WHERE deleted_at IS NULL;
-- Existing folders were executive public folders; campus backfill aids display.
UPDATE managed_folders g SET campus=COALESCE(u.campus,''), campus_key=lower(trim(regexp_replace(COALESCE(u.campus,''),'\s+campus\s*$','','i'))) FROM users u WHERE g.owner_id=u.id AND g.campus='';
CREATE TABLE IF NOT EXISTS managed_folder_members (
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id) ON DELETE RESTRICT,
 user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 invited_by BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 state VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK(state IN ('pending','accepted','declined')),
 invited_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 responded_at TIMESTAMPTZ,
 PRIMARY KEY(folder_id,user_id)
);
CREATE INDEX IF NOT EXISTS managed_folder_members_inbox_idx ON managed_folder_members(user_id,state);
CREATE INDEX IF NOT EXISTS managed_folders_campus_idx ON managed_folders(campus_key) WHERE deleted_at IS NULL;
CREATE TABLE IF NOT EXISTS managed_folder_audit (
 id BIGSERIAL PRIMARY KEY,
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(40) NOT NULL,
 target_user_id BIGINT REFERENCES users(id),
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMIT;


CREATE SCHEMA IF NOT EXISTS school_files_tourism_hospitality;
SET search_path TO school_files_tourism_hospitality, public;
BEGIN;
CREATE TABLE IF NOT EXISTS managed_files (
 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,
 campus VARCHAR(180) NOT NULL,
 campus_key VARCHAR(180) NOT NULL,
 title VARCHAR(200) NOT NULL,
 current_version INTEGER NOT NULL DEFAULT 1,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS managed_file_versions (
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 version INTEGER NOT NULL,
 filename VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL CHECK(size_bytes > 0),
 sha256 CHAR(64) NOT NULL,
 mime VARCHAR(150) NOT NULL,
 storage_key CHAR(64) NOT NULL UNIQUE,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(file_id, version)
);
CREATE TABLE IF NOT EXISTS managed_uploads (
 id CHAR(32) PRIMARY KEY,
 owner_id BIGINT NOT NULL REFERENCES users(id),
 client_key CHAR(32) NOT NULL,
 file_id BIGINT REFERENCES managed_files(id),
 expected_version INTEGER NOT NULL DEFAULT 0,
 filename VARCHAR(200) NOT NULL,
 title VARCHAR(200) NOT NULL,
 size_bytes BIGINT NOT NULL,
 sha256 CHAR(64) NOT NULL,
 received_bytes BIGINT NOT NULL DEFAULT 0,
 result_file_id BIGINT REFERENCES managed_files(id),
 result_version INTEGER,
 expires_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '7 days'),
 UNIQUE(owner_id, client_key)
);
CREATE TABLE IF NOT EXISTS managed_file_audit (
 id BIGSERIAL PRIMARY KEY,
 file_id BIGINT NOT NULL REFERENCES managed_files(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(50) NOT NULL,
 version INTEGER NOT NULL,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS managed_files_owner_idx ON managed_files(owner_id);
CREATE INDEX IF NOT EXISTS managed_files_scope_idx ON managed_files(owner_role, campus_key) WHERE deleted_at IS NULL;
COMMIT;

-- 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;

-- Apply after 001_public_folders.sql. Existing public folders stay public.
BEGIN;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_role_check;
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS campus_key VARCHAR(180) NOT NULL DEFAULT '';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS visibility VARCHAR(20) NOT NULL DEFAULT 'public' CHECK(visibility IN ('public','restricted'));
ALTER TABLE managed_folders ALTER COLUMN visibility SET DEFAULT 'restricted';
ALTER TABLE managed_folders ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
ALTER TABLE managed_folders DROP CONSTRAINT IF EXISTS managed_folders_owner_id_name_key;
CREATE UNIQUE INDEX IF NOT EXISTS managed_folders_active_name_idx ON managed_folders(owner_id,name) WHERE deleted_at IS NULL;
-- Existing folders were executive public folders; campus backfill aids display.
UPDATE managed_folders g SET campus=COALESCE(u.campus,''), campus_key=lower(trim(regexp_replace(COALESCE(u.campus,''),'\s+campus\s*$','','i'))) FROM users u WHERE g.owner_id=u.id AND g.campus='';
CREATE TABLE IF NOT EXISTS managed_folder_members (
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id) ON DELETE RESTRICT,
 user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 invited_by BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
 state VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK(state IN ('pending','accepted','declined')),
 invited_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 responded_at TIMESTAMPTZ,
 PRIMARY KEY(folder_id,user_id)
);
CREATE INDEX IF NOT EXISTS managed_folder_members_inbox_idx ON managed_folder_members(user_id,state);
CREATE INDEX IF NOT EXISTS managed_folders_campus_idx ON managed_folders(campus_key) WHERE deleted_at IS NULL;
CREATE TABLE IF NOT EXISTS managed_folder_audit (
 id BIGSERIAL PRIMARY KEY,
 folder_id BIGINT NOT NULL REFERENCES managed_folders(id),
 actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(40) NOT NULL,
 target_user_id BIGINT REFERENCES users(id),
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMIT;


RESET search_path;
