-- =====================================================
-- ?? GFI Helpdesk 4.93 ? 4.96.5 (Safe upgrade script)
-- =====================================================

-- ??????? ?? charset
ALTER DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ======================
-- swsettingsgroups
-- ======================
-- ??? PRIMARY KEY ????? ????? ??
SET @has_pk := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
	  WHERE TABLE_NAME='swsettingsgroups' AND CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_SCHEMA=DATABASE());
SET @sql := IF(@has_pk=0, 'ALTER TABLE swsettingsgroups ADD PRIMARY KEY (sgroupid);', 'SELECT "PRIMARY KEY already exists";');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ??????? ?? auto_increment
ALTER TABLE `swsettingsgroups`
  MODIFY `sgroupid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

-- ======================
-- swapplogs
-- ======================
DROP TABLE IF EXISTS `swapplogs`;
CREATE TABLE `swapplogs` (
	  `applogid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	  `appname` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
	  `logtype` SMALLINT NOT NULL DEFAULT '0',
	  `dateline` INT NOT NULL DEFAULT '0',
	  PRIMARY KEY (`applogid`),
	  KEY `idx_appname_logtype` (`appname`,`logtype`),
	  KEY `idx_logtype` (`logtype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ======================
-- swapplogdata
-- ======================
CREATE TABLE IF NOT EXISTS `swapplogdata` (
	  `applogdataid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	  `applogid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	  `contents` MEDIUMTEXT COLLATE utf8mb4_unicode_ci NOT NULL,
	  PRIMARY KEY (`applogdataid`),
	  KEY `applogid` (`applogid`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

	-- ======================
-- swtickets (add columns if missing)
-- ======================
SET @cols := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
	  WHERE TABLE_NAME='swtickets' AND COLUMN_NAME='hasattachments');
SET @sql := IF(@cols=0, 
	  'ALTER TABLE swtickets ADD COLUMN hasattachments TINYINT(1) NOT NULL DEFAULT 0, ADD COLUMN editedstaffid INT(10) UNSIGNED DEFAULT NULL, ADD COLUMN editeddateline INT(10) UNSIGNED DEFAULT NULL, ADD COLUMN isthirdparty TINYINT(1) NOT NULL DEFAULT 0;', 
	  'SELECT "Columns already exist";');
	PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

	-- ======================
-- swusers
-- ======================
SET @cols := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
	  WHERE TABLE_NAME='swusers' AND COLUMN_NAME='isvalidated');
SET @sql := IF(@cols=0, 
	  'ALTER TABLE swusers ADD COLUMN isvalidated TINYINT(1) NOT NULL DEFAULT 1 AFTER isverified, ADD COLUMN validationhash VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER isvalidated;', 
	  'SELECT "swusers columns exist";');
	PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

	-- ======================
-- swstaff
-- ======================
SET @cols := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
	  WHERE TABLE_NAME='swstaff' AND COLUMN_NAME='isenabled');
SET @sql := IF(@cols=0, 
	  'ALTER TABLE swstaff ADD COLUMN isenabled TINYINT(1) NOT NULL DEFAULT 1 AFTER isadmin, ADD COLUMN lastloginip VARCHAR(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER isenabled;', 
	  'SELECT "swstaff columns exist";');
	PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

	-- ======================
-- UTF8MB4 conversion
-- ======================
ALTER TABLE `swtickets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `swusers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `swstaff` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `swsettingsgroups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SELECT '? Upgrade patch applied successfully' AS result;