MokoSuiteClient/source/packages/com_mokowaas/admin/sql/install.mysql.sql

--
-- MokoWaaS Helpdesk Tables
--

CREATE TABLE IF NOT EXISTS `#__mokowaas_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 `#__mokowaas_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',
  `priority` ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `category_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,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_priority` (`priority`),
  KEY `idx_assigned` (`assigned_to`),
  KEY `idx_category` (`category_id`),
  KEY `idx_created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `#__mokowaas_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 `#__mokowaas_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 `#__mokowaas_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 DEFAULT '[]',
  `actions` TEXT NOT NULL DEFAULT '[]',
  `enabled` TINYINT NOT NULL DEFAULT 1,
  `ordering` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default automation rules
INSERT IGNORE INTO `#__mokowaas_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 `#__mokowaas_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 `#__mokowaas_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 `#__mokowaas_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 `#__mokowaas_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 `#__mokowaas_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)');