# Database Restructuring Migrations

This directory contains migration scripts for the major system restructuring (Phase 1: Database Cleanup).

## Migration Scripts

### 013_remove_product_variants.sql
- Removes `product_variants` table
- Removes all `variant_id` foreign keys from:
  - `sale_items`
  - `inventory_items`
  - `inventory`
  - `inventory_movements`
  - `product_prices`
  - `grn_items`
  - `purchase_order_items`
- Changes `boms.fg_variant_id` → `boms.fg_product_id`
- Changes `production_orders.fg_variant_id` → `production_orders.fg_product_id`

**⚠️ WARNING**: This migration will DELETE all product variant data!

### 014_remove_tax_tables.sql
- Removes tax-related tables:
  - `tax_categories`
  - `tax_exemptions`
  - `tax_rates`
- Removes `tax_category_id` and `default_vat_rate` from `products` table

**⚠️ WARNING**: This migration will DELETE all tax data!

### 015_remove_tax_fields_from_sales.sql
- Removes `vat` field from `sales` table
- Removes `vat_rate` and `vat_amount` fields from `sale_items` table
- Updates `sales.total` calculation (removes VAT)

### 016_add_low_stock_threshold.sql
- Adds `low_stock_threshold` field to `products` table
- Adds index for low stock queries

### 017_update_payment_methods.sql
- Updates payment `provider` ENUM from `('PAYSTACK', 'CASH', 'MOBILE', 'BANK')` to `('CASH', 'CARD', 'MPESA')`
- Migrates existing payment data:
  - PAYSTACK → CARD
  - CASH → CASH
  - MOBILE → MPESA
  - BANK → CARD

## Running Migrations

**IMPORTANT**: Always backup your database before running migrations!

```bash
# Connect to MySQL
mysql -u root -p

# Select your database
USE xyz_pos_db;

# Run migrations in order
source server/scripts/migrations/013_remove_product_variants.sql;
source server/scripts/migrations/014_remove_tax_tables.sql;
source server/scripts/migrations/015_remove_tax_fields_from_sales.sql;
source server/scripts/migrations/016_add_low_stock_threshold.sql;
source server/scripts/migrations/017_update_payment_methods.sql;
```

Or from command line:

```bash
mysql -u root -p xyz_pos_db < server/scripts/migrations/013_remove_product_variants.sql
mysql -u root -p xyz_pos_db < server/scripts/migrations/014_remove_tax_tables.sql
mysql -u root -p xyz_pos_db < server/scripts/migrations/015_remove_tax_fields_from_sales.sql
mysql -u root -p xyz_pos_db < server/scripts/migrations/016_add_low_stock_threshold.sql
mysql -u root -p xyz_pos_db < server/scripts/migrations/017_update_payment_methods.sql
```

## Migration Order

**Critical**: Migrations MUST be run in this order:
1. 013_remove_product_variants.sql (first - removes variant dependencies)
2. 014_remove_tax_tables.sql (removes tax tables)
3. 015_remove_tax_fields_from_sales.sql (removes tax fields)
4. 016_add_low_stock_threshold.sql (adds new field)
5. 017_update_payment_methods.sql (updates payment enum)

## Notes

- Migrations use conditional logic to check if columns/constraints exist before dropping them
- Foreign key constraint names may vary - the scripts attempt to drop them but may need manual adjustment
- Data migration is included where necessary (e.g., fg_variant_id → fg_product_id)
- Always test migrations on a backup/staging database first!
