
CREATE TABLE `clic23_sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `clic23_sessions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `clic23_sessions_user_id_index` (`user_id`),
  ADD KEY `clic23_sessions_last_activity_index` (`last_activity`);


CREATE TABLE `clic23_roles` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `clic23_roles` (`id`, `name`, `slug`, `created_at`, `updated_at`) VALUES
(1, 'Manager', 'manager', '2026-02-22 00:02:24', '2026-02-22 00:02:24'),
(2, 'Agent', 'agent', '2026-02-22 00:02:24', '2026-02-22 00:02:24'),
(3, 'Client', 'client', '2026-02-22 00:02:24', '2026-02-22 00:02:24');


CREATE TABLE `clic23_role_permissions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `role_id` bigint(20) UNSIGNED NOT NULL,
  `permission_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `clic23_role_permissions` (`id`, `role_id`, `permission_id`, `created_at`, `updated_at`) VALUES
(1, 1, 1, NULL, NULL),
(2, 1, 2, NULL, NULL),
(3, 1, 3, NULL, NULL),
(4, 1, 4, NULL, NULL),
(5, 1, 5, NULL, NULL),
(6, 1, 6, NULL, NULL),
(7, 1, 7, NULL, NULL),
(8, 1, 8, NULL, NULL),
(9, 1, 9, NULL, NULL),
(10, 1, 10, NULL, NULL),
(11, 1, 11, NULL, NULL),
(12, 1, 12, NULL, NULL),
(13, 1, 13, NULL, NULL),
(14, 1, 14, NULL, NULL),
(15, 1, 15, NULL, NULL),
(16, 1, 16, NULL, NULL),
(17, 1, 17, NULL, NULL),
(18, 1, 18, NULL, NULL),
(19, 1, 19, NULL, NULL),
(20, 1, 20, NULL, NULL),
(21, 1, 21, NULL, NULL),
(22, 1, 22, NULL, NULL),
(23, 1, 23, NULL, NULL),
(24, 1, 24, NULL, NULL),
(25, 1, 25, NULL, NULL),
(26, 1, 26, NULL, NULL),
(27, 1, 27, NULL, NULL),
(28, 1, 28, NULL, NULL),
(29, 1, 29, NULL, NULL),
(30, 1, 30, NULL, NULL),
(31, 1, 31, NULL, NULL),
(32, 1, 32, NULL, NULL),
(33, 1, 33, NULL, NULL),
(34, 1, 34, NULL, NULL),
(35, 1, 35, NULL, NULL),
(36, 1, 36, NULL, NULL),
(37, 1, 37, NULL, NULL),
(38, 1, 38, NULL, NULL),
(39, 1, 39, NULL, NULL),
(40, 1, 40, NULL, NULL),
(41, 1, 41, NULL, NULL),
(42, 1, 42, NULL, NULL),
(43, 1, 43, NULL, NULL),
(44, 1, 44, NULL, NULL),
(45, 1, 45, NULL, NULL),
(46, 1, 46, NULL, NULL),
(47, 1, 47, NULL, NULL),
(48, 1, 48, NULL, NULL),
(49, 1, 49, NULL, NULL),
(50, 1, 50, NULL, NULL),
(51, 1, 51, NULL, NULL),
(52, 2, 1, NULL, NULL),
(53, 2, 7, NULL, NULL),
(54, 2, 8, NULL, NULL),
(55, 2, 9, NULL, NULL),
(56, 2, 10, NULL, NULL),
(57, 2, 11, NULL, NULL),
(58, 2, 12, NULL, NULL),
(59, 2, 13, NULL, NULL),
(60, 2, 14, NULL, NULL),
(61, 2, 17, NULL, NULL),
(62, 2, 19, NULL, NULL),
(63, 2, 32, NULL, NULL),
(64, 2, 36, NULL, NULL),
(65, 2, 42, NULL, NULL),
(66, 3, 42, NULL, NULL),
(67, 3, 1, NULL, NULL);


ALTER TABLE `clic23_roles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `clic23_roles_name_unique` (`name`),
  ADD UNIQUE KEY `clic23_roles_slug_unique` (`slug`);

ALTER TABLE `clic23_role_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `clic23_role_permissions_role_id_permission_id_unique` (`role_id`,`permission_id`),
  ADD KEY `clic23_role_permissions_permission_id_foreign` (`permission_id`);


