erp-core/database/ddl/05-inventory-extensions.sql

-- =====================================================
-- SCHEMA: inventory (Extensiones)
-- PROPÓSITO: Valoración de Inventario, Lotes/Series, Conteos Cíclicos
-- MÓDULO: MGN-005 (Inventario)
-- FECHA: 2025-12-08
-- VERSION: 1.0.0
-- DEPENDENCIAS: 05-inventory.sql
-- SPECS RELACIONADAS:
--   - SPEC-VALORACION-INVENTARIO.md
--   - SPEC-TRAZABILIDAD-LOTES-SERIES.md
--   - SPEC-INVENTARIOS-CICLICOS.md
-- =====================================================

-- =====================================================
-- PARTE 1: VALORACIÓN DE INVENTARIO (SVL)
-- =====================================================

-- Tabla: stock_valuation_layers (Capas de valoración FIFO/AVCO)
CREATE TABLE inventory.stock_valuation_layers (
  -- Identificación
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Referencias
  product_id UUID NOT NULL REFERENCES inventory.products(id),
  stock_move_id UUID REFERENCES inventory.stock_moves(id),
  lot_id UUID REFERENCES inventory.lots(id),
  company_id UUID NOT NULL REFERENCES auth.companies(id),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),

  -- Valores de la capa
  quantity DECIMAL(16,4) NOT NULL,           -- Cantidad (positiva=entrada, negativa=salida)
  unit_cost DECIMAL(16,6) NOT NULL,          -- Costo unitario
  value DECIMAL(16,4) NOT NULL,              -- Valor total
  currency_id UUID REFERENCES core.currencies(id),

  -- Tracking FIFO (solo para entradas)
  remaining_qty DECIMAL(16,4) NOT NULL DEFAULT 0,    -- Cantidad restante por consumir
  remaining_value DECIMAL(16,4) NOT NULL DEFAULT 0,  -- Valor restante

  -- Diferencia de precio (facturas vs recepción)
  price_diff_value DECIMAL(16,4) DEFAULT 0,

  -- Referencias contables (usando journal_entries del schema financial)
  journal_entry_id UUID REFERENCES financial.journal_entries(id),
  journal_entry_line_id UUID REFERENCES financial.journal_entry_lines(id),

  -- Corrección de vacío (link a capa corregida)
  parent_svl_id UUID REFERENCES inventory.stock_valuation_layers(id),

  -- Metadata
  description VARCHAR(500),
  reference VARCHAR(255),

  -- Auditoría
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by UUID REFERENCES auth.users(id),

  -- Constraints
  CONSTRAINT chk_svl_value CHECK (
    ABS(value - (quantity * unit_cost)) < 0.01 OR quantity = 0
  )
);

-- Índice principal para FIFO (crítico para performance)
CREATE INDEX idx_svl_fifo_candidates ON inventory.stock_valuation_layers (
  product_id,
  remaining_qty,
  stock_move_id,
  company_id,
  created_at
) WHERE remaining_qty > 0;

-- Índice para agregación de valoración
CREATE INDEX idx_svl_valuation ON inventory.stock_valuation_layers (
  product_id,
  company_id,
  id,
  value,
  quantity
);

-- Índice por lote
CREATE INDEX idx_svl_lot ON inventory.stock_valuation_layers (lot_id)
  WHERE lot_id IS NOT NULL;

-- Índice por movimiento
CREATE INDEX idx_svl_move ON inventory.stock_valuation_layers (stock_move_id);

-- Índice por tenant
CREATE INDEX idx_svl_tenant ON inventory.stock_valuation_layers (tenant_id);

-- Comentarios
COMMENT ON TABLE inventory.stock_valuation_layers IS 'Capas de valoración de inventario para costeo FIFO/AVCO';
COMMENT ON COLUMN inventory.stock_valuation_layers.remaining_qty IS 'Cantidad aún no consumida por FIFO';
COMMENT ON COLUMN inventory.stock_valuation_layers.parent_svl_id IS 'Referencia a capa padre cuando es corrección de vacío';

-- Vista materializada para valores agregados de SVL por producto
CREATE MATERIALIZED VIEW inventory.product_valuation_summary AS
SELECT
  svl.product_id,
  svl.company_id,
  svl.tenant_id,
  SUM(svl.quantity) AS quantity_svl,
  SUM(svl.value) AS value_svl,
  CASE
    WHEN SUM(svl.quantity) > 0 THEN SUM(svl.value) / SUM(svl.quantity)
    ELSE 0
  END AS avg_cost
FROM inventory.stock_valuation_layers svl
GROUP BY svl.product_id, svl.company_id, svl.tenant_id;

CREATE UNIQUE INDEX idx_product_valuation_pk
  ON inventory.product_valuation_summary (product_id, company_id, tenant_id);

COMMENT ON MATERIALIZED VIEW inventory.product_valuation_summary IS
  'Resumen de valoración por producto - refrescar con REFRESH MATERIALIZED VIEW CONCURRENTLY';

