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