ALTER TABLE `clic23_roles`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;


ALTER TABLE `clic23_role_permissions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=68;


ALTER TABLE `clic23_role_permissions`
  ADD CONSTRAINT `clic23_role_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `clic23_permissions` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `clic23_role_permissions_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `clic23_roles` (`id`) ON DELETE CASCADE;


ALTER TABLE `clic23_users` ADD `role_id` BIGINT(20) NULL AFTER `avatar`, ADD `is_active` TINYINT(1) NULL AFTER `role_id`, ADD `remember_token` VARCHAR(100) NULL AFTER `is_active`, ADD `created_at` TIMESTAMP NULL AFTER `remember_token`, ADD `updated_at` TIMESTAMP NULL AFTER `created_at`;
ALTER TABLE `clic23_users` ADD `name` VARCHAR(250) NULL AFTER `username`;


/*change ID to id */

CREATE TABLE `clic23_ticket_priorities` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `color` varchar(255) NOT NULL DEFAULT '#3498db',
  `icon` varchar(255) NOT NULL DEFAULT 'fa-arrow-right',
  `level` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `clic23_ticket_priorities` (`id`, `name`, `color`, `icon`, `level`, `created_at`, `updated_at`) VALUES
(1, 'Bass', '#3498db', 'fa-arrow-right', 0, '2026-02-22 00:03:53', '2026-02-22 00:03:53'),
(2, 'Moyen', '#1abc9c', 'fa-arrow-right', 1, '2026-02-22 00:03:53', '2026-02-22 00:03:53'),
(3, 'Haut', '#f39c12', 'fa-arrow-up', 2, '2026-02-22 00:03:53', '2026-02-22 00:03:53'),
(4, 'Urgent', '#e74c3c', 'fa-bolt', 3, '2026-02-22 00:03:53', '2026-02-22 00:03:53');

ALTER TABLE `clic23_ticket_priorities`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `clic23_ticket_priorities`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
ALTER TABLE `clic23_custom_statuses` ADD `is_closable` TINYINT(1) NULL AFTER `text_color`, ADD `order` INT(11) NULL AFTER `is_closable`, ADD `created_at` TIMESTAMP NULL AFTER `order`, ADD `updated_at` TIMESTAMP NULL AFTER `created_at`;




CREATE TABLE `clic23_settings` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `key` varchar(255) NOT NULL,
  `value` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



INSERT INTO `clic23_settings` (`id`, `key`, `value`, `created_at`, `updated_at`) VALUES
(1, 'app_name', 'Support HelpDesk', '2026-02-22 00:02:24', '2026-02-22 00:02:24'),
(2, 'company_name', 'ClicNGo Hotels', '2026-02-22 00:02:24', '2026-02-22 00:02:24'),
(3, 'site_name', 'Help Desk ArchiDev', '2026-02-22 00:03:52', '2026-02-22 00:03:52'),
(4, 'site_desc', '<p>Logiciel de Support Ticket Professionnel</p>', '2026-02-22 00:03:52', '2026-02-24 08:44:45'),
(5, 'site_email', 'support@clicngo.biz', '2026-02-22 00:03:52', '2026-02-22 00:03:52'),
(6, 'site_logo', '1771803987_logo.png', '2026-02-22 00:03:52', '2026-02-22 22:46:27'),
(7, 'primary_color', '#0ea060', '2026-02-22 00:03:52', '2026-02-24 10:58:33'),
(8, 'second_color', '#ffcb0f', '2026-02-22 00:03:52', '2026-02-22 00:03:52'),
(9, 'register_enabled', '1', '2026-02-22 00:03:52', '2026-02-22 00:03:52'),
(10, 'enable_ticket_uploads', '1', '2026-02-22 00:03:52', '2026-02-22 00:03:52'),
(11, 'enable_ticket_guests', '0', '2026-02-22 00:03:52', '2026-02-22 00:03:52'),
(12, 'require_login', '1', '2026-02-22 00:03:53', '2026-02-22 00:03:53'),
(13, 'default_language', 'fr', '2026-02-22 00:03:53', '2026-02-22 00:03:53'),
(14, 'timezone', 'Africa/Algiers', '2026-02-22 00:03:53', '2026-02-22 00:03:53'),
(15, 'file_size_limit', '5000', '2026-02-22 00:03:53', '2026-02-22 00:03:53'),
(16, 'allowed_extensions', 'pdf,docx,xlsx,zip,rar,png,jpg,jpeg', '2026-02-22 00:03:53', '2026-02-22 00:03:53'),
(17, 'is_chat_enable', '1', '2026-02-22 00:03:53', '2026-02-22 00:03:53'),
(18, 'google_drive_enabled', '1', '2026-02-22 00:03:53', '2026-02-22 00:03:53'),
(19, 'google_drive_folder_id', 'null', '2026-02-22 00:03:53', '2026-02-24 08:44:46'),
(20, 'default_status_id', '1', '2026-02-22 00:03:53', '2026-02-22 00:03:53'),
(21, 'dark_mode', '0', '2026-02-22 00:41:46', '2026-02-24 17:16:57'),
(22, 'site_favicon', '1771803987_icon.png', '2026-02-22 01:32:42', '2026-02-22 22:46:27'),
(23, 'site_frontend_logo', '', '2026-02-22 01:32:42', '2026-02-22 01:32:42'),
(24, 'site_footer_frontend_logo', '', '2026-02-22 01:32:42', '2026-02-22 01:32:42'),
(25, 'logo_option', '1', '2026-02-22 01:32:42', '2026-02-24 09:37:50'),
(26, 'logo_width', '150', '2026-02-22 01:32:42', '2026-02-22 01:32:42'),
(27, 'logo_height', '50', '2026-02-22 01:32:42', '2026-02-22 01:32:42'),
(28, 'upload_path', './uploads/', '2026-02-22 01:32:42', '2026-02-22 01:32:42'),
(29, 'upload_path_relative', 'uploads', '2026-02-22 01:32:42', '2026-02-22 01:32:42'),
(30, 'date_format', 'd/m/Y', '2026-02-22 01:32:42', '2026-02-22 01:32:42'),
(31, 'file_types', 'pdf,docx,xlsx,zip,rar,png,jpg,jpeg,gif', '2026-02-22 01:32:42', '2026-02-22 01:32:42'),
(32, 'file_size', '5000', '2026-02-22 01:32:42', '2026-02-22 01:32:42'),
(33, 'avatar_width', '256', '2026-02-22 01:32:42', '2026-02-22 01:32:42'),
(34, 'avatar_height', '256', '2026-02-22 01:32:42', '2026-02-22 01:32:42'),
(35, 'notes', '<p>null</p>', '2026-02-22 01:32:43', '2026-02-24 09:37:50'),
(36, 'default_user_role', '2', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(37, 'register', '1', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(38, 'disable_captcha', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(39, 'google_recaptcha', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(40, 'google_recaptcha_secret', '12345678@@', '2026-02-22 01:32:43', '2026-02-22 22:46:27'),
(41, 'google_recaptcha_key', 'null', '2026-02-22 01:32:43', '2026-02-24 08:44:45'),
(42, 'login_protect', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(43, 'activate_account', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(44, 'secure_login', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(45, 'cache_time', '3600', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(46, 'enable_google_authenticator', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(47, 'user_to_chats_roles_ids', '', '2026-02-22 01:32:43', '2026-02-24 08:44:46'),
(48, 'enable_ticket_edit', '1', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(49, 'ticket_rating', '1', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(50, 'captcha_ticket', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(51, 'alert_users', 'houssamdibi1@gmail.com', '2026-02-22 01:32:43', '2026-02-22 22:46:27'),
(52, 'public_tickets', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(53, 'ticket_note_close', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(54, 'close_ticket_reply', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(55, 'default_category', '1', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(56, 'default_status', '1', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(57, 'staff_status', '1', '2026-02-22 01:32:43', '2026-02-22 22:46:27'),
(58, 'client_status', '1', '2026-02-22 01:32:43', '2026-02-22 22:46:27'),
(59, 'order_tickets', '1', '2026-02-22 01:32:43', '2026-02-22 22:46:27'),
(60, 'auto_close_tickets', '{\"enable_1\":1,\"enable_2\":1,\"enable_3\":1,\"enable_4\":1,\"enable_5\":1}', '2026-02-22 01:32:43', '2026-02-24 08:44:46'),
(61, 'nbr_days_to_close_ticket_by_status', '{\"1\":null,\"2\":null,\"3\":null,\"4\":null,\"5\":null}', '2026-02-22 01:32:43', '2026-02-24 08:44:46'),
(62, 'status_enable_disable', '{\"1\":\"1\",\"2\":\"1\",\"3\":\"1\",\"4\":\"1\",\"5\":\"1\"}', '2026-02-22 01:32:43', '2026-02-24 08:44:46'),
(63, 'field_tkt_texte', '[\"\"]', '2026-02-22 01:32:43', '2026-02-24 08:44:46'),
(64, 'field_tkt_id', '[1]', '2026-02-22 01:32:43', '2026-02-24 08:44:46'),
(65, 'field_tkt_category_ids', '[[5,10,31]]', '2026-02-22 01:32:43', '2026-02-24 08:44:46'),
(66, 'emails_category', '[]', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(67, 'emails_category_ids', '[]', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(68, 'fast_links', '[{\"title\":\"Facebook\",\"url\":\"https:\\/\\/www.facebook.com\\/\",\"icon\":\"public\",\"client_visible\":\"yes\"}]', '2026-02-22 01:32:43', '2026-02-24 08:44:46'),
(69, 'bunny_enable', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(70, 'bunny_storage_zone', 'null', '2026-02-22 01:32:43', '2026-02-24 08:44:45'),
(71, 'bunny_api_key', 'null', '2026-02-22 01:32:43', '2026-02-24 08:44:45'),
(72, 'bunny_region_host', 'storage.bunnycdn.com', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(73, 'bunny_cdn_domain', 'null', '2026-02-22 01:32:43', '2026-02-24 08:44:46'),
(74, 'bunny_default_folder', 'uploads', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(75, 'google_drive_client', 'null', '2026-02-22 01:32:43', '2026-02-24 08:44:46'),
(76, 'google_drive_secret', 'null', '2026-02-22 01:32:43', '2026-02-24 08:44:46'),
(77, 'google_drive_access_token', '', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(78, 'google_drive_refresh_token', '', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(79, 'protocol', '1', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(80, 'protocol_path', '{imap.gmail.com:993/imap/ssl}INBOX', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(81, 'protocol_ssl', '1', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(82, 'disable_cert', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(83, 'protocol_email', 'null', '2026-02-22 01:32:43', '2026-02-24 08:44:45'),
(84, 'protocol_password', '12345678@@', '2026-02-22 01:32:43', '2026-02-24 08:44:45'),
(85, 'ticket_title', '1', '2026-02-22 01:32:43', '2026-02-22 22:46:27'),
(86, 'imap_ticket_string', 'ticket', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(87, 'imap_reply_string', 're', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(88, 'panneau_administration', 'houssamdibi1@gmail.com', '2026-02-22 01:32:43', '2026-02-24 08:44:45'),
(89, 'sho_default_fealds_of_cat_id', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(90, 'status_opened_ticket_firset_time', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(91, 'status_ticket_first_agent_response', '0', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(92, 'enable_knowledge', '1', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(93, 'enable_faq', '1', '2026-02-22 01:32:43', '2026-02-22 01:32:43'),
(94, 'enable_documentation', '1', '2026-02-22 01:32:43', '2026-02-24 10:56:45'),
(95, 'login_logo', '1771803987_logo.png', '2026-02-22 22:46:27', '2026-02-22 22:46:27'),
(96, 'secondary_color', '#ffcb0f', '2026-02-24 08:44:45', '2026-02-24 08:44:45');

ALTER TABLE `clic23_settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `clic23_settings_key_unique` (`key`);

