-- =============================================================
-- ARCHIVO: 21b-inventory-extended.sql
-- DESCRIPCION: Tablas extendidas de inventario (Odoo-style)
--   Incluye: locations, products (inventory-specific), stock_quants,
--   stock_moves, stock_valuation_layers, inventory_adjustments,
--   inventory_adjustment_lines
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core V2
-- FECHA: 2026-02-05
-- DEPENDE DE: 18-warehouses.sql, 21-inventory.sql
-- COHERENCIA: Entidades TypeORM sin DDL previo
-- =============================================================

-- =====================
-- SCHEMA: inventory (ya creado en 18-warehouses.sql)
-- =====================

-- =====================
-- TIPO ENUM: location_type
-- Tipos de ubicacion Odoo-style
-- =====================
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'location_type_enum') THEN
        CREATE TYPE inventory.location_type_enum AS ENUM (
            'internal',
            'supplier',
            'customer',
            'inventory',
            'production',
            'transit'
        );
    END IF;
END$$;

-- =====================
-- TIPO ENUM: product_type
-- Tipos de producto para inventario
-- =====================
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'inv_product_type_enum') THEN
        CREATE TYPE inventory.inv_product_type_enum AS ENUM (
            'storable',
            'consumable',
            'service'
        );
    END IF;
END$$;

-- =====================
-- TIPO ENUM: tracking_type
-- Tipo de seguimiento de producto
-- =====================
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'tracking_type_enum') THEN
        CREATE TYPE inventory.tracking_type_enum AS ENUM (
            'none',
            'lot',
            'serial'
        );
    END IF;
END$$;

-- =====================
-- TIPO ENUM: valuation_method
-- Metodo de valuacion de inventario
-- =====================
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'valuation_method_enum') THEN
        CREATE TYPE inventory.valuation_method_enum AS ENUM (
            'standard',
            'fifo',
            'average'
        );
    END IF;
END$$;

-- =====================
-- TIPO ENUM: adjustment_status
-- Estado de ajuste de inventario
-- =====================
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'adjustment_status_enum') THEN
        CREATE TYPE inventory.adjustment_status_enum AS ENUM (
            'draft',
            'confirmed',
            'done',
            'cancelled'
        );
    END IF;
END$$;

-- =====================
-- TABLA: locations
-- Ubicaciones logicas de inventario (Odoo-style)
-- Complementa warehouse_locations con modelo Odoo de ubicaciones virtuales
-- (supplier, customer, inventory loss, production, transit)
-- Coherencia: location.entity.ts
-- =====================
CREATE TABLE IF NOT EXISTS inventory.locations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    warehouse_id UUID REFERENCES inventory.warehouses(id) ON DELETE SET NULL,

    -- Identificacion
    name VARCHAR(255) NOT NULL,
    complete_name VARCHAR(500),

    -- Tipo de ubicacion
    location_type inventory.location_type_enum NOT NULL,

    -- Jerarquia (auto-referencia)
    parent_id UUID REFERENCES inventory.locations(id) ON DELETE SET NULL,

    -- Flags especiales
    is_scrap_location BOOLEAN NOT NULL DEFAULT FALSE,
    is_return_location BOOLEAN NOT NULL DEFAULT FALSE,

    -- Estado
    active BOOLEAN NOT NULL DEFAULT TRUE,

    -- Metadata
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID REFERENCES auth.users(id)
);

-- Indices para locations
CREATE INDEX IF NOT EXISTS idx_locations_tenant_id ON inventory.locations(tenant_id);
CREATE INDEX IF NOT EXISTS idx_locations_warehouse_id ON inventory.locations(warehouse_id) WHERE warehouse_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_locations_parent_id ON inventory.locations(parent_id) WHERE parent_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_locations_type ON inventory.locations(location_type);
CREATE INDEX IF NOT EXISTS idx_locations_active ON inventory.locations(active) WHERE active = TRUE;
CREATE INDEX IF NOT EXISTS idx_locations_scrap ON inventory.locations(is_scrap_location) WHERE is_scrap_location = TRUE;
CREATE INDEX IF NOT EXISTS idx_locations_return ON inventory.locations(is_return_location) WHERE is_return_location = TRUE;

