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;
