-- Kenyan POS Database Schema
-- This script creates all necessary tables for the Laravel backend

USE kenyan_pos;

-- Drop tables if they exist (in reverse order due to foreign keys)
DROP TABLE IF EXISTS stock_movements;
DROP TABLE IF EXISTS menu_item_ingredients;
DROP TABLE IF EXISTS inventory_items;
DROP TABLE IF EXISTS kitchen_tickets;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS tables;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS branches;
DROP TABLE IF EXISTS restaurants;
DROP TABLE IF EXISTS jobs;
DROP TABLE IF EXISTS cache;
DROP TABLE IF EXISTS migrations;

-- Create migrations table
CREATE TABLE migrations (
    id int unsigned NOT NULL AUTO_INCREMENT,
    migration varchar(255) NOT NULL,
    batch int NOT NULL,
    PRIMARY KEY (id)
);

-- Create cache table
CREATE TABLE cache (
    `key` varchar(255) NOT NULL,
    value mediumtext NOT NULL,
    expiration int NOT NULL,
    PRIMARY KEY (`key`)
);

-- Create jobs table
CREATE TABLE jobs (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    queue varchar(255) NOT NULL,
    payload longtext NOT NULL,
    attempts tinyint unsigned NOT NULL,
    reserved_at int unsigned DEFAULT NULL,
    available_at int unsigned NOT NULL,
    created_at int unsigned NOT NULL,
    PRIMARY KEY (id),
    KEY jobs_queue_index (queue)
);

-- Create restaurants table
CREATE TABLE restaurants (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    slug varchar(255) NOT NULL UNIQUE,
    address text NOT NULL,
    phone varchar(255) NOT NULL,
    email varchar(255) NOT NULL,
    logo varchar(255) DEFAULT NULL,
    vat_rate decimal(5,2) NOT NULL DEFAULT 16.00,
    currency varchar(3) NOT NULL DEFAULT 'KES',
    settings json DEFAULT NULL,
    is_active tinyint(1) NOT NULL DEFAULT 1,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id)
);

-- Create branches table
CREATE TABLE branches (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    restaurant_id bigint unsigned NOT NULL,
    name varchar(255) NOT NULL,
    slug varchar(255) NOT NULL,
    address text NOT NULL,
    phone varchar(255) NOT NULL,
    manager_name varchar(255) DEFAULT NULL,
    is_active tinyint(1) NOT NULL DEFAULT 1,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY branches_restaurant_id_slug_unique (restaurant_id, slug),
    CONSTRAINT branches_restaurant_id_foreign FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
);

-- Create users table
CREATE TABLE users (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    restaurant_id bigint unsigned NOT NULL,
    branch_id bigint unsigned DEFAULT NULL,
    name varchar(255) NOT NULL,
    email varchar(255) NOT NULL UNIQUE,
    email_verified_at timestamp NULL DEFAULT NULL,
    password varchar(255) NOT NULL,
    phone varchar(255) DEFAULT NULL,
    role enum('admin','manager','cashier','waiter','kitchen') NOT NULL DEFAULT 'cashier',
    permissions json DEFAULT NULL,
    is_active tinyint(1) NOT NULL DEFAULT 1,
    last_login_at timestamp NULL DEFAULT NULL,
    remember_token varchar(100) DEFAULT NULL,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT users_restaurant_id_foreign FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE,
    CONSTRAINT users_branch_id_foreign FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE SET NULL
);

-- Create customers table
CREATE TABLE customers (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    restaurant_id bigint unsigned NOT NULL,
    name varchar(255) NOT NULL,
    email varchar(255) DEFAULT NULL,
    phone varchar(255) NOT NULL,
    address text DEFAULT NULL,
    date_of_birth date DEFAULT NULL,
    customer_type enum('regular','vip','new') NOT NULL DEFAULT 'new',
    total_orders int NOT NULL DEFAULT 0,
    total_spent decimal(10,2) NOT NULL DEFAULT 0.00,
    last_order_at timestamp NULL DEFAULT NULL,
    notes text DEFAULT NULL,
    is_active tinyint(1) NOT NULL DEFAULT 1,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT customers_restaurant_id_foreign FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
);