ALTER TABLE `clic23_settings`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=97;


ALTER TABLE `clic23_users` CHANGE `ID` `id` INT(11) NOT NULL AUTO_INCREMENT;
UPDATE `clic23_users` SET `role_id` = '1' WHERE `clic23_users`.`id` = 1;

UPDATE `clic23_users` SET `is_active` = '1' WHERE `clic23_users`.`id` = 1;
ALTER TABLE `clic23_ticket_categories` CHANGE `description` `description` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL;



UPDATE clic23_users SET is_active = active WHERE is_active IS NULL;

UPDATE clic23_users
SET role_id = CASE
  WHEN user_role IN (1,7,11,13) THEN 1
  WHEN user_role IN (8,9) THEN 2
  WHEN user_role IN (0,10) THEN 3
  ELSE role_id
END
WHERE role_id IS NULL;


INSERT INTO clic23_settings (`key`,`value`,`created_at`,`updated_at`)
SELECT column_name, column_value, NOW(), NOW()
FROM (
    SELECT 
        'site_name' AS column_name, site_name AS column_value FROM clic23_site_settings WHERE ID = 1
    UNION ALL
    SELECT 'site_email', site_email FROM clic23_site_settings WHERE ID = 1
    UNION ALL
    SELECT 'site_logo', site_logo FROM clic23_site_settings WHERE ID = 1
    UNION ALL
    SELECT 'paypal_email', paypal_email FROM clic23_site_settings WHERE ID = 1
) AS tmp
ON DUPLICATE KEY UPDATE
    value = VALUES(value),
    updated_at = NOW();


