erp-transportistas-database-v2/ddl/06-carriers-schema-ddl.sql

-- =============================================================================
-- ERP TRANSPORTISTAS - Schema Carriers DDL
-- =============================================================================
-- Archivo: 06-carriers-schema-ddl.sql
-- Version: 1.0.0
-- Fecha: 2026-01-25
-- Descripcion: Transportistas subcontratados, documentos, scorecard
-- =============================================================================

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

CREATE TYPE carriers.estado_carrier AS ENUM (
  'PROSPECTO',
  'EN_VALIDACION',
  'ACTIVO',
  'SUSPENDIDO',
  'BAJA'
);

CREATE TYPE carriers.tipo_contrato AS ENUM (
  'SPOT',
  'DEDICADO',
  'PREFERENTE'
);

-- =============================================================================
-- TABLA: carriers (Transportistas terceros)
-- =============================================================================

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

  -- Identificación
  codigo VARCHAR(20) NOT NULL,
  razon_social VARCHAR(200) NOT NULL,
  nombre_comercial VARCHAR(200),

  -- Fiscal
  rfc VARCHAR(13) NOT NULL,
  regimen_fiscal VARCHAR(100),

  -- SCT
  permiso_sct VARCHAR(50),
  tipo_permiso_sct VARCHAR(10),
  permiso_vigencia DATE,

  -- Contacto
  contacto_nombre VARCHAR(200),
  contacto_telefono VARCHAR(30),
  contacto_email VARCHAR(255),

  -- Dirección
  direccion TEXT,
  codigo_postal VARCHAR(10),
  ciudad VARCHAR(100),
  estado VARCHAR(100),

  -- Operación
  cobertura_estados TEXT[], -- Estados donde opera
  tipos_equipo fleet.tipo_unidad[], -- Tipos de unidad disponibles
  capacidad_unidades INT,

  -- Tipo de relación
  tipo_contrato carriers.tipo_contrato DEFAULT 'SPOT',

  -- Términos comerciales
  dias_pago INT DEFAULT 30,
  porcentaje_retencion DECIMAL(5, 2) DEFAULT 4.00,

  -- Seguros
  poliza_seguro_carga VARCHAR(100),
  poliza_vigencia DATE,
  suma_asegurada DECIMAL(15, 2),

  -- Evaluación
  calificacion DECIMAL(3, 2) DEFAULT 0,
  total_viajes INT DEFAULT 0,
  viajes_a_tiempo INT DEFAULT 0,
  incidentes INT DEFAULT 0,

  -- Estado
  estado carriers.estado_carrier DEFAULT 'PROSPECTO',
  fecha_alta DATE,
  fecha_baja DATE,
  motivo_baja TEXT,

  -- Datos bancarios
  banco VARCHAR(100),
  cuenta_bancaria VARCHAR(30),
  clabe VARCHAR(18),

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

  CONSTRAINT uq_carrier_codigo UNIQUE (tenant_id, codigo),
  CONSTRAINT uq_carrier_rfc UNIQUE (tenant_id, rfc)
);

CREATE INDEX idx_carrier_tenant ON carriers.carriers(tenant_id);
CREATE INDEX idx_carrier_estado ON carriers.carriers(tenant_id, estado);

-- =============================================================================
-- TABLA: documentos_carrier
-- =============================================================================

CREATE TABLE carriers.documentos_carrier (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),
  carrier_id UUID NOT NULL REFERENCES carriers.carriers(id),

  -- Documento
  tipo_documento VARCHAR(50) NOT NULL,
  nombre VARCHAR(200) NOT NULL,
  descripcion TEXT,

  -- Vigencia
  fecha_emision DATE,
  fecha_vencimiento DATE,

  -- Archivo
  archivo_url TEXT,
  archivo_nombre VARCHAR(255),

  -- Verificación
  verificado BOOLEAN DEFAULT FALSE,
  verificado_por UUID,
  verificado_fecha TIMESTAMPTZ,

  -- Estado
  activo BOOLEAN DEFAULT TRUE,

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

CREATE INDEX idx_doc_carrier ON carriers.documentos_carrier(carrier_id);
CREATE INDEX idx_doc_vencimiento ON carriers.documentos_carrier(fecha_vencimiento);

-- =============================================================================
-- TABLA: unidades_carrier
-- =============================================================================

CREATE TABLE carriers.unidades_carrier (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),
  carrier_id UUID NOT NULL REFERENCES carriers.carriers(id),

  -- Unidad
  numero_economico VARCHAR(20) NOT NULL,
  tipo fleet.tipo_unidad NOT NULL,
  marca VARCHAR(50),
  modelo VARCHAR(50),
  anio INT,
  placa VARCHAR(15),

  -- SCT
  configuracion_vehicular VARCHAR(10),

  -- Capacidad
  capacidad_peso_kg DECIMAL(10, 2),
  capacidad_volumen_m3 DECIMAL(10, 4),

  -- GPS
  tiene_gps BOOLEAN DEFAULT FALSE,
  gps_proveedor VARCHAR(50),

  -- Estado
  activa BOOLEAN DEFAULT TRUE,

  -- Auditoría
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_unidad_carrier ON carriers.unidades_carrier(carrier_id);