-- =====================
-- TABLA: products
-- Productos especificos de inventario (Odoo-style)
-- Diferente de products.products: este es para gestion de almacen,
-- valuacion, tracking por lote/serie, mientras products.products
-- es para comercio/retail con codigos SAT, impuestos, etc.
-- Coherencia: product.entity.ts (inventory module)
-- =====================
CREATE TABLE IF NOT EXISTS inventory.products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Identificacion
    name VARCHAR(255) NOT NULL,
    code VARCHAR(100),
    barcode VARCHAR(100),
    description TEXT,

    -- Clasificacion
    product_type inventory.inv_product_type_enum NOT NULL DEFAULT 'storable',
    tracking inventory.tracking_type_enum NOT NULL DEFAULT 'none',
    category_id UUID,

    -- Unidades de medida
    uom_id UUID NOT NULL,
    purchase_uom_id UUID,

    -- Precios y costo
    cost_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
    list_price DECIMAL(12, 2) NOT NULL DEFAULT 0,

    -- Valuacion
    valuation_method inventory.valuation_method_enum NOT NULL DEFAULT 'fifo',

    -- Flags
    is_storable BOOLEAN NOT NULL DEFAULT TRUE,
    can_be_sold BOOLEAN NOT NULL DEFAULT TRUE,
    can_be_purchased BOOLEAN NOT NULL DEFAULT TRUE,
    active BOOLEAN NOT NULL DEFAULT TRUE,

    -- Medidas fisicas
    weight DECIMAL(12, 4),
    volume DECIMAL(12, 4),

    -- Imagen
    image_url VARCHAR(500),

    -- Metadata
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES auth.users(id)
);

-- Indices para products
CREATE INDEX IF NOT EXISTS idx_inv_products_tenant_id ON inventory.products(tenant_id);
CREATE INDEX IF NOT EXISTS idx_inv_products_code ON inventory.products(code) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_inv_products_barcode ON inventory.products(barcode) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_inv_products_category_id ON inventory.products(category_id) WHERE category_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_inv_products_active ON inventory.products(active) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_inv_products_type ON inventory.products(product_type);
CREATE INDEX IF NOT EXISTS idx_inv_products_tracking ON inventory.products(tracking) WHERE tracking != 'none';
CREATE INDEX IF NOT EXISTS idx_inv_products_valuation ON inventory.products(valuation_method);

-- Unique code per tenant (soft-delete aware)
CREATE UNIQUE INDEX IF NOT EXISTS uq_inv_products_tenant_code
    ON inventory.products(tenant_id, code) WHERE deleted_at IS NULL AND code IS NOT NULL;

-- =====================
-- TABLA: stock_quants
-- Cantidades de stock por producto/ubicacion/lote (modelo Odoo)
-- Representa la cantidad real de un producto en una ubicacion especifica,
-- opcionalmente separada por lote
-- Coherencia: stock-quant.entity.ts
-- =====================
CREATE TABLE IF NOT EXISTS inventory.stock_quants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Producto y ubicacion
    product_id UUID NOT NULL REFERENCES inventory.products(id) ON DELETE CASCADE,
    location_id UUID NOT NULL REFERENCES inventory.locations(id) ON DELETE CASCADE,

    -- Lote (opcional)
    lot_id UUID REFERENCES inventory.lots(id) ON DELETE SET NULL,

    -- Cantidades
    quantity DECIMAL(16, 4) NOT NULL DEFAULT 0,
    reserved_quantity DECIMAL(16, 4) NOT NULL DEFAULT 0,

    -- Metadata
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    -- Unicidad: un solo registro por combinacion producto+ubicacion+lote
    UNIQUE(product_id, location_id, COALESCE(lot_id, '00000000-0000-0000-0000-000000000000'::UUID))
);

-- Indices para stock_quants
CREATE INDEX IF NOT EXISTS idx_stock_quants_tenant_id ON inventory.stock_quants(tenant_id);
CREATE INDEX IF NOT EXISTS idx_stock_quants_product_id ON inventory.stock_quants(product_id);
CREATE INDEX IF NOT EXISTS idx_stock_quants_location_id ON inventory.stock_quants(location_id);
CREATE INDEX IF NOT EXISTS idx_stock_quants_lot_id ON inventory.stock_quants(lot_id) WHERE lot_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_stock_quants_quantity ON inventory.stock_quants(quantity) WHERE quantity != 0;
CREATE INDEX IF NOT EXISTS idx_stock_quants_reserved ON inventory.stock_quants(reserved_quantity) WHERE reserved_quantity != 0;