ALTER TABLE `clic23_offre` CHANGE `name` `name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL, CHANGE `description` `description` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL;
ALTER TABLE `clic23_offre` CHANGE `ID` `ID` INT(11) NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`ID`);

-- Tickets menu filters + ordering (Laravel)
INSERT INTO `clic23_settings` (`key`,`value`,`created_at`,`updated_at`) VALUES
('order_tickets_by_status','',NOW(),NOW()),
('status_show_new_tickets_by_status','1',NOW(),NOW()),
('status_show_close_tickets_by_status','3',NOW(),NOW())
ON DUPLICATE KEY UPDATE
    `value` = VALUES(`value`),
    `updated_at` = NOW();


ALTER TABLE `clic23_slider_images` ADD `title` VARCHAR(255) NULL AFTER `language_code`, ADD `description` TEXT NULL AFTER `title`, ADD `enabled` TINYINT(1) NULL AFTER `description`, ADD `order` INT(11) NULL AFTER `enabled`;
CREATE TABLE IF NOT EXISTS `clic23_mobile_api_tokens` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `name` VARCHAR(120) NULL,
  `token_hash` VARCHAR(64) NOT NULL,

  `fcm_token` VARCHAR(255) NULL,
  `device_platform` VARCHAR(30) NULL,
  `device_id` VARCHAR(191) NULL,
  `app_version` VARCHAR(50) NULL,
  `push_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `fcm_token_updated_at` TIMESTAMP NULL DEFAULT NULL,

  `last_used_at` TIMESTAMP NULL DEFAULT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `revoked_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `clic23_mobile_api_tokens_token_hash_unique` (`token_hash`),

  KEY `mobile_api_tokens_lookup_idx` (`user_id`,`revoked_at`,`expires_at`),
  KEY `mobile_api_tokens_fcm_token_idx` (`fcm_token`),
  KEY `mobile_api_tokens_push_lookup_idx` (`user_id`,`push_enabled`,`revoked_at`,`expires_at`),

  CONSTRAINT `clic23_mobile_api_tokens_user_id_foreign`
    FOREIGN KEY (`user_id`)
    REFERENCES `clic23_users` (`id`)
    ON DELETE CASCADE

) ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_unicode_ci;


ALTER TABLE `clic23_ticket_custom_field_cats` CHANGE `ID` `ID` INT(11) NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`ID`);




DROP TABLE IF EXISTS `clic23_todo_tasks`;
CREATE TABLE `clic23_todo_tasks` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `created_by` int(11) NOT NULL,
  `to_user` text NOT NULL,
  `category_id` int(11) NOT NULL,
  `fields_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `description` text DEFAULT NULL,
  `priority` enum('Important','Normal') NOT NULL DEFAULT 'Normal',
  `status` text DEFAULT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `comments` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