-- =============================================================================
-- TABLA: operadores_carrier
-- =============================================================================

CREATE TABLE carriers.operadores_carrier (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),
  carrier_id UUID NOT NULL REFERENCES carriers.carriers(id),

  -- Operador
  nombre_completo VARCHAR(300) NOT NULL,
  telefono VARCHAR(30),

  -- Licencia
  tipo_licencia fleet.tipo_licencia,
  numero_licencia VARCHAR(30),
  licencia_vigencia DATE,

  -- Estado
  activo BOOLEAN DEFAULT TRUE,

  -- Auditoría
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_operador_carrier ON carriers.operadores_carrier(carrier_id);

-- =============================================================================
-- TABLA: asignaciones_carrier (Viajes asignados a carriers)
-- =============================================================================

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

  -- Carrier
  carrier_id UUID NOT NULL REFERENCES carriers.carriers(id),
  unidad_carrier_id UUID REFERENCES carriers.unidades_carrier(id),
  operador_carrier_id UUID REFERENCES carriers.operadores_carrier(id),

  -- Viaje/OT
  viaje_id UUID,
  ot_id UUID,

  -- Tarifa acordada
  tarifa_acordada DECIMAL(15, 2) NOT NULL,
  moneda VARCHAR(3) DEFAULT 'MXN',

  -- Fechas
  fecha_asignacion TIMESTAMPTZ DEFAULT NOW(),
  fecha_confirmacion TIMESTAMPTZ,

  -- Estado
  estado VARCHAR(20) DEFAULT 'PENDIENTE',
  -- PENDIENTE, CONFIRMADA, EN_PROCESO, COMPLETADA, CANCELADA

  -- Facturación
  factura_carrier VARCHAR(50),
  fecha_factura DATE,
  monto_facturado DECIMAL(15, 2),
  fecha_pago DATE,

  -- Evaluación del viaje
  calificacion INT, -- 1-5
  comentarios TEXT,

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

CREATE INDEX idx_asignacion_carrier ON carriers.asignaciones_carrier(carrier_id);
CREATE INDEX idx_asignacion_viaje ON carriers.asignaciones_carrier(viaje_id);
CREATE INDEX idx_asignacion_estado ON carriers.asignaciones_carrier(tenant_id, estado);

-- =============================================================================
-- TABLA: scorecard_carrier
-- =============================================================================

CREATE TABLE carriers.scorecard (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES public.tenants(id),
  carrier_id UUID NOT NULL REFERENCES carriers.carriers(id),

  -- Período
  periodo_inicio DATE NOT NULL,
  periodo_fin DATE NOT NULL,

  -- Métricas de servicio
  total_viajes INT DEFAULT 0,
  viajes_a_tiempo INT DEFAULT 0,
  viajes_retrasados INT DEFAULT 0,
  viajes_cancelados INT DEFAULT 0,

  -- Porcentajes
  otif_porcentaje DECIMAL(5, 2), -- On Time In Full
  puntualidad_porcentaje DECIMAL(5, 2),

  -- Incidentes
  total_incidentes INT DEFAULT 0,
  incidentes_graves INT DEFAULT 0,

  -- Calificación
  calificacion_servicio DECIMAL(3, 2),
  calificacion_documentacion DECIMAL(3, 2),
  calificacion_comunicacion DECIMAL(3, 2),
  calificacion_general DECIMAL(3, 2),

  -- Financiero
  monto_total_servicios DECIMAL(15, 2),
  monto_penalizaciones DECIMAL(15, 2),

  -- Auditoría
  calculado_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_scorecard_carrier ON carriers.scorecard(carrier_id);
CREATE INDEX idx_scorecard_periodo ON carriers.scorecard(periodo_inicio, periodo_fin);

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

ALTER TABLE carriers.carriers ENABLE ROW LEVEL SECURITY;
ALTER TABLE carriers.documentos_carrier ENABLE ROW LEVEL SECURITY;
ALTER TABLE carriers.unidades_carrier ENABLE ROW LEVEL SECURITY;
ALTER TABLE carriers.operadores_carrier ENABLE ROW LEVEL SECURITY;
ALTER TABLE carriers.asignaciones_carrier ENABLE ROW LEVEL SECURITY;
ALTER TABLE carriers.scorecard ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_carriers ON carriers.carriers
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_docs ON carriers.documentos_carrier
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_unidades ON carriers.unidades_carrier
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_operadores ON carriers.operadores_carrier
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_asignaciones ON carriers.asignaciones_carrier
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_scorecard ON carriers.scorecard
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

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

COMMENT ON TABLE carriers.carriers IS 'Transportistas subcontratados';
COMMENT ON TABLE carriers.documentos_carrier IS 'Documentos de carriers';
COMMENT ON TABLE carriers.unidades_carrier IS 'Unidades de carriers';
COMMENT ON TABLE carriers.operadores_carrier IS 'Operadores de carriers';
COMMENT ON TABLE carriers.asignaciones_carrier IS 'Asignaciones de viajes a carriers';
COMMENT ON TABLE carriers.scorecard IS 'Evaluación periódica de carriers';

-- =============================================================================
-- FIN DDL CARRIERS
-- =============================================================================