michangarrito/database/schemas/07-inventory.sql

-- =============================================================================
-- MICHANGARRITO - 07 INVENTORY
-- =============================================================================
-- Inventario y movimientos de stock
-- =============================================================================

-- Movimientos de inventario
CREATE TABLE IF NOT EXISTS inventory.inventory_movements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES catalog.products(id) ON DELETE CASCADE,

    movement_type VARCHAR(20) NOT NULL,
    quantity DECIMAL(10,3) NOT NULL,
    previous_stock DECIMAL(10,3) NOT NULL,
    new_stock DECIMAL(10,3) NOT NULL,

    unit_cost DECIMAL(10,2),
    total_cost DECIMAL(10,2),

    reference_type VARCHAR(20),
    reference_id UUID,

    notes TEXT,
    created_by UUID REFERENCES auth.users(id),

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_inventory_tenant ON inventory.inventory_movements(tenant_id);
CREATE INDEX idx_inventory_product ON inventory.inventory_movements(product_id);
CREATE INDEX idx_inventory_date ON inventory.inventory_movements(created_at);

-- Alertas de stock
CREATE TABLE IF NOT EXISTS inventory.stock_alerts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES catalog.products(id) ON DELETE CASCADE,

    current_stock INTEGER NOT NULL,
    threshold INTEGER NOT NULL,

    status VARCHAR(20) DEFAULT 'active',

    notified_at TIMESTAMPTZ,
    resolved_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_stock_alerts_tenant ON inventory.stock_alerts(tenant_id);
CREATE INDEX idx_stock_alerts_status ON inventory.stock_alerts(status);

CREATE TRIGGER update_stock_alerts_updated_at
    BEFORE UPDATE ON inventory.stock_alerts
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();