-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Nov 01, 2026 at 03:58 AM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `princesseats`
--

-- --------------------------------------------------------

--
-- Table structure for table `carts`
--

CREATE TABLE `carts` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `session_id` varchar(255) NOT NULL,
  `status` enum('active','checkout','completed','expired') NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `carts`
--

INSERT INTO `carts` (`id`, `user_id`, `session_id`, `status`, `created_at`, `updated_at`) VALUES
(1, 10, '4f6d22b1deb54d6ce1531492453d021e', 'checkout', '2026-10-23 10:25:10', '2026-10-23 11:38:38'),
(2, 10, '4f6d22b1deb54d6ce1531492453d021e', 'checkout', '2026-10-23 10:27:47', '2026-10-23 11:30:45'),
(3, 10, '3e31f6beba379c18b08263c379ccab94', 'checkout', '2026-10-23 11:43:51', '2026-10-23 11:44:08'),
(4, 10, 'a90f3e7bb90dc08f0f158da152f1c054', 'checkout', '2026-10-23 12:18:08', '2026-10-23 15:30:32'),
(5, 10, 'dc5e8bb7c501f8a732ac606d45e1929a', 'checkout', '2026-10-24 15:13:55', '2026-10-24 15:14:43'),
(6, 10, '6371fd13e23b0d126b644e72662f5b6a', 'checkout', '2026-10-24 22:25:44', '2026-10-29 16:56:29');

-- --------------------------------------------------------

--
-- Table structure for table `cart_items`
--

CREATE TABLE `cart_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `cart_id` int(10) UNSIGNED NOT NULL,
  `food_id` int(10) UNSIGNED NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `unit_price` decimal(10,2) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `cart_items`
--

INSERT INTO `cart_items` (`id`, `cart_id`, `food_id`, `quantity`, `unit_price`, `notes`, `created_at`, `updated_at`) VALUES
(1, 1, 6, 1, 250.00, NULL, '2026-10-23 10:25:10', '2026-10-23 10:25:10'),
(2, 2, 8, 1, 200.00, NULL, '2026-10-23 10:27:47', '2026-10-23 10:27:47'),
(3, 3, 8, 1, 200.00, NULL, '2026-10-23 11:43:51', '2026-10-23 11:43:51'),
(9, 4, 10, 2, 300.00, NULL, '2026-10-23 15:25:51', '2026-10-23 15:25:51'),
(10, 5, 8, 3, 200.00, NULL, '2026-10-24 15:13:55', '2026-10-24 15:13:55'),
(12, 6, 7, 1, 280.00, NULL, '2026-10-29 16:55:53', '2026-10-29 16:55:53');

-- --------------------------------------------------------

--
-- Table structure for table `foods`
--

CREATE TABLE `foods` (
  `id` int(10) UNSIGNED NOT NULL,
  `category_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `slug` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `is_available` tinyint(1) NOT NULL DEFAULT 1,
  `featured` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `foods`
--

INSERT INTO `foods` (`id`, `category_id`, `name`, `slug`, `description`, `price`, `image`, `is_available`, `featured`, `created_at`, `updated_at`) VALUES
(6, 1, 'adobo', 'adobo', 'sdrgrhthtrfgre', 250.00, 'uploads/foods/1792502334_880c5d131be00212ebb0.webp', 1, 0, '2026-10-20 06:18:54', '2026-10-20 06:18:54'),
(7, 1, 'kare kare', 'kare-kare', 'pwede sa lahat, kahit sa walang pera', 280.00, 'uploads/foods/1792719587_be658a42bc2c1b372ada.webp', 1, 0, '2026-10-23 09:39:47', '2026-10-23 09:39:47'),
(8, 1, 'balbacua', 'balbacua', 'marami ang sabaw at pwede pang mag dagdag if gusto pa ng extrang sabaw', 200.00, 'uploads/foods/1792719701_bbd1994ef1491fd2f826.webp', 1, 0, '2026-10-23 09:41:41', '2026-10-23 09:41:41'),
(9, 1, 'Bulalo', 'bulalo', 'maraming sahug at pwede pang skdkafkdkfwk', 250.00, 'uploads/foods/1792719762_a1c635af8bd2cb3893b1.webp', 1, 0, '2026-10-23 09:42:42', '2026-10-23 09:42:42'),
(10, 1, 'Cheken nuggets', 'cheken-nuggets', 'cheken na may konting harina and the oil that we use is from dsvkkvfkkrk', 300.00, 'uploads/foods/1792719872_d43608399a07c426f47c.webp', 1, 0, '2026-10-23 09:44:32', '2026-10-23 09:44:32');

