Jonathan Miller
b2b9ab4344
Platform: moko-platform CI / Gate 2: Unit Tests (8.1) (push) Has been cancelled
Platform: moko-platform CI / Gate 2: Unit Tests (8.2) (push) Has been cancelled
Platform: moko-platform CI / Gate 2: Unit Tests (8.3) (push) Has been cancelled
Platform: moko-platform CI / Gate 3: Self-Health Check (push) Has been cancelled
Platform: moko-platform CI / Gate 4: Governance (push) Has been cancelled
Platform: moko-platform CI / Gate 5: Template Integrity (push) Has been cancelled
Platform: moko-platform CI / CI Summary (push) Has been cancelled
Generic: Repo Health / Scripts governance (push) Has been cancelled
Generic: Repo Health / Repository health (push) Has been cancelled
Generic: Repo Health / Report Issues (push) Has been cancelled
Generic: Repo Health / Site Health (push) Has been cancelled
Generic: Repo Health / Access control (push) Has been cancelled
Universal: Auto Version Bump / Version Bump (push) Has been cancelled
Universal: Pre-Release / Build Pre-Release (${{ inputs.stability || github.ref_name }}) (push) Has been cancelled
Platform: moko-platform CI / Gate 1: Code Quality (push) Has been cancelled
Replace hardcoded ENUM status/priority with lookup tables (#__mokosuite_ticket_statuses, #__mokosuite_ticket_priorities) so admins can add custom values with colors and ordering. Integrate Joomla's native custom fields (com_mokosuite.ticket context) with a junction table linking field groups to ticket categories. Includes migration SQL to populate new columns from existing ENUM data.
86 lines
3.8 KiB
SQL
86 lines
3.8 KiB
SQL
-- Add contact link to tickets (optional FK to #__contact_details)
|
|
ALTER TABLE `#__mokosuite_tickets`
|
|
ADD COLUMN `contact_id` INT UNSIGNED DEFAULT NULL AFTER `category_id`,
|
|
ADD KEY `idx_contact` (`contact_id`);
|
|
|
|
-- Multi-assignee junction table (replaces single assigned_to column)
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuite_ticket_assignees` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`ticket_id` INT UNSIGNED NOT NULL,
|
|
`assignee_type` ENUM('user','group') NOT NULL DEFAULT 'user',
|
|
`assignee_id` INT NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_unique_assignment` (`ticket_id`, `assignee_type`, `assignee_id`),
|
|
KEY `idx_ticket` (`ticket_id`),
|
|
KEY `idx_assignee` (`assignee_type`, `assignee_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Migrate existing single-assignee data to junction table
|
|
INSERT IGNORE INTO `#__mokosuite_ticket_assignees` (`ticket_id`, `assignee_type`, `assignee_id`)
|
|
SELECT `id`, 'user', `assigned_to` FROM `#__mokosuite_tickets` WHERE `assigned_to` IS NOT NULL AND `assigned_to` > 0;
|
|
|
|
-- Customizable ticket statuses (replaces ENUM)
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuite_ticket_statuses` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`title` VARCHAR(100) NOT NULL,
|
|
`alias` VARCHAR(100) NOT NULL,
|
|
`color` VARCHAR(30) NOT NULL DEFAULT 'bg-secondary',
|
|
`is_default` TINYINT NOT NULL DEFAULT 0,
|
|
`is_closed` TINYINT NOT NULL DEFAULT 0,
|
|
`ordering` INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_alias` (`alias`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
INSERT IGNORE INTO `#__mokosuite_ticket_statuses` (`id`, `title`, `alias`, `color`, `is_default`, `is_closed`, `ordering`) VALUES
|
|
(1, 'Open', 'open', 'bg-primary', 1, 0, 1),
|
|
(2, 'In Progress', 'in_progress', 'bg-info', 0, 0, 2),
|
|
(3, 'Waiting', 'waiting', 'bg-warning text-dark', 0, 0, 3),
|
|
(4, 'Resolved', 'resolved', 'bg-success', 0, 0, 4),
|
|
(5, 'Closed', 'closed', 'bg-secondary', 0, 1, 5);
|
|
|
|
-- Customizable ticket priorities (replaces ENUM)
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuite_ticket_priorities` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`title` VARCHAR(100) NOT NULL,
|
|
`alias` VARCHAR(100) NOT NULL,
|
|
`color` VARCHAR(30) NOT NULL DEFAULT 'bg-secondary',
|
|
`is_default` TINYINT NOT NULL DEFAULT 0,
|
|
`weight` INT NOT NULL DEFAULT 0,
|
|
`ordering` INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_alias` (`alias`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
INSERT IGNORE INTO `#__mokosuite_ticket_priorities` (`id`, `title`, `alias`, `color`, `is_default`, `weight`, `ordering`) VALUES
|
|
(1, 'Low', 'low', 'bg-secondary', 0, 10, 1),
|
|
(2, 'Normal', 'normal', 'bg-primary', 1, 20, 2),
|
|
(3, 'High', 'high', 'bg-warning text-dark', 0, 30, 3),
|
|
(4, 'Urgent', 'urgent', 'bg-danger', 0, 40, 4);
|
|
|
|
-- Add INT FK columns for status/priority (coexist with ENUM during migration)
|
|
ALTER TABLE `#__mokosuite_tickets`
|
|
ADD COLUMN `status_id` INT UNSIGNED DEFAULT NULL AFTER `status`,
|
|
ADD COLUMN `priority_id` INT UNSIGNED DEFAULT NULL AFTER `priority`,
|
|
ADD KEY `idx_status_id` (`status_id`),
|
|
ADD KEY `idx_priority_id` (`priority_id`);
|
|
|
|
-- Populate new columns from existing ENUM values
|
|
UPDATE `#__mokosuite_tickets` t
|
|
JOIN `#__mokosuite_ticket_statuses` s ON s.alias = t.status
|
|
SET t.status_id = s.id
|
|
WHERE t.status_id IS NULL;
|
|
|
|
UPDATE `#__mokosuite_tickets` t
|
|
JOIN `#__mokosuite_ticket_priorities` p ON p.alias = t.priority
|
|
SET t.priority_id = p.id
|
|
WHERE t.priority_id IS NULL;
|
|
|
|
-- Junction: which Joomla field groups apply to which ticket categories
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuite_ticket_category_field_groups` (
|
|
`category_id` INT UNSIGNED NOT NULL,
|
|
`field_group_id` INT NOT NULL,
|
|
PRIMARY KEY (`category_id`, `field_group_id`),
|
|
KEY `idx_field_group` (`field_group_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|