-- Configuración de cuentas por categoría de producto
CREATE TABLE inventory.category_stock_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  category_id UUID NOT NULL REFERENCES core.product_categories(id),
  company_id UUID NOT NULL REFERENCES auth.companies(id),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),

  -- Cuentas de valoración
  stock_input_account_id UUID REFERENCES financial.accounts(id),      -- Entrada de stock
  stock_output_account_id UUID REFERENCES financial.accounts(id),     -- Salida de stock
  stock_valuation_account_id UUID REFERENCES financial.accounts(id),  -- Valoración (activo)
  expense_account_id UUID REFERENCES financial.accounts(id),          -- Gasto/COGS

  -- Diario para asientos de stock
  stock_journal_id UUID REFERENCES financial.journals(id),

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

  CONSTRAINT uq_category_stock_accounts
    UNIQUE (category_id, company_id, tenant_id)
);

COMMENT ON TABLE inventory.category_stock_accounts IS 'Cuentas contables para valoración de inventario por categoría';

-- Parámetros de valoración por tenant
CREATE TABLE inventory.valuation_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  company_id UUID REFERENCES auth.companies(id),

  allow_negative_stock BOOLEAN NOT NULL DEFAULT FALSE,
  default_cost_method VARCHAR(20) NOT NULL DEFAULT 'fifo'
    CHECK (default_cost_method IN ('standard', 'average', 'fifo')),
  default_valuation VARCHAR(20) NOT NULL DEFAULT 'real_time'
    CHECK (default_valuation IN ('manual', 'real_time')),
  auto_vacuum_enabled BOOLEAN NOT NULL DEFAULT TRUE,
  vacuum_batch_size INTEGER NOT NULL DEFAULT 100,

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT uq_valuation_settings_tenant_company UNIQUE (tenant_id, company_id)
);

COMMENT ON TABLE inventory.valuation_settings IS 'Configuración de valoración de inventario por tenant/empresa';

-- Extensión de product_categories para costeo (tabla en schema core)
ALTER TABLE core.product_categories ADD COLUMN IF NOT EXISTS
  cost_method VARCHAR(20) NOT NULL DEFAULT 'fifo'
  CHECK (cost_method IN ('standard', 'average', 'fifo'));

ALTER TABLE core.product_categories ADD COLUMN IF NOT EXISTS
  valuation_method VARCHAR(20) NOT NULL DEFAULT 'real_time'
  CHECK (valuation_method IN ('manual', 'real_time'));

-- Extensión de products para costeo
ALTER TABLE inventory.products ADD COLUMN IF NOT EXISTS
  standard_price DECIMAL(16,6) NOT NULL DEFAULT 0;

ALTER TABLE inventory.products ADD COLUMN IF NOT EXISTS
  lot_valuated BOOLEAN NOT NULL DEFAULT FALSE;

-- =====================================================
-- PARTE 2: TRAZABILIDAD DE LOTES Y SERIES
-- =====================================================

-- Extensión de products para tracking
ALTER TABLE inventory.products ADD COLUMN IF NOT EXISTS
  tracking VARCHAR(16) NOT NULL DEFAULT 'none'
  CHECK (tracking IN ('none', 'lot', 'serial'));

-- Configuración de caducidad
ALTER TABLE inventory.products ADD COLUMN IF NOT EXISTS
  use_expiration_date BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE inventory.products ADD COLUMN IF NOT EXISTS
  expiration_time INTEGER;  -- Días hasta caducidad desde recepción

ALTER TABLE inventory.products ADD COLUMN IF NOT EXISTS
  use_time INTEGER;  -- Días antes de caducidad para "consumir preferentemente"

ALTER TABLE inventory.products ADD COLUMN IF NOT EXISTS
  removal_time INTEGER;  -- Días antes de caducidad para remover de venta

ALTER TABLE inventory.products ADD COLUMN IF NOT EXISTS
  alert_time INTEGER;  -- Días antes de caducidad para alertar

-- Propiedades dinámicas por lote
ALTER TABLE inventory.products ADD COLUMN IF NOT EXISTS
  lot_properties_definition JSONB DEFAULT '[]';

-- Constraint de consistencia
ALTER TABLE inventory.products ADD CONSTRAINT chk_expiration_config CHECK (
  use_expiration_date = FALSE OR (
    expiration_time IS NOT NULL AND
    expiration_time > 0
  )
);

-- Índice para productos con tracking
CREATE INDEX idx_products_tracking ON inventory.products(tracking)
  WHERE tracking != 'none';

