erp-transportistas-database-v2/ddl/02-fleet-schema-ddl.sql

-- =============================================================================
-- ERP TRANSPORTISTAS - Schema Fleet DDL
-- =============================================================================
-- Archivo: 02-fleet-schema-ddl.sql
-- Version: 1.0.0
-- Fecha: 2026-01-25
-- Descripcion: Unidades, remolques, operadores, documentos, licencias
-- =============================================================================

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

CREATE TYPE fleet.tipo_licencia AS ENUM (
  'A',      -- Motociclista
  'B',      -- Automovilista particular
  'C',      -- Chofer particular
  'D',      -- Chofer público pasajeros
  'E',      -- Chofer público carga
  'F'       -- Federal (SCT)
);

CREATE TYPE fleet.estado_operador AS ENUM (
  'ACTIVO',
  'EN_VIAJE',
  'DESCANSO',
  'VACACIONES',
  'INCAPACIDAD',
  'SUSPENDIDO',
  'BAJA'
);

CREATE TYPE fleet.tipo_documento AS ENUM (
  'LICENCIA',
  'INE',
  'CURP',
  'RFC',
  'NSS',
  'TARJETA_CIRCULACION',
  'POLIZA_SEGURO',
  'VERIFICACION',
  'PERMISO_SCT',
  'CERTIFICADO_FISICO',
  'ANTIDOPING',
  'OTRO'
);

-- =============================================================================
-- TABLA: unidades (Tractoras y vehículos)
-- =============================================================================

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

  -- Identificación
  numero_economico VARCHAR(20) NOT NULL,
  tipo fleet.tipo_unidad NOT NULL,

  -- Vehículo
  marca VARCHAR(50),
  modelo VARCHAR(50),
  anio INT,
  color VARCHAR(30),
  numero_serie VARCHAR(50),
  numero_motor VARCHAR(50),

  -- Placas
  placa VARCHAR(15),
  placa_estado VARCHAR(50),

  -- SCT
  permiso_sct VARCHAR(50),
  tipo_permiso_sct VARCHAR(10),
  configuracion_vehicular VARCHAR(10), -- C2, C3, T3S2, etc.

  -- Capacidades
  capacidad_peso_kg DECIMAL(10, 2),
  capacidad_volumen_m3 DECIMAL(10, 4),
  capacidad_pallets INT,

  -- Combustible
  tipo_combustible VARCHAR(20), -- DIESEL, GASOLINA, GAS
  rendimiento_km_litro DECIMAL(6, 2),
  capacidad_tanque_litros DECIMAL(8, 2),

  -- Odómetro
  odometro_actual INT DEFAULT 0,
  odometro_ultimo_servicio INT,

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

  -- Estado
  estado fleet.estado_unidad DEFAULT 'DISPONIBLE',
  ubicacion_actual_lat DECIMAL(10, 7),
  ubicacion_actual_lng DECIMAL(10, 7),
  ultima_actualizacion_ubicacion TIMESTAMPTZ,

  -- Propiedad
  es_propia BOOLEAN DEFAULT TRUE,
  propietario_id UUID, -- Si no es propia, referencia al carrier

  -- Costos
  costo_adquisicion DECIMAL(15, 2),
  fecha_adquisicion DATE,
  valor_actual DECIMAL(15, 2),

  -- Fechas importantes
  fecha_verificacion_proxima DATE,
  fecha_poliza_vencimiento DATE,
  fecha_permiso_vencimiento DATE,

  -- Activo
  activo BOOLEAN DEFAULT TRUE,
  fecha_baja DATE,
  motivo_baja TEXT,

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

  CONSTRAINT uq_unidad_numero UNIQUE (tenant_id, numero_economico),
  CONSTRAINT uq_unidad_placa UNIQUE (tenant_id, placa)
);

CREATE INDEX idx_unidad_tenant ON fleet.unidades(tenant_id);
CREATE INDEX idx_unidad_tipo ON fleet.unidades(tenant_id, tipo);
CREATE INDEX idx_unidad_estado ON fleet.unidades(tenant_id, estado);
CREATE INDEX idx_unidad_activo ON fleet.unidades(tenant_id, activo) WHERE activo = TRUE;

