
--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;


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;