-- Tabla: lots (Lotes y números de serie)
CREATE TABLE inventory.lots (
  -- Identificación
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(128) NOT NULL,
  ref VARCHAR(256),  -- Referencia interna/externa

  -- Relaciones
  product_id UUID NOT NULL REFERENCES inventory.products(id),
  company_id UUID NOT NULL REFERENCES auth.companies(id),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),

  -- Fechas de caducidad
  expiration_date TIMESTAMPTZ,
  use_date TIMESTAMPTZ,      -- Best-before
  removal_date TIMESTAMPTZ,  -- Fecha de retiro FEFO
  alert_date TIMESTAMPTZ,    -- Fecha de alerta

  -- Control de alertas
  expiry_alerted BOOLEAN NOT NULL DEFAULT FALSE,

  -- Propiedades dinámicas (heredadas del producto)
  lot_properties JSONB DEFAULT '{}',

  -- Ubicación (si solo hay una)
  location_id UUID REFERENCES inventory.locations(id),

  -- Auditoría
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by UUID REFERENCES auth.users(id),

  -- Constraints
  CONSTRAINT uk_lot_product_company UNIQUE (product_id, name, company_id)
);

-- Índices para lots
CREATE INDEX idx_lots_product ON inventory.lots(product_id);
CREATE INDEX idx_lots_tenant ON inventory.lots(tenant_id);
CREATE INDEX idx_lots_expiration ON inventory.lots(expiration_date)
  WHERE expiration_date IS NOT NULL;
CREATE INDEX idx_lots_removal ON inventory.lots(removal_date)
  WHERE removal_date IS NOT NULL;
CREATE INDEX idx_lots_alert ON inventory.lots(alert_date)
  WHERE alert_date IS NOT NULL AND NOT expiry_alerted;

-- Extensión para búsqueda por trigram (requiere pg_trgm)
-- CREATE INDEX idx_lots_name_trgm ON inventory.lots USING GIN (name gin_trgm_ops);

COMMENT ON TABLE inventory.lots IS 'Lotes y números de serie para trazabilidad de productos';

-- Extensión de quants para lotes
ALTER TABLE inventory.quants ADD COLUMN IF NOT EXISTS
  lot_id UUID REFERENCES inventory.lots(id);

ALTER TABLE inventory.quants ADD COLUMN IF NOT EXISTS
  in_date TIMESTAMPTZ NOT NULL DEFAULT NOW();

-- Fecha de remoción para FEFO (heredada del lote)
ALTER TABLE inventory.quants ADD COLUMN IF NOT EXISTS
  removal_date TIMESTAMPTZ;

-- Índices optimizados para quants
CREATE INDEX idx_quants_lot ON inventory.quants(lot_id)
  WHERE lot_id IS NOT NULL;

CREATE INDEX idx_quants_fefo ON inventory.quants(product_id, location_id, removal_date, in_date)
  WHERE quantity > 0;

CREATE INDEX idx_quants_fifo ON inventory.quants(product_id, location_id, in_date)
  WHERE quantity > 0;

-- Extensión de stock_moves para lotes (tracking de lotes en movimientos)
ALTER TABLE inventory.stock_moves ADD COLUMN IF NOT EXISTS
  lot_id UUID REFERENCES inventory.lots(id);

ALTER TABLE inventory.stock_moves ADD COLUMN IF NOT EXISTS
  lot_name VARCHAR(128);  -- Para creación on-the-fly

ALTER TABLE inventory.stock_moves ADD COLUMN IF NOT EXISTS
  tracking VARCHAR(16);  -- Copia del producto (none, lot, serial)

-- Índices para lotes en movimientos
CREATE INDEX IF NOT EXISTS idx_stock_moves_lot ON inventory.stock_moves(lot_id)
  WHERE lot_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_stock_moves_lot_name ON inventory.stock_moves(lot_name)
  WHERE lot_name IS NOT NULL;

-- Tabla de relación para trazabilidad de manufactura (consume/produce)
CREATE TABLE inventory.stock_move_consume_rel (
  consume_move_id UUID NOT NULL REFERENCES inventory.stock_moves(id) ON DELETE CASCADE,
  produce_move_id UUID NOT NULL REFERENCES inventory.stock_moves(id) ON DELETE CASCADE,
  quantity DECIMAL(16,4) NOT NULL DEFAULT 0,  -- Cantidad consumida/producida
  PRIMARY KEY (consume_move_id, produce_move_id)
);

CREATE INDEX idx_consume_rel_consume ON inventory.stock_move_consume_rel(consume_move_id);
CREATE INDEX idx_consume_rel_produce ON inventory.stock_move_consume_rel(produce_move_id);

COMMENT ON TABLE inventory.stock_move_consume_rel IS 'Relación M:N para trazabilidad de consumo en manufactura';

-- Tabla: removal_strategies (Estrategias de salida)
CREATE TABLE inventory.removal_strategies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(64) NOT NULL,
  code VARCHAR(16) NOT NULL UNIQUE
    CHECK (code IN ('fifo', 'lifo', 'fefo', 'closest')),
  description TEXT,
  is_active BOOLEAN NOT NULL DEFAULT TRUE
);

-- Datos iniciales de estrategias
INSERT INTO inventory.removal_strategies (name, code, description) VALUES
  ('First In, First Out', 'fifo', 'El stock más antiguo sale primero'),
  ('Last In, First Out', 'lifo', 'El stock más reciente sale primero'),
  ('First Expiry, First Out', 'fefo', 'El stock que caduca primero sale primero'),
  ('Closest Location', 'closest', 'El stock de ubicación más cercana sale primero')