-- Create tables table
CREATE TABLE `tables` (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    branch_id bigint unsigned NOT NULL,
    name varchar(255) NOT NULL,
    section varchar(255) NOT NULL DEFAULT 'main',
    capacity int NOT NULL DEFAULT 4,
    status enum('available','occupied','reserved','maintenance') NOT NULL DEFAULT 'available',
    qr_code varchar(255) DEFAULT NULL,
    is_active tinyint(1) NOT NULL DEFAULT 1,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY tables_branch_id_name_unique (branch_id, name),
    CONSTRAINT tables_branch_id_foreign FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE CASCADE
);

-- Create categories table
CREATE TABLE categories (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    restaurant_id bigint unsigned NOT NULL,
    name varchar(255) NOT NULL,
    slug varchar(255) NOT NULL,
    description text DEFAULT NULL,
    image varchar(255) DEFAULT NULL,
    color varchar(7) NOT NULL DEFAULT '#3B82F6',
    sort_order int NOT NULL DEFAULT 0,
    is_active tinyint(1) NOT NULL DEFAULT 1,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY categories_restaurant_id_slug_unique (restaurant_id, slug),
    CONSTRAINT categories_restaurant_id_foreign FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
);

-- Create menu_items table
CREATE TABLE menu_items (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    restaurant_id bigint unsigned NOT NULL,
    category_id bigint unsigned NOT NULL,
    name varchar(255) NOT NULL,
    slug varchar(255) NOT NULL,
    description text DEFAULT NULL,
    price decimal(8,2) NOT NULL,
    cost_price decimal(8,2) DEFAULT NULL,
    image varchar(255) DEFAULT NULL,
    prep_time int NOT NULL DEFAULT 15,
    kitchen_stations json DEFAULT NULL,
    allergens json DEFAULT NULL,
    nutritional_info json DEFAULT NULL,
    is_available tinyint(1) NOT NULL DEFAULT 1,
    is_featured tinyint(1) NOT NULL DEFAULT 0,
    sort_order int NOT NULL DEFAULT 0,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY menu_items_restaurant_id_slug_unique (restaurant_id, slug),
    CONSTRAINT menu_items_restaurant_id_foreign FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE,
    CONSTRAINT menu_items_category_id_foreign FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE
);

-- Create orders table
CREATE TABLE orders (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    restaurant_id bigint unsigned NOT NULL,
    branch_id bigint unsigned NOT NULL,
    user_id bigint unsigned NOT NULL,
    customer_id bigint unsigned DEFAULT NULL,
    table_id bigint unsigned DEFAULT NULL,
    order_number varchar(255) NOT NULL UNIQUE,
    type enum('dine_in','takeaway','delivery') NOT NULL DEFAULT 'dine_in',
    status enum('pending','confirmed','preparing','ready','served','cancelled') NOT NULL DEFAULT 'pending',
    subtotal decimal(10,2) NOT NULL DEFAULT 0.00,
    tax_amount decimal(10,2) NOT NULL DEFAULT 0.00,
    discount_amount decimal(10,2) NOT NULL DEFAULT 0.00,
    total_amount decimal(10,2) NOT NULL DEFAULT 0.00,
    notes text DEFAULT NULL,
    special_instructions text DEFAULT NULL,
    estimated_time int DEFAULT NULL,
    served_at timestamp NULL DEFAULT NULL,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT orders_restaurant_id_foreign FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE,
    CONSTRAINT orders_branch_id_foreign FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE CASCADE,
    CONSTRAINT orders_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT orders_customer_id_foreign FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE SET NULL,
    CONSTRAINT orders_table_id_foreign FOREIGN KEY (table_id) REFERENCES `tables` (id) ON DELETE SET NULL
);

-- Create order_items table
CREATE TABLE order_items (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    order_id bigint unsigned NOT NULL,
    menu_item_id bigint unsigned NOT NULL,
    quantity int NOT NULL DEFAULT 1,
    unit_price decimal(8,2) NOT NULL,
    total_price decimal(8,2) NOT NULL,
    special_instructions text DEFAULT NULL,
    status enum('pending','preparing','ready','served') NOT NULL DEFAULT 'pending',
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT order_items_order_id_foreign FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
    CONSTRAINT order_items_menu_item_id_foreign FOREIGN KEY (menu_item_id) REFERENCES menu_items (id) ON DELETE CASCADE
);

