-- Create tables
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','staff') NOT NULL DEFAULT 'staff',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
);

-- Insert default users (password = `admin` and `staff` hashed with password_hash)
INSERT INTO `users` (`username`, `password`, `role`) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin'), -- password: admin
('staff', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'staff'); -- password: staff

CREATE TABLE IF NOT EXISTS `menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text,
  `price` decimal(10,2) NOT NULL,
  `category` varchar(50) DEFAULT NULL,
  `dietary_tags` varchar(200) DEFAULT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `available` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `items` text NOT NULL, -- JSON encoded
  `total` decimal(10,2) NOT NULL,
  `status` enum('pending','preparing','ready','completed') DEFAULT 'pending',
  `order_type` enum('pickup','delivery') DEFAULT 'pickup',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `tables` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `table_number` varchar(10) NOT NULL,
  `capacity` int(11) NOT NULL,
  `status` enum('free','occupied','reserved') DEFAULT 'free',
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `reservations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(100) NOT NULL,
  `party_size` int(11) NOT NULL,
  `date_time` datetime NOT NULL,
  `table_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `table_id` (`table_id`)
);

CREATE TABLE IF NOT EXISTS `inventory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `quantity` decimal(10,2) NOT NULL,
  `threshold` decimal(10,2) DEFAULT 0,
  `unit` varchar(20) DEFAULT 'pcs',
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `total_visits` int(11) DEFAULT 0,
  `favorite_items` text,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `promotions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `type` enum('percentage','fixed') DEFAULT 'percentage',
  `value` decimal(10,2) NOT NULL,
  `expiry_date` date DEFAULT NULL,
  `usage_limit` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
);

CREATE TABLE IF NOT EXISTS `website_content` (
  `id` int(11) NOT NULL DEFAULT 1,
  `homepage_hero` text,
  `about_text` text,
  `contact_address` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(50) DEFAULT NULL,
  `contact_hours` text,
  `footer_links` text, -- JSON
  PRIMARY KEY (`id`)
);

-- Insert default website content
INSERT INTO `website_content` (`id`, `homepage_hero`, `about_text`, `contact_address`, `contact_phone`, `contact_hours`, `footer_links`) VALUES
(1, 'Welcome to Rugsan East African Cuisine', 'Our story is one of passion and love for East African flavors...', '2424 13th Ave S #101, Fargo, ND 58103', '(701) 293-1820', 'Mon–Wed 9am–9pm, Thu–Fri 9am–10pm, Sat–Sun 10am–9pm', '[{"label":"Facebook","url":"#"},{"label":"Instagram","url":"#"}]');

-- Insert sample menu items
INSERT INTO `menu` (`name`, `description`, `price`, `category`, `dietary_tags`, `available`) VALUES
('Pita Bread with Beef Curry', 'Soft pita bread paired with rich spiced beef curry', 13.08, 'Breakfast', 'Halal', 1),
('Goat Meat with Rice', 'Tender goat meat with rice, lettuce & white sauce', 25.99, 'Lunch/Dinner', 'Halal, Gluten-free', 1),
('Sports Kaykay Chicken Curry', 'Large kaykay portion topped with chicken curry', 36.94, 'KayKay', 'Halal', 1);

-- Insert sample tables
INSERT INTO `tables` (`table_number`, `capacity`, `status`) VALUES
('1', 2, 'free'), ('2', 4, 'free'), ('3', 6, 'free'), ('4', 2, 'free');