MokoSuite/source/packages/com_mokosuite/admin/sql/install.mysql.sql

--
-- MokoSuite Helpdesk Tables
--

CREATE TABLE IF NOT EXISTS `#__mokosuite_ticket_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `alias` VARCHAR(255) NOT NULL DEFAULT '',
  `description` TEXT,
  `auto_assign_user` INT DEFAULT NULL,
  `sla_response_minutes` INT UNSIGNED NOT NULL DEFAULT 480,
  `sla_resolution_minutes` INT UNSIGNED NOT NULL DEFAULT 2880,
  `ordering` INT NOT NULL DEFAULT 0,
  `published` TINYINT NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `idx_alias` (`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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);

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);

CREATE TABLE IF NOT EXISTS `#__mokosuite_tickets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `subject` VARCHAR(512) NOT NULL,
  `body` TEXT NOT NULL,
  `status` ENUM('open','in_progress','waiting','resolved','closed') NOT NULL DEFAULT 'open',
  `status_id` INT UNSIGNED DEFAULT NULL,
  `priority` ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `priority_id` INT UNSIGNED DEFAULT NULL,
  `category_id` INT UNSIGNED DEFAULT NULL,
  `contact_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT NOT NULL DEFAULT 0,
  `assigned_to` INT DEFAULT NULL,
  `created` DATETIME NOT NULL,
  `modified` DATETIME DEFAULT NULL,
  `resolved` DATETIME DEFAULT NULL,
  `closed` DATETIME DEFAULT NULL,
  `sla_response_due` DATETIME DEFAULT NULL,
  `sla_resolution_due` DATETIME DEFAULT NULL,
  `sla_responded` TINYINT NOT NULL DEFAULT 0,
  `satisfaction_rating` TINYINT UNSIGNED DEFAULT NULL,
  `satisfaction_feedback` TEXT DEFAULT NULL,
  `satisfaction_rated_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_status_id` (`status_id`),
  KEY `idx_priority` (`priority`),
  KEY `idx_priority_id` (`priority_id`),
  KEY `idx_assigned` (`assigned_to`),
  KEY `idx_category` (`category_id`),
  KEY `idx_contact` (`contact_id`),
  KEY `idx_created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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;

CREATE TABLE IF NOT EXISTS `#__mokosuite_ticket_replies` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id` INT UNSIGNED NOT NULL,
  `user_id` INT NOT NULL DEFAULT 0,
  `body` TEXT NOT NULL,
  `is_internal` TINYINT NOT NULL DEFAULT 0,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ticket` (`ticket_id`),
  KEY `idx_created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuite_ticket_canned` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `body` TEXT NOT NULL,
  `category_id` INT UNSIGNED DEFAULT NULL,
  `ordering` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuite_ticket_attachments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id` INT UNSIGNED NOT NULL,
  `reply_id` INT UNSIGNED DEFAULT NULL,
  `filename` VARCHAR(255) NOT NULL,
  `filepath` VARCHAR(512) NOT NULL,
  `filesize` INT UNSIGNED NOT NULL DEFAULT 0,
  `mimetype` VARCHAR(100) NOT NULL DEFAULT '',
  `uploaded_by` INT NOT NULL DEFAULT 0,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ticket` (`ticket_id`),
  KEY `idx_reply` (`reply_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuite_ticket_automation` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `trigger_event` VARCHAR(50) NOT NULL DEFAULT 'ticket_created',
  `conditions` TEXT NOT NULL,
  `actions` TEXT NOT NULL,
  `behavior` ENUM('append','always_new','skip_if_open') NOT NULL DEFAULT 'append',
  `enabled` TINYINT NOT NULL DEFAULT 1,
  `ordering` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_trigger` (`trigger_event`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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;

-- Default automation rules
INSERT IGNORE INTO `#__mokosuite_ticket_automation` (`id`, `title`, `trigger_event`, `conditions`, `actions`, `enabled`, `ordering`) VALUES
(1, 'Auto-close resolved tickets after 7 days', 'scheduled', '[{"field":"status","op":"eq","value":"resolved"},{"field":"age_hours","op":"gt","value":"168"}]', '[{"type":"set_status","value":"closed"},{"type":"add_note","value":"Auto-closed after 7 days with no response."}]', 1, 1),
(2, 'Escalate urgent tickets with no response in 1 hour', 'scheduled', '[{"field":"priority","op":"eq","value":"urgent"},{"field":"sla_responded","op":"eq","value":"0"},{"field":"age_hours","op":"gt","value":"1"}]', '[{"type":"add_note","value":"SLA BREACH: Urgent ticket has no staff response after 1 hour."}]', 1, 2),
(3, 'Notify on high priority ticket creation', 'ticket_created', '[{"field":"priority","op":"in","value":"high,urgent"}]', '[{"type":"add_note","value":"High/urgent ticket created — requires immediate attention."}]', 1, 3);

-- Default categories
INSERT IGNORE INTO `#__mokosuite_ticket_categories` (`id`, `title`, `alias`, `description`, `sla_response_minutes`, `sla_resolution_minutes`, `ordering`) VALUES
(1, 'General Support', 'general-support', 'General questions and assistance', 480, 2880, 1),
(2, 'Bug Report', 'bug-report', 'Report a software bug or issue', 240, 1440, 2),
(3, 'Feature Request', 'feature-request', 'Request a new feature or enhancement', 1440, 10080, 3),
(4, 'Billing', 'billing', 'Billing, invoicing, and payment questions', 240, 1440, 4),
(5, 'Urgent / Outage', 'urgent-outage', 'Site down or critical issue', 60, 240, 5);

--
-- Privacy Guard Tables
--

CREATE TABLE IF NOT EXISTS `#__mokosuite_consent_log` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `category` VARCHAR(50) NOT NULL,
  `action` ENUM('granted','revoked') NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL DEFAULT '',
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuite_data_requests` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `type` ENUM('export','delete','anonymize') NOT NULL,
  `status` ENUM('pending','processing','completed','denied') NOT NULL DEFAULT 'pending',
  `notes` TEXT,
  `processed_by` INT DEFAULT NULL,
  `created` DATETIME NOT NULL,
  `processed` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokosuite_retention_policies` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `content_type` VARCHAR(100) NOT NULL,
  `retention_days` INT UNSIGNED NOT NULL DEFAULT 365,
  `action` ENUM('anonymize','delete','archive') NOT NULL DEFAULT 'anonymize',
  `enabled` TINYINT NOT NULL DEFAULT 1,
  `description` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default retention policies
INSERT IGNORE INTO `#__mokosuite_retention_policies` (`id`, `content_type`, `retention_days`, `action`, `enabled`, `description`) VALUES
(1, 'action_logs', 90, 'delete', 1, 'Delete action log entries older than 90 days'),
(2, 'waf_logs', 30, 'delete', 1, 'Delete WAF block logs older than 30 days'),
(3, 'sessions', 7, 'delete', 1, 'Purge expired sessions older than 7 days'),
(4, 'inactive_users', 730, 'anonymize', 0, 'Anonymize users inactive for 2 years (disabled by default)'),
(5, 'closed_tickets', 365, 'anonymize', 0, 'Anonymize closed tickets older than 1 year (disabled by default)');