
--for due date 
ALTER TABLE uv_ticket
ADD COLUMN due_days FLOAT,
ADD COLUMN due_date DATE;

--for visibility ticket type
ALTER TABLE uv_ticket_type MODIFY COLUMN visibility TINYINT(1) NOT NULL DEFAULT 2;
ALTER TABLE uv_ticket_type
ADD COLUMN visibility TINYINT(1) NOT NULL DEFAULT 2;

--Add decider in customfields
ALTER TABLE uv_pkg_uvdesk_form_component_custom_fields 
ADD COLUMN decider TINYINT(1) NOT NULL DEFAULT 0;

ALTER TABLE uv_thread ADD COLUMN status_update VARCHAR(50) NULL;


ALTER TABLE uv_thread ADD COLUMN time_spent DECIMAL(10,2) NULL;


ALTER TABLE uv_ticket ADD COLUMN time_spent DECIMAL(10,2) NULL;

--Add these column before executing Automate workflow
ALTER TABLE uv_workflow
ADD COLUMN related_field_id INT NULL;

ALTER TABLE uv_workflow ADD COLUMN condition_meta JSON DEFAULT NULL;


Triggers:

//Insert

DELIMITER $$

CREATE TRIGGER update_ticket_time_spent
AFTER INSERT ON uv_thread
FOR EACH ROW
BEGIN
    DECLARE total_time_spent DECIMAL(10,2);
    
    -- Calculate the sum of time_spent for the corresponding ticket_id
    SELECT SUM(time_spent) INTO total_time_spent
    FROM uv_thread
    WHERE ticket_id = NEW.ticket_id;

    -- Update the uv_ticket table with the new time_spent value
    UPDATE uv_ticket
    SET time_spent = total_time_spent
    WHERE id = NEW.ticket_id;
END$$

DELIMITER ;

//Update

DELIMITER $$

CREATE TRIGGER update_ticket_time_spent_after_update
AFTER UPDATE ON uv_thread
FOR EACH ROW
BEGIN
    DECLARE total_time_spent DECIMAL(10,2);
    
    -- Calculate the sum of time_spent for the corresponding ticket_id
    SELECT SUM(time_spent) INTO total_time_spent
    FROM uv_thread
    WHERE ticket_id = NEW.ticket_id;

    -- Update the uv_ticket table with the new time_spent value
    UPDATE uv_ticket
    SET time_spent = total_time_spent
    WHERE id = NEW.ticket_id;
END$$

DELIMITER ;

//Delete

DELIMITER $$

CREATE TRIGGER update_ticket_time_spent_after_delete
AFTER DELETE ON uv_thread
FOR EACH ROW
BEGIN
    DECLARE total_time_spent DECIMAL(10,2);
    
    -- Calculate the sum of time_spent for the corresponding ticket_id after deletion
    SELECT SUM(time_spent) INTO total_time_spent
    FROM uv_thread
    WHERE ticket_id = OLD.ticket_id;

    -- Update the uv_ticket table with the new time_spent value
    UPDATE uv_ticket
    SET time_spent = total_time_spent
    WHERE id = OLD.ticket_id;
END$$

DELIMITER ;

ALTER TABLE uv_user_instance ADD gstNumber VARCHAR(15) DEFAULT NULL, ADD panNumber VARCHAR(10) DEFAULT NULL, ADD address TEXT DEFAULT NULL;


--Procedure for AutoWorkflow ticket type creation
DELIMITER $$

