-- Menu Management System Database Export
-- Execute this file in your PostgreSQL database after creating it

-- Drop existing tables if they exist (use with caution)

-- Create Categories table
CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  name_arabic TEXT NOT NULL,
  description TEXT,
  description_arabic TEXT,
  start_time TEXT,
  end_time TEXT,
  is_active BOOLEAN DEFAULT true,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create Subcategories table
CREATE TABLE subcategories (
  id SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES categories(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  name_arabic TEXT NOT NULL,
  description TEXT,
  description_arabic TEXT,
  is_active BOOLEAN DEFAULT true,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create Menu Items table
CREATE TABLE menu_items (
  id SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
  subcategory_id INTEGER REFERENCES subcategories(id) ON DELETE SET NULL,
  name TEXT NOT NULL,
  name_arabic TEXT NOT NULL,
  description TEXT,
  description_arabic TEXT,
  price DECIMAL(10,3) NOT NULL,
  image TEXT,
  video TEXT,
  is_active BOOLEAN DEFAULT true,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create Modifier Categories table
CREATE TABLE modifier_categories (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  name_arabic TEXT NOT NULL,
  is_required BOOLEAN DEFAULT false,
  max_selections INTEGER DEFAULT 1,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create Modifiers table
CREATE TABLE modifiers (
  id SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES modifier_categories(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  name_arabic TEXT NOT NULL,
  price DECIMAL(10,3) NOT NULL DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create Menu Item Modifiers junction table
CREATE TABLE menu_item_modifiers (
  id SERIAL PRIMARY KEY,
  menu_item_id INTEGER REFERENCES menu_items(id) ON DELETE CASCADE,
  modifier_category_id INTEGER REFERENCES modifier_categories(id) ON DELETE CASCADE,
  custom_price DECIMAL(10,3)
);

-- Create Addon Categories table
CREATE TABLE addon_categories (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  name_arabic TEXT NOT NULL,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create Addons table
CREATE TABLE addons (
  id SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES addon_categories(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  name_arabic TEXT NOT NULL,
  price DECIMAL(10,3) NOT NULL,
  is_active BOOLEAN DEFAULT true,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create Menu Item Addons junction table
CREATE TABLE menu_item_addons (
  id SERIAL PRIMARY KEY,
  menu_item_id INTEGER REFERENCES menu_items(id) ON DELETE CASCADE,
  addon_category_id INTEGER REFERENCES addon_categories(id) ON DELETE CASCADE
);

-- Create Customer Feedback table
CREATE TABLE feedback (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  phone TEXT NOT NULL,
  email TEXT,
  message TEXT NOT NULL,
  rating INTEGER DEFAULT 5,
  is_archived BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Insert sample categories with time-based availability
INSERT INTO categories (name, name_arabic, description, description_arabic, start_time, end_time, sort_order) VALUES
('Breakfast', 'الإفطار', 'Start your day with our delicious breakfast items', 'ابدأ يومك مع وجبات الإفطار اللذيذة', '06:00', '11:00', 1),
('Lunch', 'الغداء', 'Hearty meals perfect for lunch time', 'وجبات شهية مثالية لوقت الغداء', '11:00', '16:00', 2),
('Dinner', 'العشاء', 'Elegant dinner options for evening dining', 'خيارات عشاء أنيقة لتناول الطعام المسائي', '16:00', '23:00', 3),
('Beverages', 'المشروبات', 'Refreshing drinks available all day', 'مشروبات منعشة متاحة طوال اليوم', '06:00', '23:59', 4),
('Desserts', 'الحلويات', 'Sweet treats to end your meal', 'حلويات لذيذة لإنهاء وجبتك', '12:00', '23:00', 5);

-- Insert sample menu items
INSERT INTO menu_items (category_id, name, name_arabic, description, description_arabic, price, image) VALUES
(1, 'Pancakes with Berries', 'البان كيك مع التوت', 'Fluffy pancakes topped with fresh berries and maple syrup', 'بان كيك ناعم مع التوت الطازج وشراب القيقب', 3.500, 'https://images.unsplash.com/photo-1567620905732-2d1ec7ab7445?w=500'),
(1, 'Breakfast Sandwich', 'ساندويش الإفطار', 'Grilled sandwich with eggs, cheese, and turkey', 'ساندويش مشوي مع البيض والجبن والديك الرومي', 2.750, 'https://images.unsplash.com/photo-1481070555726-e2fe8357725c?w=500'),
(2, 'Grilled Chicken Salad', 'سلطة الدجاج المشوي', 'Fresh mixed greens with grilled chicken breast', 'خضروات ورقية طازجة مع صدر دجاج مشوي', 4.250, 'https://images.unsplash.com/photo-1540420773420-3366772f4999?w=500'),
(2, 'Beef Burger', 'برجر اللحم', 'Juicy beef patty with lettuce, tomato, and special sauce', 'قطعة لحم عصارية مع الخس والطماطم والصوص الخاص', 5.500, 'https://images.unsplash.com/photo-1568901346375-23c9450c58cd?w=500'),
(3, 'Grilled Salmon', 'السلمون المشوي', 'Fresh salmon fillet with herbs and lemon', 'فيليه سلمون طازج مع الأعشاب والليمون', 8.750, 'https://images.unsplash.com/photo-1467003909585-2f8a72700288?w=500'),
(3, 'Lamb Kabab', 'كباب الخروف', 'Traditional lamb kabab with rice and vegetables', 'كباب خروف تقليدي مع الأرز والخضار', 7.250, 'https://images.unsplash.com/photo-1529692236671-f1f6cf9683ba?w=500'),
(4, 'Fresh Orange Juice', 'عصير البرتقال الطازج', 'Freshly squeezed orange juice', 'عصير برتقال طازج معصور', 1.500, 'https://images.unsplash.com/photo-1621506289937-a8e4df240d0b?w=500'),
(4, 'Turkish Coffee', 'القهوة التركية', 'Traditional Turkish coffee served with sweets', 'قهوة تركية تقليدية تقدم مع الحلويات', 1.250, 'https://images.unsplash.com/photo-1495474472287-4d71bcdd2085?w=500'),
(5, 'Chocolate Cake', 'كيكة الشوكولاتة', 'Rich chocolate cake with cream frosting', 'كيكة شوكولاتة غنية مع كريمة', 2.500, 'https://images.unsplash.com/photo-1578985545062-69928b1d9587?w=500'),
(5, 'Baklava', 'البقلاوة', 'Traditional Middle Eastern pastry with honey', 'معجنات شرق أوسطية تقليدية مع العسل', 1.750, 'https://images.unsplash.com/photo-1519676867240-f03562e64548?w=500');

-- Insert modifier categories and modifiers
INSERT INTO modifier_categories (name, name_arabic, is_required, max_selections) VALUES
('Size Options', 'خيارات الحجم', true, 1),
('Extra Toppings', 'إضافات إضافية', false, 0);

INSERT INTO modifiers (category_id, name, name_arabic, price) VALUES
(1, 'Small', 'صغير', 0.000),
(1, 'Medium', 'وسط', 0.500),
(1, 'Large', 'كبير', 1.000),
(2, 'Extra Cheese', 'جبنة إضافية', 0.500),
(2, 'Extra Vegetables', 'خضار إضافية', 0.750);

-- Insert addon categories and addons
INSERT INTO addon_categories (name, name_arabic) VALUES
('Sides', 'الأطباق الجانبية'),
('Drinks', 'المشروبات');

INSERT INTO addons (category_id, name, name_arabic, price) VALUES
(1, 'French Fries', 'بطاطس مقلية', 1.500),
(1, 'Side Salad', 'سلطة جانبية', 1.250),
(2, 'Soft Drink', 'مشروب غازي', 0.750),
(2, 'Bottled Water', 'مياه معبأة', 0.500);

-- Insert sample feedback
INSERT INTO feedback (name, phone, email, message, rating) VALUES
('Ahmed Al-Mansouri', '+965 9999 8888', 'ahmed@example.com', 'Excellent food quality and great service. The lamb kabab was perfectly cooked!', 5),
('Sarah Johnson', '+965 5555 4444', 'sarah@example.com', 'Loved the breakfast options. The pancakes were fluffy and delicious.', 4),
('Mohammad Hassan', '+965 7777 6666', 'mohammad@example.com', 'Good variety of dishes. Would love to see more vegetarian options.', 4);

-- Create indexes for better performance
CREATE INDEX idx_categories_is_active ON categories(is_active);
CREATE INDEX idx_categories_sort_order ON categories(sort_order);
CREATE INDEX idx_menu_items_category_id ON menu_items(category_id);
CREATE INDEX idx_menu_items_is_active ON menu_items(is_active);
CREATE INDEX idx_menu_items_sort_order ON menu_items(sort_order);
CREATE INDEX idx_feedback_created_at ON feedback(created_at);
CREATE INDEX idx_feedback_is_archived ON feedback(is_archived);

-- Grant permissions (adjust as needed for your database user)
-- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_database_user;
-- GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_database_user;