ALTER TABLE `crm_accounts` DROP COLUMN IF EXISTS `id_partner_server`, DROP COLUMN IF EXISTS `refPartner`;

DELETE FROM `sys_appconfig` WHERE `setting` = 'crm_partnert_administrativo';

ALTER TABLE `sys_items` DROP COLUMN IF EXISTS `id_partner_server`;

ALTER TABLE `sys_invoices` DROP COLUMN IF EXISTS `fm_id_partner_center`;

ALTER TABLE `sys_invoiceitems` MODIFY `description` TEXT NULL DEFAULT NULL;

ALTER TABLE `factura_maestra` DROP COLUMN IF EXISTS `valor_pagado`;

ALTER TABLE `sys_tickets` 
DROP COLUMN IF EXISTS `email_client`,
DROP COLUMN IF EXISTS `phone_client`,
DROP COLUMN IF EXISTS `contact_client`,
DROP COLUMN IF EXISTS `created_origin`,
DROP COLUMN IF EXISTS `staff_created`,
DROP COLUMN IF EXISTS `opening_date`,
DROP COLUMN IF EXISTS `crm_admin_sys_tickets_id`,
DROP COLUMN IF EXISTS `support_start_date`,
DROP COLUMN IF EXISTS `closing_date`,
DROP COLUMN IF EXISTS `aname_staff_assigned`,
DROP COLUMN IF EXISTS `staff_name_created`,
DROP COLUMN IF EXISTS `status_id`,
DROP COLUMN IF EXISTS `expected_closing_date`,
DROP COLUMN IF EXISTS `UUID`;

DROP TABLE IF EXISTS `sys_ticketsdocuments`;
DROP TABLE IF EXISTS `sys_ticketstatus`;
DROP TABLE IF EXISTS `sys_ticketscategory`;
DROP TABLE IF EXISTS `sys_tickets_by_category`;

ALTER TABLE `sys_ticketreplies` 
DROP COLUMN IF EXISTS `UUID`,
DROP COLUMN IF EXISTS `tid_crm_admin`,
DROP COLUMN IF EXISTS `who_sent`;

ALTER TABLE `sys_ticketreplies` MODIFY `attachments` TEXT NULL DEFAULT NULL;

ALTER TABLE `credit_plan_payment_method` DROP COLUMN IF EXISTS `tipo_verificacion_micro_deposito`;





ALTER TABLE `crm_accounts` ADD `id_partner_server` INT NULL DEFAULT NULL AFTER `secondary_phone`, ADD `refPartner` INT NULL DEFAULT NULL COMMENT '1 = have reference not delete, any can delete' AFTER `id_partner_server`;

INSERT INTO `sys_appconfig` (`id`, `setting`, `value`) VALUES (NULL, 'crm_partnert_administrativo', '2');

ALTER TABLE `sys_items` ADD `id_partner_server` INT NULL DEFAULT NULL AFTER `tax_code`;

ALTER TABLE `sys_invoices` ADD `fm_id_partner_center` INT NULL DEFAULT NULL COMMENT 'saved id master invoices partner center' AFTER `refiere_clients_nombre`;

ALTER TABLE `sys_invoiceitems` CHANGE `description` `description` MEDIUMTEXT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL;

ALTER TABLE `factura_maestra` ADD `valor_pagado` DOUBLE NULL DEFAULT NULL AFTER `payment_gateway_type`;
 

ALTER TABLE `sys_tickets` ADD `email_client` VARCHAR(255) NULL DEFAULT NULL AFTER `oid`, ADD `phone_client` VARCHAR(255) NULL DEFAULT NULL AFTER `email_client`, ADD `contact_client` VARCHAR(500) NULL DEFAULT NULL AFTER `phone_client`;