DROP PROCEDURE IF EXISTS CreateTicketTypesFromCustomField $$
CREATE PROCEDURE CreateTicketTypesFromCustomField(IN customFieldId INT)
BEGIN
    DECLARE v_field_type VARCHAR(255);
    DECLARE v_decider TINYINT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_name VARCHAR(255);
    DECLARE v_sort_order INT;

    -- Cursor to iterate checkbox options
    DECLARE cur_options CURSOR FOR
        SELECT name, sort_order 
        FROM uv_pkg_uvdesk_form_component_custom_fields_values
        WHERE custom_field_id = p_custom_field_id
        ORDER BY sort_order ASC;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Step 1: Get field_type and decider for this custom field
    SELECT field_type, decider 
    INTO v_field_type, v_decider
    FROM uv_pkg_uvdesk_form_component_custom_fields
    WHERE id = p_custom_field_id;

    -- Step 2: Proceed only if it's a decider checkbox
    IF v_field_type = 'checkbox' AND v_decider = 1 THEN
        OPEN cur_options;

        read_loop: LOOP
            FETCH cur_options INTO v_name, v_sort_order;
            IF done THEN
                LEAVE read_loop;
            END IF;

            -- Insert into uv_ticket_type only if it doesn't already exist
            IF NOT EXISTS (
                SELECT 1 FROM uv_ticket_type 
                WHERE code = v_name COLLATE utf8mb4_unicode_ci
            ) THEN
                INSERT INTO uv_ticket_type (code, is_active, visibility, description)
                VALUES (v_name COLLATE utf8mb4_unicode_ci, 1, 3, NULL);
            END IF;

        END LOOP;

        CLOSE cur_options;
    END IF;
END$$

DELIMITER ;


--Procedure for AutoWorkflow Ticket creation
DELIMITER $$

