Yard Control - Gestión de Ubicaciones en Patio

Estructura de Base de Datos Relacional (PostgreSQL DDL)

-- MÓDULO 1: RECEPCIÓN (INBOUND)
CREATE TABLE purchase_orders (
    po_id SERIAL PRIMARY KEY,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    supplier_name VARCHAR(150) NOT NULL,
    status VARCHAR(20) DEFAULT 'Pending' CHECK (status IN ('Pending', 'Partial', 'Completed')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE purchase_order_items (
    poi_id SERIAL PRIMARY KEY,
    po_id INT REFERENCES purchase_orders(po_id) ON DELETE CASCADE,
    sku VARCHAR(50) NOT NULL,
    name VARCHAR(150) NOT NULL,
    expected_qty INT NOT NULL CHECK (expected_qty > 0),
    received_qty INT DEFAULT 0 CHECK (received_qty >= 0)
);

-- MÓDULO 2: ALMACENAJE (STORAGE & PUTAWAY)
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(50) CHECK (category IN ('Bobina de papel', 'caja', 'madera', 'fierro', 'auto', 'camión')),
    um VARCHAR(20) CHECK (um IN ('Bobina', 'caja', 'Kg', 'Ton', 'M2', 'Pallet')),
    rotation_type VARCHAR(10) DEFAULT 'FIFO' CHECK (rotation_type IN ('FIFO', 'FEFO')),
    weight_unit DECIMAL(10,2),
    volume_unit DECIMAL(10,2)
);

CREATE TABLE warehouse_locations (
    location_id SERIAL PRIMARY KEY,
    aisle VARCHAR(10) NOT NULL,
    rack VARCHAR(10) NOT NULL,
    shelf VARCHAR(10) NOT NULL,
    level VARCHAR(10) NOT NULL,
    max_weight DECIMAL(10,2),
    max_volume DECIMAL(10,2),
    UNIQUE (aisle, rack, shelf, level)
);

CREATE TABLE inventory_batches (
    batch_id SERIAL PRIMARY KEY,
    sku VARCHAR(50) REFERENCES products(sku),
    batch_number VARCHAR(50) NOT NULL,
    entry_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expiry_date DATE -- Requerido para estrategia FEFO
);

-- Soporte para Múltiples Ubicaciones por Producto / Lote
CREATE TABLE inventory_locations (
    inv_loc_id SERIAL PRIMARY KEY,
    batch_id INT REFERENCES inventory_batches(batch_id) ON DELETE CASCADE,
    location_id INT REFERENCES warehouse_locations(location_id) ON DELETE CASCADE,
    quantity INT NOT NULL CHECK (quantity >= 0)
);

-- MÓDULO 3: DESPACHO (OUTBOUND)
CREATE TABLE sales_orders (
    so_id SERIAL PRIMARY KEY,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    customer_name VARCHAR(150) NOT NULL,
    delivery_address VARCHAR(250) NOT NULL,
    status VARCHAR(20) DEFAULT 'Pending' CHECK (status IN ('Pending', 'Allocated', 'Picked', 'Shipped')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sales_order_items (
    soi_id SERIAL PRIMARY KEY,
    so_id INT REFERENCES sales_orders(so_id) ON DELETE CASCADE,
    sku VARCHAR(50) REFERENCES products(sku),
    quantity INT NOT NULL CHECK (quantity > 0),
    picked_qty INT DEFAULT 0 CHECK (picked_qty >= 0)
);

CREATE TABLE picking_waves (
    wave_id SERIAL PRIMARY KEY,
    wave_number VARCHAR(50) UNIQUE NOT NULL,
    status VARCHAR(20) DEFAULT 'Created' CHECK (status IN ('Created', 'In_Progress', 'Completed')),
    assigned_user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- MODULO 4: RECEPCION TERRESTRE AVANZADA
CREATE TABLE terrestre_recepciones (
    recepcion_id SERIAL PRIMARY KEY,
    sequence_number INT NOT NULL,
    driver_name VARCHAR(150) NOT NULL,
    rut_dni VARCHAR(20) NOT NULL,
    license_plate VARCHAR(12) NOT NULL,
    transport_id VARCHAR(20) NOT NULL,
    document_type VARCHAR(20) CHECK (document_type IN ('Guia', 'Factura')),
    document_number VARCHAR(50) NOT NULL,
    customer_name VARCHAR(150) NOT NULL,
    unit_code VARCHAR(80) NOT NULL,
    container_type VARCHAR(80),
    condition VARCHAR(20) CHECK (condition IN ('Bueno', 'Malo', 'Regular')),
    quantity INT NOT NULL,
    observations TEXT,
    storage_location VARCHAR(40) DEFAULT 'TRANSIT',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- MODULO 5: LOGISTICA INVERSA
CREATE TABLE reverse_logistics_rma (
    rma_id SERIAL PRIMARY KEY,
    rma_number VARCHAR(50) UNIQUE NOT NULL,
    customer_name VARCHAR(150) NOT NULL,
    sku VARCHAR(50) NOT NULL,
    return_reason VARCHAR(80) NOT NULL,
    product_condition VARCHAR(40) NOT NULL,
    inventory_state VARCHAR(40) NOT NULL,
    destination_location VARCHAR(60) NOT NULL,
    erp_webhook_status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- MODULO 6: DESPACHO Y EXPEDICION
CREATE TABLE dispatches (
    dispatch_id SERIAL PRIMARY KEY,
    route_sheet_number VARCHAR(50) NOT NULL,
    dock_code VARCHAR(30) NOT NULL,
    truck_plate VARCHAR(12) NOT NULL,
    seal_number VARCHAR(60) NOT NULL,
    guide_number VARCHAR(60) NOT NULL,
    loaded_packages INT NOT NULL,
    total_packages INT NOT NULL,
    status VARCHAR(30) DEFAULT 'Carga Parcial',
    departed_at TIMESTAMP
);