ARCH-001: Multi-warehouse support

[AliiiBenn]

ARCH-001: Multi-Warehouse Support

Overview

Implement multi-warehouse architecture with warehouse_id column in all tables, enabling consolidated reporting and cross-warehouse analytics.

Description

Multi-warehouse support allows managing multiple warehouses (Paris, Lyon, Marseille, etc.) in a single system:

• All tables include warehouse_id column
• Warehouse metadata table (name, location, WMS type)
• Per-warehouse analyses (--warehouse paris)
• Consolidated reporting (all warehouses combined)
• Cross-warehouse comparisons

Architecture Changes

Schema Modifications

-- 1. Warehouse metadata table
CREATE TABLE warehouses (
    id TEXT PRIMARY KEY,              -- e.g., 'paris', 'lyon', 'marseille'
    name TEXT NOT NULL,               -- 'Paris Warehouse'
    location TEXT,                    -- 'Paris, France'
    wms_type TEXT,                    -- 'Manhattan', 'SAP EWM', 'Blue Yonder'
    timezone TEXT,                    -- 'Europe/Paris'
    language TEXT,                    -- 'fr', 'en'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT 1
);

-- 2. Add warehouse_id to all existing tables
ALTER TABLE produits ADD COLUMN warehouse_id TEXT NOT NULL DEFAULT 'default';
ALTER TABLE mouvements ADD COLUMN warehouse_id TEXT NOT NULL DEFAULT 'default';
ALTER TABLE commandes ADD COLUMN warehouse_id TEXT NOT NULL DEFAULT 'default';

-- 3. Update primary keys to be composite
-- Old: PRIMARY KEY (no_produit)
-- New: PRIMARY KEY (no_produit, warehouse_id)

-- 4. Add foreign keys to warehouses table
ALTER TABLE produits ADD CONSTRAINT fk_produits_warehouse
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);

ALTER TABLE mouvements ADD CONSTRAINT fk_mouvements_warehouse
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);

Data Migration Script

def migrate_to_multi_warehouse(db_path: str, default_warehouse_id: str = 'wh1'):
    """
    Migrate existing database to multi-warehouse schema.
    """
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # 1. Create warehouses table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS warehouses (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            location TEXT,
            wms_type TEXT,
            timezone TEXT DEFAULT 'Europe/Paris',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # 2. Insert default warehouse
    cursor.execute(f"""
        INSERT OR IGNORE INTO warehouses (id, name, location)
        VALUES ('{default_warehouse_id}', 'Default Warehouse', 'Unknown')
    """)

    # 3. Add warehouse_id columns
    for table in ['produits', 'mouvements', 'commandes']:
        cursor.execute(f"ALTER TABLE {table} ADD COLUMN warehouse_id TEXT")
        cursor.execute(f"UPDATE {table} SET warehouse_id = '{default_warehouse_id}'")

    # 4. Recreate indexes with warehouse_id
    cursor.execute("""
        CREATE INDEX IF NOT EXISTS idx_mouvements_warehouse_date
        ON mouvements(warehouse_id, date_heure)
    """)

    conn.commit()
    conn.close()

    print(f"✅ Migrated to multi-warehouse schema")
    print(f"   Default warehouse ID: {default_warehouse_id}")

Configuration Structure

Warehouses Configuration

config/warehouses.yaml:

warehouses:
  paris:
    name: "Paris Warehouse"
    location: "Paris, France"
    wms_type: "Manhattan"
    timezone: "Europe/Paris"
    language: "fr"

    # Data sources
    sources:
      produits: "data/paris/produits.xlsx"
      mouvements: "data/paris/mouvements.xlsx"
      commandes: "data/paris/commandes.xlsx"

    # WMS-specific mappings
    value_mappings:
      type:
        ENTRÉE: inbound
        SORTIE: outbound

  lyon:
    name: "Lyon Warehouse"
    location: "Lyon, France"
    wms_type: "SAP EWM"
    timezone: "Europe/Paris"
    language: "en"

    sources:
      produits: "data/lyon/produits.csv"
      mouvements: "data/lyon/movements.csv"
      commandes: "data/lyon/orders.csv"

CLI Changes

All Commands Support --warehouse Option

# Import specific warehouse
wareflow import --warehouse paris

# Analyze specific warehouse
wareflow analyze --warehouse lyon --abc

# Analyze all warehouses (consolidated)
wareflow analyze --abc

# Export specific warehouse
wareflow export --warehouse marseille

# Run pipeline for specific warehouse
wareflow run --warehouse paris

# Status for specific warehouse
wareflow status --warehouse lyon

SQL Query Modifications

Before (single warehouse):

query = "SELECT * FROM mouvements WHERE date_heure >= ?"

After (multi-warehouse):

def build_warehouse_query(base_query, warehouse_id=None):
    """Add warehouse_id filter if specified."""
    if warehouse_id:
        return f"{base_query} AND warehouse_id = '{warehouse_id}'"
    return base_query

# Usage
query = "SELECT * FROM mouvements WHERE date_heure >= ?"
query = build_warehouse_query(query, warehouse_id='paris')

Implementation Plan

Phase 1: Schema Migration (1 day)

• Create warehouses table
• Add warehouse_id columns
• Update primary keys (composite)
• Add foreign key constraints
• Create migration script

Phase 2: CLI Updates (1 day)

• Add --warehouse option to all commands
• Update SQL queries for warehouse filtering
• Implement consolidated queries (no warehouse filter)
• Update error messages

Phase 3: Documentation & Testing (1-2 days)

• Document migration process
• Create test data for multiple warehouses
• Test all commands with warehouse filter
• Test consolidated reports
• Update user documentation

Success Criteria

• Successfully migrate existing schema
• Add warehouses table with metadata
• All commands support --warehouse option
• Consolidated queries work (no warehouse filter)
• Cross-warehouse comparisons available
• Backward compatibility maintained

Backward Compatibility

Default Behavior (No Breaking Change)

# Old command (still works)
wareflow import

# Internally becomes:
wareflow import --warehouse default
# OR uses warehouse_id from config.yaml

Migration Path

1. Phase 1: Add schema (optional, opt-in)
2. Phase 2: Default to single-warehouse mode
3. Phase 3: Encourage multi-warehouse adoption
4. Phase 4: Multi-warehouse becomes standard

Future Enhancements

See Separate Issues

• ARCH-002: Cross-warehouse comparison
• ARCH-003: Consolidated reporting
• ARCH-004: Data synchronization across warehouses

Dependencies

Required

• CORE-001 (import command)

Related Issues

• Enables: ARCH-002 (Cross-warehouse comparison)
• Enables: ARCH-003 (Consolidated reporting)
• Blocks: None (backward compatible)

References

• Multi-warehouse architecture: docs/FUTURE.md
• Schema: docs/SCHEMA.md
• Migration guide: docs/MIGRATION.md (to be created)

Notes

This is a major architectural change that should be:

1. Implemented carefully with migration scripts
2. Backward compatible (existing projects work unchanged)
3. Well documented (migration guides)
4. Thoroughly tested (all commands with/without warehouse filter)

The multi-warehouse architecture is essential for scaling to multiple locations while maintaining a single codebase.