BEGIN;

CREATE TABLE IF NOT EXISTS platform.agentic_workspace_versions (
    id_version BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(255) NOT NULL,
    sucursal_id INTEGER NOT NULL,
    empleado_digital_id INTEGER,
    login_id INTEGER,
    user_id INTEGER,
    client_id INTEGER,
    workspace_id UUID NOT NULL REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    operation_id BIGINT REFERENCES platform.agentic_workspace_operations(id_operation),
    action VARCHAR(80) NOT NULL,
    entity_type VARCHAR(40) NOT NULL,
    entity_id VARCHAR(120) NOT NULL,
    folder_id INTEGER,
    file_id INTEGER,
    artifact_id INTEGER,
    version_number INTEGER NOT NULL,
    before_state JSONB NOT NULL DEFAULT '{}'::jsonb,
    after_state JSONB NOT NULL DEFAULT '{}'::jsonb,
    diff_summary JSONB NOT NULL DEFAULT '{}'::jsonb,
    actor_snapshot JSONB NOT NULL DEFAULT '{}'::jsonb,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT chk_agentic_workspace_versions_scope CHECK (business_id <> '' AND sucursal_id > 0),
    CONSTRAINT chk_agentic_workspace_versions_entity CHECK (entity_type <> '' AND entity_id <> ''),
    CONSTRAINT chk_agentic_workspace_versions_number CHECK (version_number > 0),
    CONSTRAINT chk_agentic_workspace_versions_before_object CHECK (jsonb_typeof(before_state) = 'object'),
    CONSTRAINT chk_agentic_workspace_versions_after_object CHECK (jsonb_typeof(after_state) = 'object'),
    CONSTRAINT chk_agentic_workspace_versions_diff_object CHECK (jsonb_typeof(diff_summary) = 'object'),
    CONSTRAINT chk_agentic_workspace_versions_actor_object CHECK (jsonb_typeof(actor_snapshot) = 'object'),
    CONSTRAINT chk_agentic_workspace_versions_metadata_object CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE UNIQUE INDEX IF NOT EXISTS agentic_workspace_versions_entity_number_uq
    ON platform.agentic_workspace_versions (workspace_id, entity_type, entity_id, version_number);

CREATE INDEX IF NOT EXISTS agentic_workspace_versions_workspace_created_idx
    ON platform.agentic_workspace_versions (business_id, sucursal_id, workspace_id, created_at DESC);

CREATE INDEX IF NOT EXISTS agentic_workspace_versions_file_created_idx
    ON platform.agentic_workspace_versions (workspace_id, file_id, created_at DESC)
    WHERE file_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_versions_folder_created_idx
    ON platform.agentic_workspace_versions (workspace_id, folder_id, created_at DESC)
    WHERE folder_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_versions_artifact_created_idx
    ON platform.agentic_workspace_versions (workspace_id, artifact_id, created_at DESC)
    WHERE artifact_id IS NOT NULL;

COMMENT ON TABLE platform.agentic_workspace_versions IS
    'Private Agentic Workspace audit/version history. Stores before/after snapshots, deterministic diffs and actor identity per workspace mutation.';
COMMENT ON COLUMN platform.agentic_workspace_versions.actor_snapshot IS
    'Business employee/runtime identity that performed the change. This is audit metadata, not public UI copy.';
COMMENT ON COLUMN platform.agentic_workspace_versions.diff_summary IS
    'Deterministic structured diff, including bounded text highlights for LLM explanations.';
COMMENT ON COLUMN platform.agentic_workspace_versions.before_state IS
    'Snapshot before the mutation. File content may be included for text files and bounded by Brain logic.';
COMMENT ON COLUMN platform.agentic_workspace_versions.after_state IS
    'Snapshot after the mutation. This complements agentic_workspace_operations for navigable version history.';

COMMIT;
