-- Fix Database Issues for Brocco-Lee App
-- Run this in your Supabase SQL Editor

-- 1. Add missing RLS policies for products table
-- Allow admins to insert, update, and delete products
CREATE POLICY "Admins can insert products" ON products FOR INSERT WITH CHECK (
  auth.jwt() ->> 'email' = 'soufyane.sba@gmail.com'
);

CREATE POLICY "Admins can update products" ON products FOR UPDATE USING (
  auth.jwt() ->> 'email' = 'soufyane.sba@gmail.com'
);

CREATE POLICY "Admins can delete products" ON products FOR DELETE USING (
  auth.jwt() ->> 'email' = 'soufyane.sba@gmail.com'
);

-- 2. Ensure image_url column exists and has proper constraints
-- This will add the column if it doesn't exist, or modify it if it does
DO $$ 
BEGIN
    -- Check if image_url column exists
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'products' AND column_name = 'image_url'
    ) THEN
        -- Add image_url column if it doesn't exist
        ALTER TABLE products ADD COLUMN image_url VARCHAR(500);
    ELSE
        -- Modify existing column to ensure proper type and constraints
        ALTER TABLE products ALTER COLUMN image_url TYPE VARCHAR(500);
    END IF;
    
    -- Set default value for existing rows
    UPDATE products SET image_url = '' WHERE image_url IS NULL;
    
    -- Make image_url NOT NULL with default empty string
    ALTER TABLE products ALTER COLUMN image_url SET DEFAULT '';
    ALTER TABLE products ALTER COLUMN image_url SET NOT NULL;
END $$;

-- 3. Add updated_at trigger for products table
-- This will automatically update the updated_at timestamp when a product is modified
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Drop existing trigger if it exists
DROP TRIGGER IF EXISTS update_products_updated_at ON products;

-- Create trigger for products table
CREATE TRIGGER update_products_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 4. Verify the current table structure
-- This will show you the current structure of the products table
SELECT 
    column_name, 
    data_type, 
    is_nullable, 
    column_default
FROM information_schema.columns 
WHERE table_name = 'products' 
ORDER BY ordinal_position;

-- 5. Show current RLS policies for products table
SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies 
WHERE tablename = 'products';

-- 6. Test insert permission (run this as admin user)
-- INSERT INTO products (name, category, price_per_kg, price_per_unit, image_url, stock_quantity, is_available) 
-- VALUES ('Test Product', 'vegetables', 10.00, 0, 'https://example.com/test.jpg', 100, true);

-- 7. Update existing products to have empty image_url if they don't have one
UPDATE products SET image_url = '' WHERE image_url IS NULL;

-- 8. Verify the fix worked
SELECT 
    id, 
    name, 
    category, 
    image_url, 
    price_per_kg, 
    price_per_unit,
    stock_quantity,
    is_available
FROM products 
LIMIT 5;
