Saltar a contenido

0012 - mae_locales_cliente

"""mae_locales_cliente

Revision ID: 0012
Revises: 0011
Create Date: 2026-09-20
"""
from typing import Sequence, Union

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "0012"
down_revision: Union[str, None] = "0011"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE clientes.mae_locales_cliente (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            empresa_id BIGINT NOT NULL,
            cliente_id UUID NOT NULL,
            codigo VARCHAR(30) NOT NULL,
            nombre VARCHAR(150) NOT NULL,
            direccion TEXT NOT NULL,
            ciudad_id UUID,
            latitud NUMERIC(9,6),
            longitud NUMERIC(9,6),
            is_activo BOOLEAN NOT NULL DEFAULT true,
            created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            deleted_at TIMESTAMPTZ,
            created_by UUID,
            updated_by UUID,
            CONSTRAINT uq_locales_cliente_empresa_id UNIQUE (empresa_id, id),
            CONSTRAINT uq_locales_cliente_empresa_cliente_codigo
                UNIQUE (empresa_id, cliente_id, codigo),
            CONSTRAINT fk_locales_cliente
                FOREIGN KEY (empresa_id, cliente_id)
                REFERENCES clientes.mae_clientes (empresa_id, id),
            CONSTRAINT ck_locales_cliente_coordenadas_pareja
                CHECK ((latitud IS NULL) = (longitud IS NULL)),
            CONSTRAINT ck_locales_cliente_latitud
                CHECK (latitud BETWEEN -90 AND 90),
            CONSTRAINT ck_locales_cliente_longitud
                CHECK (longitud BETWEEN -180 AND 180)
        );

        CREATE INDEX ix_locales_cliente_empresa_cliente
            ON clientes.mae_locales_cliente (empresa_id, cliente_id);
        CREATE INDEX ix_locales_cliente_empresa_ciudad
            ON clientes.mae_locales_cliente (empresa_id, ciudad_id);

        ALTER TABLE clientes.mae_locales_cliente ENABLE ROW LEVEL SECURITY;
        CREATE POLICY rls_locales_cliente_empresa
            ON clientes.mae_locales_cliente
            USING (empresa_id = current_setting('app.current_empresa_id', true)::BIGINT)
            WITH CHECK (empresa_id = current_setting('app.current_empresa_id', true)::BIGINT);
        """
    )

    op.execute(
        """
        CREATE OR REPLACE FUNCTION clientes.fn_validar_cliente_de_local()
        RETURNS trigger
        LANGUAGE plpgsql
        AS $$
        DECLARE
            v_activo BOOLEAN;
            v_deleted_at TIMESTAMPTZ;
        BEGIN
            IF TG_OP = 'UPDATE' THEN
                IF NEW.empresa_id IS NOT DISTINCT FROM OLD.empresa_id
                   AND NEW.cliente_id IS NOT DISTINCT FROM OLD.cliente_id
                   AND NOT (NEW.is_activo AND NOT OLD.is_activo)
                   AND NOT (NEW.deleted_at IS NULL AND OLD.deleted_at IS NOT NULL) THEN
                    RETURN NEW;
                END IF;
            END IF;

            SELECT c.is_activo, c.deleted_at
              INTO v_activo, v_deleted_at
              FROM clientes.mae_clientes AS c
             WHERE c.empresa_id = NEW.empresa_id
               AND c.id = NEW.cliente_id
             FOR SHARE;

            IF NOT FOUND OR NOT v_activo OR v_deleted_at IS NOT NULL THEN
                RAISE EXCEPTION 'El local requiere un cliente activo y vigente';
            END IF;

            RETURN NEW;
        END;
        $$;

        CREATE TRIGGER trg_validar_cliente_de_local
        BEFORE INSERT OR UPDATE ON clientes.mae_locales_cliente
        FOR EACH ROW EXECUTE FUNCTION clientes.fn_validar_cliente_de_local();
        """
    )


def downgrade() -> None:
    op.execute(
        "DROP TRIGGER IF EXISTS trg_validar_cliente_de_local ON clientes.mae_locales_cliente;"
    )
    op.execute("DROP FUNCTION IF EXISTS clientes.fn_validar_cliente_de_local();")
    op.execute("DROP TABLE IF EXISTS clientes.mae_locales_cliente;")