-- =============================================================================
-- TABLA: remolques
-- =============================================================================

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

  -- Identificación
  numero_economico VARCHAR(20) NOT NULL,
  tipo fleet.tipo_unidad NOT NULL, -- CAJA_SECA, PLATAFORMA, etc.

  -- Vehículo
  marca VARCHAR(50),
  modelo VARCHAR(50),
  anio INT,
  numero_serie VARCHAR(50),

  -- Placas
  placa VARCHAR(15),
  placa_estado VARCHAR(50),

  -- Dimensiones
  largo_metros DECIMAL(6, 2),
  ancho_metros DECIMAL(6, 2),
  alto_metros DECIMAL(6, 2),

  -- Capacidades
  capacidad_peso_kg DECIMAL(10, 2),
  capacidad_volumen_m3 DECIMAL(10, 4),
  capacidad_pallets INT,

  -- Refrigeración (si aplica)
  es_refrigerado BOOLEAN DEFAULT FALSE,
  marca_refrigeracion VARCHAR(50),
  modelo_refrigeracion VARCHAR(50),
  temperatura_min DECIMAL(5, 2),
  temperatura_max DECIMAL(5, 2),

  -- Estado
  estado fleet.estado_unidad DEFAULT 'DISPONIBLE',

  -- Propiedad
  es_propia BOOLEAN DEFAULT TRUE,
  propietario_id UUID,

  -- Fechas importantes
  fecha_verificacion_proxima DATE,
  fecha_poliza_vencimiento DATE,

  -- Activo
  activo BOOLEAN DEFAULT TRUE,

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

  CONSTRAINT uq_remolque_numero UNIQUE (tenant_id, numero_economico)
);

CREATE INDEX idx_remolque_tenant ON fleet.remolques(tenant_id);
CREATE INDEX idx_remolque_tipo ON fleet.remolques(tenant_id, tipo);
CREATE INDEX idx_remolque_estado ON fleet.remolques(tenant_id, estado);

-- =============================================================================
-- TABLA: operadores (Conductores)
-- =============================================================================

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

  -- Identificación
  numero_empleado VARCHAR(20) NOT NULL,
  nombre VARCHAR(100) NOT NULL,
  apellido_paterno VARCHAR(100) NOT NULL,
  apellido_materno VARCHAR(100),
  nombre_completo VARCHAR(300) GENERATED ALWAYS AS (
    nombre || ' ' || apellido_paterno || COALESCE(' ' || apellido_materno, '')
  ) STORED,

  -- Documentos de identidad
  curp VARCHAR(18),
  rfc VARCHAR(13),
  nss VARCHAR(15), -- Número Seguro Social

  -- Contacto
  telefono VARCHAR(30),
  telefono_emergencia VARCHAR(30),
  email VARCHAR(255),

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

  -- Datos de nacimiento
  fecha_nacimiento DATE,
  lugar_nacimiento VARCHAR(100),
  nacionalidad VARCHAR(50) DEFAULT 'Mexicana',

  -- Licencia de conducir
  tipo_licencia fleet.tipo_licencia,
  numero_licencia VARCHAR(30),
  licencia_vigencia DATE,
  licencia_estado_expedicion VARCHAR(50),

  -- Certificaciones
  certificado_fisico_vigencia DATE,
  antidoping_vigencia DATE,
  capacitacion_materiales_peligrosos BOOLEAN DEFAULT FALSE,
  capacitacion_mp_vigencia DATE,

  -- Estado
  estado fleet.estado_operador DEFAULT 'ACTIVO',

  -- Unidad asignada (default)
  unidad_asignada_id UUID REFERENCES fleet.unidades(id),

  -- Métricas de desempeño
  calificacion DECIMAL(3, 2) DEFAULT 5.00,
  total_viajes INT DEFAULT 0,
  total_km INT DEFAULT 0,
  incidentes INT DEFAULT 0,

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

  -- Salario
  salario_base DECIMAL(12, 2),
  tipo_pago VARCHAR(20), -- 'FIJO', 'POR_VIAJE', 'MIXTO'

  -- Fechas
  fecha_ingreso DATE,
  fecha_baja DATE,
  motivo_baja TEXT,

  -- Activo
  activo BOOLEAN DEFAULT TRUE,

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

  CONSTRAINT uq_operador_numero UNIQUE (tenant_id, numero_empleado),
  CONSTRAINT uq_operador_curp UNIQUE (tenant_id, curp)
);

