BEGIN;

ALTER TABLE platform.agentic_workspace_artifacts
    ADD COLUMN IF NOT EXISTS s3_bucket text,
    ADD COLUMN IF NOT EXISTS s3_key text,
    ADD COLUMN IF NOT EXISTS s3_region text,
    ADD COLUMN IF NOT EXISTS kms_key_id text,
    ADD COLUMN IF NOT EXISTS version_id text,
    ADD COLUMN IF NOT EXISTS etag text,
    ADD COLUMN IF NOT EXISTS storage_metadata jsonb NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE platform.agentic_workspace_artifacts
    DROP CONSTRAINT IF EXISTS chk_agentic_workspace_artifacts_storage;

ALTER TABLE platform.agentic_workspace_artifacts
    ADD CONSTRAINT chk_agentic_workspace_artifacts_storage
    CHECK (storage_backend IN ('postgres', 'protected_frontend_path', 's3'));

CREATE INDEX IF NOT EXISTS idx_agentic_workspace_artifacts_s3_scope
    ON platform.agentic_workspace_artifacts (business_id, sucursal_id, storage_backend, s3_bucket)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS idx_agentic_workspace_artifacts_s3_key
    ON platform.agentic_workspace_artifacts (s3_key)
    WHERE storage_backend = 's3' AND soft_delete IS NOT TRUE;

COMMENT ON COLUMN platform.agentic_workspace_artifacts.s3_bucket IS
    'Private S3 bucket for Agentic Workspace binary artifacts.';
COMMENT ON COLUMN platform.agentic_workspace_artifacts.s3_key IS
    'Private technical S3 key. Authorization is enforced by PostgreSQL scope and Brain/OpHub endpoints, not by this path.';
COMMENT ON COLUMN platform.agentic_workspace_artifacts.s3_region IS
    'AWS region for the artifact bucket, default us-east-2.';
COMMENT ON COLUMN platform.agentic_workspace_artifacts.kms_key_id IS
    'KMS key id or alias used for server-side encryption when storage_backend=s3.';
COMMENT ON COLUMN platform.agentic_workspace_artifacts.version_id IS
    'S3 object version id when bucket versioning is enabled.';
COMMENT ON COLUMN platform.agentic_workspace_artifacts.etag IS
    'S3 ETag returned by PutObject.';
COMMENT ON COLUMN platform.agentic_workspace_artifacts.storage_metadata IS
    'Backend-specific storage metadata excluding secrets.';

COMMIT;
