BEGIN;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

ALTER TABLE platform.agentic_workspace_folders
    ADD COLUMN IF NOT EXISTS trashed_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trash_expires_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trashed_by_login_id INTEGER,
    ADD COLUMN IF NOT EXISTS trashed_by_user_id INTEGER,
    ADD COLUMN IF NOT EXISTS trash_batch_id UUID,
    ADD COLUMN IF NOT EXISTS trash_original_path TEXT,
    ADD COLUMN IF NOT EXISTS trash_restored_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trash_finalized_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trash_metadata JSONB NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE platform.agentic_workspace_files
    ADD COLUMN IF NOT EXISTS trashed_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trash_expires_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trashed_by_login_id INTEGER,
    ADD COLUMN IF NOT EXISTS trashed_by_user_id INTEGER,
    ADD COLUMN IF NOT EXISTS trash_batch_id UUID,
    ADD COLUMN IF NOT EXISTS trash_original_path TEXT,
    ADD COLUMN IF NOT EXISTS trash_restored_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trash_finalized_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trash_metadata JSONB NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE platform.agentic_workspace_artifacts
    ADD COLUMN IF NOT EXISTS trashed_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trash_expires_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trashed_by_login_id INTEGER,
    ADD COLUMN IF NOT EXISTS trashed_by_user_id INTEGER,
    ADD COLUMN IF NOT EXISTS trash_batch_id UUID,
    ADD COLUMN IF NOT EXISTS trash_original_path TEXT,
    ADD COLUMN IF NOT EXISTS trash_restored_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trash_finalized_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trash_metadata JSONB NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE platform.agentic_workspace_links
    ADD COLUMN IF NOT EXISTS archived BOOLEAN NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS archived_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trashed_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trash_expires_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trashed_by_login_id INTEGER,
    ADD COLUMN IF NOT EXISTS trashed_by_user_id INTEGER,
    ADD COLUMN IF NOT EXISTS trash_batch_id UUID,
    ADD COLUMN IF NOT EXISTS trash_original_path TEXT,
    ADD COLUMN IF NOT EXISTS trash_restored_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trash_finalized_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS trash_metadata JSONB NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE platform.agentic_workspace_folders
    DROP CONSTRAINT IF EXISTS chk_agentic_workspace_folders_trash_retention,
    ADD CONSTRAINT chk_agentic_workspace_folders_trash_retention CHECK (
        trashed_at IS NULL
        OR trash_expires_at = trashed_at + INTERVAL '168 hours'
    ),
    DROP CONSTRAINT IF EXISTS chk_agentic_workspace_folders_trash_metadata_object,
    ADD CONSTRAINT chk_agentic_workspace_folders_trash_metadata_object CHECK (jsonb_typeof(trash_metadata) = 'object');

ALTER TABLE platform.agentic_workspace_files
    DROP CONSTRAINT IF EXISTS chk_agentic_workspace_files_trash_retention,
    ADD CONSTRAINT chk_agentic_workspace_files_trash_retention CHECK (
        trashed_at IS NULL
        OR trash_expires_at = trashed_at + INTERVAL '168 hours'
    ),
    DROP CONSTRAINT IF EXISTS chk_agentic_workspace_files_trash_metadata_object,
    ADD CONSTRAINT chk_agentic_workspace_files_trash_metadata_object CHECK (jsonb_typeof(trash_metadata) = 'object');

ALTER TABLE platform.agentic_workspace_artifacts
    DROP CONSTRAINT IF EXISTS chk_agentic_workspace_artifacts_trash_retention,
    ADD CONSTRAINT chk_agentic_workspace_artifacts_trash_retention CHECK (
        trashed_at IS NULL
        OR trash_expires_at = trashed_at + INTERVAL '168 hours'
    ),
    DROP CONSTRAINT IF EXISTS chk_agentic_workspace_artifacts_trash_metadata_object,
    ADD CONSTRAINT chk_agentic_workspace_artifacts_trash_metadata_object CHECK (jsonb_typeof(trash_metadata) = 'object');

