SELECT 1; /* MIGRACION 034: Crear tabla base de idempotencia POS multi-tenant | Fecha: 2026-02-14 */

CREATE TABLE IF NOT EXISTS pos_idempotency_requests (
    id BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    login_id INTEGER NOT NULL,
    endpoint_scope VARCHAR(150) NOT NULL,
    idempotency_key VARCHAR(128) NOT NULL,
    request_hash CHAR(64) NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'processing',
    response_http_status INTEGER,
    response_body JSONB,
    resource_type VARCHAR(80),
    resource_id TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_pos_idempotency_requests_scope_key UNIQUE (business_id, endpoint_scope, idempotency_key),
    CONSTRAINT chk_pos_idempotency_status CHECK (status IN ('processing', 'completed', 'failed')),
    CONSTRAINT chk_pos_idempotency_business_id CHECK (length(trim(business_id)) > 0),
    CONSTRAINT chk_pos_idempotency_scope CHECK (length(trim(endpoint_scope)) > 0),
    CONSTRAINT chk_pos_idempotency_key CHECK (length(trim(idempotency_key)) BETWEEN 8 AND 128),
    CONSTRAINT chk_pos_idempotency_hash CHECK (request_hash ~ '^[a-f0-9]{64}$'),
    CONSTRAINT chk_pos_idempotency_http_status CHECK (
        response_http_status IS NULL OR (response_http_status BETWEEN 100 AND 599)
    )
);

CREATE INDEX IF NOT EXISTS idx_pos_idempotency_status_created_at
    ON pos_idempotency_requests (status, created_at);

CREATE INDEX IF NOT EXISTS idx_pos_idempotency_business_status_created_at
    ON pos_idempotency_requests (business_id, status, created_at);

CREATE INDEX IF NOT EXISTS idx_pos_idempotency_created_at
    ON pos_idempotency_requests (created_at);

COMMENT ON TABLE pos_idempotency_requests IS
    'Registro de solicitudes idempotentes del POS para prevenir duplicados por retries/red.';

COMMENT ON COLUMN pos_idempotency_requests.endpoint_scope IS
    'Scope logico del endpoint, por ejemplo ventas:create o pagos:registrar.';

COMMENT ON COLUMN pos_idempotency_requests.idempotency_key IS
    'Llave enviada por cliente, unica por tenant y scope.';

COMMENT ON COLUMN pos_idempotency_requests.request_hash IS
    'SHA256 del payload canonico. Si cambia para la misma key, se rechaza con 409.';

COMMENT ON COLUMN pos_idempotency_requests.response_body IS
    'Respuesta JSON serializada para replay de requests completados o fallidos.';
