-- Migration script to apply changes from CaseWallet branch to caseB branch
-- Run this on the database to update the schema

-- Changes to products table
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(10,2);
ALTER TABLE products ALTER COLUMN category_id DROP NOT NULL;
ALTER TABLE products ALTER COLUMN category_id SET DEFAULT NULL;
ALTER TABLE products DROP CONSTRAINT IF EXISTS products_category_id_fkey; -- Drop foreign key constraint
ALTER TABLE products ADD COLUMN IF NOT EXISTS tax_rates jsonb DEFAULT '{"vat": "G", "stt": ""}';

-- Changes to ingredients table
ALTER TABLE ingredients ALTER COLUMN quantity TYPE NUMERIC(8,2);
ALTER TABLE ingredients ALTER COLUMN price TYPE NUMERIC(8,2);

-- Changes to payments table
ALTER TABLE payments ALTER COLUMN total_amount TYPE NUMERIC(10,2);
ALTER TABLE payments ALTER COLUMN total_amount SET DEFAULT NULL;

-- Changes to sales_receipts table
ALTER TABLE sales_receipts ALTER COLUMN totalAmount TYPE NUMERIC(10,2);

-- Remove index
DROP INDEX IF EXISTS idx_payments_total_amount;

-- Update existing dummy data in products (if needed, assuming specific product names)
-- Note: These updates assume the products exist; adjust as necessary
UPDATE products SET name = 'Crest Chicken #18', price = 20.55 WHERE name = 'Chicken';
UPDATE products SET name = 'FMF Sungrown Rice 10kg', price = 12.75 WHERE name = 'Rice';
