BEGIN;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS platform.agentic_workspace_task_runs (
    task_run_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    run_key CHAR(64) NOT NULL,
    job_id VARCHAR(80) REFERENCES platform.agentic_workspace_jobs(job_id),
    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 REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    channel VARCHAR(40) NOT NULL DEFAULT 'whatsapp',
    channel_identity_hash CHAR(64),
    status VARCHAR(40) NOT NULL DEFAULT 'planned',
    request_text TEXT NOT NULL DEFAULT '',
    request_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    plan_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    output_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    verification_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    repair_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    summary_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    cancelled_at TIMESTAMPTZ,
    CONSTRAINT chk_agentic_workspace_task_runs_scope CHECK (business_id <> '' AND sucursal_id > 0),
    CONSTRAINT chk_agentic_workspace_task_runs_status CHECK (
        status IN (
            'planned',
            'running',
            'awaiting_input',
            'awaiting_confirmation',
            'needs_repair',
            'partially_completed',
            'completed',
            'failed',
            'cancelled'
        )
    ),
    CONSTRAINT chk_agentic_workspace_task_runs_json CHECK (
        jsonb_typeof(request_payload) = 'object'
        AND jsonb_typeof(plan_payload) = 'object'
        AND jsonb_typeof(output_payload) = 'object'
        AND jsonb_typeof(verification_payload) = 'object'
        AND jsonb_typeof(repair_payload) = 'object'
        AND jsonb_typeof(summary_payload) = 'object'
        AND jsonb_typeof(metadata) = 'object'
    )
);

CREATE TABLE IF NOT EXISTS platform.agentic_workspace_task_steps (
    task_step_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    task_run_id UUID NOT NULL REFERENCES platform.agentic_workspace_task_runs(task_run_id) ON DELETE CASCADE,
    step_key CHAR(64) NOT NULL,
    step_order INTEGER NOT NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'planned',
    action VARCHAR(100) NOT NULL DEFAULT 'noop',
    domain VARCHAR(80) NOT NULL DEFAULT 'workspace',
    title TEXT NOT NULL DEFAULT '',
    target_type VARCHAR(80),
    target_id TEXT,
    target_path TEXT,
    input_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    output_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    verification_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    repair_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    evidence_refs JSONB NOT NULL DEFAULT '[]'::jsonb,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    operation_id BIGINT REFERENCES platform.agentic_workspace_operations(id_operation),
    confirmation_id UUID REFERENCES platform.agentic_workspace_confirmations(confirmation_id),
    idempotency_key CHAR(64),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    CONSTRAINT chk_agentic_workspace_task_steps_order CHECK (step_order > 0),
    CONSTRAINT chk_agentic_workspace_task_steps_status CHECK (
        status IN (
            'planned',
            'resolving',
            'running',
            'verifying',
            'completed',
            'blocked',
            'failed',
            'skipped',
            'repairing'
        )
    ),
    CONSTRAINT chk_agentic_workspace_task_steps_json CHECK (
        jsonb_typeof(input_payload) = 'object'
        AND jsonb_typeof(output_payload) = 'object'
        AND jsonb_typeof(verification_payload) = 'object'
        AND jsonb_typeof(repair_payload) = 'object'
        AND jsonb_typeof(evidence_refs) = 'array'
        AND jsonb_typeof(metadata) = 'object'
    )
);

