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


CREATE TABLE restaurants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    address VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(30) DEFAULT NULL,
    email VARCHAR(120) DEFAULT NULL,
    timezone VARCHAR(60) NOT NULL DEFAULT 'Asia/Kolkata',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tables (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    table_number VARCHAR(20) NOT NULL,
    seats INT UNSIGNED NOT NULL DEFAULT 2,
    status ENUM('available','occupied','needs_cleaning') NOT NULL DEFAULT 'available',
    merged_into_table_id BIGINT UNSIGNED DEFAULT NULL,
    assigned_waiter_id BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_table_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
    CONSTRAINT fk_table_merged_into FOREIGN KEY (merged_into_table_id) REFERENCES tables(id) ON DELETE SET NULL,
    CONSTRAINT fk_table_waiter FOREIGN KEY (assigned_waiter_id) REFERENCES staff_users(id) ON DELETE SET NULL,
    UNIQUE KEY uk_rest_table_num (restaurant_id, table_number),
    INDEX idx_table_id (id),
    INDEX idx_table_status (status),
    INDEX idx_table_assigned_waiter (assigned_waiter_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    display_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_category_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
    INDEX idx_category_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE menu_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    image_path VARCHAR(255) DEFAULT NULL,
    price DECIMAL(10,2) NOT NULL,
    tags VARCHAR(255) DEFAULT NULL,
    dietary_tags VARCHAR(255) DEFAULT NULL,
    availability_state ENUM('available','out_of_stock','lunch_only','dinner_only','seasonal') NOT NULL DEFAULT 'available',
    display_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_item_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
    CONSTRAINT fk_item_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    INDEX idx_item_category (category_id),
    INDEX idx_item_active (is_active),
    INDEX idx_item_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE item_availability_schedules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    menu_item_id BIGINT UNSIGNED NOT NULL,
    day_of_week TINYINT UNSIGNED NOT NULL COMMENT '0=Sunday, 6=Saturday',
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_schedule_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE,
    INDEX idx_schedule_item (menu_item_id),
    INDEX idx_schedule_day_time (day_of_week, start_time, end_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    full_name VARCHAR(120) DEFAULT NULL,
    phone VARCHAR(25) DEFAULT NULL,
    email VARCHAR(120) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_customer_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    table_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED DEFAULT NULL,
    created_by_staff_id BIGINT UNSIGNED DEFAULT NULL,
    order_type ENUM('dine_in') NOT NULL DEFAULT 'dine_in',
    status ENUM('received','preparing','ready','served','closed','cancelled') NOT NULL DEFAULT 'received',
    special_notes VARCHAR(255) DEFAULT NULL,
    call_waiter_requested TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_order_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_table FOREIGN KEY (table_id) REFERENCES tables(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    CONSTRAINT fk_order_staff FOREIGN KEY (created_by_staff_id) REFERENCES staff_users(id) ON DELETE SET NULL,
    INDEX idx_order_id (id),
    INDEX idx_order_table_id (table_id),
    INDEX idx_order_created_at (created_at),
    INDEX idx_order_staff_user_id (created_by_staff_id),
    INDEX idx_order_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    menu_item_id BIGINT UNSIGNED NOT NULL,
    item_name_snapshot VARCHAR(150) NOT NULL,
    unit_price_snapshot DECIMAL(10,2) NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    line_total DECIMAL(10,2) NOT NULL,
    special_notes VARCHAR(255) DEFAULT NULL,
    status ENUM('received','preparing','ready','served') NOT NULL DEFAULT 'received',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_order_item_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_item_menu_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE RESTRICT,
    INDEX idx_order_items_order_id (order_id),
    INDEX idx_order_items_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE manual_charges (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    added_by_staff_id BIGINT UNSIGNED DEFAULT NULL,
    title VARCHAR(120) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_manual_charge_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_manual_charge_staff FOREIGN KEY (added_by_staff_id) REFERENCES staff_users(id) ON DELETE SET NULL,
    INDEX idx_manual_charge_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bills (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL UNIQUE,
    subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    manual_charges_total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_percent DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    service_charge_percent DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    service_charge_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    grand_total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    split_count INT UNSIGNED NOT NULL DEFAULT 1,
    is_paid TINYINT(1) NOT NULL DEFAULT 0,
    closed_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_bill_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_bill_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bill_id BIGINT UNSIGNED NOT NULL,
    paid_by_staff_id BIGINT UNSIGNED DEFAULT NULL,
    amount DECIMAL(10,2) NOT NULL,
    method ENUM('cash','card','upi','other') NOT NULL,
    reference_note VARCHAR(255) DEFAULT NULL,
    paid_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_payment_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE CASCADE,
    CONSTRAINT fk_payment_staff FOREIGN KEY (paid_by_staff_id) REFERENCES staff_users(id) ON DELETE SET NULL,
    INDEX idx_payment_bill (bill_id),
    INDEX idx_payment_paid_at (paid_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE qr_codes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    table_id BIGINT UNSIGNED NOT NULL,
    qr_data TEXT NOT NULL,
    image_path VARCHAR(255) DEFAULT NULL,
    generated_at DATETIME NOT NULL,
    created_by_staff_id BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_qr_table FOREIGN KEY (table_id) REFERENCES tables(id) ON DELETE CASCADE,
    CONSTRAINT fk_qr_staff FOREIGN KEY (created_by_staff_id) REFERENCES staff_users(id) ON DELETE SET NULL,
    INDEX idx_qr_table_id (table_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    setting_key VARCHAR(120) NOT NULL,
    setting_value VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_settings_rest FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
    UNIQUE KEY uk_setting_key (restaurant_id, setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO restaurants (id, name, address, phone, email, timezone)
VALUES (1, 'Cafe QR', 'Main Street', '+91-0000000000', 'hello@cafeqr.local', 'Asia/Kolkata');

INSERT INTO settings (restaurant_id, setting_key, setting_value) VALUES
(1, 'tax_percent', '5.00'),
(1, 'service_charge_percent', '10.00');

INSERT INTO tables (restaurant_id, table_number, seats, status) VALUES
(1, 'T1', 4, 'available'),
(1, 'T2', 4, 'available'),
(1, 'T3', 2, 'available'),
(1, 'T4', 6, 'available');

INSERT INTO categories (restaurant_id, name, display_order, is_active) VALUES
(1, 'Starters', 1, 1),
(1, 'Main Course', 2, 1),
(1, 'Beverages', 3, 1),
(1, 'Desserts', 4, 1);

INSERT INTO menu_items (restaurant_id, category_id, name, description, image_path, price, tags, dietary_tags, availability_state, display_order, is_active) VALUES
(1, 1, 'Paneer Tikka', 'Smoky grilled cottage cheese cubes.', 'assets/images/paneer_tikka.jpg', 320.00, 'spicy,chef-special', 'vegetarian,gluten-free', 'available', 1, 1),
(1, 2, 'Veg Biryani', 'Fragrant basmati rice with vegetables and spices.', 'assets/images/veg_biryani.jpg', 290.00, 'best-seller', 'vegetarian', 'available', 1, 1),
(1, 3, 'Cold Coffee', 'Blended cold coffee with cream.', 'assets/images/cold_coffee.jpg', 180.00, 'chilled', 'vegetarian', 'lunch_only', 1, 1),
(1, 4, 'Chocolate Brownie', 'Warm brownie with chocolate sauce.', 'assets/images/brownie.jpg', 220.00, 'sweet', 'vegetarian', 'seasonal', 1, 1);