erp-transportistas-database-v2/ddl/07-billing-transport-ddl.sql

-- =============================================================================
-- ERP TRANSPORTISTAS - Schema Billing Transport DDL
-- =============================================================================
-- Archivo: 07-billing-transport-ddl.sql
-- Version: 1.0.0
-- Fecha: 2026-01-25
-- Descripcion: Tarifas, facturacion, recargos especificos de transporte
-- =============================================================================

-- =============================================================================
-- TIPOS ENUMERADOS
-- =============================================================================

CREATE TYPE billing.tipo_tarifa AS ENUM (
  'POR_VIAJE',
  'POR_KM',
  'POR_TONELADA',
  'POR_M3',
  'POR_PALLET',
  'POR_HORA',
  'MIXTA'
);

CREATE TYPE billing.tipo_recargo AS ENUM (
  'FUEL_SURCHARGE',
  'DETENTION',
  'MANIOBRAS',
  'CUSTODIA',
  'ESCOLTA',
  'PERNOCTA',
  'ESTADIAS',
  'FALSO_FLETE',
  'SEGURO_ADICIONAL',
  'OTRO'
);

CREATE TYPE billing.estado_factura AS ENUM (
  'BORRADOR',
  'EMITIDA',
  'ENVIADA',
  'PAGADA',
  'PARCIAL',
  'VENCIDA',
  'CANCELADA'
);

-- =============================================================================
-- TABLA: lanes (Rutas origen-destino)
-- =============================================================================

CREATE TABLE billing.lanes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),

  -- Identificación
  codigo VARCHAR(50) NOT NULL,
  nombre VARCHAR(200) NOT NULL,

  -- Origen
  origen_ciudad VARCHAR(100) NOT NULL,
  origen_estado VARCHAR(100) NOT NULL,
  origen_codigo_postal VARCHAR(10),

  -- Destino
  destino_ciudad VARCHAR(100) NOT NULL,
  destino_estado VARCHAR(100) NOT NULL,
  destino_codigo_postal VARCHAR(10),

  -- Distancia
  distancia_km DECIMAL(10, 2),
  tiempo_estimado_horas DECIMAL(6, 2),

  -- Estado
  activo BOOLEAN DEFAULT TRUE,

  -- Auditoría
  created_at TIMESTAMPTZ DEFAULT NOW(),
  created_by_id UUID NOT NULL
);

CREATE INDEX idx_lane_tenant ON billing.lanes(tenant_id);
CREATE UNIQUE INDEX idx_lane_codigo ON billing.lanes(tenant_id, codigo);

-- =============================================================================
-- TABLA: tarifas
-- =============================================================================

CREATE TABLE billing.tarifas (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),

  -- Identificación
  codigo VARCHAR(50) NOT NULL,
  nombre VARCHAR(200) NOT NULL,
  descripcion TEXT,

  -- Cliente (opcional para tarifas generales)
  cliente_id UUID,

  -- Lane (opcional)
  lane_id UUID REFERENCES billing.lanes(id),

  -- Tipo de servicio
  modalidad_servicio transport.modalidad_servicio,
  tipo_equipo fleet.tipo_unidad,

  -- Tipo de tarifa
  tipo_tarifa billing.tipo_tarifa NOT NULL,

  -- Precios
  tarifa_base DECIMAL(15, 2) NOT NULL,
  tarifa_km DECIMAL(10, 4),
  tarifa_tonelada DECIMAL(10, 4),
  tarifa_m3 DECIMAL(10, 4),
  tarifa_pallet DECIMAL(10, 4),
  tarifa_hora DECIMAL(10, 4),

  -- Mínimos
  minimo_facturar DECIMAL(15, 2),
  peso_minimo_kg DECIMAL(10, 2),

  -- Moneda
  moneda VARCHAR(3) DEFAULT 'MXN',

  -- Vigencia
  fecha_inicio DATE NOT NULL,
  fecha_fin DATE,

  -- Estado
  activa BOOLEAN DEFAULT TRUE,

  -- Auditoría
  created_at TIMESTAMPTZ DEFAULT NOW(),
  created_by_id UUID NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_tarifa_tenant ON billing.tarifas(tenant_id);
CREATE INDEX idx_tarifa_cliente ON billing.tarifas(cliente_id);
CREATE INDEX idx_tarifa_lane ON billing.tarifas(lane_id);
CREATE INDEX idx_tarifa_activa ON billing.tarifas(tenant_id, activa, fecha_inicio);

-- =============================================================================
-- TABLA: recargos_catalogo
-- =============================================================================

CREATE TABLE billing.recargos_catalogo (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),

  -- Identificación
  codigo VARCHAR(50) NOT NULL,
  nombre VARCHAR(200) NOT NULL,
  tipo billing.tipo_recargo NOT NULL,
  descripcion TEXT,

  -- Monto
  es_porcentaje BOOLEAN DEFAULT FALSE,
  monto DECIMAL(15, 4) NOT NULL, -- Si es_porcentaje=true, es porcentaje; sino monto fijo
  moneda VARCHAR(3) DEFAULT 'MXN',

  -- Aplicación
  aplica_automatico BOOLEAN DEFAULT FALSE,
  condicion_aplicacion TEXT, -- Descripción de cuándo aplica

  -- Estado
  activo BOOLEAN DEFAULT TRUE,

  -- Auditoría
  created_at TIMESTAMPTZ DEFAULT NOW(),
  created_by_id UUID NOT NULL
);

CREATE INDEX idx_recargo_tenant ON billing.recargos_catalogo(tenant_id);
CREATE UNIQUE INDEX idx_recargo_codigo ON billing.recargos_catalogo(tenant_id, codigo);