INSERT INTO `clic23_todo_tasks` (`id`, `title`, `created_by`, `to_user`, `category_id`, `fields_data`, `description`, `priority`, `status`, `start_date`, `end_date`, `comments`, `created_at`, `updated_at`) VALUES
(2, 'testing', 32, '[\"32\"]', 1, '{\"Sous-t\\u00e2che\":\"G\\u00e9n\\u00e9ration des rapports de ventes pour les affili\\u00e9s.\"}', 'fdfdfdf', 'Important', 'pending', '2025-01-23 05:19:00', '2025-01-23 04:25:00', '[{\"content\":\"OK pas de probl\\u00e8me\",\"created_by\":null},{\"content\":\"non\",\"created_by\":null},{\"content\":\"merci\",\"created_by\":null},{\"content\":\"okkk\",\"created_by\":null}]', '2025-01-23 04:19:10', '2025-01-23 04:43:16');

ALTER TABLE `clic23_todo_tasks`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `clic23_todo_tasks`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
DROP TABLE IF EXISTS `clic23_todo_task_categories`;
CREATE TABLE `clic23_todo_task_categories` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`fields`)),
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



INSERT INTO `clic23_todo_task_categories` (`id`, `name`, `fields`, `created_at`, `updated_at`) VALUES
(1, 'Paiement des Affiliés', '{\"1\":{\"name\":\"Sous-t\\u00e2che\",\"type\":\"select\",\"options\":\"V\\u00e9rification des commissions dues aux agences partenaires,G\\u00e9n\\u00e9ration des rapports de ventes pour les affili\\u00e9s.,Validation des montants \\u00e0 payer,Traitement des paiements via virement bancaire ou autres moyens,Envoi des re\\u00e7us de paiement aux affili\\u00e9s\"}}', '2024-11-23 11:13:15', '2024-11-23 11:24:43'),
(2, 'Rappel des Paiements de Factures', '{\"1\":{\"name\":\"Sous-t\\u00e2che\",\"type\":\"select\",\"options\":\"Identification des factures impay\\u00e9es,Envoi de notifications par email aux clients retardataires,Appels t\\u00e9l\\u00e9phoniques pour rappeler les paiements en retard,Mise en place d\\u2019un calendrier automatis\\u00e9 pour les rappels,G\\u00e9n\\u00e9ration de rapports hebdomadaires sur les paiements re\\u00e7us et en attente\"}}', '2024-11-23 11:14:30', NULL),
(3, 'Gestion des Réservations de Groupes', '{\"1\":{\"name\":\"Sous-t\\u00e2che\",\"type\":\"select\",\"options\":\"R\\u00e9servation des h\\u00f4tels pour les groupes (plus de 10 personnes), Organisation des activit\\u00e9s sp\\u00e9cifiques pour les groupes, Coordination avec les agences partenaires pour g\\u00e9rer les grandes r\\u00e9servations, Validation des conditions de paiement pour les groupes, Envoi des confirmations et documents aux responsables de groupe\"}}', '2024-11-23 11:16:25', NULL),
(4, 'Gestion des Frais Internes', '{\"1\":{\"name\":\"Sous-t\\u00e2che\",\"type\":\"select\",\"options\":\"Suivi des d\\u00e9penses op\\u00e9rationnelles (bureau, fournitures), Validation des frais de d\\u00e9placement des collaborateurs, Pr\\u00e9paration des rapports mensuels de d\\u00e9penses internes, Validation des paiements des factures (eau, \\u00e9lectricit\\u00e9, Internet), Pr\\u00e9vision budg\\u00e9taire pour les d\\u00e9penses internes\"}}', '2024-11-23 11:16:51', NULL),
(5, 'Campagnes Promotionnelles', '{\"1\":{\"name\":\"Sous-t\\u00e2che\",\"type\":\"select\",\"options\":\"Envoi de promotions sp\\u00e9ciales \\u00e0 des affili\\u00e9s sp\\u00e9cifiques, Mise \\u00e0 jour des partenaires affili\\u00e9s sur les nouvelles offres, Validation des budgets pour les campagnes publicitaires, Analyse des r\\u00e9sultats des campagnes par r\\u00e9gion, Coordination avec les agences partenaires pour les offres combin\\u00e9es\"}}', '2024-11-23 11:17:20', NULL),
(6, 'Gestion des Dépenses', '{\"1\":{\"name\":\"Affili\\u00e9\",\"type\":\"text\",\"options\":\"\"},\"2\":{\"name\":\"Montant pay\\u00e9\",\"type\":\"text\",\"options\":\"\"},\"3\":{\"name\":\"Justification de la d\\u00e9pense\",\"type\":\"textarea\",\"options\":\"\"}}', '2024-11-23 11:24:31', '2024-11-23 11:25:23'),
(7, 'catégorie test', '{\"1\":{\"name\":\"type\",\"type\":\"select\",\"options\":\"select1,select2,select3,select4,select5\"},\"2\":{\"name\":\"Nom\",\"type\":\"text\",\"options\":\"\"},\"3\":{\"name\":\"Pr\\u00e9nom\",\"type\":\"text\",\"options\":\"\"},\"4\":{\"name\":\"Information\",\"type\":\"textarea\",\"options\":\"\"}}', '2024-11-23 11:39:31', '2024-11-23 11:40:11');

ALTER TABLE `clic23_todo_task_categories`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `clic23_todo_task_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
