-- ============================================================
-- Dabil — Restaurant Admin Schema Additions
-- Run AFTER the main dabil_schema.sql
-- ============================================================

-- Add 'staff' / 'waiter' distinction to users role enum
ALTER TABLE `users`
  MODIFY `role` enum('super_admin','restaurant_admin','staff','waiter','customer') DEFAULT 'customer';

-- --------------------------------------------------------
-- notifications — real-time events per restaurant
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `restaurant_id` int(11) NOT NULL,
  `type` enum('qr_scan','new_order','order_ready','payment_received','low_stock') DEFAULT 'new_order',
  `title` varchar(200) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `related_id` int(11) DEFAULT NULL,   -- order_id or qr_scan id
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `restaurant_id` (`restaurant_id`),
  KEY `is_read` (`is_read`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- restaurant_tables — physical tables management
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `restaurant_tables` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `restaurant_id` int(11) NOT NULL,
  `table_number` varchar(20) NOT NULL,
  `label` varchar(100) DEFAULT NULL,     -- e.g. "Window Seat", "VIP 1"
  `capacity` int(11) DEFAULT 4,
  `qr_code_path` varchar(255) DEFAULT NULL,
  `status` enum('available','occupied','reserved') DEFAULT 'available',
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `restaurant_id` (`restaurant_id`),
  CONSTRAINT `rtables_ibfk_1` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- staff_shifts — optional shift tracking
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `staff_shifts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `restaurant_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `shift_date` date NOT NULL,
  `clock_in` datetime DEFAULT NULL,
  `clock_out` datetime DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `restaurant_id` (`restaurant_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `shifts_ibfk_1` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `shifts_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add table_id to orders for linking to restaurant_tables
ALTER TABLE `orders`
  ADD COLUMN `table_id` int(11) DEFAULT NULL AFTER `table_number`,
  ADD KEY `table_id` (`table_id`);

-- Add assigned_staff to orders
ALTER TABLE `orders`
  ADD COLUMN `assigned_to` int(11) DEFAULT NULL AFTER `table_id`,
  ADD KEY `assigned_to` (`assigned_to`);
