-- Check Database Status for Brocco-Lee App
-- Run this in your Supabase SQL Editor to diagnose issues

-- 1. Check if products table exists and its structure
SELECT 
    table_name,
    table_type
FROM information_schema.tables 
WHERE table_name = 'products';

-- 2. Check products table structure
SELECT 
    column_name, 
    data_type, 
    is_nullable, 
    column_default,
    character_maximum_length
FROM information_schema.columns 
WHERE table_name = 'products' 
ORDER BY ordinal_position;

-- 3. Check if image_url column exists and its properties
SELECT 
    column_name,
    data_type,
    is_nullable,
    column_default,
    character_maximum_length
FROM information_schema.columns 
WHERE table_name = 'products' AND column_name = 'image_url';

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

-- 5. Check if RLS is enabled on products table
SELECT 
    schemaname,
    tablename,
    rowsecurity
FROM pg_tables 
WHERE tablename = 'products';

-- 6. Check current products data (first 5 rows)
SELECT 
    id, 
    name, 
    category, 
    image_url, 
    price_per_kg, 
    price_per_unit,
    stock_quantity,
    is_available,
    created_at,
    updated_at
FROM products 
ORDER BY id 
LIMIT 5;

-- 7. Check for any products with NULL image_url
SELECT 
    COUNT(*) as products_with_null_image_url
FROM products 
WHERE image_url IS NULL;

-- 8. Check current user permissions
SELECT 
    current_user,
    session_user,
    current_database();

-- 9. Test if we can insert a product (this will fail if permissions are wrong)
-- Uncomment the next line to test insert permissions
-- 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);

-- 10. Check if there are any constraints that might prevent inserts
SELECT 
    conname as constraint_name,
    contype as constraint_type,
    pg_get_constraintdef(oid) as constraint_definition
FROM pg_constraint 
WHERE conrelid = 'products'::regclass;