ON CONFLICT (code) DO NOTHING;

COMMENT ON TABLE inventory.removal_strategies IS 'Estrategias de salida de inventario (FIFO, LIFO, FEFO)';

-- Agregar estrategia a categorías y ubicaciones
ALTER TABLE core.product_categories ADD COLUMN IF NOT EXISTS
  removal_strategy_id UUID REFERENCES inventory.removal_strategies(id);

ALTER TABLE inventory.locations ADD COLUMN IF NOT EXISTS
  removal_strategy_id UUID REFERENCES inventory.removal_strategies(id);

-- =====================================================
-- PARTE 3: CONTEOS CÍCLICOS
-- =====================================================

-- Extensión de locations para conteo cíclico
ALTER TABLE inventory.locations ADD COLUMN IF NOT EXISTS
  cyclic_inventory_frequency INTEGER DEFAULT 0;

ALTER TABLE inventory.locations ADD COLUMN IF NOT EXISTS
  last_inventory_date DATE;

ALTER TABLE inventory.locations ADD COLUMN IF NOT EXISTS
  abc_classification VARCHAR(1) DEFAULT 'C'
  CHECK (abc_classification IN ('A', 'B', 'C'));

COMMENT ON COLUMN inventory.locations.cyclic_inventory_frequency IS
  'Días entre conteos cíclicos. 0 = deshabilitado';
COMMENT ON COLUMN inventory.locations.abc_classification IS
  'Clasificación ABC: A=Alta rotación, B=Media, C=Baja';

-- Índice para ubicaciones pendientes de conteo
CREATE INDEX idx_locations_cyclic_inventory
  ON inventory.locations(last_inventory_date, cyclic_inventory_frequency)
  WHERE cyclic_inventory_frequency > 0;

-- Extensión de quants para inventario
ALTER TABLE inventory.quants ADD COLUMN IF NOT EXISTS
  inventory_quantity DECIMAL(18,4);

ALTER TABLE inventory.quants ADD COLUMN IF NOT EXISTS
  inventory_quantity_set BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE inventory.quants ADD COLUMN IF NOT EXISTS
  inventory_date DATE;

ALTER TABLE inventory.quants ADD COLUMN IF NOT EXISTS
  last_count_date DATE;

ALTER TABLE inventory.quants ADD COLUMN IF NOT EXISTS
  is_outdated BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE inventory.quants ADD COLUMN IF NOT EXISTS
  assigned_user_id UUID REFERENCES auth.users(id);

ALTER TABLE inventory.quants ADD COLUMN IF NOT EXISTS
  count_notes TEXT;

COMMENT ON COLUMN inventory.quants.inventory_quantity IS
  'Cantidad contada por el usuario';
COMMENT ON COLUMN inventory.quants.is_outdated IS
  'TRUE si quantity cambió después de establecer inventory_quantity';

-- Índices para conteo
CREATE INDEX idx_quants_inventory_date ON inventory.quants(inventory_date)
  WHERE inventory_date IS NOT NULL;

CREATE INDEX idx_quants_assigned_user ON inventory.quants(assigned_user_id)
  WHERE assigned_user_id IS NOT NULL;

