MokoSuiteEvent/source/packages/plg_system_mokosuiteevent/sql/install.mysql.sql

--
-- MokoSuite Event Tables
--

CREATE TABLE IF NOT EXISTS `#__mokosuiteevent_events` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `venue_id` INT UNSIGNED DEFAULT NULL,
  `start_date` DATETIME NOT NULL,
  `end_date` DATETIME NOT NULL,
  `timezone` VARCHAR(50) NOT NULL DEFAULT 'UTC',
  `capacity` INT UNSIGNED NOT NULL DEFAULT 0,
  `registered_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` ENUM('draft','published','cancelled','completed') NOT NULL DEFAULT 'draft',
  `is_recurring` TINYINT NOT NULL DEFAULT 0,
  `recurrence_rule` VARCHAR(255) NOT NULL DEFAULT '',
  `cover_image` VARCHAR(500) NOT NULL DEFAULT '',
  `organizer_contact_id` INT DEFAULT NULL,
  `published` TINYINT NOT NULL DEFAULT 1,
  `created` DATETIME NOT NULL,
  `modified` DATETIME DEFAULT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_venue` (`venue_id`),
  KEY `idx_dates` (`start_date`, `end_date`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteevent_ticket_types` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `event_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `quantity` INT UNSIGNED NOT NULL DEFAULT 0,
  `sold` INT UNSIGNED NOT NULL DEFAULT 0,
  `max_per_order` INT UNSIGNED NOT NULL DEFAULT 10,
  `sale_start` DATETIME DEFAULT NULL,
  `sale_end` DATETIME DEFAULT NULL,
  `published` TINYINT NOT NULL DEFAULT 1,
  `ordering` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_event` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteevent_registrations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `event_id` INT UNSIGNED NOT NULL,
  `contact_id` INT DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(50) NOT NULL DEFAULT '',
  `status` ENUM('pending','confirmed','cancelled','waitlisted','checked_in') NOT NULL DEFAULT 'pending',
  `total_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `payment_status` ENUM('unpaid','paid','refunded') NOT NULL DEFAULT 'unpaid',
  `notes` TEXT,
  `registered_at` DATETIME NOT NULL,
  `checked_in_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_event` (`event_id`),
  KEY `idx_contact` (`contact_id`),
  KEY `idx_status` (`status`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteevent_tickets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `registration_id` INT UNSIGNED NOT NULL,
  `ticket_type_id` INT UNSIGNED NOT NULL,
  `qr_code` VARCHAR(100) NOT NULL DEFAULT '',
  `attendee_name` VARCHAR(255) NOT NULL DEFAULT '',
  `checked_in` TINYINT NOT NULL DEFAULT 0,
  `checked_in_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_qr` (`qr_code`),
  KEY `idx_registration` (`registration_id`),
  KEY `idx_type` (`ticket_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteevent_venues` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `address` TEXT,
  `city` VARCHAR(100) NOT NULL DEFAULT '',
  `state` VARCHAR(50) NOT NULL DEFAULT '',
  `postal_code` VARCHAR(20) NOT NULL DEFAULT '',
  `capacity` INT UNSIGNED NOT NULL DEFAULT 0,
  `amenities` TEXT,
  `contact_name` VARCHAR(255) NOT NULL DEFAULT '',
  `contact_phone` VARCHAR(50) NOT NULL DEFAULT '',
  `contact_email` VARCHAR(255) NOT NULL DEFAULT '',
  `published` TINYINT NOT NULL DEFAULT 1,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteevent_speakers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `contact_id` INT DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL DEFAULT '',
  `company` VARCHAR(255) NOT NULL DEFAULT '',
  `bio` TEXT,
  `photo` VARCHAR(500) NOT NULL DEFAULT '',
  `website` VARCHAR(500) NOT NULL DEFAULT '',
  `published` TINYINT NOT NULL DEFAULT 1,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_contact` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteevent_sponsors` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `event_id` INT UNSIGNED NOT NULL,
  `contact_id` INT DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `tier` ENUM('platinum','gold','silver','bronze','community') NOT NULL DEFAULT 'community',
  `logo` VARCHAR(500) NOT NULL DEFAULT '',
  `website` VARCHAR(500) NOT NULL DEFAULT '',
  `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `benefits` TEXT,
  `published` TINYINT NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `idx_event` (`event_id`),
  KEY `idx_tier` (`tier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteevent_sessions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `event_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `room` VARCHAR(100) NOT NULL DEFAULT '',
  `track` VARCHAR(100) NOT NULL DEFAULT '',
  `start_time` DATETIME NOT NULL,
  `end_time` DATETIME NOT NULL,
  `capacity` INT UNSIGNED NOT NULL DEFAULT 0,
  `ordering` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_event` (`event_id`),
  KEY `idx_time` (`start_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuiteevent_session_speakers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `session_id` INT UNSIGNED NOT NULL,
  `speaker_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_session_speaker` (`session_id`, `speaker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;