erp-transportistas-database-v2/ddl/04-fuel-schema-ddl.sql

-- =============================================================================
-- ERP TRANSPORTISTAS - Schema Fuel DDL
-- =============================================================================
-- Archivo: 04-fuel-schema-ddl.sql
-- Version: 1.0.0
-- Fecha: 2026-01-25
-- Descripcion: Combustible, peajes, gastos de viaje, viaticos
-- =============================================================================

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

CREATE TYPE fuel.tipo_carga_combustible AS ENUM (
  'VALE',
  'TARJETA',
  'EFECTIVO',
  'FACTURA_DIRECTA'
);

CREATE TYPE fuel.tipo_gasto AS ENUM (
  'COMBUSTIBLE',
  'PEAJE',
  'VIATICO',
  'HOSPEDAJE',
  'ALIMENTOS',
  'ESTACIONAMIENTO',
  'MULTA',
  'MANIOBRA',
  'REPARACION_MENOR',
  'OTRO'
);

CREATE TYPE fuel.estado_gasto AS ENUM (
  'PENDIENTE',
  'APROBADO',
  'RECHAZADO',
  'PAGADO'
);

-- =============================================================================
-- TABLA: cargas_combustible
-- =============================================================================

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

  -- Unidad y viaje
  unidad_id UUID NOT NULL,
  viaje_id UUID,
  operador_id UUID NOT NULL,

  -- Carga
  tipo_carga fuel.tipo_carga_combustible NOT NULL,
  tipo_combustible VARCHAR(20) NOT NULL, -- DIESEL, GASOLINA, GAS
  litros DECIMAL(10, 3) NOT NULL,
  precio_litro DECIMAL(10, 4) NOT NULL,
  total DECIMAL(12, 2) NOT NULL,

  -- Odómetro
  odometro_carga INT,
  rendimiento_calculado DECIMAL(6, 2), -- km/litro desde última carga

  -- Ubicación
  estacion_id UUID,
  estacion_nombre VARCHAR(200),
  estacion_direccion TEXT,
  latitud DECIMAL(10, 7),
  longitud DECIMAL(10, 7),

  -- Vale/Factura
  numero_vale VARCHAR(50),
  numero_factura VARCHAR(50),
  folio_ticket VARCHAR(50),

  -- Fecha
  fecha_carga TIMESTAMPTZ NOT NULL,

  -- Aprobación
  estado fuel.estado_gasto DEFAULT 'PENDIENTE',
  aprobado_por UUID,
  aprobado_fecha TIMESTAMPTZ,

  -- Evidencia
  foto_ticket_url TEXT,

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

CREATE INDEX idx_carga_unidad ON fuel.cargas_combustible(unidad_id);
CREATE INDEX idx_carga_viaje ON fuel.cargas_combustible(viaje_id);
CREATE INDEX idx_carga_fecha ON fuel.cargas_combustible(tenant_id, fecha_carga);

-- =============================================================================
-- TABLA: cruces_peaje
-- =============================================================================

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

  -- Unidad y viaje
  unidad_id UUID NOT NULL,
  viaje_id UUID,
  operador_id UUID,

  -- Peaje
  caseta_nombre VARCHAR(200) NOT NULL,
  caseta_codigo VARCHAR(50),
  carretera VARCHAR(200),

  -- Monto
  monto DECIMAL(10, 2) NOT NULL,
  tipo_pago VARCHAR(20), -- EFECTIVO, TAG, PREPAGO

  -- TAG (si aplica)
  tag_numero VARCHAR(50),

  -- Ubicación
  latitud DECIMAL(10, 7),
  longitud DECIMAL(10, 7),

  -- Fecha
  fecha_cruce TIMESTAMPTZ NOT NULL,

  -- Comprobante
  numero_ticket VARCHAR(50),
  foto_ticket_url TEXT,

  -- Estado
  estado fuel.estado_gasto DEFAULT 'APROBADO',

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

CREATE INDEX idx_peaje_unidad ON fuel.cruces_peaje(unidad_id);
CREATE INDEX idx_peaje_viaje ON fuel.cruces_peaje(viaje_id);
CREATE INDEX idx_peaje_fecha ON fuel.cruces_peaje(tenant_id, fecha_cruce);

-- =============================================================================
-- TABLA: gastos_viaje
-- =============================================================================

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

  -- Viaje y operador
  viaje_id UUID NOT NULL,
  operador_id UUID NOT NULL,

  -- Gasto
  tipo_gasto fuel.tipo_gasto NOT NULL,
  descripcion VARCHAR(500) NOT NULL,
  monto DECIMAL(12, 2) NOT NULL,

  -- Comprobante
  tiene_factura BOOLEAN DEFAULT FALSE,
  numero_factura VARCHAR(50),
  numero_ticket VARCHAR(50),
  foto_comprobante_url TEXT,

  -- Ubicación
  lugar VARCHAR(200),
  latitud DECIMAL(10, 7),
  longitud DECIMAL(10, 7),

  -- Fecha
  fecha_gasto TIMESTAMPTZ NOT NULL,

  -- Estado
  estado fuel.estado_gasto DEFAULT 'PENDIENTE',
  aprobado_por UUID,
  aprobado_fecha TIMESTAMPTZ,
  motivo_rechazo TEXT,

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