DROP PROCEDURE IF EXISTS create_combination_workflowsTC $$
CREATE PROCEDURE create_combination_workflowsTC(IN customFieldId INT)
BEGIN
    /* ------------ Declarations ------------ */
    DECLARE cf_type            VARCHAR(50);
    DECLARE cf_decider         INT;
    DECLARE total              INT DEFAULT 0;
    DECLARE k                  INT DEFAULT 0;

    DECLARE option_name        VARCHAR(255);
    DECLARE ticket_type_id     VARCHAR(10);
    DECLARE mapped_type_id     VARCHAR(10);
    DECLARE ticket_status_id   INT;
    DECLARE current_sort_order INT DEFAULT 0;
    DECLARE new_workflow_id    INT;

    DECLARE conditionBlock     TEXT;
    DECLARE fullCondition      TEXT;
    DECLARE actionsText        TEXT;
    DECLARE meta               JSON;

    /* ------------ Temp table ------------ */
    DROP TEMPORARY TABLE IF EXISTS tmp_names;
    CREATE TEMPORARY TABLE tmp_names (
        id   INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255)
    );

    INSERT INTO tmp_names(name)
    SELECT name
    FROM uv_pkg_uvdesk_form_component_custom_fields_values
    WHERE custom_field_id = customFieldId
    ORDER BY id;   -- deterministic

    SELECT COUNT(*) INTO total FROM tmp_names;

    /* ------------ Field metadata ------------ */
    SELECT field_type, decider
      INTO cf_type, cf_decider
    FROM uv_pkg_uvdesk_form_component_custom_fields
    WHERE id = customFieldId;

    /* ------------ Base ticket_type ------------ */
    SELECT tt.id
      INTO ticket_type_id
    FROM uv_pkg_uvdesk_form_component_custom_fields_types cft
    JOIN uv_ticket_type tt ON cft.ticket_type_id = tt.id
    WHERE cft.custom_fields_id = customFieldId
    LIMIT 1;

    /* ------------ Pick a ticket_status (open) ------------ */
    SELECT id
      INTO ticket_status_id
    FROM uv_ticket_status
    WHERE code = 'open'
    LIMIT 1;

    /* ------------ Sort order ------------ */
    SELECT IFNULL(MAX(sort_order), 0)
      INTO current_sort_order
    FROM uv_workflow;

    /* ------------ Main logic ------------ */
    IF cf_type = 'checkbox' AND cf_decider = 1 AND total > 0 THEN
        SET k = 0;
        loop_opts: WHILE k < total DO

            SELECT name INTO option_name
            FROM tmp_names
            ORDER BY id
            LIMIT k, 1;

            /* Map option -> uv_ticket_type.id (collation-safe) */
            SELECT t.id INTO mapped_type_id
            FROM uv_ticket_type t
            JOIN uv_pkg_uvdesk_form_component_custom_fields_values f
              ON CONVERT(t.code USING utf8mb4) COLLATE utf8mb4_unicode_ci =
                 CONVERT(f.name USING utf8mb4) COLLATE utf8mb4_unicode_ci
            WHERE f.custom_field_id = customFieldId
              AND CONVERT(f.name USING utf8mb4) COLLATE utf8mb4_unicode_ci =
                  CONVERT(option_name USING utf8mb4) COLLATE utf8mb4_unicode_ci
            LIMIT 1;

            /* ---- Avoid duplicates ---- */
            IF EXISTS (
                SELECT 1
                FROM uv_workflow
                WHERE related_field_id = customFieldId
                  AND JSON_CONTAINS_PATH(condition_meta, 'one',
                        CONCAT('$.custom_field_', customFieldId))
                  AND JSON_CONTAINS(condition_meta,
                        JSON_ARRAY(option_name),
                        CONCAT('$.custom_field_', customFieldId))
            ) THEN
                SET k = k + 1;
                ITERATE loop_opts;
            END IF;

            /* ---- Build serialized PHP conditions ---- */
            SET conditionBlock = CONCAT(
              'i:0;a:4:{s:9:"operation";s:2:"&&";',
                's:4:"type";s:10:"TicketType";',
                's:5:"match";s:2:"is";',
                's:5:"value";s:', CHAR_LENGTH(ticket_type_id),
                ':"', ticket_type_id,'";}',
              'i:1;a:4:{s:9:"operation";s:2:"&&";',
                's:4:"type";s:', CHAR_LENGTH(CONCAT('custom_field_',customFieldId)),
                ':"custom_field_', customFieldId,'";',
                's:5:"match";s:2:"is";',
                's:5:"value";s:', CHAR_LENGTH(option_name),
                ':"', option_name,'";}'
            );
            SET fullCondition = CONCAT('a:2:{', conditionBlock, '}');

            /* ---- Actions ---- */
            SET actionsText = CONCAT(
              'a:1:{i:0;a:2:{',
                's:4:"type";s:25:"uvdesk.ticket.update_type";',
                's:5:"value";s:', CHAR_LENGTH(mapped_type_id),
                ':"', mapped_type_id,'";',
              '}}'
            );

            /* ---- Meta JSON ---- */
            SET meta = JSON_OBJECT(
                'ticket_type',       ticket_type_id,
                'ticket_status',     ticket_status_id,
                CONCAT('custom_field_', customFieldId), JSON_ARRAY(option_name)
            );

            /* ---- Insert workflow ---- */
            SET current_sort_order = current_sort_order + 1;

            INSERT INTO uv_workflow (
                name, description, conditions, actions, sort_order,
                is_predefind, status, date_added, date_updated,
                priority, wf_tag, related_field_id, condition_meta
            ) VALUES (
                CONCAT('Auto Workflow (Created) – ', option_name),
                CONCAT('On create: ', option_name),
                fullCondition,
                actionsText,
                current_sort_order,
                1, 1, NOW(), NOW(),
                0, '', customFieldId, meta
            );

            SET new_workflow_id = LAST_INSERT_ID();

            /* ---- Register event ---- */
            INSERT INTO uv_workflow_events (workflow_id, event_id, event)
            VALUES (new_workflow_id, new_workflow_id, 'uvdesk.ticket.created');

            SET k = k + 1;
        END WHILE loop_opts;
    END IF;
END$$

DELIMITER ;