-- --------------------------------------------------------

--
-- Table structure for table `food_categories`
--

CREATE TABLE `food_categories` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `food_categories`
--

INSERT INTO `food_categories` (`id`, `name`, `slug`, `description`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Main Course', 'main-course', 'Heavy meals and main dishes', 'active', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(2, 'Dessert', 'dessert', 'Sweet desserts', 'active', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(3, 'Drinks', 'drinks', 'Cold and hot beverages', 'active', '2026-10-16 08:50:02', '2026-10-16 08:50:02');

-- --------------------------------------------------------

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `version` varchar(255) NOT NULL,
  `class` varchar(255) NOT NULL,
  `group` varchar(255) NOT NULL,
  `namespace` varchar(255) NOT NULL,
  `time` int(11) NOT NULL,
  `batch` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `table_id` int(10) UNSIGNED DEFAULT NULL,
  `order_code` varchar(50) NOT NULL,
  `status` enum('pending','confirmed','preparing','ready','served','completed','cancelled') NOT NULL DEFAULT 'pending',
  `payment_status` enum('unpaid','paid','partial','refunded') NOT NULL DEFAULT 'unpaid',
  `total_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `order_type` enum('delivery','dine_in') NOT NULL DEFAULT 'delivery'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`id`, `user_id`, `table_id`, `order_code`, `status`, `payment_status`, `total_amount`, `notes`, `created_by`, `created_at`, `updated_at`, `order_type`) VALUES
(4, 10, 2, 'ORD-20261023073032-086E46', 'served', 'paid', 600.00, 'Customer: customer\nOrder type: dine_in\nTable: 2', NULL, '2026-10-23 15:30:32', '2026-10-23 15:34:38', 'dine_in'),
(5, 10, 17, 'ORD-20261024071443-35CD12', 'served', 'paid', 600.00, 'Customer: customer\nOrder type: dine_in\nTable: 17', NULL, '2026-10-24 15:14:43', '2026-10-24 15:26:05', 'dine_in'),
(6, NULL, NULL, 'POS-20261025075344-521D0D', 'completed', 'paid', 250.00, 'Walk-in cash sale\nCustomer: andre\nCashier notes: fdedfefef\n', 7, '2026-10-25 07:53:44', '2026-10-25 07:53:44', 'dine_in'),
(7, 10, 7, 'ORD-20261029085629-D2CDCC', 'confirmed', 'paid', 280.00, 'Customer: customer\nOrder type: dine_in\nTable: 7', NULL, '2026-10-29 16:56:29', '2026-10-29 08:58:15', 'dine_in');

-- --------------------------------------------------------

--
-- Table structure for table `order_items`
--

CREATE TABLE `order_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `food_id` int(10) UNSIGNED NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `unit_price` decimal(10,2) NOT NULL,
  `subtotal` decimal(10,2) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `order_items`
--

INSERT INTO `order_items` (`id`, `order_id`, `food_id`, `quantity`, `unit_price`, `subtotal`, `notes`, `created_at`, `updated_at`) VALUES
(4, 4, 10, 2, 300.00, 600.00, NULL, '2026-10-23 15:30:32', '2026-10-23 15:30:32'),
(5, 5, 8, 3, 200.00, 600.00, NULL, '2026-10-24 15:14:43', '2026-10-24 15:14:43'),
(6, 7, 7, 1, 280.00, 280.00, NULL, '2026-10-29 16:56:29', '2026-10-29 16:56:29');

-- --------------------------------------------------------

--
-- Table structure for table `order_status_history`
--

CREATE TABLE `order_status_history` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `old_status` varchar(50) DEFAULT NULL,
  `new_status` varchar(50) NOT NULL,
  `changed_by` int(10) UNSIGNED DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `order_status_history`
--

INSERT INTO `order_status_history` (`id`, `order_id`, `old_status`, `new_status`, `changed_by`, `notes`, `created_at`) VALUES
(1, 4, 'pending', 'confirmed', 7, 'Payment approved by cashier; sent to kitchen.', '2026-10-23 07:31:54'),
(2, 4, 'confirmed', 'preparing', 8, 'Updated by chef.', '2026-10-23 07:32:44'),
(3, 4, 'preparing', 'ready', 8, 'Updated by chef.', '2026-10-23 07:32:50'),
(4, 4, 'ready', 'served', 9, 'Marked served by waiter.', '2026-10-23 07:34:38'),
(5, 5, 'pending', 'confirmed', 7, 'Payment approved by cashier; sent to kitchen.', '2026-10-24 07:23:15'),
(6, 5, 'confirmed', 'preparing', 8, 'Updated by chef.', '2026-10-24 07:24:43'),
(7, 5, 'preparing', 'ready', 8, 'Updated by chef.', '2026-10-24 07:25:49'),
(8, 5, 'ready', 'served', 9, 'Marked served by waiter.', '2026-10-24 07:26:05'),
(9, 6, NULL, 'completed', 7, 'Walk-in cash sale recorded by cashier.', '2026-10-25 07:53:44'),
(10, 7, 'pending', 'confirmed', 7, 'Payment approved by cashier; sent to kitchen.', '2026-10-29 08:58:15');

-- --------------------------------------------------------

--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `payment_method` enum('cash','gcash','card','bank_transfer') NOT NULL DEFAULT 'cash',
  `amount` decimal(10,2) NOT NULL,
  `status` enum('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
  `transaction_reference` varchar(255) DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `payment_proof` varchar(255) DEFAULT NULL,
  `reviewed_by` int(10) UNSIGNED DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `review_note` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `payments`
--

INSERT INTO `payments` (`id`, `order_id`, `payment_method`, `amount`, `status`, `transaction_reference`, `paid_at`, `created_at`, `updated_at`, `payment_proof`, `reviewed_by`, `reviewed_at`, `review_note`) VALUES
(4, 4, 'gcash', 600.00, 'paid', '1234455', '2026-10-23 07:31:54', '2026-10-23 15:30:32', '2026-10-23 15:31:54', 'uploads/payment-proofs/1792740653_9cbce587b4b3119995a3.jpg', 7, '2026-10-23 07:31:54', NULL),
(5, 5, 'gcash', 600.00, 'paid', '09876543234', '2026-10-24 07:23:15', '2026-10-24 15:14:43', '2026-10-24 15:23:15', 'uploads/payment-proofs/1792826121_df89878db48ef1ec8bd5.jpg', 7, '2026-10-24 07:23:15', NULL),
(6, 6, 'cash', 250.00, 'paid', NULL, '2026-10-25 07:53:44', '2026-10-25 15:53:45', '2026-10-25 15:53:45', NULL, 7, '2026-10-25 07:53:44', NULL),
(7, 7, 'cash', 280.00, 'paid', NULL, '2026-10-29 08:58:15', '2026-10-29 16:56:29', '2026-10-29 16:58:15', NULL, 7, '2026-10-29 08:58:15', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `restaurant_tables`
--

CREATE TABLE `restaurant_tables` (
  `id` int(10) UNSIGNED NOT NULL,
  `table_number` int(11) NOT NULL,
  `capacity` int(11) NOT NULL,
  `location` varchar(100) DEFAULT NULL,
  `status` enum('available','reserved','occupied','closed') NOT NULL DEFAULT 'available',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `restaurant_tables`
--

INSERT INTO `restaurant_tables` (`id`, `table_number`, `capacity`, `location`, `status`, `created_at`, `updated_at`) VALUES
(1, 1, 2, 'Indoor', 'available', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(2, 2, 2, 'Indoor', 'available', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(3, 3, 4, 'Indoor', 'reserved', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(4, 4, 4, 'Indoor', 'available', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(5, 5, 6, 'Outdoor', 'occupied', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(6, 6, 2, 'Outdoor', 'available', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(7, 7, 4, 'Indoor', 'available', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(8, 8, 8, 'VIP', 'available', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(9, 9, 4, 'Indoor', 'available', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(10, 10, 2, 'Outdoor', 'available', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(11, 11, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(12, 12, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(13, 13, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(14, 14, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(15, 15, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(16, 16, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(17, 17, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(18, 18, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(19, 19, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(20, 20, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(21, 21, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(22, 22, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(23, 23, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(24, 24, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(25, 25, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(26, 26, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(27, 27, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(28, 28, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(29, 29, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(30, 30, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(31, 31, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(32, 32, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(33, 33, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(34, 34, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(35, 35, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(36, 36, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(37, 37, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(38, 38, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(39, 39, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(40, 40, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(41, 41, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(42, 42, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(43, 43, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(44, 44, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(45, 45, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(46, 46, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(47, 47, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(48, 48, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(49, 49, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08'),
(50, 50, 4, 'Indoor', 'available', '2026-10-23 12:26:08', '2026-10-23 12:26:08');

-- --------------------------------------------------------

--
-- Table structure for table `staff_assignments`
--

CREATE TABLE `staff_assignments` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `role` enum('cashier','chef','waiter','manager') NOT NULL DEFAULT 'waiter',
  `table_id` int(10) UNSIGNED DEFAULT NULL,
  `assignment_date` date NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `system_settings`
--

CREATE TABLE `system_settings` (
  `id` int(10) UNSIGNED NOT NULL,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `system_settings`
--

INSERT INTO `system_settings` (`id`, `setting_key`, `setting_value`, `description`, `created_at`, `updated_at`) VALUES
(1, 'restaurant_name', 'PrincessEats', 'Restaurant name', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(2, 'currency', 'PHP', 'System currency', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(3, 'tax_rate', '0.12', 'VAT rate', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(4, 'opening_hours', '08:00-22:00', 'Restaurant operating hours', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(5, 'contact_number', '09123456789', 'Support contact number', '2026-10-16 08:50:02', '2026-10-16 08:50:02'),
(6, 'gcash_account_name', 'muna hksalsd', 'Restaurant GCash account holder name', '2026-10-23 11:43:18', '2026-10-23 11:43:18'),
(7, 'gcash_account_number', '09876543234', 'Restaurant GCash account number', '2026-10-23 11:43:18', '2026-10-23 11:43:18');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `password` varchar(255) NOT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `role` enum('admin','cashier','chef','waiter','customer') NOT NULL DEFAULT 'customer',
  `status` enum('active','inactive','banned') NOT NULL DEFAULT 'active',
  `avatar` varchar(255) DEFAULT NULL,
  `remember_token` varchar(255) DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`, `email`, `password`, `phone`, `role`, `status`, `avatar`, `remember_token`, `last_login_at`, `created_at`, `updated_at`, `deleted_at`) VALUES
(6, 'Admin User', 'admin@princesseats.com', '$2y$10$DWHbz7sMr3higgDrDky8ruuH/kInwHe7ungKIwYQsh5uoTHGIp42O', NULL, 'admin', 'active', NULL, NULL, NULL, '2026-10-20 04:55:31', '2026-10-20 04:55:31', NULL),
(7, 'muda', 'muda@gmail.com', '$2y$10$UrtcNiSlFczLbkpNZsnyeO1gFgDCiGljpcZQTQCPAWjvU0hmpYvFW', NULL, 'cashier', 'active', NULL, NULL, NULL, '2026-10-20 05:31:36', '2026-10-20 05:31:36', NULL),
(8, 'chef', 'chef@gmail.com', '$2y$10$jNmS.2fOpA/Qji/0PhaVBOmsEFi6kP3UuXTMGsEmfmPwr7.ptKZWq', NULL, 'chef', 'active', NULL, NULL, NULL, '2026-10-20 05:45:43', '2026-10-20 05:45:43', NULL),
(9, 'waiter', 'waiter@gmail.com', '$2y$10$PXQ5IbuTG8OGISo7Xg0XbupSAAoZg3zNR.kv0Tqbl1HN6D1HWgEqy', NULL, 'waiter', 'active', NULL, NULL, NULL, '2026-10-20 05:47:31', '2026-10-20 05:47:31', NULL),
(10, 'customer', 'customer@gmail.com', '$2y$10$FAAtgDJ89UvcPTvTZvOxh.8jDdK1DUEvy5CXdMTAHpW6bBwcwQMHa', NULL, 'customer', 'active', NULL, NULL, NULL, '2026-10-20 06:00:08', '2026-10-20 06:00:08', NULL);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `carts`
--
ALTER TABLE `carts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_cart_user` (`user_id`);

--
-- Indexes for table `cart_items`
--
ALTER TABLE `cart_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_cart_items_cart` (`cart_id`),
  ADD KEY `idx_cart_items_food` (`food_id`);

--
-- Indexes for table `foods`
--
ALTER TABLE `foods`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD KEY `idx_category_id` (`category_id`);

--
-- Indexes for table `food_categories`
--
ALTER TABLE `food_categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`);

--
-- Indexes for table `migrations`
--
ALTER TABLE `migrations`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `order_code` (`order_code`),
  ADD KEY `idx_orders_user` (`user_id`),
  ADD KEY `idx_orders_table` (`table_id`),
  ADD KEY `idx_orders_created_by` (`created_by`);

--
-- Indexes for table `order_items`
--
ALTER TABLE `order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_order_items_order` (`order_id`),
  ADD KEY `idx_order_items_food` (`food_id`);

--
-- Indexes for table `order_status_history`
--
ALTER TABLE `order_status_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_order_history_order` (`order_id`),
  ADD KEY `idx_order_history_changed_by` (`changed_by`);

--
-- Indexes for table `payments`
--
ALTER TABLE `payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_payments_order` (`order_id`),
  ADD KEY `payments_reviewed_by_foreign` (`reviewed_by`);

--
-- Indexes for table `restaurant_tables`
--
ALTER TABLE `restaurant_tables`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `table_number` (`table_number`);

--
-- Indexes for table `staff_assignments`
--
ALTER TABLE `staff_assignments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_staff_user` (`user_id`),
  ADD KEY `idx_staff_table` (`table_id`);

--
-- Indexes for table `system_settings`
--
ALTER TABLE `system_settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `setting_key` (`setting_key`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `carts`
--
ALTER TABLE `carts`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `cart_items`
--
ALTER TABLE `cart_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `foods`
--
ALTER TABLE `foods`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `food_categories`
--
ALTER TABLE `food_categories`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `migrations`
--
ALTER TABLE `migrations`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `order_items`
--
ALTER TABLE `order_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `order_status_history`
--
ALTER TABLE `order_status_history`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `payments`
--
ALTER TABLE `payments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `restaurant_tables`
--
ALTER TABLE `restaurant_tables`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=51;

--
-- AUTO_INCREMENT for table `staff_assignments`
--
ALTER TABLE `staff_assignments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `system_settings`
--
ALTER TABLE `system_settings`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `carts`
--
ALTER TABLE `carts`
  ADD CONSTRAINT `fk_carts_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `cart_items`
--
ALTER TABLE `cart_items`
  ADD CONSTRAINT `fk_cart_items_cart` FOREIGN KEY (`cart_id`) REFERENCES `carts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_cart_items_food` FOREIGN KEY (`food_id`) REFERENCES `foods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `foods`
--
ALTER TABLE `foods`
  ADD CONSTRAINT `fk_foods_category` FOREIGN KEY (`category_id`) REFERENCES `food_categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `fk_orders_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_orders_table` FOREIGN KEY (`table_id`) REFERENCES `restaurant_tables` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `order_items`
--
ALTER TABLE `order_items`
  ADD CONSTRAINT `fk_order_items_food` FOREIGN KEY (`food_id`) REFERENCES `foods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_order_items_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `order_status_history`
--
ALTER TABLE `order_status_history`
  ADD CONSTRAINT `fk_order_history_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_order_history_user` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `payments`
--
ALTER TABLE `payments`
  ADD CONSTRAINT `fk_payments_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `payments_reviewed_by_foreign` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `staff_assignments`
--
ALTER TABLE `staff_assignments`
  ADD CONSTRAINT `fk_staff_table` FOREIGN KEY (`table_id`) REFERENCES `restaurant_tables` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_staff_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