CREATE INDEX idx_gasto_viaje ON fuel.gastos_viaje(viaje_id);
CREATE INDEX idx_gasto_operador ON fuel.gastos_viaje(operador_id);
CREATE INDEX idx_gasto_estado ON fuel.gastos_viaje(tenant_id, estado);

-- =============================================================================
-- TABLA: anticipos_viaticos
-- =============================================================================

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

  -- Viaje y operador
  viaje_id UUID NOT NULL,
  operador_id UUID NOT NULL,

  -- Anticipo
  monto_solicitado DECIMAL(12, 2) NOT NULL,
  monto_aprobado DECIMAL(12, 2),
  monto_comprobado DECIMAL(12, 2) DEFAULT 0,
  monto_reintegro DECIMAL(12, 2) DEFAULT 0,

  -- Conceptos desglosados
  combustible_estimado DECIMAL(12, 2),
  peajes_estimado DECIMAL(12, 2),
  viaticos_estimado DECIMAL(12, 2),

  -- Estado
  estado VARCHAR(20) DEFAULT 'SOLICITADO',
  -- SOLICITADO, APROBADO, ENTREGADO, COMPROBANDO, LIQUIDADO

  -- Fechas
  fecha_solicitud TIMESTAMPTZ DEFAULT NOW(),
  fecha_aprobacion TIMESTAMPTZ,
  fecha_entrega TIMESTAMPTZ,
  fecha_liquidacion TIMESTAMPTZ,

  -- Aprobaciones
  aprobado_por UUID,
  entregado_por UUID,
  liquidado_por UUID,

  -- Observaciones
  observaciones TEXT,

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

CREATE INDEX idx_anticipo_viaje ON fuel.anticipos_viaticos(viaje_id);
CREATE INDEX idx_anticipo_operador ON fuel.anticipos_viaticos(operador_id);
CREATE INDEX idx_anticipo_estado ON fuel.anticipos_viaticos(tenant_id, estado);

-- =============================================================================
-- TABLA: control_rendimiento
-- =============================================================================

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

  -- Unidad
  unidad_id UUID NOT NULL,

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

  -- Métricas
  km_recorridos INT NOT NULL,
  litros_consumidos DECIMAL(12, 3) NOT NULL,
  rendimiento_real DECIMAL(6, 2) NOT NULL,
  rendimiento_esperado DECIMAL(6, 2),
  variacion_porcentaje DECIMAL(5, 2),

  -- Costos
  costo_total_combustible DECIMAL(15, 2),
  costo_por_km DECIMAL(8, 4),

  -- Alertas
  tiene_anomalia BOOLEAN DEFAULT FALSE,
  tipo_anomalia VARCHAR(50),
  descripcion_anomalia TEXT,

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

CREATE INDEX idx_rendimiento_unidad ON fuel.control_rendimiento(unidad_id);
CREATE INDEX idx_rendimiento_fecha ON fuel.control_rendimiento(tenant_id, fecha_inicio);

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

ALTER TABLE fuel.cargas_combustible ENABLE ROW LEVEL SECURITY;
ALTER TABLE fuel.cruces_peaje ENABLE ROW LEVEL SECURITY;
ALTER TABLE fuel.gastos_viaje ENABLE ROW LEVEL SECURITY;
ALTER TABLE fuel.anticipos_viaticos ENABLE ROW LEVEL SECURITY;
ALTER TABLE fuel.control_rendimiento ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_cargas ON fuel.cargas_combustible
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_peajes ON fuel.cruces_peaje
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_gastos ON fuel.gastos_viaje
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_anticipos ON fuel.anticipos_viaticos
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_rendimiento ON fuel.control_rendimiento
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

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

COMMENT ON TABLE fuel.cargas_combustible IS 'Registro de cargas de combustible';
COMMENT ON TABLE fuel.cruces_peaje IS 'Cruces de casetas de peaje';
COMMENT ON TABLE fuel.gastos_viaje IS 'Gastos diversos durante el viaje';
COMMENT ON TABLE fuel.anticipos_viaticos IS 'Anticipos entregados a operadores';
COMMENT ON TABLE fuel.control_rendimiento IS 'Control de rendimiento de combustible por unidad';

-- =============================================================================
-- FIN DDL FUEL
-- =============================================================================