-- Create payments table
CREATE TABLE payments (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    order_id bigint unsigned NOT NULL,
    method enum('cash','card','mpesa','bank_transfer') NOT NULL,
    amount decimal(10,2) NOT NULL,
    reference varchar(255) DEFAULT NULL,
    status enum('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending',
    transaction_id varchar(255) DEFAULT NULL,
    mpesa_receipt varchar(255) DEFAULT NULL,
    processed_at timestamp NULL DEFAULT NULL,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT payments_order_id_foreign FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE
);

-- Create kitchen_tickets table
CREATE TABLE kitchen_tickets (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    order_id bigint unsigned NOT NULL,
    order_item_id bigint unsigned DEFAULT NULL,
    station varchar(255) NOT NULL DEFAULT 'main',
    priority enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    status enum('pending','acknowledged','preparing','ready','served') NOT NULL DEFAULT 'pending',
    estimated_time int DEFAULT NULL,
    special_instructions text DEFAULT NULL,
    acknowledged_at timestamp NULL DEFAULT NULL,
    started_at timestamp NULL DEFAULT NULL,
    completed_at timestamp NULL DEFAULT NULL,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT kitchen_tickets_order_id_foreign FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
    CONSTRAINT kitchen_tickets_order_item_id_foreign FOREIGN KEY (order_item_id) REFERENCES order_items (id) ON DELETE CASCADE
);

-- Create inventory_items table
CREATE TABLE inventory_items (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    restaurant_id bigint unsigned NOT NULL,
    name varchar(255) NOT NULL,
    category varchar(255) NOT NULL,
    unit varchar(50) NOT NULL DEFAULT 'pcs',
    current_stock decimal(10,2) NOT NULL DEFAULT 0.00,
    minimum_stock decimal(10,2) NOT NULL DEFAULT 0.00,
    maximum_stock decimal(10,2) NOT NULL DEFAULT 0.00,
    cost_per_unit decimal(8,2) NOT NULL DEFAULT 0.00,
    supplier varchar(255) DEFAULT NULL,
    expiry_date date DEFAULT NULL,
    location varchar(255) DEFAULT NULL,
    barcode varchar(255) DEFAULT NULL,
    is_active tinyint(1) NOT NULL DEFAULT 1,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT inventory_items_restaurant_id_foreign FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
);

-- Create stock_movements table
CREATE TABLE stock_movements (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    inventory_item_id bigint unsigned NOT NULL,
    user_id bigint unsigned NOT NULL,
    type enum('in','out','adjustment','waste','transfer') NOT NULL,
    quantity decimal(10,2) NOT NULL,
    unit_cost decimal(8,2) DEFAULT NULL,
    total_cost decimal(10,2) DEFAULT NULL,
    reference varchar(255) DEFAULT NULL,
    reason varchar(255) DEFAULT NULL,
    notes text DEFAULT NULL,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT stock_movements_inventory_item_id_foreign FOREIGN KEY (inventory_item_id) REFERENCES inventory_items (id) ON DELETE CASCADE,
    CONSTRAINT stock_movements_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

-- Create menu_item_ingredients table
CREATE TABLE menu_item_ingredients (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    menu_item_id bigint unsigned NOT NULL,
    inventory_item_id bigint unsigned NOT NULL,
    quantity decimal(8,3) NOT NULL,
    unit varchar(50) NOT NULL,
    created_at timestamp NULL DEFAULT NULL,
    updated_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT menu_item_ingredients_menu_item_id_foreign FOREIGN KEY (menu_item_id) REFERENCES menu_items (id) ON DELETE CASCADE,
    CONSTRAINT menu_item_ingredients_inventory_item_id_foreign FOREIGN KEY (inventory_item_id) REFERENCES inventory_items (id) ON DELETE CASCADE
);

-- Insert migration records
INSERT INTO migrations (migration, batch) VALUES
('0001_01_01_000000_create_users_table', 1),
('0001_01_01_000001_create_cache_table', 1),
('0001_01_01_000002_create_jobs_table', 1),
('2024_01_01_000001_create_restaurants_table', 1),
('2024_01_01_000002_create_branches_table', 1),
('2024_01_01_000003_create_users_table', 1),
('2024_01_01_000004_create_tables_table', 1),
('2024_01_01_000005_create_categories_table', 1),
('2024_01_01_000006_create_menu_items_table', 1),
('2024_01_01_000007_create_orders_table', 1),
('2024_01_01_000008_create_order_items_table', 1),
('2024_01_01_000009_create_payments_table', 1),
('2024_01_01_000010_create_kitchen_tickets_table', 1),
('2024_01_01_000011_create_inventory_items_table', 1),
('2024_01_01_000012_create_stock_movements_table', 1),
('2024_01_01_000013_create_menu_item_ingredients_table', 1),
('2024_01_01_000014_create_customers_table', 1);