-- ============================================================================
-- MIGRACION 012
-- Tabla: crm.clientes
-- Objetivo:
--   - Agregar trazabilidad de ultimo actor que actualizo `apodo`
--   - Soportar actor humano (auth.login.id_login) o empleado digital (ai.empleado_digital.id_empleado)
--   - Validar forma JSONB y existencia de referencia por negocio
-- ============================================================================

ALTER TABLE crm.clientes
    ADD COLUMN IF NOT EXISTS apodo_updated_by jsonb;

COMMENT ON COLUMN crm.clientes.apodo_updated_by IS
'Actor que actualizo por ultima vez crm.clientes.apodo. JSONB objeto: {"actor_type":"human_login|digital_employee","actor_id":<int>}';

ALTER TABLE crm.clientes
    DROP CONSTRAINT IF EXISTS clientes_apodo_updated_by_shape_chk;

ALTER TABLE crm.clientes
    ADD CONSTRAINT clientes_apodo_updated_by_shape_chk
    CHECK (
        apodo_updated_by IS NULL
        OR (
            jsonb_typeof(apodo_updated_by) = 'object'
            AND apodo_updated_by ? 'actor_type'
            AND apodo_updated_by ? 'actor_id'
            AND jsonb_typeof(apodo_updated_by -> 'actor_type') = 'string'
            AND (apodo_updated_by ->> 'actor_type') IN ('human_login', 'digital_employee')
            AND jsonb_typeof(apodo_updated_by -> 'actor_id') = 'number'
            AND (apodo_updated_by ->> 'actor_id') ~ '^[0-9]+$'
            AND (apodo_updated_by ->> 'actor_id')::bigint > 0
        )
    );

CREATE OR REPLACE FUNCTION crm.validate_clientes_apodo_updated_by_ref()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_actor_type text;
    v_actor_id integer;
    v_exists boolean;
BEGIN
    IF NEW.apodo_updated_by IS NULL THEN
        RETURN NEW;
    END IF;

    v_actor_type := NEW.apodo_updated_by ->> 'actor_type';
    v_actor_id := (NEW.apodo_updated_by ->> 'actor_id')::integer;

    IF v_actor_type = 'human_login' THEN
        SELECT EXISTS (
            SELECT 1
            FROM auth.login AS l
            WHERE l.id_login = v_actor_id
              AND l.business_id = NEW.business_id
              AND (l.soft_delete = false OR l.soft_delete IS NULL)
        ) INTO v_exists;
    ELSIF v_actor_type = 'digital_employee' THEN
        SELECT EXISTS (
            SELECT 1
            FROM ai.empleado_digital AS ed
            WHERE ed.id_empleado = v_actor_id
              AND ed.business_id = NEW.business_id
              AND (ed.soft_delete = false OR ed.soft_delete IS NULL)
        ) INTO v_exists;
    ELSE
        RAISE EXCEPTION
            'apodo_updated_by.actor_type invalido: %', v_actor_type;
    END IF;

    IF NOT v_exists THEN
        RAISE EXCEPTION
            'apodo_updated_by referencia inexistente o fuera del negocio (actor_type=%, actor_id=%, business_id=%)',
            v_actor_type, v_actor_id, NEW.business_id;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_validate_clientes_apodo_updated_by_ref ON crm.clientes;

CREATE TRIGGER trg_validate_clientes_apodo_updated_by_ref
    BEFORE INSERT OR UPDATE OF apodo_updated_by, business_id
    ON crm.clientes
    FOR EACH ROW
    EXECUTE FUNCTION crm.validate_clientes_apodo_updated_by_ref();
