MokoSuiteBooking/source/packages/plg_system_mokosuitebooking/sql/install.mysql.sql

CREATE TABLE IF NOT EXISTS `#__mokosuitebooking_services` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `category` VARCHAR(100) NOT NULL DEFAULT '',
  `duration_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 60,
  `buffer_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `max_participants` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `requires_deposit` TINYINT NOT NULL DEFAULT 0,
  `deposit_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `color` VARCHAR(7) NOT NULL DEFAULT '#3788d8',
  `photo` VARCHAR(500) NOT NULL DEFAULT '',
  `published` TINYINT NOT NULL DEFAULT 1,
  `ordering` INT NOT NULL DEFAULT 0,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitebooking_staff` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `contact_id` INT DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL DEFAULT '',
  `phone` VARCHAR(50) NOT NULL DEFAULT '',
  `title` VARCHAR(100) NOT NULL DEFAULT '',
  `bio` TEXT,
  `photo` VARCHAR(500) NOT NULL DEFAULT '',
  `color` VARCHAR(7) NOT NULL DEFAULT '#3788d8',
  `max_daily_bookings` TINYINT UNSIGNED NOT NULL DEFAULT 20,
  `rating` DECIMAL(3,2) NOT NULL DEFAULT 5.00,
  `total_bookings` INT UNSIGNED NOT NULL DEFAULT 0,
  `published` TINYINT NOT NULL DEFAULT 1,
  `ordering` INT NOT NULL DEFAULT 0,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_contact` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitebooking_staff_services` (
  `staff_id` INT UNSIGNED NOT NULL,
  `service_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`staff_id`, `service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitebooking_schedules` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `staff_id` INT UNSIGNED NOT NULL,
  `location_id` INT UNSIGNED DEFAULT NULL,
  `day_of_week` TINYINT UNSIGNED NOT NULL,
  `start_time` TIME NOT NULL,
  `end_time` TIME NOT NULL,
  `is_recurring` TINYINT NOT NULL DEFAULT 1,
  `specific_date` DATE DEFAULT NULL,
  `override_type` ENUM('available','unavailable') DEFAULT NULL,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_staff` (`staff_id`),
  KEY `idx_location` (`location_id`),
  KEY `idx_day` (`day_of_week`),
  KEY `idx_date` (`specific_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitebooking_bookings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `booking_ref` VARCHAR(20) NOT NULL,
  `service_id` INT UNSIGNED NOT NULL,
  `staff_id` INT UNSIGNED DEFAULT NULL,
  `location_id` INT UNSIGNED DEFAULT NULL,
  `customer_contact_id` INT DEFAULT NULL,
  `customer_name` VARCHAR(255) NOT NULL,
  `customer_email` VARCHAR(255) NOT NULL DEFAULT '',
  `customer_phone` VARCHAR(50) NOT NULL DEFAULT '',
  `status` ENUM('pending','confirmed','checked_in','in_progress','completed','cancelled','no_show') NOT NULL DEFAULT 'pending',
  `booking_date` DATE NOT NULL,
  `start_time` TIME NOT NULL,
  `end_time` TIME NOT NULL,
  `participants` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `deposit_paid` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `payment_status` ENUM('pending','deposit_paid','paid','refunded') NOT NULL DEFAULT 'pending',
  `source` ENUM('admin','online','phone','walk_in') NOT NULL DEFAULT 'online',
  `notes` TEXT,
  `customer_notes` TEXT,
  `reminder_sent` TINYINT NOT NULL DEFAULT 0,
  `created` DATETIME NOT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_ref` (`booking_ref`),
  KEY `idx_service` (`service_id`),
  KEY `idx_staff` (`staff_id`),
  KEY `idx_customer` (`customer_contact_id`),
  KEY `idx_status` (`status`),
  KEY `idx_date` (`booking_date`),
  KEY `idx_datetime` (`booking_date`, `start_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitebooking_locations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `address` VARCHAR(500) NOT NULL DEFAULT '',
  `city` VARCHAR(100) NOT NULL DEFAULT '',
  `state` VARCHAR(100) NOT NULL DEFAULT '',
  `postal_code` VARCHAR(20) NOT NULL DEFAULT '',
  `phone` VARCHAR(50) NOT NULL DEFAULT '',
  `email` VARCHAR(255) NOT NULL DEFAULT '',
  `timezone` VARCHAR(50) NOT NULL DEFAULT 'America/New_York',
  `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 `#__mokosuitebooking_waitlist` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `service_id` INT UNSIGNED NOT NULL,
  `staff_id` INT UNSIGNED DEFAULT NULL,
  `location_id` INT UNSIGNED DEFAULT NULL,
  `customer_contact_id` INT DEFAULT NULL,
  `customer_name` VARCHAR(255) NOT NULL,
  `customer_phone` VARCHAR(50) NOT NULL DEFAULT '',
  `customer_email` VARCHAR(255) NOT NULL DEFAULT '',
  `preferred_date` DATE DEFAULT NULL,
  `preferred_time` TIME DEFAULT NULL,
  `status` ENUM('waiting','offered','booked','expired','cancelled') NOT NULL DEFAULT 'waiting',
  `position` INT UNSIGNED NOT NULL DEFAULT 0,
  `notes` TEXT,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_service` (`service_id`),
  KEY `idx_customer` (`customer_contact_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuitebooking_booking_history` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `booking_id` INT UNSIGNED NOT NULL,
  `action` ENUM('created','confirmed','rescheduled','cancelled','completed','no_show','reminder_sent') NOT NULL,
  `old_date` DATE DEFAULT NULL,
  `old_time` TIME DEFAULT NULL,
  `new_date` DATE DEFAULT NULL,
  `new_time` TIME DEFAULT NULL,
  `note` TEXT,
  `created` DATETIME NOT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_booking` (`booking_id`),
  KEY `idx_action` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;