USE restpo;

-- Expenses table for daily expense tracking
CREATE TABLE IF NOT EXISTS `expenses` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `restaurant_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `description` TEXT NULL,
  `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `expense_date` DATE NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- KDS (Kitchen Display System) order tracking
ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `kitchen_status` ENUM('queued','preparing','ready','served') DEFAULT 'queued';
ALTER TABLE `order_items` ADD COLUMN IF NOT EXISTS `kitchen_note` TEXT NULL;