-- =============================================================================
-- TABLA: facturas_transporte
-- =============================================================================

CREATE TABLE billing.facturas_transporte (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),

  -- Identificación
  serie VARCHAR(10),
  folio VARCHAR(20) NOT NULL,
  uuid_cfdi UUID, -- UUID del CFDI timbrado

  -- Cliente
  cliente_id UUID NOT NULL,
  cliente_rfc VARCHAR(13) NOT NULL,
  cliente_razon_social VARCHAR(200) NOT NULL,
  cliente_uso_cfdi VARCHAR(10),

  -- Fechas
  fecha_emision TIMESTAMPTZ NOT NULL,
  fecha_vencimiento DATE,

  -- Totales
  subtotal DECIMAL(15, 2) NOT NULL,
  descuento DECIMAL(15, 2) DEFAULT 0,
  iva DECIMAL(15, 2) DEFAULT 0,
  retencion_iva DECIMAL(15, 2) DEFAULT 0,
  retencion_isr DECIMAL(15, 2) DEFAULT 0,
  total DECIMAL(15, 2) NOT NULL,
  moneda VARCHAR(3) DEFAULT 'MXN',
  tipo_cambio DECIMAL(10, 4) DEFAULT 1,

  -- Pago
  forma_pago VARCHAR(10),
  metodo_pago VARCHAR(10),
  condiciones_pago VARCHAR(200),

  -- Relacionados
  viaje_ids UUID[],
  ot_ids UUID[],

  -- CFDI
  xml_cfdi TEXT,
  pdf_url TEXT,

  -- Estado
  estado billing.estado_factura DEFAULT 'BORRADOR',

  -- Pago
  monto_pagado DECIMAL(15, 2) DEFAULT 0,
  fecha_pago TIMESTAMPTZ,

  -- Cancelación
  fecha_cancelacion TIMESTAMPTZ,
  motivo_cancelacion TEXT,

  -- Auditoría
  created_at TIMESTAMPTZ DEFAULT NOW(),
  created_by_id UUID NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_factura_tenant ON billing.facturas_transporte(tenant_id);
CREATE INDEX idx_factura_cliente ON billing.facturas_transporte(cliente_id);
CREATE INDEX idx_factura_estado ON billing.facturas_transporte(tenant_id, estado);
CREATE INDEX idx_factura_fecha ON billing.facturas_transporte(fecha_emision);
CREATE UNIQUE INDEX idx_factura_folio ON billing.facturas_transporte(tenant_id, serie, folio);

-- =============================================================================
-- TABLA: lineas_factura
-- =============================================================================

CREATE TABLE billing.lineas_factura (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),
  factura_id UUID NOT NULL REFERENCES billing.facturas_transporte(id),

  -- Secuencia
  linea INT NOT NULL,

  -- Concepto
  descripcion TEXT NOT NULL,
  clave_producto_sat VARCHAR(10), -- Catálogo SAT
  unidad_sat VARCHAR(10),

  -- Cantidades
  cantidad DECIMAL(12, 4) NOT NULL,
  precio_unitario DECIMAL(15, 4) NOT NULL,
  descuento DECIMAL(15, 2) DEFAULT 0,
  importe DECIMAL(15, 2) NOT NULL,

  -- Impuestos
  iva_tasa DECIMAL(5, 2) DEFAULT 16,
  iva_monto DECIMAL(15, 2),

  -- Referencia
  viaje_id UUID,
  ot_id UUID,
  recargo_id UUID REFERENCES billing.recargos_catalogo(id)
);

CREATE INDEX idx_linea_factura ON billing.lineas_factura(factura_id);

-- =============================================================================
-- TABLA: fuel_surcharge (Índice de combustible)
-- =============================================================================

CREATE TABLE billing.fuel_surcharge (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),

  -- Período
  fecha_inicio DATE NOT NULL,
  fecha_fin DATE NOT NULL,

  -- Precios de referencia
  precio_diesel_referencia DECIMAL(10, 4), -- Precio base
  precio_diesel_actual DECIMAL(10, 4),

  -- Surcharge
  porcentaje_surcharge DECIMAL(5, 2) NOT NULL,

  -- Estado
  activo BOOLEAN DEFAULT TRUE,

  -- Auditoría
  created_at TIMESTAMPTZ DEFAULT NOW(),
  created_by_id UUID NOT NULL
);

CREATE INDEX idx_fuel_surcharge_fecha ON billing.fuel_surcharge(tenant_id, fecha_inicio, fecha_fin);

-- =============================================================================
-- RLS POLICIES
-- =============================================================================

ALTER TABLE billing.lanes ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.tarifas ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.recargos_catalogo ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.facturas_transporte ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.lineas_factura ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.fuel_surcharge ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_lanes ON billing.lanes
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_tarifas ON billing.tarifas
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_recargos ON billing.recargos_catalogo
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_facturas ON billing.facturas_transporte
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_lineas ON billing.lineas_factura
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_fuel ON billing.fuel_surcharge
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- =============================================================================
-- COMENTARIOS
-- =============================================================================

COMMENT ON TABLE billing.lanes IS 'Rutas origen-destino para tarifas';
COMMENT ON TABLE billing.tarifas IS 'Catálogo de tarifas de transporte';
COMMENT ON TABLE billing.recargos_catalogo IS 'Catálogo de recargos aplicables';
COMMENT ON TABLE billing.facturas_transporte IS 'Facturas emitidas a clientes';
COMMENT ON TABLE billing.lineas_factura IS 'Detalle de líneas de factura';
COMMENT ON TABLE billing.fuel_surcharge IS 'Índices de fuel surcharge por período';

-- =============================================================================
-- FIN DDL BILLING
-- =============================================================================