-- ============================================
-- RESTOMANAGER - COMPLETE DATABASE SCHEMA
-- ============================================
-- Run this SQL to set up the entire database
-- Compatible with MySQL 5.7+ / MariaDB 10.3+
-- ============================================

-- Create database (adjust name as needed)
CREATE DATABASE IF NOT EXISTS ktqagbbg_royalretae 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

USE ktqagbbg_royalretae;

-- ============================================
-- 1. SYSTEM SETTINGS
-- ============================================
DROP TABLE IF EXISTS system_settings;
CREATE TABLE system_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    restaurant_name VARCHAR(255) NOT NULL DEFAULT 'RestoManager',
    restaurant_address TEXT,
    restaurant_phone VARCHAR(50),
    restaurant_email VARCHAR(255),
    tax_rate DECIMAL(5,2) DEFAULT 10.00,
    currency VARCHAR(10) DEFAULT 'INR',
    currency_symbol VARCHAR(10) DEFAULT '₹',
    timezone VARCHAR(50) DEFAULT 'Asia/Kolkata',
    logo_path VARCHAR(255),
    theme_color VARCHAR(20) DEFAULT '#4e73df',
    receipt_footer TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO system_settings (restaurant_name, restaurant_address, restaurant_phone, restaurant_email, currency, currency_symbol, timezone) 
VALUES ('RestoManager', '123 Restaurant Street, Food City', '+91 98765 43210', 'info@restomanager.com', 'INR', '₹', 'Asia/Kolkata');

-- ============================================
-- 2. ADMINS (Users)
-- ============================================
DROP TABLE IF EXISTS admins;
CREATE TABLE admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    avatar VARCHAR(255),
    role ENUM('Super Admin', 'Admin', 'Manager', 'Cashier', 'Kitchen Staff') DEFAULT 'Admin',
    status ENUM('Active', 'Inactive', 'Suspended') DEFAULT 'Active',
    last_login DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default admin: admin@restaurant.com / password
INSERT INTO admins (name, email, password, role, status) 
VALUES ('System Administrator', 'admin@restaurant.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Super Admin', 'Active');

-- ============================================
-- 3. CUSTOMERS
-- ============================================
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(20),
    email VARCHAR(255),
    address TEXT,
    loyalty_points INT DEFAULT 0,
    total_visits INT DEFAULT 0,
    last_visit DATE,
    notes TEXT,
    status ENUM('Active', 'Inactive', 'Blocked') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO customers (name, mobile, email, address, loyalty_points, total_visits) VALUES
('Alice Cooper', '98765-10001', 'alice@email.com', '123 Oak Street', 150, 5),
('Bob Martin', '98765-10002', 'bob@email.com', '456 Pine Avenue', 80, 3),
('Carol White', '98765-10003', 'carol@email.com', '789 Elm Drive', 200, 8),
('Daniel Lee', '98765-10004', 'daniel@email.com', '321 Maple Road', 50, 2);

