-- phpMyAdmin SQL Dump -- version 5.2.2 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Sep 09, 2025 at 10:57 AM -- Server version: 10.6.22-MariaDB -- PHP Version: 8.3.19 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: `wabotbiz_resto` -- -- -------------------------------------------------------- -- -- Table structure for table `cashier_sessions` -- CREATE TABLE `cashier_sessions` ( `session_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `start_time` timestamp NOT NULL DEFAULT current_timestamp(), `end_time` timestamp NULL DEFAULT NULL, `opening_balance` decimal(15,2) NOT NULL, `closing_balance` decimal(15,2) DEFAULT NULL, `opening_notes` text DEFAULT NULL COMMENT 'JSON breakdown of opening cash', `closing_notes` text DEFAULT NULL COMMENT 'JSON breakdown of closing cash', `calculated_revenue` decimal(15,2) DEFAULT NULL COMMENT 'Total cash payments during session', `variance` decimal(15,2) DEFAULT NULL COMMENT 'Difference: closing_balance vs (opening_balance + calculated_revenue)', `status` enum('open','closed') NOT NULL DEFAULT 'open' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `categories` -- CREATE TABLE `categories` ( `category_id` int(11) NOT NULL, `name` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `customers` -- CREATE TABLE `customers` ( `customer_id` int(11) NOT NULL, `name` varchar(100) NOT NULL, `phone` varchar(20) DEFAULT NULL, `address` text DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `menu_items` -- CREATE TABLE `menu_items` ( `item_id` int(11) NOT NULL, `name` varchar(100) NOT NULL, `description` text DEFAULT NULL, `price` decimal(10,2) NOT NULL, `category_id` int(11) DEFAULT NULL, `image_url` varchar(255) DEFAULT 'https://via.placeholder.com/400x300.png?text=Menu', `is_available` tinyint(1) DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `orders` -- CREATE TABLE `orders` ( `order_id` int(11) NOT NULL, `invoice_number` varchar(50) DEFAULT NULL, `table_id` int(11) NOT NULL, `customer_id` int(11) DEFAULT 1, `order_time` timestamp NOT NULL DEFAULT current_timestamp(), `status` enum('pending','preparing','served','paid','cancelled') NOT NULL DEFAULT 'pending', `payment_intent` varchar(50) DEFAULT NULL COMMENT 'Metode pembayaran yang diinginkan pelanggan', `total_amount` decimal(10,2) NOT NULL, `discount` decimal(10,2) NOT NULL DEFAULT 0.00, `tax` decimal(10,2) NOT NULL DEFAULT 0.00 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `order_items` -- CREATE TABLE `order_items` ( `order_item_id` int(11) NOT NULL, `order_id` int(11) NOT NULL, `item_id` int(11) NOT NULL, `quantity` int(11) NOT NULL, `price_per_item` decimal(10,2) NOT NULL, `notes` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `payments` -- CREATE TABLE `payments` ( `payment_id` int(11) NOT NULL, `order_id` int(11) NOT NULL, `payment_method` enum('cash','card','qris') NOT NULL, `amount` decimal(10,2) NOT NULL, `proof_of_payment_url` varchar(255) DEFAULT NULL, `payment_time` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `settings` -- CREATE TABLE `settings` ( `setting_key` varchar(50) NOT NULL, `setting_value` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `tables` -- CREATE TABLE `tables` ( `table_id` int(11) NOT NULL, `table_number` varchar(10) NOT NULL, `qr_code_identifier` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `user_id` int(11) NOT NULL, `username` varchar(50) NOT NULL, `password_hash` varchar(255) NOT NULL, `full_name` varchar(100) DEFAULT NULL, `role` enum('admin','cashier','kitchen') NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `users` -- INSERT INTO `users` (`user_id`, `username`, `password_hash`, `full_name`, `role`) VALUES (1, 'admin', '$2y$10$w.5z0/j5CR6RAXoSU6wltOQiSKWswycx/IP.h/lArWmey4QLFhd.K', 'Administrator', 'admin'); -- -- Indexes for dumped tables -- -- -- Indexes for table `cashier_sessions` -- ALTER TABLE `cashier_sessions` ADD PRIMARY KEY (`session_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `categories` -- ALTER TABLE `categories` ADD PRIMARY KEY (`category_id`); -- -- Indexes for table `customers` -- ALTER TABLE `customers` ADD PRIMARY KEY (`customer_id`); -- -- Indexes for table `menu_items` -- ALTER TABLE `menu_items` ADD PRIMARY KEY (`item_id`), ADD KEY `category_id` (`category_id`); -- -- Indexes for table `orders` -- ALTER TABLE `orders` ADD PRIMARY KEY (`order_id`), ADD UNIQUE KEY `invoice_number` (`invoice_number`), ADD KEY `table_id` (`table_id`), ADD KEY `orders_ibfk_2` (`customer_id`); -- -- Indexes for table `order_items` -- ALTER TABLE `order_items` ADD PRIMARY KEY (`order_item_id`), ADD KEY `order_id` (`order_id`), ADD KEY `item_id` (`item_id`); -- -- Indexes for table `payments` -- ALTER TABLE `payments` ADD PRIMARY KEY (`payment_id`), ADD KEY `order_id` (`order_id`); -- -- Indexes for table `settings` -- ALTER TABLE `settings` ADD PRIMARY KEY (`setting_key`); -- -- Indexes for table `tables` -- ALTER TABLE `tables` ADD PRIMARY KEY (`table_id`), ADD UNIQUE KEY `qr_code_identifier` (`qr_code_identifier`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`user_id`), ADD UNIQUE KEY `username` (`username`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `cashier_sessions` -- ALTER TABLE `cashier_sessions` MODIFY `session_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT for table `categories` -- ALTER TABLE `categories` MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT for table `customers` -- ALTER TABLE `customers` MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT for table `menu_items` -- ALTER TABLE `menu_items` MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7; -- -- AUTO_INCREMENT for table `orders` -- ALTER TABLE `orders` MODIFY `order_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10; -- -- AUTO_INCREMENT for table `order_items` -- ALTER TABLE `order_items` MODIFY `order_item_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19; -- -- AUTO_INCREMENT for table `payments` -- ALTER TABLE `payments` MODIFY `payment_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10; -- -- AUTO_INCREMENT for table `tables` -- ALTER TABLE `tables` MODIFY `table_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- Constraints for dumped tables -- -- -- Constraints for table `cashier_sessions` -- ALTER TABLE `cashier_sessions` ADD CONSTRAINT `cashier_sessions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`); -- -- Constraints for table `menu_items` -- ALTER TABLE `menu_items` ADD CONSTRAINT `menu_items_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`); -- -- Constraints for table `orders` -- ALTER TABLE `orders` ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`table_id`) REFERENCES `tables` (`table_id`), ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`); -- -- Constraints for table `order_items` -- ALTER TABLE `order_items` ADD CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`), ADD CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `menu_items` (`item_id`); -- -- Constraints for table `payments` -- ALTER TABLE `payments` ADD CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`); 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 */;