-- =====================================================
-- Bade Canteen - Manual Database Setup SQL
-- For PostgreSQL (Supabase)
-- =====================================================

-- =====================================================
-- 1. CREATE TABLES
-- =====================================================

-- Users table
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  student_id VARCHAR(50) UNIQUE,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  mobile VARCHAR(15),
  role VARCHAR(20) DEFAULT 'student' CHECK (role IN ('student', 'kitchen', 'admin')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Food items table
CREATE TABLE IF NOT EXISTS food_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  category VARCHAR(50) NOT NULL CHECK (category IN ('breakfast', 'lunch', 'snacks', 'beverages')),
  price DECIMAL(10, 2) NOT NULL,
  image_url TEXT,
  prep_time INTEGER DEFAULT 15,
  availability VARCHAR(20) DEFAULT 'available' CHECK (availability IN ('available', 'not_available', 'out_of_order')),
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Orders table
CREATE TABLE IF NOT EXISTS orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_number VARCHAR(20) UNIQUE NOT NULL,
  student_id UUID REFERENCES users(id) ON DELETE CASCADE,
  status VARCHAR(20) DEFAULT 'ordered' CHECK (status IN ('ordered', 'preparing', 'ready', 'completed', 'cancelled')),
  total DECIMAL(10, 2) NOT NULL,
  payment_method VARCHAR(20),
  estimated_time INTEGER,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Order items table
CREATE TABLE IF NOT EXISTS order_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
  food_id UUID REFERENCES food_items(id) ON DELETE CASCADE,
  quantity INTEGER NOT NULL DEFAULT 1,
  price DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Payments table (saved payment details)
CREATE TABLE IF NOT EXISTS payments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  student_id UUID REFERENCES users(id) ON DELETE CASCADE,
  payment_method VARCHAR(20) NOT NULL,
  name VARCHAR(255),
  mobile VARCHAR(15),
  upi_id VARCHAR(100),
  card_last4 VARCHAR(4),
  is_default BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Notifications table
CREATE TABLE IF NOT EXISTS notifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
  message TEXT NOT NULL,
  read_status BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Push subscriptions table
CREATE TABLE IF NOT EXISTS push_subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  endpoint TEXT NOT NULL,
  p256dh TEXT NOT NULL,
  auth TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, endpoint)
);

-- =====================================================
-- 2. CREATE INDEXES
-- =====================================================

CREATE INDEX IF NOT EXISTS idx_orders_student ON orders(student_id);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_food_items_category ON food_items(category);
CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications(user_id);

-- =====================================================
-- 3. ENABLE ROW LEVEL SECURITY
-- =====================================================

ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE food_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE push_subscriptions ENABLE ROW LEVEL SECURITY;

-- =====================================================
-- 4. INSERT SAMPLE FOOD ITEMS
-- =====================================================

-- BREAKFAST ITEMS
INSERT INTO food_items (name, category, price, prep_time, description, availability)
VALUES 
  ('Masala Dosa', 'breakfast', 40.00, 15, 'Crispy dosa with potato filling', 'available'),
  ('Idli Sambar', 'breakfast', 30.00, 10, 'Steamed rice cakes with sambar', 'available'),
  ('Poha', 'breakfast', 25.00, 10, 'Flattened rice with spices', 'available');

-- LUNCH ITEMS
INSERT INTO food_items (name, category, price, prep_time, description, availability)
VALUES 
  ('Veg Thali', 'lunch', 60.00, 20, 'Complete vegetarian meal', 'available'),
  ('Dal Rice', 'lunch', 50.00, 15, 'Lentils with steamed rice', 'available'),
  ('Chole Bhature', 'lunch', 55.00, 20, 'Chickpea curry with fried bread', 'available');

-- SNACKS ITEMS
INSERT INTO food_items (name, category, price, prep_time, description, availability)
VALUES 
  ('Samosa', 'snacks', 15.00, 5, 'Crispy fried pastry with filling', 'available'),
  ('Vada Pav', 'snacks', 20.00, 5, 'Spiced potato fritter in bun', 'available'),
  ('Pav Bhaji', 'snacks', 45.00, 15, 'Vegetable curry with bread', 'available');

-- BEVERAGES ITEMS
INSERT INTO food_items (name, category, price, prep_time, description, availability)
VALUES 
  ('Chai', 'beverages', 10.00, 5, 'Indian spiced tea', 'available'),
  ('Coffee', 'beverages', 15.00, 5, 'Hot filter coffee', 'available'),
  ('Cold Coffee', 'beverages', 30.00, 5, 'Chilled coffee with ice cream', 'available');

-- =====================================================
-- 5. CREATE TEST USERS
-- =====================================================

-- Test Student
INSERT INTO users (student_id, name, email, mobile, role)
VALUES ('TEST001', 'Test Student', 'test@student.com', '9876543210', 'student');

-- Kitchen Staff
INSERT INTO users (student_id, name, email, mobile, role)
VALUES ('KITCHEN001', 'Kitchen Staff', 'kitchen@canteen.com', '9876543211', 'kitchen');

-- Admin User
INSERT INTO users (student_id, name, email, mobile, role)
VALUES ('ADMIN001', 'Admin User', 'admin@canteen.com', '9876543212', 'admin');

-- =====================================================
-- 6. VERIFICATION QUERIES
-- =====================================================

-- Check if tables were created
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' 
ORDER BY table_name;

-- Count food items
SELECT category, COUNT(*) as count 
FROM food_items 
GROUP BY category 
ORDER BY category;

-- List all food items
SELECT name, category, price, prep_time, availability 
FROM food_items 
ORDER BY category, name;

-- List all users
SELECT student_id, name, role 
FROM users 
ORDER BY role, name;

-- =====================================================
-- DONE!
-- =====================================================