-- =====================
-- TABLA: stock_moves
-- Movimientos individuales de stock vinculados a un picking (Odoo-style)
-- Cada linea de un picking genera un stock_move
-- Coherencia: stock-move.entity.ts
-- =====================
CREATE TABLE IF NOT EXISTS inventory.stock_moves (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Picking padre
    picking_id UUID NOT NULL REFERENCES inventory.pickings(id) ON DELETE CASCADE,

    -- Producto
    product_id UUID NOT NULL REFERENCES inventory.products(id) ON DELETE RESTRICT,
    product_uom_id UUID NOT NULL,

    -- Ubicaciones origen y destino
    location_id UUID NOT NULL REFERENCES inventory.locations(id),
    location_dest_id UUID NOT NULL REFERENCES inventory.locations(id),

    -- Cantidades
    product_qty DECIMAL(16, 4) NOT NULL,
    quantity_done DECIMAL(16, 4) NOT NULL DEFAULT 0,

    -- Lote (opcional)
    lot_id UUID REFERENCES inventory.lots(id) ON DELETE SET NULL,

    -- Estado (reutiliza move_status_enum de 21-inventory.sql)
    status inventory.move_status_enum NOT NULL DEFAULT 'draft',

    -- Fecha del movimiento
    date TIMESTAMPTZ,

    -- Documento origen
    origin VARCHAR(255),

    -- Metadata
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID REFERENCES auth.users(id)
);

-- Indices para stock_moves
CREATE INDEX IF NOT EXISTS idx_stock_moves_tenant_id ON inventory.stock_moves(tenant_id);
CREATE INDEX IF NOT EXISTS idx_stock_moves_picking_id ON inventory.stock_moves(picking_id);
CREATE INDEX IF NOT EXISTS idx_stock_moves_product_id ON inventory.stock_moves(product_id);
CREATE INDEX IF NOT EXISTS idx_stock_moves_status ON inventory.stock_moves(status);
CREATE INDEX IF NOT EXISTS idx_stock_moves_date ON inventory.stock_moves(date) WHERE date IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_stock_moves_location ON inventory.stock_moves(location_id);
CREATE INDEX IF NOT EXISTS idx_stock_moves_location_dest ON inventory.stock_moves(location_dest_id);
CREATE INDEX IF NOT EXISTS idx_stock_moves_lot_id ON inventory.stock_moves(lot_id) WHERE lot_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_stock_moves_origin ON inventory.stock_moves(origin) WHERE origin IS NOT NULL;

-- =====================
-- TABLA: stock_valuation_layers
-- Capas de valuacion de inventario (FIFO/AVCO/Standard)
-- Cada movimiento de stock genera una capa de valuacion para
-- rastrear el costo historico del inventario
-- Coherencia: stock-valuation-layer.entity.ts
-- =====================
CREATE TABLE IF NOT EXISTS inventory.stock_valuation_layers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Producto y empresa
    product_id UUID NOT NULL REFERENCES inventory.products(id) ON DELETE RESTRICT,
    company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,

    -- Cantidades y valores
    quantity DECIMAL(16, 4) NOT NULL,
    unit_cost DECIMAL(12, 2) NOT NULL,
    value DECIMAL(16, 2) NOT NULL,

    -- Remanente (para FIFO)
    remaining_qty DECIMAL(16, 4) NOT NULL,
    remaining_value DECIMAL(16, 2) NOT NULL,

    -- Movimiento de stock origen
    stock_move_id UUID REFERENCES inventory.stock_moves(id) ON DELETE SET NULL,

    -- Descripcion
    description VARCHAR(255),

    -- Contabilidad (referencia a asientos contables)
    account_move_id UUID,
    journal_entry_id UUID,

    -- Metadata
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID REFERENCES auth.users(id)
);