CREATE INDEX idx_operador_tenant ON fleet.operadores(tenant_id);
CREATE INDEX idx_operador_estado ON fleet.operadores(tenant_id, estado);
CREATE INDEX idx_operador_activo ON fleet.operadores(tenant_id, activo) WHERE activo = TRUE;
CREATE INDEX idx_operador_licencia ON fleet.operadores(licencia_vigencia);

-- =============================================================================
-- TABLA: documentos_flota
-- =============================================================================

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

  -- Referencia polimórfica
  entidad_tipo VARCHAR(20) NOT NULL, -- 'UNIDAD', 'REMOLQUE', 'OPERADOR'
  entidad_id UUID NOT NULL,

  -- Documento
  tipo_documento fleet.tipo_documento NOT NULL,
  nombre VARCHAR(200) NOT NULL,
  numero_documento VARCHAR(100),
  descripcion TEXT,

  -- Vigencia
  fecha_emision DATE,
  fecha_vencimiento DATE,
  dias_alerta_vencimiento INT DEFAULT 30,

  -- Archivo
  archivo_url TEXT,
  archivo_nombre VARCHAR(255),
  archivo_tipo VARCHAR(50),
  archivo_tamano_bytes BIGINT,

  -- Estado
  verificado BOOLEAN DEFAULT FALSE,
  verificado_por UUID,
  verificado_fecha TIMESTAMPTZ,

  -- Activo
  activo BOOLEAN DEFAULT TRUE,

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

CREATE INDEX idx_documento_entidad ON fleet.documentos_flota(entidad_tipo, entidad_id);
CREATE INDEX idx_documento_vencimiento ON fleet.documentos_flota(fecha_vencimiento) WHERE activo = TRUE;
CREATE INDEX idx_documento_tipo ON fleet.documentos_flota(tenant_id, tipo_documento);

-- =============================================================================
-- TABLA: asignaciones_unidad_operador
-- =============================================================================

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

  unidad_id UUID NOT NULL REFERENCES fleet.unidades(id),
  operador_id UUID NOT NULL REFERENCES fleet.operadores(id),
  remolque_id UUID REFERENCES fleet.remolques(id),

  -- Vigencia de asignación
  fecha_inicio TIMESTAMPTZ NOT NULL,
  fecha_fin TIMESTAMPTZ,

  -- Activa
  activa BOOLEAN DEFAULT TRUE,

  -- Motivo
  motivo VARCHAR(200),

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

CREATE INDEX idx_asignacion_unidad ON fleet.asignaciones(unidad_id, activa);
CREATE INDEX idx_asignacion_operador ON fleet.asignaciones(operador_id, activa);

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

ALTER TABLE fleet.unidades ENABLE ROW LEVEL SECURITY;
ALTER TABLE fleet.remolques ENABLE ROW LEVEL SECURITY;
ALTER TABLE fleet.operadores ENABLE ROW LEVEL SECURITY;
ALTER TABLE fleet.documentos_flota ENABLE ROW LEVEL SECURITY;
ALTER TABLE fleet.asignaciones ENABLE ROW LEVEL SECURITY;

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

CREATE POLICY tenant_isolation_remolques ON fleet.remolques
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

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

CREATE POLICY tenant_isolation_documentos ON fleet.documentos_flota
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

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

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

COMMENT ON TABLE fleet.unidades IS 'Unidades motrices (tractoras, camiones, camionetas)';
COMMENT ON TABLE fleet.remolques IS 'Remolques, cajas, plataformas, tanques';
COMMENT ON TABLE fleet.operadores IS 'Operadores/conductores de la flota';
COMMENT ON TABLE fleet.documentos_flota IS 'Documentos de unidades, remolques y operadores';
COMMENT ON TABLE fleet.asignaciones IS 'Historial de asignaciones unidad-operador';

-- =============================================================================
-- FIN DDL FLEET
-- =============================================================================