-- Tabla: inventory_count_sessions (Sesiones de conteo)
CREATE TABLE inventory.inventory_count_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code VARCHAR(20) NOT NULL,
  name VARCHAR(200),

  -- Alcance del conteo
  location_ids UUID[] NOT NULL,  -- Ubicaciones a contar
  product_ids UUID[],            -- NULL = todos los productos
  category_ids UUID[],           -- Filtrar por categorías

  -- Configuración
  count_type VARCHAR(20) NOT NULL DEFAULT 'cycle'
    CHECK (count_type IN ('cycle', 'full', 'spot')),
  -- 'cycle': Conteo cíclico programado
  -- 'full': Inventario físico completo
  -- 'spot': Conteo puntual/aleatorio

  -- Estado
  state VARCHAR(20) NOT NULL DEFAULT 'draft'
    CHECK (state IN ('draft', 'in_progress', 'pending_review', 'done', 'cancelled')),

  -- Fechas
  scheduled_date DATE,
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,

  -- Asignación
  responsible_id UUID REFERENCES auth.users(id),
  team_ids UUID[],  -- Usuarios asignados al conteo

  -- Resultados
  total_quants INTEGER DEFAULT 0,
  counted_quants INTEGER DEFAULT 0,
  discrepancy_quants INTEGER DEFAULT 0,
  total_value_diff DECIMAL(18,2) DEFAULT 0,

  -- Auditoría
  company_id UUID NOT NULL REFERENCES auth.companies(id),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  warehouse_id UUID REFERENCES inventory.warehouses(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by UUID NOT NULL REFERENCES auth.users(id),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Índices para sesiones
CREATE INDEX idx_count_sessions_state ON inventory.inventory_count_sessions(state);
CREATE INDEX idx_count_sessions_scheduled ON inventory.inventory_count_sessions(scheduled_date);
CREATE INDEX idx_count_sessions_tenant ON inventory.inventory_count_sessions(tenant_id);

-- Secuencia para código de sesión
CREATE SEQUENCE IF NOT EXISTS inventory.inventory_count_seq START 1;

COMMENT ON TABLE inventory.inventory_count_sessions IS 'Sesiones de conteo cíclico de inventario';

-- Tabla: inventory_count_lines (Líneas de conteo detalladas)
CREATE TABLE inventory.inventory_count_lines (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id UUID NOT NULL REFERENCES inventory.inventory_count_sessions(id) ON DELETE CASCADE,
  quant_id UUID REFERENCES inventory.quants(id),

  -- Producto
  product_id UUID NOT NULL REFERENCES inventory.products(id),
  location_id UUID NOT NULL REFERENCES inventory.locations(id),
  lot_id UUID REFERENCES inventory.lots(id),
  -- package_id: Reservado para futura extensión de empaquetado
  -- package_id UUID REFERENCES inventory.packages(id),

  -- Cantidades
  theoretical_qty DECIMAL(18,4) NOT NULL DEFAULT 0,  -- Del sistema
  counted_qty DECIMAL(18,4),                         -- Contada

  -- Valoración
  unit_cost DECIMAL(18,6),

  -- Estado
  state VARCHAR(20) NOT NULL DEFAULT 'pending'
    CHECK (state IN ('pending', 'counted', 'conflict', 'applied')),

  -- Conteo
  counted_by UUID REFERENCES auth.users(id),
  counted_at TIMESTAMPTZ,
  notes TEXT,

  -- Resolución de conflictos
  conflict_reason VARCHAR(100),
  resolution VARCHAR(20)
    CHECK (resolution IS NULL OR resolution IN ('keep_counted', 'keep_system', 'recount')),
  resolved_by UUID REFERENCES auth.users(id),
  resolved_at TIMESTAMPTZ,

  -- Movimiento generado
  stock_move_id UUID REFERENCES inventory.stock_moves(id)
);

-- Índices para líneas de conteo
CREATE INDEX idx_count_lines_session ON inventory.inventory_count_lines(session_id);
CREATE INDEX idx_count_lines_state ON inventory.inventory_count_lines(state);
CREATE INDEX idx_count_lines_product ON inventory.inventory_count_lines(product_id);

COMMENT ON TABLE inventory.inventory_count_lines IS 'Líneas detalladas de conteo de inventario';

-- Tabla: abc_classification_rules (Reglas de clasificación ABC)
CREATE TABLE inventory.abc_classification_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(100) NOT NULL,

  -- Criterio de clasificación
  classification_method VARCHAR(20) NOT NULL DEFAULT 'value'
    CHECK (classification_method IN ('value', 'movement', 'revenue')),
  -- 'value': Por valor de inventario
  -- 'movement': Por frecuencia de movimiento
  -- 'revenue': Por ingresos generados

  -- Umbrales (porcentaje acumulado)
  threshold_a DECIMAL(5,2) NOT NULL DEFAULT 80.00,  -- Top 80%
  threshold_b DECIMAL(5,2) NOT NULL DEFAULT 95.00,  -- 80-95%
  -- Resto es C (95-100%)

  -- Frecuencias de conteo recomendadas (días)
  frequency_a INTEGER NOT NULL DEFAULT 7,    -- Clase A: semanal
  frequency_b INTEGER NOT NULL DEFAULT 30,   -- Clase B: mensual
  frequency_c INTEGER NOT NULL DEFAULT 90,   -- Clase C: trimestral

  -- Aplicación
  warehouse_id UUID REFERENCES inventory.warehouses(id),
  category_ids UUID[],  -- Categorías a las que aplica

  -- Estado
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  last_calculation TIMESTAMPTZ,

  -- Auditoría
  company_id UUID NOT NULL REFERENCES auth.companies(id),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by UUID NOT NULL REFERENCES auth.users(id),

  CONSTRAINT chk_thresholds CHECK (threshold_a < threshold_b AND threshold_b <= 100)
);

COMMENT ON TABLE inventory.abc_classification_rules IS 'Reglas de clasificación ABC para priorización de conteos';

-- Tabla: product_abc_classification (Clasificación ABC por producto)
CREATE TABLE inventory.product_abc_classification (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL REFERENCES inventory.products(id),
  rule_id UUID NOT NULL REFERENCES inventory.abc_classification_rules(id),

  -- Clasificación
  classification VARCHAR(1) NOT NULL
    CHECK (classification IN ('A', 'B', 'C')),

  -- Métricas calculadas
  metric_value DECIMAL(18,2) NOT NULL,  -- Valor usado para clasificar
  cumulative_percent DECIMAL(5,2) NOT NULL,   -- % acumulado
  rank_position INTEGER NOT NULL,        -- Posición en ranking

  -- Período de cálculo
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,

  -- Frecuencia asignada
  assigned_frequency INTEGER NOT NULL,

  calculated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT uq_product_rule UNIQUE (product_id, rule_id)
);