-- Indices para stock_valuation_layers
CREATE INDEX IF NOT EXISTS idx_valuation_layers_tenant_id ON inventory.stock_valuation_layers(tenant_id);
CREATE INDEX IF NOT EXISTS idx_valuation_layers_product_id ON inventory.stock_valuation_layers(product_id);
CREATE INDEX IF NOT EXISTS idx_valuation_layers_company_id ON inventory.stock_valuation_layers(company_id);
CREATE INDEX IF NOT EXISTS idx_valuation_layers_stock_move_id ON inventory.stock_valuation_layers(stock_move_id) WHERE stock_move_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_valuation_layers_remaining_qty ON inventory.stock_valuation_layers(remaining_qty) WHERE remaining_qty > 0;
CREATE INDEX IF NOT EXISTS idx_valuation_layers_account_move ON inventory.stock_valuation_layers(account_move_id) WHERE account_move_id IS NOT NULL;

-- =====================
-- TABLA: inventory_adjustments
-- Ajustes de inventario (conteos y correcciones)
-- Agrupa lineas de ajuste para una ubicacion y fecha especifica
-- Coherencia: inventory-adjustment.entity.ts
-- =====================
CREATE TABLE IF NOT EXISTS inventory.inventory_adjustments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,

    -- Identificacion
    name VARCHAR(100) NOT NULL,

    -- Ubicacion de ajuste
    location_id UUID NOT NULL REFERENCES inventory.locations(id),

    -- Fecha del ajuste
    date DATE NOT NULL,

    -- Estado
    status inventory.adjustment_status_enum NOT NULL DEFAULT 'draft',

    -- Notas
    notes TEXT,

    -- Metadata
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID REFERENCES auth.users(id)
);

-- Indices para inventory_adjustments
CREATE INDEX IF NOT EXISTS idx_adjustments_tenant_id ON inventory.inventory_adjustments(tenant_id);
CREATE INDEX IF NOT EXISTS idx_adjustments_company_id ON inventory.inventory_adjustments(company_id);
CREATE INDEX IF NOT EXISTS idx_adjustments_status ON inventory.inventory_adjustments(status);
CREATE INDEX IF NOT EXISTS idx_adjustments_date ON inventory.inventory_adjustments(date);
CREATE INDEX IF NOT EXISTS idx_adjustments_location ON inventory.inventory_adjustments(location_id);

