-- ============================================================
-- TIFFIN — Food Delivery Platform
-- Database schema (MySQL 5.7+ / 8.0)
-- ============================================================

CREATE DATABASE IF NOT EXISTS getmugqj_food
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE getmugqj_food;

-- ---------------------------------------------------------------
-- USERS  (customers, restaurant owners, delivery partners, admin)
-- ---------------------------------------------------------------
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  phone VARCHAR(20) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('customer','restaurant','delivery','admin') NOT NULL DEFAULT 'customer',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- RESTAURANTS
-- ---------------------------------------------------------------
CREATE TABLE restaurants (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_user_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  description VARCHAR(500) DEFAULT NULL,
  cuisine VARCHAR(120) DEFAULT NULL,
  address_line VARCHAR(255) NOT NULL,
  city VARCHAR(100) NOT NULL,
  lat DECIMAL(10,7) DEFAULT NULL,
  lng DECIMAL(10,7) DEFAULT NULL,
  phone VARCHAR(20) DEFAULT NULL,
  cover_image VARCHAR(255) DEFAULT NULL,
  avg_cost_for_two DECIMAL(8,2) DEFAULT 300.00,
  rating DECIMAL(2,1) NOT NULL DEFAULT 4.0,
  rating_count INT UNSIGNED NOT NULL DEFAULT 0,
  is_open TINYINT(1) NOT NULL DEFAULT 1,
  is_approved TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_city (city),
  INDEX idx_cuisine (cuisine)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- MENU CATEGORIES  (e.g. Starters, Main Course, Desserts)
-- ---------------------------------------------------------------
CREATE TABLE categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id INT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- MENU ITEMS
-- ---------------------------------------------------------------
CREATE TABLE menu_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  restaurant_id INT UNSIGNED NOT NULL,
  category_id INT UNSIGNED DEFAULT NULL,
  name VARCHAR(150) NOT NULL,
  description VARCHAR(400) DEFAULT NULL,
  price DECIMAL(8,2) NOT NULL,
  image_url VARCHAR(255) DEFAULT NULL,
  is_veg TINYINT(1) NOT NULL DEFAULT 1,
  is_available TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  INDEX idx_restaurant (restaurant_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- CUSTOMER ADDRESSES
-- ---------------------------------------------------------------
CREATE TABLE addresses (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  label VARCHAR(40) NOT NULL DEFAULT 'Home',
  address_line VARCHAR(255) NOT NULL,
  city VARCHAR(100) NOT NULL,
  lat DECIMAL(10,7) DEFAULT NULL,
  lng DECIMAL(10,7) DEFAULT NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- CART  (persisted server-side, one active restaurant at a time)
-- ---------------------------------------------------------------
CREATE TABLE cart_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  restaurant_id INT UNSIGNED NOT NULL,
  menu_item_id INT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL DEFAULT 1,
  notes VARCHAR(255) DEFAULT NULL,
  UNIQUE KEY uniq_user_item (user_id, menu_item_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
  FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- DELIVERY PARTNERS  (extra profile info for role='delivery' users)
-- ---------------------------------------------------------------
CREATE TABLE delivery_partners (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL UNIQUE,
  vehicle_type ENUM('bike','scooter','bicycle','car') NOT NULL DEFAULT 'bike',
  is_available TINYINT(1) NOT NULL DEFAULT 1,
  current_lat DECIMAL(10,7) DEFAULT NULL,
  current_lng DECIMAL(10,7) DEFAULT NULL,
  rating DECIMAL(2,1) NOT NULL DEFAULT 5.0,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- ORDERS
-- ---------------------------------------------------------------
CREATE TABLE orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  restaurant_id INT UNSIGNED NOT NULL,
  delivery_partner_id INT UNSIGNED DEFAULT NULL,
  address_id INT UNSIGNED NOT NULL,
  status ENUM('placed','accepted','preparing','ready_for_pickup','picked_up','delivered','cancelled')
    NOT NULL DEFAULT 'placed',
  subtotal DECIMAL(9,2) NOT NULL,
  delivery_fee DECIMAL(8,2) NOT NULL DEFAULT 30.00,
  tax DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  total DECIMAL(9,2) NOT NULL,
  payment_method ENUM('cod','card','upi') NOT NULL DEFAULT 'cod',
  payment_status ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',
  delivery_instructions VARCHAR(255) DEFAULT NULL,
  cancel_reason VARCHAR(255) DEFAULT NULL,
  placed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  delivered_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id),
  FOREIGN KEY (delivery_partner_id) REFERENCES delivery_partners(id),
  FOREIGN KEY (address_id) REFERENCES addresses(id),
  INDEX idx_status (status),
  INDEX idx_user (user_id),
  INDEX idx_restaurant (restaurant_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- ORDER ITEMS  (snapshot of price/name at time of order)
-- ---------------------------------------------------------------
CREATE TABLE order_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id INT UNSIGNED NOT NULL,
  menu_item_id INT UNSIGNED DEFAULT NULL,
  name_snapshot VARCHAR(150) NOT NULL,
  price_snapshot DECIMAL(8,2) NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- ORDER STATUS HISTORY  (drives the tracking timeline)
-- ---------------------------------------------------------------
CREATE TABLE order_status_history (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id INT UNSIGNED NOT NULL,
  status VARCHAR(30) NOT NULL,
  note VARCHAR(255) DEFAULT NULL,
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- REVIEWS
-- ---------------------------------------------------------------
CREATE TABLE reviews (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id INT UNSIGNED NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  restaurant_id INT UNSIGNED NOT NULL,
  rating TINYINT UNSIGNED NOT NULL,
  comment VARCHAR(500) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ================================================================
-- SEED DATA — demo accounts + sample restaurants so the app works
-- out of the box. Password for every demo account is:  Passw0rd!
-- Hash below is password_hash('Passw0rd!', PASSWORD_DEFAULT)
-- ================================================================
INSERT INTO users (name, email, phone, password_hash, role) VALUES
('Aarav Sharma', 'customer@demo.com', '9000000001', '$2y$10$rW0bKklSz24g8xsXvHKxQuE/8El8bGxuLyg1t0vHzwCRgcr.VsD4e', 'customer'),
('Spice Route Owner', 'restaurant@demo.com', '9000000002', '$2y$10$rW0bKklSz24g8xsXvHKxQuE/8El8bGxuLyg1t0vHzwCRgcr.VsD4e', 'restaurant'),
('Ravi Kumar', 'delivery@demo.com', '9000000003', '$2y$10$rW0bKklSz24g8xsXvHKxQuE/8El8bGxuLyg1t0vHzwCRgcr.VsD4e', 'delivery'),
('Admin', 'admin@demo.com', '9000000000', '$2y$10$rW0bKklSz24g8xsXvHKxQuE/8El8bGxuLyg1t0vHzwCRgcr.VsD4e', 'admin');

INSERT INTO delivery_partners (user_id, vehicle_type, is_available) VALUES (3, 'bike', 1);

INSERT INTO restaurants (owner_user_id, name, slug, description, cuisine, address_line, city, phone, cover_image, avg_cost_for_two, rating, rating_count) VALUES
(2, 'Spice Route Kitchen', 'spice-route-kitchen', 'Home-style North Indian curries and tandoor classics.', 'North Indian, Mughlai', '12 MG Road', 'Siliguri', '9830000001', 'https://images.unsplash.com/photo-1585937421612-70a008356fbe?w=800', 350.00, 4.3, 210),
(2, 'Momo Junction', 'momo-junction', 'Steamed and fried momos, thukpa and Tibetan comfort food.', 'Tibetan, Chinese', '4 Hill Cart Road', 'Siliguri', '9830000002', 'https://images.unsplash.com/photo-1626777552726-4a6b54c97e46?w=800', 200.00, 4.5, 340),
(2, 'South Wok', 'south-wok', 'Dosas, idlis and filter coffee done the traditional way.', 'South Indian', '9 Sevoke Road', 'Siliguri', '9830000003', 'https://images.unsplash.com/photo-1589301760014-d929f3979dbc?w=800', 250.00, 4.2, 155);

INSERT INTO categories (restaurant_id, name, sort_order) VALUES
(1,'Starters',1),(1,'Main Course',2),(1,'Breads',3),
(2,'Momos',1),(2,'Soups',2),
(3,'Breakfast',1),(3,'Beverages',2);

INSERT INTO menu_items (restaurant_id, category_id, name, description, price, image_url, is_veg) VALUES
(1,1,'Paneer Tikka','Chargrilled cottage cheese with mint chutney.',220.00,'https://images.unsplash.com/photo-1567188040759-fb8a883dc6d8?w=500',1),
(1,2,'Butter Chicken','Creamy tomato curry with tender chicken.',280.00,'https://images.unsplash.com/photo-1603894584373-5ac82b2ae398?w=500',0),
(1,2,'Dal Makhani','Slow-cooked black lentils finished with cream.',180.00,'https://images.unsplash.com/photo-1546833999-b9f581a1996d?w=500',1),
(1,3,'Butter Naan','Tandoor-baked bread brushed with butter.',45.00,'https://images.unsplash.com/photo-1626132647523-66b3bf5e8f9c?w=500',1),
(2,4,'Steamed Chicken Momo (8pc)','Juicy chicken dumplings with spicy chutney.',140.00,'https://images.unsplash.com/photo-1541696490-8744a5dc0228?w=500',0),
(2,4,'Veg Fried Momo (8pc)','Crisp fried veg dumplings.',120.00,'https://images.unsplash.com/photo-1625398407796-82650a8c135f?w=500',1),
(2,5,'Thukpa','Warming noodle soup with vegetables.',150.00,'https://images.unsplash.com/photo-1547592180-85f173990554?w=500',1),
(3,6,'Masala Dosa','Crisp rice crepe with spiced potato filling.',110.00,'https://images.unsplash.com/photo-1668236543090-82eba5ee5976?w=500',1),
(3,6,'Idli Sambar (4pc)','Steamed rice cakes with lentil soup.',90.00,'https://images.unsplash.com/photo-1589301760014-d929f3979dbc?w=500',1),
(3,7,'Filter Coffee','South Indian style filter coffee.',50.00,'https://images.unsplash.com/photo-1621806851805-3e4e3c8b1a5b?w=500',1);