CREATE TABLE `sys_ticketsdocuments` (
  `id` int(11) NOT NULL,
  `UUID` varchar(255) DEFAULT NULL,
  `UUID_ENC` varchar(255) DEFAULT NULL,
  `crm_admin_sys_tickets_id` int(11) DEFAULT NULL COMMENT 'Only for CRMs that are not administrative in partner configuration',
  `sys_ticket_id` int(11) DEFAULT NULL,
  `nombre_original` varchar(500) DEFAULT NULL,
  `extension` varchar(255) DEFAULT NULL,
  `url_crm` text DEFAULT NULL,
  `nombre_img_sistema` varchar(300) DEFAULT NULL,
  `url` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 
ALTER TABLE `sys_ticketsdocuments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_std_std_id_from_sys_tickets` (`sys_ticket_id`);

 
ALTER TABLE `sys_ticketsdocuments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

 
ALTER TABLE `sys_ticketsdocuments`
  ADD CONSTRAINT `fk_std_std_id_from_sys_tickets` FOREIGN KEY (`sys_ticket_id`) REFERENCES `sys_tickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

 

ALTER TABLE `sys_tickets` ADD `created_origin` INT NULL DEFAULT NULL COMMENT '1=create side crm administrativo/ 2=clients crm' AFTER `model`, ADD `staff_created` INT NULL DEFAULT NULL COMMENT 'only for crm administrativo' AFTER `created_origin`;


ALTER TABLE `sys_tickets` ADD `opening_date` TIMESTAMP NULL DEFAULT NULL AFTER `staff_created`;

ALTER TABLE `sys_tickets` ADD `crm_admin_sys_tickets_id` INT NULL DEFAULT NULL COMMENT 'this only for crm between crm administrative and crm clientes' AFTER `id`;

ALTER TABLE `sys_tickets` ADD `support_start_date` TIMESTAMP NULL DEFAULT NULL COMMENT 'When assigned to staff' AFTER `opening_date`, ADD `closing_date` TIMESTAMP NULL DEFAULT NULL COMMENT 'Date the ticket was closed' AFTER `support_start_date`;


ALTER TABLE `sys_tickets` ADD `aname_staff_assigned` VARCHAR(500) NULL DEFAULT NULL AFTER `aid`;
  

ALTER TABLE `sys_tickets` ADD `staff_name_created` VARCHAR(500) NULL DEFAULT NULL AFTER `staff_created`;

 
CREATE TABLE `sys_ticketstatus` (
  `id` int(11) NOT NULL,
  `UUID` varchar(255) DEFAULT NULL,
  `UUID_ENC` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `number_days` int(11) DEFAULT NULL,
  `descriptions` text DEFAULT NULL,
  `show_services` int(11) DEFAULT NULL COMMENT '1=show / 2=Do not show',
  `is_can_delete` int(11) DEFAULT NULL COMMENT '1=no / 2=si',
  `active` int(11) DEFAULT NULL COMMENT '1=active / 2=inactive'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 

INSERT INTO `sys_ticketstatus` (`id`, `UUID`, `UUID_ENC`, `name`, `number_days`, `descriptions`, `show_services`, `is_can_delete`, `active`) VALUES
(1, 'ab3ce7-6a44-4c88-ad64-87a4c3854-308e0', 'qzznakRMiK1kh6TDhUMI4A', 'New', 5, 'Only for when tickets are created', 1, 1, 1),
(2, '1ac0f5-865d-4e3b-b561-ca6adf493-ff317', 'GsD1hl1OO7VhymrfST_zFw', 'In progress', 10, NULL, 1, 2, 1),
(3, '08e068-7cb9-4fff-845a-31eed4e9a-21302', 'COBofLlP_4RaMe7U6aITAg', 'Assigned', 3, 'That\'s when he is assigned to a staff', 1, 2, 1),
(4, '6c53d2-690f-4032-9e1f-e34fb0050-eb503', 'bFPSaQ9AMp4f40-wBQ61Aw', 'Waiting for customer', 7, NULL, 1, 2, 1),
(5, 'f5e906-266c-463f-86b3-1771f8b03-816a4', '9ekGJmxGP4azF3H4sDgWpA', 'Closed', 0, 'That\'s when we close a ticket.', 2, 1, 1),
(6, '0c757e-d756-4943-a85a-fd8a8082b-60d96', 'ruNlrb9O1KN2zBguaw50Uw', 'Reopened', 4, '1', 2, 2, 1);

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

 
ALTER TABLE `sys_ticketstatus`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
COMMIT;


CREATE TABLE `sys_ticketscategory` (
  `id` int(11) NOT NULL,
  `UUID` varchar(255) DEFAULT NULL,
  `UUID_ENC` varchar(255) DEFAULT NULL,
  `name` varchar(500) DEFAULT NULL,
  `active` int(11) DEFAULT NULL COMMENT '1=active / 2=inactive'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 
INSERT INTO `sys_ticketscategory` (`id`, `UUID`, `UUID_ENC`, `name`, `active`) VALUES
(1, 'ab3ce7-6a44-4c88-ad64-87a4c3854-308e0', 'qzznakRMiK1kh6TDhUMI4A', 'Specific module', 1),
(2, '1ac0f5-865d-4e3b-b561-ca6adf493-ff317', 'GsD1hl1OO7VhymrfST_zFw', 'IT', 1),
(3, '08e068-7cb9-4fff-845a-31eed4e9a-21302', 'COBofLlP_4RaMe7U6aITAg', 'Support', 1);

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

 
ALTER TABLE `sys_ticketscategory`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;
 


ALTER TABLE `sys_tickets` ADD `status_id` INT NULL DEFAULT NULL AFTER `status`;

 
ALTER TABLE `sys_tickets` ADD `expected_closing_date` DATE NULL DEFAULT NULL AFTER `closing_date`, ADD `UUID` TEXT NULL DEFAULT NULL AFTER `expected_closing_date`;


CREATE TABLE `sys_tickets_by_category` (
  `id` int(11) NOT NULL,
  `sys_ticket_id_crm_admin` int(11) DEFAULT NULL,
  `sys_ticket_id` int(11) DEFAULT NULL,
  `sys_tickets_category_id` int(11) DEFAULT NULL,
  `sys_tickets_category_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

 
ALTER TABLE `sys_tickets_by_category`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
 

ALTER TABLE `sys_ticketreplies` ADD `UUID` TEXT NULL DEFAULT NULL AFTER `id`, ADD `tid_crm_admin` INT NULL DEFAULT NULL AFTER `UUID`;

ALTER TABLE `sys_ticketreplies` ADD `who_sent` INT NULL DEFAULT NULL COMMENT '1=crm administrative / 2= crm cliente' AFTER `rating`;


ALTER TABLE `sys_ticketreplies` CHANGE `attachments` `attachments` LONGTEXT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL;


ALTER TABLE `credit_plan_payment_method` ADD `tipo_verificacion_micro_deposito` VARCHAR(255) NULL DEFAULT NULL AFTER `verificado`;

 