CREATE TABLE IF NOT EXISTS platform.agentic_workspace_interactions (
    interaction_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    task_run_id UUID NOT NULL REFERENCES platform.agentic_workspace_task_runs(task_run_id) ON DELETE CASCADE,
    task_step_id UUID REFERENCES platform.agentic_workspace_task_steps(task_step_id) ON DELETE SET NULL,
    confirmation_id UUID REFERENCES platform.agentic_workspace_confirmations(confirmation_id),
    interaction_type VARCHAR(40) NOT NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'pending',
    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 REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    channel VARCHAR(40) NOT NULL DEFAULT 'whatsapp',
    prompt_text TEXT NOT NULL DEFAULT '',
    response_text TEXT NOT NULL DEFAULT '',
    safe_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    response_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    expires_at TIMESTAMPTZ,
    resolved_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT chk_agentic_workspace_interactions_scope CHECK (business_id <> '' AND sucursal_id > 0),
    CONSTRAINT chk_agentic_workspace_interactions_type CHECK (
        interaction_type IN ('clarification', 'confirmation', 'choice', 'repair_decision', 'steer', 'cancel')
    ),
    CONSTRAINT chk_agentic_workspace_interactions_status CHECK (
        status IN ('pending', 'answered', 'approved', 'rejected', 'ambiguous', 'cancelled', 'expired')
    ),
    CONSTRAINT chk_agentic_workspace_interactions_json CHECK (
        jsonb_typeof(safe_payload) = 'object'
        AND jsonb_typeof(response_payload) = 'object'
        AND jsonb_typeof(metadata) = 'object'
    )
);

CREATE TABLE IF NOT EXISTS platform.agentic_workspace_locks (
    lock_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES platform.agentic_workspace_workspaces(workspace_id),
    business_id VARCHAR(255) NOT NULL,
    sucursal_id INTEGER NOT NULL,
    entity_type VARCHAR(80) NOT NULL,
    entity_id TEXT,
    normalized_path TEXT,
    task_run_id UUID REFERENCES platform.agentic_workspace_task_runs(task_run_id) ON DELETE SET NULL,
    task_step_id UUID REFERENCES platform.agentic_workspace_task_steps(task_step_id) ON DELETE SET NULL,
    actor_snapshot JSONB NOT NULL DEFAULT '{}'::jsonb,
    status VARCHAR(40) NOT NULL DEFAULT 'active',
    acquired_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at TIMESTAMPTZ NOT NULL,
    released_at TIMESTAMPTZ,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT chk_agentic_workspace_locks_scope CHECK (business_id <> '' AND sucursal_id > 0),
    CONSTRAINT chk_agentic_workspace_locks_target CHECK (
        entity_type <> '' AND (COALESCE(entity_id, '') <> '' OR COALESCE(normalized_path, '') <> '')
    ),
    CONSTRAINT chk_agentic_workspace_locks_status CHECK (status IN ('active', 'released', 'expired')),
    CONSTRAINT chk_agentic_workspace_locks_json CHECK (
        jsonb_typeof(actor_snapshot) = 'object'
        AND jsonb_typeof(metadata) = 'object'
    )
);

ALTER TABLE platform.agentic_workspace_jobs
    ADD COLUMN IF NOT EXISTS task_run_id UUID REFERENCES platform.agentic_workspace_task_runs(task_run_id);

ALTER TABLE platform.agentic_workspace_progress_events
    ADD COLUMN IF NOT EXISTS task_run_id UUID REFERENCES platform.agentic_workspace_task_runs(task_run_id),
    ADD COLUMN IF NOT EXISTS task_step_id UUID REFERENCES platform.agentic_workspace_task_steps(task_step_id);

ALTER TABLE platform.agentic_workspace_operations
    ADD COLUMN IF NOT EXISTS task_run_id UUID REFERENCES platform.agentic_workspace_task_runs(task_run_id),
    ADD COLUMN IF NOT EXISTS task_step_id UUID REFERENCES platform.agentic_workspace_task_steps(task_step_id);

ALTER TABLE platform.agentic_workspace_versions
    ADD COLUMN IF NOT EXISTS task_run_id UUID REFERENCES platform.agentic_workspace_task_runs(task_run_id),
    ADD COLUMN IF NOT EXISTS task_step_id UUID REFERENCES platform.agentic_workspace_task_steps(task_step_id);