--Procedure for AutoWorkflow Combinations
DELIMITER $$
DROP PROCEDURE IF EXISTS create_combination_workflows $$
CREATE PROCEDURE create_combination_workflows(IN customFieldId INT)
BEGIN
    -- your existing variables
    DECLARE cf_type VARCHAR(50);
    DECLARE cf_decider INT;
    DECLARE total INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 0;
    DECLARE k INT DEFAULT 0;
    DECLARE mask INT;
    DECLARE option_name VARCHAR(255);
    DECLARE current_selected_name VARCHAR(255);
    DECLARE next_selected_name VARCHAR(255);
    DECLARE next_index INT;
    DECLARE combo_name TEXT;
    DECLARE conditionBlock TEXT;
    DECLARE fullCondition TEXT;
    DECLARE actionsText TEXT;
    DECLARE new_workflow_id INT;
    DECLARE ticket_type_id VARCHAR(10);
    DECLARE update_type_id VARCHAR(10);
    DECLARE ticket_status_id VARCHAR(10) DEFAULT '4';
    DECLARE update_status_id VARCHAR(10);
    DECLARE selected_count INT;
    DECLARE meta JSON;

    -- helper variables for unselected loop
    DECLARE unselected_count INT;
    DECLARE sel_name VARCHAR(255);
    DECLARE unsel_name VARCHAR(255);
    DECLARE u INT DEFAULT 0;

    -- new: for dynamic sort_order
    DECLARE current_sort_order INT DEFAULT 0;

    -- build the list of option names
    DROP TEMPORARY TABLE IF EXISTS tmp_names;
    CREATE TEMPORARY TABLE tmp_names (
      id   INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255)
    );
    INSERT INTO tmp_names (name)
      SELECT name
        FROM uv_pkg_uvdesk_form_component_custom_fields_values
       WHERE custom_field_id = customFieldId;

    SELECT COUNT(*) INTO total FROM tmp_names;
    SELECT field_type, decider
      INTO cf_type, cf_decider
      FROM uv_pkg_uvdesk_form_component_custom_fields
     WHERE id = customFieldId;

    -- Get current max sort_order
    SELECT IFNULL(MAX(sort_order), 0) INTO current_sort_order FROM uv_workflow;

    IF cf_type = 'checkbox'
       AND cf_decider = 1
       AND total > 0
    THEN
      WHILE i < POW(2, total) DO
        SET j = 0;
        SET selected_count = 0;

        DROP TEMPORARY TABLE IF EXISTS tmp_selected;
        CREATE TEMPORARY TABLE tmp_selected (name VARCHAR(255));
        DROP TEMPORARY TABLE IF EXISTS tmp_unselected;
        CREATE TEMPORARY TABLE tmp_unselected (name VARCHAR(255));

        WHILE j < total DO
          SET mask = POW(2, j);
          SELECT name INTO option_name
            FROM tmp_names
           WHERE id = j + 1;
          IF (i & mask) > 0 THEN
            INSERT INTO tmp_selected(name) VALUES (option_name);
            SET selected_count = selected_count + 1;
          ELSE
            INSERT INTO tmp_unselected(name) VALUES (option_name);
          END IF;
          SET j = j + 1;
        END WHILE;

        IF selected_count > 0 THEN
          SET k = 0;
          WHILE k < selected_count DO
            SELECT name INTO current_selected_name
              FROM tmp_selected
             LIMIT k, 1;

            SELECT id INTO ticket_type_id
              FROM uv_ticket_type
             WHERE code COLLATE utf8mb4_unicode_ci = current_selected_name COLLATE utf8mb4_unicode_ci
             LIMIT 1;

            IF k < selected_count - 1 THEN
              SET next_index = k + 1;
              SELECT name INTO next_selected_name
                FROM tmp_selected
               LIMIT next_index, 1;
              SELECT id INTO update_type_id
                FROM uv_ticket_type
               WHERE code COLLATE utf8mb4_unicode_ci = next_selected_name COLLATE utf8mb4_unicode_ci
               LIMIT 1;
            ELSE
              SET update_type_id = ticket_type_id;
            END IF;

            IF k = selected_count - 1 THEN
              SET update_status_id = '5';
            ELSE
              SET update_status_id = '1';
            END IF;

            SELECT GROUP_CONCAT(name SEPARATOR ' + ') INTO combo_name
              FROM tmp_selected;

            SET conditionBlock = CONCAT(
              'i:0;a:4:{s:9:"operation";s:2:"&&";',
              's:4:"type";s:10:"TicketType";',
              's:5:"match";s:2:"is";',
              's:5:"value";s:', CHAR_LENGTH(ticket_type_id),
              ':"', ticket_type_id, '";}',
              'i:1;a:4:{s:9:"operation";s:2:"&&";',
              's:4:"type";s:12:"TicketStatus";',
              's:5:"match";s:2:"is";',
              's:5:"value";s:', CHAR_LENGTH(ticket_status_id),
              ':"', ticket_status_id, '";}'
            );

            SET j = 0;
            WHILE j < selected_count DO
              SELECT name INTO sel_name
                FROM tmp_selected
               LIMIT j, 1;
              SET conditionBlock = CONCAT(
                conditionBlock,
                'i:', j + 2, ';a:4:{s:9:"operation";s:2:"&&";',
                's:4:"type";s:15:"custom_field_', customFieldId, '";',
                's:5:"match";s:2:"is";',
                's:5:"value";s:', CHAR_LENGTH(sel_name),
                ':"', sel_name, '";}'
              );
              SET j = j + 1;
            END WHILE;

            SELECT COUNT(*) INTO unselected_count FROM tmp_unselected;
            SET u = 0;
            WHILE u < unselected_count DO
              SELECT name INTO unsel_name
                FROM tmp_unselected
               LIMIT u, 1;
              SET conditionBlock = CONCAT(
                conditionBlock,
                'i:', j + 2, ';a:4:{s:9:"operation";s:2:"&&";',
                's:4:"type";s:15:"custom_field_', customFieldId, '";',
                's:5:"match";s:5:"isNot";',
                's:5:"value";s:', CHAR_LENGTH(unsel_name),
                ':"', unsel_name, '";}'
              );
              SET j = j + 1;
              SET u = u + 1;
            END WHILE;

            SET fullCondition = CONCAT('a:', j + 2, ':{', conditionBlock, '}');
            SET actionsText = CONCAT(
              'a:2:{',
              'i:0;a:2:{s:4:"type";s:25:"uvdesk.ticket.update_type";',
              's:5:"value";s:', CHAR_LENGTH(update_type_id),
              ':"', update_type_id, '";}',
              'i:1;a:2:{s:4:"type";s:27:"uvdesk.ticket.update_status";',
              's:5:"value";s:', CHAR_LENGTH(update_status_id),
              ':"', update_status_id, '";}',
              '}'
            );

            -- ✅ Dynamically increment sort_order
            SET current_sort_order = current_sort_order + 1;

            -- Build metadata JSON for this workflow from its conditions
            SET meta = JSON_OBJECT(
                'ticket_type',   ticket_type_id,
                'ticket_status', ticket_status_id,
                 CONCAT('custom_field_', customFieldId),
                (SELECT JSON_ARRAYAGG(name) FROM tmp_selected),
                CONCAT('custom_field_', customFieldId, '_not'),
                (SELECT JSON_ARRAYAGG(name) FROM tmp_unselected)
            );


            -- Insert workflow
            INSERT INTO uv_workflow (
              name, description, conditions, actions, sort_order,
              is_predefind, status, date_added, date_updated,
              priority, wf_tag, related_field_id, condition_meta
            ) VALUES (
              CONCAT('Auto Workflow - ', combo_name, ' → ', current_selected_name),
              CONCAT('Auto-generated for ', current_selected_name),
              fullCondition,
              actionsText,
              current_sort_order,
              1, 1, NOW(), NOW(), 0, '', customFieldId, meta
            );

            SET new_workflow_id = LAST_INSERT_ID();

            INSERT INTO uv_workflow_events (workflow_id, event_id, event)
            VALUES
              (new_workflow_id, new_workflow_id, 'uvdesk.ticket.status_updated'),
              (new_workflow_id, new_workflow_id, 'uvdesk.ticket.agent_reply');

            SET k = k + 1;
          END WHILE;
        END IF;

        SET i = i + 1;
        END WHILE;
    END IF;
END$$

DELIMITER ;

