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;")