ALTER TABLE platform.agentic_workspace_confirmations
    ADD COLUMN IF NOT EXISTS task_run_id UUID REFERENCES platform.agentic_workspace_task_runs(task_run_id),
    ADD COLUMN IF NOT EXISTS task_step_id UUID REFERENCES platform.agentic_workspace_task_steps(task_step_id);

CREATE UNIQUE INDEX IF NOT EXISTS agentic_workspace_task_runs_run_key_uq
    ON platform.agentic_workspace_task_runs (business_id, sucursal_id, run_key);

CREATE INDEX IF NOT EXISTS agentic_workspace_task_runs_scope_status_idx
    ON platform.agentic_workspace_task_runs (
        business_id,
        sucursal_id,
        workspace_id,
        login_id,
        user_id,
        status,
        created_at DESC
    );

CREATE INDEX IF NOT EXISTS agentic_workspace_task_runs_job_idx
    ON platform.agentic_workspace_task_runs (job_id)
    WHERE job_id IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS agentic_workspace_task_steps_run_step_key_uq
    ON platform.agentic_workspace_task_steps (task_run_id, step_key);

CREATE INDEX IF NOT EXISTS agentic_workspace_task_steps_run_order_idx
    ON platform.agentic_workspace_task_steps (task_run_id, step_order);

CREATE INDEX IF NOT EXISTS agentic_workspace_task_steps_status_idx
    ON platform.agentic_workspace_task_steps (task_run_id, status, updated_at DESC);

CREATE INDEX IF NOT EXISTS agentic_workspace_interactions_run_status_idx
    ON platform.agentic_workspace_interactions (task_run_id, status, created_at DESC);

CREATE INDEX IF NOT EXISTS agentic_workspace_interactions_actor_pending_idx
    ON platform.agentic_workspace_interactions (
        business_id,
        sucursal_id,
        workspace_id,
        login_id,
        user_id,
        interaction_type,
        status,
        created_at DESC
    );

CREATE INDEX IF NOT EXISTS agentic_workspace_interactions_confirmation_idx
    ON platform.agentic_workspace_interactions (confirmation_id)
    WHERE confirmation_id IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS agentic_workspace_locks_active_target_uq
    ON platform.agentic_workspace_locks (
        workspace_id,
        entity_type,
        COALESCE(entity_id, ''),
        COALESCE(normalized_path, '')
    )
    WHERE status = 'active';

CREATE INDEX IF NOT EXISTS agentic_workspace_locks_expiry_idx
    ON platform.agentic_workspace_locks (status, expires_at);

CREATE INDEX IF NOT EXISTS agentic_workspace_progress_task_run_idx
    ON platform.agentic_workspace_progress_events (task_run_id, task_step_id, created_at DESC)
    WHERE task_run_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_operations_task_run_idx
    ON platform.agentic_workspace_operations (task_run_id, task_step_id, created_at DESC)
    WHERE task_run_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_versions_task_run_idx
    ON platform.agentic_workspace_versions (task_run_id, task_step_id, created_at DESC)
    WHERE task_run_id IS NOT NULL;

COMMENT ON TABLE platform.agentic_workspace_task_runs IS
    'Durable task ledger for one complex Agentic Workspace employee request.';
COMMENT ON TABLE platform.agentic_workspace_task_steps IS
    'Durable subactions within an Agentic Workspace task run, including verification and repair metadata.';
COMMENT ON TABLE platform.agentic_workspace_interactions IS
    'Unified human clarification, confirmation, choice and repair-decision prompts scoped to the actor who must answer.';
COMMENT ON TABLE platform.agentic_workspace_locks IS
    'TTL collaboration locks for shared Agentic Workspace targets before mutating folders, files, artifacts or follow-ups.';
COMMENT ON COLUMN platform.agentic_workspace_task_steps.evidence_refs IS
    'Client-safe references to source media or documents that grounded this step; no bucket keys or private URLs.';
COMMENT ON COLUMN platform.agentic_workspace_locks.actor_snapshot IS
    'Non-secret actor metadata used to explain lock conflicts in human language.';

COMMIT;