-- Índice para búsqueda de clasificación
CREATE INDEX idx_product_abc ON inventory.product_abc_classification(product_id, rule_id);

COMMENT ON TABLE inventory.product_abc_classification IS 'Clasificación ABC calculada por producto';

-- Extensión de stock_moves para marcar movimientos de inventario
ALTER TABLE inventory.stock_moves ADD COLUMN IF NOT EXISTS
  is_inventory BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE inventory.stock_moves ADD COLUMN IF NOT EXISTS
  inventory_session_id UUID REFERENCES inventory.inventory_count_sessions(id);

CREATE INDEX idx_moves_is_inventory ON inventory.stock_moves(is_inventory)
  WHERE is_inventory = TRUE;

-- =====================================================
-- PARTE 4: FUNCIONES DE UTILIDAD
-- =====================================================

-- Función: Ejecutar algoritmo FIFO para consumo de capas
CREATE OR REPLACE FUNCTION inventory.run_fifo(
  p_product_id UUID,
  p_quantity DECIMAL,
  p_company_id UUID,
  p_lot_id UUID DEFAULT NULL
)
RETURNS TABLE(
  total_value DECIMAL,
  unit_cost DECIMAL,
  remaining_qty DECIMAL
) AS $$
DECLARE
  v_candidate RECORD;
  v_qty_to_take DECIMAL;
  v_qty_taken DECIMAL;
  v_value_taken DECIMAL;
  v_total_value DECIMAL := 0;
  v_qty_pending DECIMAL := p_quantity;
  v_last_unit_cost DECIMAL := 0;
BEGIN
  -- Obtener candidatos FIFO ordenados
  FOR v_candidate IN
    SELECT id, remaining_qty as r_qty, remaining_value as r_val, unit_cost as u_cost
    FROM inventory.stock_valuation_layers
    WHERE product_id = p_product_id
      AND remaining_qty > 0
      AND company_id = p_company_id
      AND (p_lot_id IS NULL OR lot_id = p_lot_id)
    ORDER BY created_at ASC, id ASC
    FOR UPDATE
  LOOP
    EXIT WHEN v_qty_pending <= 0;

    v_qty_taken := LEAST(v_candidate.r_qty, v_qty_pending);
    v_value_taken := ROUND(v_qty_taken * (v_candidate.r_val / v_candidate.r_qty), 4);

    -- Actualizar capa candidata
    UPDATE inventory.stock_valuation_layers
    SET remaining_qty = remaining_qty - v_qty_taken,
        remaining_value = remaining_value - v_value_taken
    WHERE id = v_candidate.id;

    v_qty_pending := v_qty_pending - v_qty_taken;
    v_total_value := v_total_value + v_value_taken;
    v_last_unit_cost := v_candidate.u_cost;
  END LOOP;

  -- Si queda cantidad pendiente (stock negativo)
  IF v_qty_pending > 0 THEN
    v_total_value := v_total_value + (v_last_unit_cost * v_qty_pending);
    RETURN QUERY SELECT
      -v_total_value,
      v_total_value / p_quantity,
      -v_qty_pending;
  ELSE
    RETURN QUERY SELECT
      -v_total_value,
      v_total_value / p_quantity,
      0::DECIMAL;
  END IF;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION inventory.run_fifo IS 'Ejecuta algoritmo FIFO y consume capas de valoración';

-- Función: Calcular clasificación ABC
CREATE OR REPLACE FUNCTION inventory.calculate_abc_classification(
  p_rule_id UUID,
  p_period_months INTEGER DEFAULT 12
)
RETURNS TABLE (
  product_id UUID,
  classification VARCHAR(1),
  metric_value DECIMAL,
  cumulative_percent DECIMAL,
  rank_position INTEGER
) AS $$
DECLARE
  v_rule RECORD;
  v_total_value DECIMAL;
BEGIN
  -- Obtener regla
  SELECT * INTO v_rule
  FROM inventory.abc_classification_rules
  WHERE id = p_rule_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Regla ABC no encontrada: %', p_rule_id;
  END IF;

  -- Crear tabla temporal con métricas
  CREATE TEMP TABLE tmp_abc_metrics AS
  SELECT
    q.product_id,
    SUM(q.quantity * COALESCE(p.standard_price, 0)) as metric_value
  FROM inventory.quants q
  JOIN inventory.products p ON p.id = q.product_id
  WHERE q.quantity > 0
    AND (v_rule.warehouse_id IS NULL OR q.warehouse_id = v_rule.warehouse_id)
  GROUP BY q.product_id;

  -- Calcular total
  SELECT COALESCE(SUM(metric_value), 0) INTO v_total_value FROM tmp_abc_metrics;

  -- Retornar clasificación
  RETURN QUERY
  WITH ranked AS (
    SELECT
      tm.product_id,
      tm.metric_value,
      ROW_NUMBER() OVER (ORDER BY tm.metric_value DESC) as rank_pos,
      SUM(tm.metric_value) OVER (ORDER BY tm.metric_value DESC) /
        NULLIF(v_total_value, 0) * 100 as cum_pct
    FROM tmp_abc_metrics tm
  )
  SELECT
    r.product_id,
    CASE
      WHEN r.cum_pct <= v_rule.threshold_a THEN 'A'::VARCHAR(1)
      WHEN r.cum_pct <= v_rule.threshold_b THEN 'B'::VARCHAR(1)
      ELSE 'C'::VARCHAR(1)
    END as classification,
    r.metric_value,
    ROUND(r.cum_pct, 2),
    r.rank_pos::INTEGER
  FROM ranked r;

  DROP TABLE IF EXISTS tmp_abc_metrics;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION inventory.calculate_abc_classification IS 'Calcula clasificación ABC para productos según regla';

