docs/database/migrations/migration_warehouse_fields.sql

-- MongoDB Migration Script for Warehouse Fields
-- This script should be run in MongoDB shell or via MongoDB Compass

-- Update warehouses missing created_at field
db.scm_warehouse.updateMany(
  { "created_at": { $exists: false } },
  { 
    $set: { 
      "created_at": "2025-01-01T00:00:00Z",
      "created_by": "system"
    } 
  }
);

-- Update warehouses with null created_at
db.scm_warehouse.updateMany(
  { "created_at": null },
  { 
    $set: { 
      "created_at": "2025-01-01T00:00:00Z",
      "created_by": "system"
    } 
  }
);

-- Update warehouses missing created_by field
db.scm_warehouse.updateMany(
  { "created_by": { $exists: false } },
  { $set: { "created_by": "system" } }
);

-- Update warehouses with null created_by
db.scm_warehouse.updateMany(
  { "created_by": null },
  { $set: { "created_by": "system" } }
);

-- Update warehouses missing updated_at field
db.scm_warehouse.updateMany(
  { "updated_at": { $exists: false } },
  { $set: { "updated_at": null } }
);

-- Update warehouses missing updated_by field
db.scm_warehouse.updateMany(
  { "updated_by": { $exists: false } },
  { $set: { "updated_by": null } }
);

-- Update warehouses missing capabilities field
db.scm_warehouse.updateMany(
  { "capabilities": { $exists: false } },
  { 
    $set: { 
      "capabilities": {
        "can_receive": true,
        "can_fulfil": true,
        "can_sell": false,
        "can_adjust": true,
        "can_stock_take": true
      }
    } 
  }
);

-- Update warehouses with null capabilities
db.scm_warehouse.updateMany(
  { "capabilities": null },
  { 
    $set: { 
      "capabilities": {
        "can_receive": true,
        "can_fulfil": true,
        "can_sell": false,
        "can_adjust": true,
        "can_stock_take": true
      }
    } 
  }
);

-- Verification queries
print("=== Migration Verification ===");

print("Warehouses missing created_at:");
db.scm_warehouse.countDocuments({ "created_at": { $exists: false } });

print("Warehouses with null created_at:");
db.scm_warehouse.countDocuments({ "created_at": null });

print("Warehouses missing created_by:");
db.scm_warehouse.countDocuments({ "created_by": { $exists: false } });

print("Warehouses with null created_by:");
db.scm_warehouse.countDocuments({ "created_by": null });

print("Warehouses missing capabilities:");
db.scm_warehouse.countDocuments({ "capabilities": { $exists: false } });

print("Warehouses with null capabilities:");
db.scm_warehouse.countDocuments({ "capabilities": null });

print("Total warehouses:");
db.scm_warehouse.countDocuments({});

print("=== Sample Documents ===");
db.scm_warehouse.find({}).limit(2).pretty();