ALTER TABLE platform.agentic_workspace_links
    DROP CONSTRAINT IF EXISTS chk_agentic_workspace_links_trash_retention,
    ADD CONSTRAINT chk_agentic_workspace_links_trash_retention CHECK (
        trashed_at IS NULL
        OR trash_expires_at = trashed_at + INTERVAL '168 hours'
    ),
    DROP CONSTRAINT IF EXISTS chk_agentic_workspace_links_trash_metadata_object,
    ADD CONSTRAINT chk_agentic_workspace_links_trash_metadata_object CHECK (jsonb_typeof(trash_metadata) = 'object');

CREATE TABLE IF NOT EXISTS platform.agentic_workspace_trash_items (
    id_trash_item BIGSERIAL PRIMARY KEY,
    trash_batch_id UUID NOT NULL,
    workspace_id UUID NOT NULL REFERENCES platform.agentic_workspace_workspaces(workspace_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,
    item_type VARCHAR(40) NOT NULL,
    item_id BIGINT NOT NULL,
    folder_id INTEGER,
    file_id INTEGER,
    artifact_id INTEGER,
    link_id INTEGER,
    original_path TEXT,
    original_name TEXT,
    status VARCHAR(40) NOT NULL DEFAULT 'trashed',
    trashed_at TIMESTAMPTZ NOT NULL,
    trash_expires_at TIMESTAMPTZ NOT NULL,
    restored_at TIMESTAMPTZ,
    finalized_at TIMESTAMPTZ,
    actor_snapshot JSONB NOT NULL DEFAULT '{}'::jsonb,
    restore_metadata 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(),
    CONSTRAINT chk_agentic_workspace_trash_items_scope CHECK (business_id <> '' AND sucursal_id > 0),
    CONSTRAINT chk_agentic_workspace_trash_items_type CHECK (
        item_type IN ('folder', 'file', 'artifact', 'link')
    ),
    CONSTRAINT chk_agentic_workspace_trash_items_status CHECK (
        status IN ('trashed', 'restored', 'finalized')
    ),
    CONSTRAINT chk_agentic_workspace_trash_items_retention CHECK (
        trash_expires_at = trashed_at + INTERVAL '168 hours'
    ),
    CONSTRAINT chk_agentic_workspace_trash_items_json CHECK (
        jsonb_typeof(actor_snapshot) = 'object'
        AND jsonb_typeof(restore_metadata) = 'object'
        AND jsonb_typeof(metadata) = 'object'
    )
);

CREATE TABLE IF NOT EXISTS platform.agentic_workspace_item_relationships (
    id_relationship 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),
    relationship_type VARCHAR(80) NOT NULL,
    source_item_type VARCHAR(40) NOT NULL,
    source_item_id BIGINT NOT NULL,
    target_item_type VARCHAR(40) NOT NULL,
    target_item_id BIGINT NOT NULL,
    source_folder_id INTEGER,
    source_file_id INTEGER,
    source_artifact_id INTEGER,
    target_folder_id INTEGER,
    target_file_id INTEGER,
    target_artifact_id INTEGER,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    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_item_relationships_scope CHECK (business_id <> '' AND sucursal_id > 0),
    CONSTRAINT chk_agentic_workspace_item_relationships_type CHECK (
        relationship_type <> ''
        AND source_item_type IN ('folder', 'file', 'artifact', 'link')
        AND target_item_type IN ('folder', 'file', 'artifact', 'link')
    ),
    CONSTRAINT chk_agentic_workspace_item_relationships_metadata CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE UNIQUE INDEX IF NOT EXISTS agentic_workspace_trash_items_active_item_uq
    ON platform.agentic_workspace_trash_items (workspace_id, item_type, item_id)
    WHERE status = 'trashed';

CREATE INDEX IF NOT EXISTS agentic_workspace_trash_items_scope_status_idx
    ON platform.agentic_workspace_trash_items (
        business_id,
        sucursal_id,
        workspace_id,
        status,
        trash_expires_at,
        trashed_at DESC
    );

CREATE INDEX IF NOT EXISTS agentic_workspace_trash_items_batch_idx
    ON platform.agentic_workspace_trash_items (trash_batch_id, status);

CREATE UNIQUE INDEX IF NOT EXISTS agentic_workspace_item_relationships_active_uq
    ON platform.agentic_workspace_item_relationships (
        workspace_id,
        relationship_type,
        source_item_type,
        source_item_id,
        target_item_type,
        target_item_id
    )
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS agentic_workspace_item_relationships_source_idx
    ON platform.agentic_workspace_item_relationships (
        workspace_id,
        source_item_type,
        source_item_id,
        relationship_type
    )
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS agentic_workspace_item_relationships_target_idx
    ON platform.agentic_workspace_item_relationships (
        workspace_id,
        target_item_type,
        target_item_id,
        relationship_type
    )
    WHERE soft_delete IS NOT TRUE;

DROP INDEX IF EXISTS platform.agentic_workspace_folders_workspace_path_uq;
DROP INDEX IF EXISTS platform.agentic_workspace_folders_legacy_scope_path_uq;
DROP INDEX IF EXISTS platform.agentic_workspace_files_folder_slug_uq;

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

CREATE UNIQUE INDEX 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 trashed_at IS NULL
      AND archived IS NOT TRUE
      AND workspace_id IS NULL;

CREATE UNIQUE INDEX agentic_workspace_files_folder_slug_uq
    ON platform.agentic_workspace_files (folder_id, slug)
    WHERE soft_delete IS NOT TRUE
      AND trashed_at IS NULL
      AND archived IS NOT TRUE;

DROP INDEX IF EXISTS platform.agentic_workspace_files_storage_scope_idx;
DROP INDEX IF EXISTS platform.agentic_workspace_files_s3_key_idx;
DROP INDEX IF EXISTS platform.agentic_workspace_artifacts_s3_scope;
DROP INDEX IF EXISTS platform.agentic_workspace_artifacts_s3_key;
DROP INDEX IF EXISTS platform.idx_agentic_workspace_artifacts_s3_scope;
DROP INDEX IF EXISTS platform.idx_agentic_workspace_artifacts_s3_key;
DROP INDEX IF EXISTS platform.agentic_workspace_links_workspace_target_idx;

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
      AND trashed_at IS NULL
      AND archived 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
      AND trashed_at IS NULL;

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
      AND trashed_at IS NULL
      AND archived 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
      AND trashed_at IS NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_links_workspace_target_idx
    ON platform.agentic_workspace_links (workspace_id, target_type, target_id)
    WHERE soft_delete IS NOT TRUE
      AND trashed_at IS NULL
      AND archived IS NOT TRUE;

CREATE INDEX IF NOT EXISTS agentic_workspace_folders_archived_idx
    ON platform.agentic_workspace_folders (business_id, sucursal_id, workspace_id, archived, updated_at DESC)
    WHERE soft_delete IS NOT TRUE
      AND trashed_at IS NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_files_archived_idx
    ON platform.agentic_workspace_files (business_id, sucursal_id, workspace_id, archived, updated_at DESC)
    WHERE soft_delete IS NOT TRUE
      AND trashed_at IS NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_artifacts_archived_idx
    ON platform.agentic_workspace_artifacts (business_id, sucursal_id, workspace_id, archived, updated_at DESC)
    WHERE soft_delete IS NOT TRUE
      AND trashed_at IS NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_links_archived_idx
    ON platform.agentic_workspace_links (business_id, sucursal_id, workspace_id, archived, created_at DESC)
    WHERE soft_delete IS NOT TRUE
      AND trashed_at IS NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_folders_trash_idx
    ON platform.agentic_workspace_folders (business_id, sucursal_id, workspace_id, trash_expires_at, trashed_at DESC)
    WHERE soft_delete IS NOT TRUE
      AND trashed_at IS NOT NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_files_trash_idx
    ON platform.agentic_workspace_files (business_id, sucursal_id, workspace_id, trash_expires_at, trashed_at DESC)
    WHERE soft_delete IS NOT TRUE
      AND trashed_at IS NOT NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_artifacts_trash_idx
    ON platform.agentic_workspace_artifacts (business_id, sucursal_id, workspace_id, trash_expires_at, trashed_at DESC)
    WHERE soft_delete IS NOT TRUE
      AND trashed_at IS NOT NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_links_trash_idx
    ON platform.agentic_workspace_links (business_id, sucursal_id, workspace_id, trash_expires_at, trashed_at DESC)
    WHERE soft_delete IS NOT TRUE
      AND trashed_at IS NOT NULL;

ALTER TABLE auth.permission_catalog
    DROP CONSTRAINT IF EXISTS permission_catalog_action_id_check,
    DROP CONSTRAINT IF EXISTS permission_catalog_category_id_check,
    DROP CONSTRAINT IF EXISTS permission_catalog_permission_code_format_chk,
    ADD CONSTRAINT permission_catalog_action_id_check CHECK (action_id >= 1 AND action_id <= 9),
    ADD CONSTRAINT permission_catalog_category_id_check CHECK (category_id >= 1 AND category_id <= 99),
    ADD CONSTRAINT permission_catalog_permission_code_format_chk CHECK (
        permission_code = (category_id * 10) + action_id
        AND permission_code >= 11
        AND permission_code <= 999
    );

INSERT INTO auth.permission_catalog (
    permission_code,
    category_id,
    action_id,
    category_key,
    category_label,
    action_key,
    action_label,
    display_order,
    active,
    legacy_observed
)
VALUES
    (631, 63, 1, 'agentic_workspace_lifecycle', 'Agentic Workspace Lifecycle', 'archive_list', 'Ver archivados', 631, TRUE, FALSE),
    (632, 63, 2, 'agentic_workspace_lifecycle', 'Agentic Workspace Lifecycle', 'archive_write', 'Archivar', 632, TRUE, FALSE),
    (633, 63, 3, 'agentic_workspace_lifecycle', 'Agentic Workspace Lifecycle', 'unarchive_write', 'Desarchivar', 633, TRUE, FALSE),
    (634, 63, 4, 'agentic_workspace_lifecycle', 'Agentic Workspace Lifecycle', 'trash_list', 'Ver papelera', 634, TRUE, FALSE),
    (635, 63, 5, 'agentic_workspace_lifecycle', 'Agentic Workspace Lifecycle', 'trash_write', 'Mover a papelera', 635, TRUE, FALSE),
    (636, 63, 6, 'agentic_workspace_lifecycle', 'Agentic Workspace Lifecycle', 'trash_restore', 'Restaurar papelera', 636, TRUE, FALSE),
    (637, 63, 7, 'agentic_workspace_lifecycle', 'Agentic Workspace Lifecycle', 'trash_finalize', 'Finalizar papelera', 637, TRUE, FALSE)
ON CONFLICT (permission_code) DO UPDATE SET
    category_key = EXCLUDED.category_key,
    category_label = EXCLUDED.category_label,
    action_key = EXCLUDED.action_key,
    action_label = EXCLUDED.action_label,
    active = TRUE,
    updated_at = now();

COMMENT ON TABLE platform.agentic_workspace_trash_items IS
    'Recoverable Agentic Workspace trash ledger. Rows stay restorable for exactly 168 hours, then the finalizer marks source rows terminal soft_delete.';
COMMENT ON TABLE platform.agentic_workspace_item_relationships IS
    'Durable relationships between Agentic Workspace items, including artifact/source Markdown sidecars that must follow lifecycle actions.';
COMMENT ON COLUMN platform.agentic_workspace_trash_items.trash_expires_at IS
    'Exact retention boundary: trashed_at + 168 hours. Application finalizer uses this timestamp idempotently.';
COMMENT ON COLUMN platform.agentic_workspace_item_relationships.relationship_type IS
    'Examples: artifact_source_file, media_sidecar, markdown_sidecar, markdown_context_sidecar. Relationships are logical links, not public UI labels.';

COMMIT;
