MokoSuiteResto/source/packages/plg_system_mokosuiteresto/sql/install.mysql.sql

CREATE TABLE IF NOT EXISTS `#__mokosuiteresto_menus` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `menu_type` ENUM('breakfast','lunch','dinner','brunch','drinks','desserts','specials','kids') NOT NULL DEFAULT 'dinner',
  `available_from` TIME DEFAULT NULL,
  `available_to` TIME DEFAULT NULL,
  `published` TINYINT NOT NULL DEFAULT 1,
  `ordering` INT NOT NULL DEFAULT 0,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteresto_menu_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `menu_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `category` VARCHAR(100) NOT NULL DEFAULT '',
  `price` DECIMAL(10,2) NOT NULL,
  `calories` SMALLINT UNSIGNED DEFAULT NULL,
  `allergens` VARCHAR(500) NOT NULL DEFAULT '',
  `dietary_tags` VARCHAR(255) NOT NULL DEFAULT '',
  `prep_time_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 15,
  `photo` VARCHAR(500) NOT NULL DEFAULT '',
  `available` TINYINT NOT NULL DEFAULT 1,
  `published` TINYINT NOT NULL DEFAULT 1,
  `ordering` INT NOT NULL DEFAULT 0,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_menu` (`menu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteresto_modifiers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `menu_item_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `modifier_type` ENUM('add_on','size','preparation','sauce','side','substitution') NOT NULL DEFAULT 'add_on',
  `price_adjustment` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `ordering` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_item` (`menu_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteresto_tables` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `table_number` VARCHAR(20) NOT NULL,
  `section` VARCHAR(100) NOT NULL DEFAULT '',
  `capacity` TINYINT UNSIGNED NOT NULL DEFAULT 4,
  `table_type` ENUM('indoor','outdoor','patio','bar','private','booth') NOT NULL DEFAULT 'indoor',
  `status` ENUM('available','occupied','reserved','cleaning','closed') NOT NULL DEFAULT 'available',
  `published` TINYINT NOT NULL DEFAULT 1,
  `ordering` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteresto_reservations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `reservation_ref` VARCHAR(20) NOT NULL,
  `customer_contact_id` INT DEFAULT NULL,
  `customer_name` VARCHAR(255) NOT NULL,
  `customer_phone` VARCHAR(50) NOT NULL DEFAULT '',
  `table_id` INT UNSIGNED DEFAULT NULL,
  `party_size` TINYINT UNSIGNED NOT NULL DEFAULT 2,
  `reservation_date` DATE NOT NULL,
  `reservation_time` TIME NOT NULL,
  `duration_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 90,
  `status` ENUM('pending','confirmed','seated','completed','cancelled','no_show') NOT NULL DEFAULT 'pending',
  `special_requests` TEXT,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_ref` (`reservation_ref`),
  KEY `idx_customer` (`customer_contact_id`),
  KEY `idx_date` (`reservation_date`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteresto_orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_ref` VARCHAR(20) NOT NULL,
  `order_type` ENUM('dine_in','takeout','delivery','catering') NOT NULL DEFAULT 'dine_in',
  `table_id` INT UNSIGNED DEFAULT NULL,
  `customer_name` VARCHAR(255) NOT NULL DEFAULT '',
  `status` ENUM('pending','preparing','ready','served','completed','cancelled') NOT NULL DEFAULT 'pending',
  `subtotal` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `tax` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `tip` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `total` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `payment_method` ENUM('cash','card','tab','comp') NOT NULL DEFAULT 'card',
  `payment_status` ENUM('pending','paid','refunded') NOT NULL DEFAULT 'pending',
  `server_name` VARCHAR(255) NOT NULL DEFAULT '',
  `notes` TEXT,
  `created` DATETIME NOT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_ref` (`order_ref`),
  KEY `idx_table` (`table_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteresto_order_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NOT NULL,
  `menu_item_id` INT UNSIGNED NOT NULL,
  `quantity` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `unit_price` DECIMAL(10,2) NOT NULL,
  `modifiers` JSON DEFAULT NULL,
  `modifiers_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `special_instructions` VARCHAR(500) NOT NULL DEFAULT '',
  `status` ENUM('pending','preparing','ready','served','cancelled') NOT NULL DEFAULT 'pending',
  PRIMARY KEY (`id`),
  KEY `idx_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteresto_inventory` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `category` ENUM('produce','meat','seafood','dairy','dry_goods','beverages','supplies','other') NOT NULL DEFAULT 'other',
  `unit` VARCHAR(50) NOT NULL DEFAULT 'each',
  `current_qty` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `reorder_level` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `unit_cost` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `supplier` VARCHAR(255) NOT NULL DEFAULT '',
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteresto_inventory_transactions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `inventory_id` INT UNSIGNED NOT NULL,
  `transaction_type` ENUM('received','used','waste','adjustment','transfer') NOT NULL DEFAULT 'received',
  `quantity` DECIMAL(10,2) NOT NULL,
  `unit_cost` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `reference` VARCHAR(255) NOT NULL DEFAULT '',
  `created` DATETIME NOT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_inventory` (`inventory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteresto_staff_shifts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `staff_name` VARCHAR(255) NOT NULL,
  `contact_id` INT DEFAULT NULL,
  `role` ENUM('server','bartender','host','cook','chef','dishwasher','manager','busser') NOT NULL DEFAULT 'server',
  `shift_date` DATE NOT NULL,
  `start_time` TIME NOT NULL,
  `end_time` TIME NOT NULL,
  `status` ENUM('scheduled','active','completed','no_show','cancelled') NOT NULL DEFAULT 'scheduled',
  `tips_earned` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_date` (`shift_date`),
  KEY `idx_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;