-- Database Validation Script for Brocco-Lee App
-- Run this in your Supabase SQL Editor to check if everything is set up correctly

-- 1. Check if tables exist
SELECT 
  table_name,
  CASE 
    WHEN table_name IS NOT NULL THEN '✅ EXISTS'
    ELSE '❌ MISSING'
  END as status
FROM information_schema.tables 
WHERE table_name IN ('products', 'orders', 'order_items')
ORDER BY table_name;

-- 2. Check table structures
SELECT 
  table_name,
  column_name,
  data_type,
  is_nullable,
  column_default
FROM information_schema.columns 
WHERE table_name IN ('products', 'orders', 'order_items')
ORDER BY table_name, ordinal_position;

-- 3. Check RLS policies
SELECT 
  schemaname,
  tablename,
  policyname,
  cmd,
  permissive,
  roles,
  qual,
  with_check
FROM pg_policies 
WHERE tablename IN ('products', 'orders', 'order_items')
ORDER BY tablename, policyname;

-- 4. Check if products table has data
SELECT 
  'products' as table_name,
  COUNT(*) as record_count,
  CASE 
    WHEN COUNT(*) > 0 THEN '✅ HAS DATA'
    ELSE '❌ NO DATA'
  END as status
FROM products
UNION ALL
SELECT 
  'orders' as table_name,
  COUNT(*) as record_count,
  CASE 
    WHEN COUNT(*) >= 0 THEN '✅ EXISTS'
    ELSE '❌ ERROR'
  END as status
FROM orders
UNION ALL
SELECT 
  'order_items' as table_name,
  COUNT(*) as record_count,
  CASE 
    WHEN COUNT(*) >= 0 THEN '✅ EXISTS'
    ELSE '❌ ERROR'
  END as status
FROM order_items;

-- 5. Check sample product data
SELECT 
  id,
  name,
  category,
  price_per_kg,
  price_per_unit,
  stock_quantity,
  is_available
FROM products 
ORDER BY id 
LIMIT 5;

-- 6. Test foreign key constraints
-- This will show if there are any constraint issues
SELECT 
  tc.table_name, 
  kcu.column_name, 
  ccu.table_name AS foreign_table_name,
  ccu.column_name AS foreign_column_name 
FROM 
  information_schema.table_constraints AS tc 
  JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
  JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
    AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' 
  AND tc.table_name IN ('orders', 'order_items');

-- 7. Check if real-time is enabled
SELECT 
  schemaname,
  tablename,
  CASE 
    WHEN schemaname IS NOT NULL THEN '✅ ENABLED'
    ELSE '❌ NOT ENABLED'
  END as realtime_status
FROM pg_publication_tables 
WHERE tablename = 'orders';

-- 8. Test basic permissions (this will show if RLS is working)
-- Note: This will fail if you're not authenticated, which is expected
SELECT 
  'RLS Test' as test_name,
  CASE 
    WHEN COUNT(*) >= 0 THEN '✅ RLS Working'
    ELSE '❌ RLS Error'
  END as status
FROM products 
LIMIT 1;