-- ============================================
-- 4. RESTAURANT TABLES
-- ============================================
DROP TABLE IF EXISTS restaurant_tables;
CREATE TABLE restaurant_tables (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_no VARCHAR(20) NOT NULL UNIQUE,
    capacity INT NOT NULL DEFAULT 4,
    location VARCHAR(50) DEFAULT 'Main Hall',
    status ENUM('Available', 'Occupied', 'Reserved', 'Cleaning', 'Maintenance') DEFAULT 'Available',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO restaurant_tables (table_no, capacity, location, status) VALUES
('T01', 2, 'Window', 'Available'),
('T02', 4, 'Window', 'Available'),
('T03', 4, 'Main Hall', 'Available'),
('T04', 6, 'Main Hall', 'Available'),
('T05', 2, 'Bar Area', 'Available'),
('T06', 8, 'Private Room', 'Available'),
('T07', 4, 'Garden', 'Available'),
('T08', 2, 'Garden', 'Available'),
('T09', 4, 'Main Hall', 'Available'),
('T10', 6, 'Main Hall', 'Available');

-- ============================================
-- 5. CATEGORIES
-- ============================================
DROP TABLE IF EXISTS categories;
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(50) DEFAULT 'fa-utensils',
    display_order INT DEFAULT 0,
    status ENUM('Active', 'Inactive') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO categories (name, description, icon, display_order) VALUES
('Starters', 'Appetizers and starters', 'fa-bread-slice', 1),
('Main Course', 'Delicious main dishes', 'fa-drumstick-bite', 2),
('Desserts', 'Sweet treats', 'fa-ice-cream', 3),
('Beverages', 'Drinks and refreshments', 'fa-glass-martini', 4),
('Salads', 'Fresh and healthy salads', 'fa-leaf', 5);

-- ============================================
-- 6. MENU ITEMS
-- ============================================
DROP TABLE IF EXISTS menu;
CREATE TABLE menu (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    food_name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2) DEFAULT 0,
    image VARCHAR(255),
    prep_time INT DEFAULT 15,
    is_vegetarian BOOLEAN DEFAULT FALSE,
    is_spicy BOOLEAN DEFAULT FALSE,
    is_featured BOOLEAN DEFAULT FALSE,
    is_bestseller BOOLEAN DEFAULT FALSE,
    status ENUM('Available', 'Unavailable', 'Out of Stock') DEFAULT 'Available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO menu (category_id, food_name, description, price, cost_price, prep_time, is_vegetarian, is_featured, status) VALUES
(1, 'Caesar Salad', 'Fresh romaine lettuce with Caesar dressing', 299.00, 120.00, 10, FALSE, TRUE, 'Available'),
(1, 'Garlic Bread', 'Toasted bread with garlic butter', 149.00, 50.00, 5, TRUE, FALSE, 'Available'),
(1, 'Bruschetta', 'Grilled bread with tomato and basil', 199.00, 80.00, 8, TRUE, TRUE, 'Available'),
(2, 'Grilled Salmon', 'Fresh salmon with herbs and lemon', 599.00, 280.00, 20, FALSE, TRUE, 'Available'),
(2, 'Chicken Alfredo', 'Creamy pasta with grilled chicken', 449.00, 180.00, 15, FALSE, FALSE, 'Available'),
(2, 'Margherita Pizza', 'Classic pizza with mozzarella and basil', 349.00, 120.00, 18, TRUE, TRUE, 'Available'),
(2, 'Beef Burger', 'Premium beef patty with fresh toppings', 399.00, 160.00, 12, FALSE, TRUE, 'Available'),
(3, 'Chocolate Lava Cake', 'Warm chocolate cake with molten center', 249.00, 80.00, 15, TRUE, TRUE, 'Available'),
(3, 'Tiramisu', 'Classic Italian coffee-flavored dessert', 299.00, 100.00, 5, TRUE, FALSE, 'Available'),
(4, 'Fresh Orange Juice', 'Squeezed fresh daily', 149.00, 40.00, 2, TRUE, FALSE, 'Available'),
(4, 'Iced Coffee', 'Cold brew with milk', 199.00, 60.00, 3, TRUE, TRUE, 'Available'),
(5, 'Greek Salad', 'Feta cheese, olives, and fresh vegetables', 249.00, 100.00, 8, TRUE, FALSE, 'Available');

-- ============================================
-- 7. INVENTORY
-- ============================================
DROP TABLE IF EXISTS inventory;
CREATE TABLE inventory (
    id INT PRIMARY KEY AUTO_INCREMENT,
    item_name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    stock INT NOT NULL DEFAULT 0,
    min_stock INT NOT NULL DEFAULT 10,
    unit VARCHAR(20) DEFAULT 'pcs',
    unit_price DECIMAL(10,2) DEFAULT 0,
    supplier VARCHAR(255),
    supplier_phone VARCHAR(20),
    supplier_email VARCHAR(255),
    last_restocked DATE,
    status ENUM('In Stock', 'Low Stock', 'Out of Stock', 'Discontinued') DEFAULT 'In Stock',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO inventory (item_name, category, stock, min_stock, unit, unit_price, supplier) VALUES
('Chicken Breast', 'Meat', 50, 20, 'kg', 350.00, 'Premium Meats Co.'),
('Salmon Fillet', 'Seafood', 30, 10, 'kg', 800.00, 'Ocean Fresh Ltd.'),
('Mozzarella Cheese', 'Dairy', 40, 15, 'kg', 450.00, 'Dairy Best Farms'),
('Tomatoes', 'Vegetables', 80, 30, 'kg', 40.00, 'Green Valley Produce'),
('Basil', 'Herbs', 20, 10, 'bunch', 30.00, 'Herb Garden'),
('Flour', 'Bakery', 100, 40, 'kg', 35.00, 'Grain Masters'),
('Olive Oil', 'Pantry', 25, 10, 'L', 600.00, 'Mediterranean Imports'),
('Coffee Beans', 'Beverages', 30, 15, 'kg', 900.00, 'Roast Masters');

-- ============================================
-- 8. STAFF
-- ============================================
DROP TABLE IF EXISTS staff;
CREATE TABLE staff (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) DEFAULT 0,
    phone VARCHAR(20),
    email VARCHAR(255),
    address TEXT,
    emergency_contact VARCHAR(20),
    emergency_name VARCHAR(100),
    join_date DATE NOT NULL,
    leave_date DATE,
    status ENUM('Active', 'Inactive', 'On Leave', 'Terminated') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO staff (name, position, salary, phone, email, join_date, status) VALUES
('John Smith', 'Head Chef', 45000.00, '98765-43210', 'john@restaurant.com', '2024-01-15', 'Active'),
('Sarah Johnson', 'Sous Chef', 32000.00, '98765-43211', 'sarah@restaurant.com', '2024-02-01', 'Active'),
('Mike Davis', 'Waiter', 22000.00, '98765-43212', 'mike@restaurant.com', '2024-03-10', 'Active'),
('Emily Brown', 'Cashier', 25000.00, '98765-43213', 'emily@restaurant.com', '2024-01-20', 'Active'),
('David Wilson', 'Kitchen Helper', 18000.00, '98765-43214', 'david@restaurant.com', '2024-04-01', 'Active');

-- ============================================
-- 9. ORDERS
-- ============================================
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(20) UNIQUE,
    customer_id INT,
    table_id INT,
    order_type ENUM('Dine-in', 'Takeaway', 'Delivery', 'Online') DEFAULT 'Dine-in',
    status ENUM('Pending', 'Preparing', 'Ready', 'Served', 'Completed', 'Cancelled', 'Refunded') DEFAULT 'Pending',
    payment_status ENUM('Pending', 'Paid', 'Partial', 'Failed', 'Refunded') DEFAULT 'Pending',
    payment_method ENUM('Cash', 'Card', 'UPI', 'Wallet', 'Online', 'Split') DEFAULT 'Cash',
    subtotal DECIMAL(10,2) DEFAULT 0,
    tax DECIMAL(10,2) DEFAULT 0,
    discount DECIMAL(10,2) DEFAULT 0,
    discount_type ENUM('Fixed', 'Percentage') DEFAULT 'Fixed',
    delivery_charge DECIMAL(10,2) DEFAULT 0,
    grand_total DECIMAL(10,2) DEFAULT 0,
    amount_paid DECIMAL(10,2) DEFAULT 0,
    amount_due DECIMAL(10,2) DEFAULT 0,
    notes TEXT,
    served_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE SET NULL,
    FOREIGN KEY (served_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 10. ORDER ITEMS
-- ============================================
DROP TABLE IF EXISTS order_items;
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    menu_id INT NOT NULL,
    qty INT NOT NULL DEFAULT 1,
    price DECIMAL(10,2) NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    notes TEXT,
    status ENUM('Pending', 'Preparing', 'Ready', 'Served', 'Cancelled') DEFAULT 'Pending',
    prepared_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (menu_id) REFERENCES menu(id) ON DELETE CASCADE,
    FOREIGN KEY (prepared_by) REFERENCES staff(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 11. PAYMENTS
-- ============================================
DROP TABLE IF EXISTS payments;
CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method ENUM('Cash', 'Card', 'UPI', 'Wallet', 'Online', 'Split') NOT NULL,
    transaction_id VARCHAR(255),
    card_last4 VARCHAR(4),
    status ENUM('Success', 'Pending', 'Failed', 'Refunded') DEFAULT 'Success',
    notes TEXT,
    processed_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (processed_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 12. ACTIVITY LOGS
-- ============================================
DROP TABLE IF EXISTS activity_logs;
CREATE TABLE activity_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    details TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 13. RESERVATIONS
-- ============================================
DROP TABLE IF EXISTS reservations;
CREATE TABLE reservations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    table_id INT,
    guest_name VARCHAR(100) NOT NULL,
    guest_phone VARCHAR(20),
    guest_email VARCHAR(255),
    reservation_date DATE NOT NULL,
    reservation_time TIME NOT NULL,
    party_size INT NOT NULL DEFAULT 2,
    special_requests TEXT,
    status ENUM('Confirmed', 'Pending', 'Cancelled', 'Completed', 'No-Show') DEFAULT 'Pending',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 14. EXPENSES
-- ============================================
DROP TABLE IF EXISTS expenses;
CREATE TABLE expenses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    category VARCHAR(100) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    expense_date DATE NOT NULL,
    receipt_image VARCHAR(255),
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- INDEXES FOR PERFORMANCE
-- ============================================
CREATE INDEX idx_orders_date ON orders(created_at);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_table ON orders(table_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_menu ON order_items(menu_id);
CREATE INDEX idx_payments_order ON payments(order_id);
CREATE INDEX idx_inventory_status ON inventory(status);
CREATE INDEX idx_customers_name ON customers(name);
CREATE INDEX idx_activity_logs_user ON activity_logs(user_id);
CREATE INDEX idx_activity_logs_created ON activity_logs(created_at);

-- ============================================
-- VERIFICATION
-- ============================================
SELECT 'Database setup complete!' AS status;
SELECT CONCAT('Tables created: ', COUNT(*)) AS table_count FROM information_schema.tables WHERE table_schema = 'ktqagbbg_royalretae';