erp-core-database-v2/ddl/18-warehouses.sql

-- =============================================================
-- ARCHIVO: 18-warehouses.sql
-- DESCRIPCION: Almacenes y ubicaciones
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core V2
-- FECHA: 2026-01-13
-- =============================================================

-- =====================
-- SCHEMA: inventory (compartido con 19-inventory.sql)
-- =====================
CREATE SCHEMA IF NOT EXISTS inventory;

-- =====================
-- TABLA: warehouses
-- Almacenes/bodegas para inventario
-- =====================
CREATE TABLE IF NOT EXISTS inventory.warehouses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    branch_id UUID REFERENCES core.branches(id) ON DELETE SET NULL,

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

    -- Tipo
    warehouse_type VARCHAR(20) DEFAULT 'standard', -- standard, transit, returns, quarantine, virtual

    -- Direccion
    address_line1 VARCHAR(200),
    address_line2 VARCHAR(200),
    city VARCHAR(100),
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(3) DEFAULT 'MEX',

    -- Contacto
    manager_name VARCHAR(100),
    phone VARCHAR(30),
    email VARCHAR(255),

    -- Geolocalizacion
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),

    -- Capacidad
    capacity_units INTEGER,       -- Capacidad en unidades
    capacity_volume DECIMAL(10, 4), -- Capacidad en m3
    capacity_weight DECIMAL(10, 4), -- Capacidad en kg

    -- Configuracion
    settings JSONB DEFAULT '{}',
    -- Ejemplo: {"allow_negative": false, "auto_reorder": true}

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,
    is_default BOOLEAN DEFAULT FALSE,

    -- 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,

    UNIQUE(tenant_id, code)
);

-- Indices para warehouses
CREATE INDEX IF NOT EXISTS idx_warehouses_tenant ON inventory.warehouses(tenant_id);
CREATE INDEX IF NOT EXISTS idx_warehouses_branch ON inventory.warehouses(branch_id);
CREATE INDEX IF NOT EXISTS idx_warehouses_code ON inventory.warehouses(code);
CREATE INDEX IF NOT EXISTS idx_warehouses_type ON inventory.warehouses(warehouse_type);
CREATE INDEX IF NOT EXISTS idx_warehouses_active ON inventory.warehouses(is_active) WHERE is_active = TRUE;
CREATE INDEX IF NOT EXISTS idx_warehouses_default ON inventory.warehouses(tenant_id, is_default) WHERE is_default = TRUE;

-- =====================
-- TABLA: warehouse_locations
-- Ubicaciones dentro de almacenes (pasillos, racks, estantes)
-- =====================
CREATE TABLE IF NOT EXISTS inventory.warehouse_locations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    warehouse_id UUID NOT NULL REFERENCES inventory.warehouses(id) ON DELETE CASCADE,
    parent_id UUID REFERENCES inventory.warehouse_locations(id) ON DELETE SET NULL,

    -- Identificacion
    code VARCHAR(30) NOT NULL,
    name VARCHAR(100) NOT NULL,
    barcode VARCHAR(50),

    -- Tipo de ubicacion
    location_type VARCHAR(20) DEFAULT 'shelf', -- zone, aisle, rack, shelf, bin

    -- Jerarquia
    hierarchy_path TEXT,         -- /warehouse-01/zone-a/rack-1/shelf-2
    hierarchy_level INTEGER DEFAULT 0,

    -- Coordenadas dentro del almacen
    aisle VARCHAR(10),
    rack VARCHAR(10),
    shelf VARCHAR(10),
    bin VARCHAR(10),

    -- Capacidad
    capacity_units INTEGER,
    capacity_volume DECIMAL(10, 4),
    capacity_weight DECIMAL(10, 4),

    -- Restricciones
    allowed_product_types TEXT[] DEFAULT '{}', -- Tipos de producto permitidos
    temperature_range JSONB,     -- {"min": -20, "max": 4} para productos refrigerados
    humidity_range JSONB,        -- {"min": 30, "max": 50}

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,
    is_pickable BOOLEAN DEFAULT TRUE,    -- Se puede tomar inventario
    is_receivable BOOLEAN DEFAULT TRUE,  -- Se puede recibir inventario

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

    UNIQUE(warehouse_id, code)
);

-- Indices para warehouse_locations
CREATE INDEX IF NOT EXISTS idx_warehouse_locations_warehouse ON inventory.warehouse_locations(warehouse_id);
CREATE INDEX IF NOT EXISTS idx_warehouse_locations_parent ON inventory.warehouse_locations(parent_id);
CREATE INDEX IF NOT EXISTS idx_warehouse_locations_code ON inventory.warehouse_locations(code);
CREATE INDEX IF NOT EXISTS idx_warehouse_locations_type ON inventory.warehouse_locations(location_type);
CREATE INDEX IF NOT EXISTS idx_warehouse_locations_hierarchy ON inventory.warehouse_locations(hierarchy_path);
CREATE INDEX IF NOT EXISTS idx_warehouse_locations_active ON inventory.warehouse_locations(is_active) WHERE is_active = TRUE;
CREATE INDEX IF NOT EXISTS idx_warehouse_locations_barcode ON inventory.warehouse_locations(barcode);

-- =====================
-- TABLA: warehouse_zones
-- Zonas logicas de almacen (para organizacion)
-- =====================
CREATE TABLE IF NOT EXISTS inventory.warehouse_zones (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    warehouse_id UUID NOT NULL REFERENCES inventory.warehouses(id) ON DELETE CASCADE,

    -- Identificacion
    code VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    color VARCHAR(20),

    -- Tipo de zona
    zone_type VARCHAR(20) DEFAULT 'storage', -- storage, picking, packing, shipping, receiving, quarantine

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,

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

    UNIQUE(warehouse_id, code)
);

-- Indices para warehouse_zones
CREATE INDEX IF NOT EXISTS idx_warehouse_zones_warehouse ON inventory.warehouse_zones(warehouse_id);
CREATE INDEX IF NOT EXISTS idx_warehouse_zones_type ON inventory.warehouse_zones(zone_type);

-- =====================
-- COMENTARIOS
-- =====================
COMMENT ON TABLE inventory.warehouses IS 'Almacenes y bodegas para gestion de inventario';
COMMENT ON COLUMN inventory.warehouses.warehouse_type IS 'Tipo: standard, transit (en transito), returns (devoluciones), quarantine (cuarentena), virtual';
COMMENT ON COLUMN inventory.warehouses.is_default IS 'Almacen por defecto para operaciones';

COMMENT ON TABLE inventory.warehouse_locations IS 'Ubicaciones fisicas dentro de almacenes (racks, estantes, bins)';
COMMENT ON COLUMN inventory.warehouse_locations.location_type IS 'Tipo: zone, aisle, rack, shelf, bin';
COMMENT ON COLUMN inventory.warehouse_locations.is_pickable IS 'Se puede hacer picking desde esta ubicacion';
COMMENT ON COLUMN inventory.warehouse_locations.is_receivable IS 'Se puede recibir inventario en esta ubicacion';

COMMENT ON TABLE inventory.warehouse_zones IS 'Zonas logicas para organizar el almacen';
COMMENT ON COLUMN inventory.warehouse_zones.zone_type IS 'Tipo: storage, picking, packing, shipping, receiving, quarantine';