-- Función: Obtener próximos conteos programados
CREATE OR REPLACE FUNCTION inventory.get_pending_counts(
  p_days_ahead INTEGER DEFAULT 7
)
RETURNS TABLE (
  location_id UUID,
  location_name VARCHAR,
  next_inventory_date DATE,
  days_overdue INTEGER,
  quant_count INTEGER,
  total_value DECIMAL
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    l.id,
    l.name,
    (l.last_inventory_date + (l.cyclic_inventory_frequency || ' days')::INTERVAL)::DATE as next_inv_date,
    (CURRENT_DATE - (l.last_inventory_date + (l.cyclic_inventory_frequency || ' days')::INTERVAL)::DATE)::INTEGER as days_over,
    COUNT(q.id)::INTEGER as q_count,
    COALESCE(SUM(q.quantity * COALESCE(p.standard_price, 0)), 0) as t_value
  FROM inventory.locations l
  LEFT JOIN inventory.quants q ON q.location_id = l.id
  LEFT JOIN inventory.products p ON p.id = q.product_id
  WHERE l.cyclic_inventory_frequency > 0
    AND (l.last_inventory_date + (l.cyclic_inventory_frequency || ' days')::INTERVAL)::DATE <= CURRENT_DATE + p_days_ahead
    AND l.location_type = 'internal'
  GROUP BY l.id, l.name, l.last_inventory_date, l.cyclic_inventory_frequency
  ORDER BY next_inv_date;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION inventory.get_pending_counts IS 'Obtiene ubicaciones con conteos cíclicos pendientes';

-- Función: Marcar quants como desactualizados cuando cambia cantidad
CREATE OR REPLACE FUNCTION inventory.mark_quants_outdated()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.quantity != NEW.quantity AND OLD.inventory_quantity_set = TRUE THEN
    NEW.is_outdated := TRUE;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_quant_outdated
  BEFORE UPDATE OF quantity ON inventory.quants
  FOR EACH ROW
  EXECUTE FUNCTION inventory.mark_quants_outdated();

-- Función: Calcular fechas de caducidad al crear lote
CREATE OR REPLACE FUNCTION inventory.compute_lot_expiration_dates()
RETURNS TRIGGER AS $$
DECLARE
  v_product RECORD;
BEGIN
  -- Obtener configuración del producto
  SELECT
    use_expiration_date,
    expiration_time,
    use_time,
    removal_time,
    alert_time
  INTO v_product
  FROM inventory.products
  WHERE id = NEW.product_id;

  -- Si el producto usa fechas de caducidad y no se especificó expiration_date
  IF v_product.use_expiration_date AND NEW.expiration_date IS NULL THEN
    NEW.expiration_date := NOW() + (v_product.expiration_time || ' days')::INTERVAL;

    IF v_product.use_time IS NOT NULL THEN
      NEW.use_date := NEW.expiration_date - (v_product.use_time || ' days')::INTERVAL;
    END IF;

    IF v_product.removal_time IS NOT NULL THEN
      NEW.removal_date := NEW.expiration_date - (v_product.removal_time || ' days')::INTERVAL;
    END IF;

    IF v_product.alert_time IS NOT NULL THEN
      NEW.alert_date := NEW.expiration_date - (v_product.alert_time || ' days')::INTERVAL;
    END IF;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_lot_expiration_dates
  BEFORE INSERT ON inventory.lots
  FOR EACH ROW
  EXECUTE FUNCTION inventory.compute_lot_expiration_dates();

-- Función: Limpiar valor de la vista materializada
CREATE OR REPLACE FUNCTION inventory.refresh_product_valuation_summary()
RETURNS void AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY inventory.product_valuation_summary;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION inventory.refresh_product_valuation_summary IS 'Refresca la vista materializada de valoración de productos';

-- =====================================================
-- PARTE 5: TRIGGERS DE ACTUALIZACIÓN
-- =====================================================

-- Trigger: Actualizar updated_at para lots
CREATE OR REPLACE FUNCTION inventory.update_lots_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_lots_updated_at
  BEFORE UPDATE ON inventory.lots
  FOR EACH ROW
  EXECUTE FUNCTION inventory.update_lots_timestamp();

-- Trigger: Actualizar updated_at para count_sessions
CREATE TRIGGER trg_count_sessions_updated_at
  BEFORE UPDATE ON inventory.inventory_count_sessions
  FOR EACH ROW
  EXECUTE FUNCTION inventory.update_lots_timestamp();

-- Trigger: Actualizar estadísticas de sesión al modificar líneas
CREATE OR REPLACE FUNCTION inventory.update_session_stats()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE inventory.inventory_count_sessions
  SET
    counted_quants = (
      SELECT COUNT(*) FROM inventory.inventory_count_lines
      WHERE session_id = COALESCE(NEW.session_id, OLD.session_id)
        AND state IN ('counted', 'applied')
    ),
    discrepancy_quants = (
      SELECT COUNT(*) FROM inventory.inventory_count_lines
      WHERE session_id = COALESCE(NEW.session_id, OLD.session_id)
        AND state = 'counted'
        AND (counted_qty - theoretical_qty) != 0
    ),
    total_value_diff = (
      SELECT COALESCE(SUM(ABS((counted_qty - theoretical_qty) * COALESCE(unit_cost, 0))), 0)
      FROM inventory.inventory_count_lines
      WHERE session_id = COALESCE(NEW.session_id, OLD.session_id)
        AND counted_qty IS NOT NULL
    ),
    updated_at = NOW()
  WHERE id = COALESCE(NEW.session_id, OLD.session_id);

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_session_stats
  AFTER INSERT OR UPDATE OR DELETE ON inventory.inventory_count_lines
  FOR EACH ROW
  EXECUTE FUNCTION inventory.update_session_stats();

-- =====================================================
-- PARTE 6: VISTAS
-- =====================================================

-- Vista: Lotes próximos a caducar
CREATE OR REPLACE VIEW inventory.expiring_lots_view AS
SELECT
  l.id,
  l.name as lot_name,
  l.product_id,
  p.name as product_name,
  p.default_code as sku,
  l.expiration_date,
  l.removal_date,
  EXTRACT(DAY FROM l.expiration_date - NOW()) as days_until_expiry,
  COALESCE(SUM(q.quantity), 0) as stock_qty,
  l.company_id,
  l.tenant_id
FROM inventory.lots l
JOIN inventory.products p ON p.id = l.product_id
LEFT JOIN inventory.quants q ON q.lot_id = l.id
LEFT JOIN inventory.locations loc ON q.location_id = loc.id
WHERE l.expiration_date IS NOT NULL
  AND l.expiration_date > NOW()
  AND loc.location_type = 'internal'
GROUP BY l.id, p.id
HAVING COALESCE(SUM(q.quantity), 0) > 0;

COMMENT ON VIEW inventory.expiring_lots_view IS 'Vista de lotes con stock próximos a caducar';

-- Vista: Resumen de conteos por ubicación
CREATE OR REPLACE VIEW inventory.location_count_summary_view AS
SELECT
  l.id as location_id,
  l.name as location_name,
  l.warehouse_id,
  w.name as warehouse_name,
  l.cyclic_inventory_frequency,
  l.last_inventory_date,
  (l.last_inventory_date + (l.cyclic_inventory_frequency || ' days')::INTERVAL)::DATE as next_inventory_date,
  l.abc_classification,
  COUNT(q.id) as quant_count,
  COALESCE(SUM(q.quantity * COALESCE(p.standard_price, 0)), 0) as total_value
FROM inventory.locations l
LEFT JOIN inventory.warehouses w ON l.warehouse_id = w.id
LEFT JOIN inventory.quants q ON q.location_id = l.id AND q.quantity > 0
LEFT JOIN inventory.products p ON q.product_id = p.id
WHERE l.location_type = 'internal'
  AND l.cyclic_inventory_frequency > 0
GROUP BY l.id, w.id;

COMMENT ON VIEW inventory.location_count_summary_view IS 'Resumen de configuración de conteo cíclico por ubicación';

-- =====================================================
-- COMENTARIOS EN TABLAS
-- =====================================================

COMMENT ON TABLE inventory.stock_valuation_layers IS 'Capas de valoración de inventario para costeo FIFO/AVCO';
COMMENT ON TABLE inventory.lots IS 'Lotes y números de serie para trazabilidad';
-- Nota: La tabla anterior se renombró a stock_move_consume_rel
COMMENT ON TABLE inventory.removal_strategies IS 'Estrategias de salida de inventario (FIFO/LIFO/FEFO)';
COMMENT ON TABLE inventory.inventory_count_sessions IS 'Sesiones de conteo cíclico de inventario';
COMMENT ON TABLE inventory.inventory_count_lines IS 'Líneas detalladas de conteo de inventario';
COMMENT ON TABLE inventory.abc_classification_rules IS 'Reglas de clasificación ABC para priorización';
COMMENT ON TABLE inventory.product_abc_classification IS 'Clasificación ABC calculada por producto';
COMMENT ON TABLE inventory.category_stock_accounts IS 'Cuentas contables de valoración por categoría';
COMMENT ON TABLE inventory.valuation_settings IS 'Configuración de valoración por tenant/empresa';

-- =====================================================
-- FIN DE EXTENSIONES INVENTORY
-- =====================================================