-- Migration Script: Add order_number field to orders table
-- Run this in your Supabase SQL Editor to update existing database

-- 1. Add the order_number column to existing orders table
ALTER TABLE orders 
ADD COLUMN IF NOT EXISTS order_number VARCHAR(20);

-- 2. Add unique constraint to order_number
ALTER TABLE orders 
ADD CONSTRAINT orders_order_number_unique UNIQUE (order_number);

-- 3. Add customer information fields
ALTER TABLE orders 
ADD COLUMN IF NOT EXISTS customer_name VARCHAR(255),
ADD COLUMN IF NOT EXISTS customer_phone VARCHAR(20),
ADD COLUMN IF NOT EXISTS delivery_address TEXT;

-- 4. Update existing orders with generated order numbers
-- This will generate order numbers for existing orders based on their creation date
UPDATE orders 
SET order_number = CONCAT(
  TO_CHAR(created_at, 'YYYY-MM-DD'),
  '_',
  CASE 
    WHEN ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at) <= 99 
    THEN 'A-' || LPAD(ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at)::TEXT, 2, '0')
    WHEN ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at) <= 198 
    THEN 'B-' || LPAD((ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at) - 99)::TEXT, 2, '0')
    WHEN ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at) <= 297 
    THEN 'C-' || LPAD((ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at) - 198)::TEXT, 2, '0')
    ELSE 'D-' || LPAD((ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at) - 297)::TEXT, 2, '0')
  END
)
WHERE order_number IS NULL;

-- 5. Make order_number NOT NULL after populating existing records
ALTER TABLE orders 
ALTER COLUMN order_number SET NOT NULL;

-- 6. Create index for better performance
CREATE INDEX IF NOT EXISTS idx_orders_order_number ON orders(order_number);
CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);

-- 7. Verify the migration
SELECT 
  COUNT(*) as total_orders,
  COUNT(order_number) as orders_with_numbers,
  MIN(order_number) as first_order_number,
  MAX(order_number) as last_order_number
FROM orders;

-- 8. Show sample of generated order numbers
SELECT 
  id,
  order_number,
  created_at,
  status,
  total_price
FROM orders 
ORDER BY created_at DESC 
LIMIT 10;