-- =====================
-- TABLA: inventory_adjustment_lines
-- Lineas individuales de ajuste de inventario
-- Cada linea compara cantidad teorica vs contada para un producto
-- Coherencia: inventory-adjustment-line.entity.ts
-- =====================
CREATE TABLE IF NOT EXISTS inventory.inventory_adjustment_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    adjustment_id UUID NOT NULL REFERENCES inventory.inventory_adjustments(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Producto y ubicacion
    product_id UUID NOT NULL REFERENCES inventory.products(id) ON DELETE RESTRICT,
    location_id UUID NOT NULL REFERENCES inventory.locations(id),

    -- Lote (opcional)
    lot_id UUID REFERENCES inventory.lots(id) ON DELETE SET NULL,

    -- Cantidades
    theoretical_qty DECIMAL(16, 4) NOT NULL DEFAULT 0,
    counted_qty DECIMAL(16, 4) NOT NULL DEFAULT 0,
    difference_qty DECIMAL(16, 4) GENERATED ALWAYS AS (counted_qty - theoretical_qty) STORED,

    -- Unidad de medida
    uom_id UUID,

    -- Notas
    notes TEXT,

    -- Metadata
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Indices para inventory_adjustment_lines
CREATE INDEX IF NOT EXISTS idx_adjustment_lines_adjustment_id ON inventory.inventory_adjustment_lines(adjustment_id);
CREATE INDEX IF NOT EXISTS idx_adjustment_lines_product_id ON inventory.inventory_adjustment_lines(product_id);
CREATE INDEX IF NOT EXISTS idx_adjustment_lines_location_id ON inventory.inventory_adjustment_lines(location_id);
CREATE INDEX IF NOT EXISTS idx_adjustment_lines_lot_id ON inventory.inventory_adjustment_lines(lot_id) WHERE lot_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_adjustment_lines_tenant_id ON inventory.inventory_adjustment_lines(tenant_id);

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

-- locations
COMMENT ON TABLE inventory.locations IS 'Ubicaciones logicas de inventario (Odoo-style). Incluye ubicaciones virtuales: supplier, customer, inventory loss, production, transit';
COMMENT ON COLUMN inventory.locations.location_type IS 'Tipo: internal (almacen), supplier (proveedor virtual), customer (cliente virtual), inventory (perdida/ajuste), production (produccion), transit (en transito)';
COMMENT ON COLUMN inventory.locations.complete_name IS 'Nombre completo con jerarquia (ej: WH/Stock/Zone-A)';
COMMENT ON COLUMN inventory.locations.is_scrap_location IS 'Si es TRUE, productos movidos aqui se consideran scrap/desperdicio';
COMMENT ON COLUMN inventory.locations.is_return_location IS 'Si es TRUE, esta ubicacion se usa para devoluciones';

-- products (inventory)
COMMENT ON TABLE inventory.products IS 'Productos para gestion de inventario (Odoo-style). Diferente de products.products que es para comercio/retail';
COMMENT ON COLUMN inventory.products.product_type IS 'Tipo: storable (se almacena), consumable (se consume sin tracking), service (no se almacena)';
COMMENT ON COLUMN inventory.products.tracking IS 'Seguimiento: none, lot (por lote), serial (numero de serie unico)';
COMMENT ON COLUMN inventory.products.valuation_method IS 'Metodo de valuacion: standard (costo fijo), fifo (primero en entrar), average (costo promedio)';
COMMENT ON COLUMN inventory.products.is_storable IS 'Flag derivado de product_type=storable. Facilita filtros directos';

-- stock_quants
COMMENT ON TABLE inventory.stock_quants IS 'Cantidades reales de stock por producto/ubicacion/lote (modelo Odoo quant)';
COMMENT ON COLUMN inventory.stock_quants.quantity IS 'Cantidad total disponible en esta ubicacion';
COMMENT ON COLUMN inventory.stock_quants.reserved_quantity IS 'Cantidad reservada para pickings pendientes';

-- stock_moves
COMMENT ON TABLE inventory.stock_moves IS 'Movimientos individuales de stock vinculados a un picking. Cada linea de picking genera un move';
COMMENT ON COLUMN inventory.stock_moves.product_qty IS 'Cantidad solicitada del movimiento';
COMMENT ON COLUMN inventory.stock_moves.quantity_done IS 'Cantidad realmente procesada';
COMMENT ON COLUMN inventory.stock_moves.status IS 'Estado: draft, waiting, confirmed, assigned, done, cancelled';
COMMENT ON COLUMN inventory.stock_moves.origin IS 'Documento origen (ej: PO-2026-001, SO-2026-001)';

-- stock_valuation_layers
COMMENT ON TABLE inventory.stock_valuation_layers IS 'Capas de valuacion de inventario para FIFO/AVCO/Standard. Cada movimiento crea una capa';
COMMENT ON COLUMN inventory.stock_valuation_layers.remaining_qty IS 'Cantidad restante en esta capa (para FIFO: se consume de las capas mas antiguas)';
COMMENT ON COLUMN inventory.stock_valuation_layers.remaining_value IS 'Valor monetario restante en esta capa';
COMMENT ON COLUMN inventory.stock_valuation_layers.account_move_id IS 'Referencia al asiento contable generado (si aplica)';

-- inventory_adjustments
COMMENT ON TABLE inventory.inventory_adjustments IS 'Ajustes de inventario para corregir diferencias entre stock teorico y real';
COMMENT ON COLUMN inventory.inventory_adjustments.status IS 'Estado: draft (borrador), confirmed (confirmado), done (aplicado), cancelled';

-- inventory_adjustment_lines
COMMENT ON TABLE inventory.inventory_adjustment_lines IS 'Lineas de ajuste: cada una compara cantidad teorica vs contada para un producto en una ubicacion';
COMMENT ON COLUMN inventory.inventory_adjustment_lines.theoretical_qty IS 'Cantidad segun el sistema antes del conteo';
COMMENT ON COLUMN inventory.inventory_adjustment_lines.counted_qty IS 'Cantidad real contada fisicamente';
COMMENT ON COLUMN inventory.inventory_adjustment_lines.difference_qty IS 'Diferencia calculada: counted_qty - theoretical_qty (columna generada)';