BEGIN;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS platform.agentic_workspace_workspaces (
    workspace_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_key CHAR(64) NOT NULL UNIQUE,
    business_id VARCHAR(255) NOT NULL,
    sucursal_id INTEGER NOT NULL,
    empleado_digital_id INTEGER,
    login_id INTEGER,
    user_id INTEGER,
    channel VARCHAR(40) NOT NULL DEFAULT 'whatsapp',
    channel_identity_hash CHAR(64),
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    archived BOOLEAN NOT NULL DEFAULT FALSE,
    archived_at TIMESTAMPTZ,
    soft_delete BOOLEAN NOT NULL DEFAULT FALSE,
    soft_delete_date TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT chk_agentic_workspace_workspaces_scope CHECK (business_id <> '' AND sucursal_id > 0),
    CONSTRAINT chk_agentic_workspace_workspaces_metadata_object CHECK (jsonb_typeof(metadata) = 'object')
);

ALTER TABLE platform.agentic_workspace_folders
    ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    ADD COLUMN IF NOT EXISTS user_id INTEGER;

ALTER TABLE platform.agentic_workspace_files
    ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    ADD COLUMN IF NOT EXISTS user_id INTEGER,
    ADD COLUMN IF NOT EXISTS storage_backend VARCHAR(40) NOT NULL DEFAULT 'postgres',
    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 content_type VARCHAR(120) NOT NULL DEFAULT 'text/plain; charset=utf-8',
    ADD COLUMN IF NOT EXISTS size_bytes INTEGER NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS sha256 CHAR(64),
    ADD COLUMN IF NOT EXISTS storage_metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS content_text_preview TEXT NOT NULL DEFAULT '',
    ADD COLUMN IF NOT EXISTS search_text TEXT NOT NULL DEFAULT '';

ALTER TABLE platform.agentic_workspace_artifacts
    ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    ADD COLUMN IF NOT EXISTS user_id INTEGER,
    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_links
    ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    ADD COLUMN IF NOT EXISTS user_id INTEGER;

ALTER TABLE platform.agentic_workspace_memories
    ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    ADD COLUMN IF NOT EXISTS user_id INTEGER;

ALTER TABLE platform.agentic_workspace_operations
    ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    ADD COLUMN IF NOT EXISTS user_id INTEGER;

ALTER TABLE platform.agentic_workspace_router_decisions
    ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    ADD COLUMN IF NOT EXISTS user_id INTEGER;

ALTER TABLE platform.agentic_workspace_jobs
    ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    ADD COLUMN IF NOT EXISTS user_id INTEGER;

ALTER TABLE platform.agentic_workspace_progress_events
    ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    ADD COLUMN IF NOT EXISTS user_id INTEGER;

ALTER TABLE platform.agentic_workspace_errors
    ADD COLUMN IF NOT EXISTS workspace_id UUID REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    ADD COLUMN IF NOT EXISTS user_id INTEGER;

ALTER TABLE platform.agentic_workspace_files
    DROP CONSTRAINT IF EXISTS chk_agentic_workspace_files_storage;

ALTER TABLE platform.agentic_workspace_files
    ADD CONSTRAINT chk_agentic_workspace_files_storage
    CHECK (storage_backend IN ('postgres', 's3'));

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

DROP INDEX IF EXISTS platform.agentic_workspace_folders_scope_path_uq;

CREATE UNIQUE INDEX IF NOT EXISTS agentic_workspace_folders_workspace_path_uq
    ON platform.agentic_workspace_folders (workspace_id, path)
    WHERE soft_delete IS NOT TRUE AND workspace_id IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS agentic_workspace_folders_legacy_scope_path_uq
    ON platform.agentic_workspace_folders (
        business_id,
        sucursal_id,
        COALESCE(empleado_digital_id, 0),
        COALESCE(login_id, 0),
        COALESCE(client_id, 0),
        path
    )
    WHERE soft_delete IS NOT TRUE AND workspace_id IS NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_workspaces_scope_idx
    ON platform.agentic_workspace_workspaces (
        business_id,
        sucursal_id,
        empleado_digital_id,
        login_id,
        user_id,
        created_at DESC
    )
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS agentic_workspace_files_storage_scope_idx
    ON platform.agentic_workspace_files (business_id, sucursal_id, workspace_id, storage_backend)
    WHERE soft_delete IS NOT TRUE;

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

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

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

COMMENT ON TABLE platform.agentic_workspace_workspaces IS
    'Persistent Agentic Workspace identity per exact business, branch, digital employee, human login/user and channel scope.';
COMMENT ON COLUMN platform.agentic_workspace_workspaces.workspace_id IS
    'Stable UUID used in PostgreSQL metadata and S3 object keys.';
COMMENT ON COLUMN platform.agentic_workspace_workspaces.workspace_key IS
    'SHA256 key over the exact normalized workspace scope; avoids nullable unique ambiguity.';
COMMENT ON COLUMN platform.agentic_workspace_files.content_text IS
    'Legacy full body for postgres-backed rows only. New s3-backed rows keep this empty.';
COMMENT ON COLUMN platform.agentic_workspace_files.content_text_preview IS
    'Bounded preview for UI/search; not the canonical full body.';
COMMENT ON COLUMN platform.agentic_workspace_files.s3_key IS
    'Private technical S3 key for file body. Authorization is enforced by PostgreSQL scope and Brain/OpHub endpoints.';
COMMENT ON COLUMN platform.agentic_workspace_artifacts.s3_key IS
    'Private technical S3 key for artifact binary. Authorization is enforced by PostgreSQL scope and Brain/OpHub endpoints.';

COMMIT;
