Migration from project.task to helpdesk.ticket

BEGIN;
\set HELP_PID 49
INSERT INTO helpdesk_stage (id,name,sequence,fold,create_uid,create_date,write_uid,write_date) select id,name,sequence,fold,create_uid,create_date,write_uid,write_date from project_task_type where id in (select type_id from project_task_type_rel where project_id in (:HELP_PID));

--> insert team_id
insert into helpdesk_team(id,alias_id,message_last_post,name,company_id,sequence,color,create_uid,create_date,write_uid,write_date,assign_method) select :HELP_PID,31,p.message_last_post,a.name,a.company_id,p.sequence,p.color,p.create_uid,p.create_date,p.write_uid,p.write_date,'manual' from project_project as p inner join account_analytic_account as a ON a.id = p.analytic_account_id where p.id = :HELP_PID;

--> relation between teams and stages
insert into team_stage_rel (helpdesk_team_id,helpdesk_stage_id) select :HELP_PID,type_id from project_task_type_rel where project_id = :HELP_PID;

--> Migrate HELP project
insert into helpdesk_ticket(access_token,id,name,team_id,description,active,company_id,color,kanban_state,user_id,partner_id,partner_email,priority,stage_id,assign_date,close_date,message_last_post,activity_date_deadline,create_uid,create_date,write_uid,write_date) select md5(md5(random()::varchar || id::varchar) || clock_timestamp()::varchar)::uuid::varchar,id,name,project_id,description,active,company_id,color,kanban_state,user_id,partner_id,email_from,priority,stage_id,date_assign,date_end,message_last_post,activity_date_deadline,create_uid,create_date,write_uid,write_date from project_task where project_id = :HELP_PID;

--> tags
insert into helpdesk_tag_helpdesk_ticket_rel(helpdesk_ticket_id,helpdesk_tag_id) select project_task_id,project_tags_id from project_tags_project_task_rel where project_task_id in (:HELP_PID);
insert into helpdesk_tag(id,name,color,create_uid,create_date,write_uid,write_date) select * from project_tags where id in (select helpdesk_tag_id from helpdesk_tag_helpdesk_ticket_rel);

--> change mail_message
Update mail_message set model = 'helpdesk.ticket' where model = 'project.task' and res_id in (select id from project_task where project_id in (:HELP_PID));

--> change followers
Update mail_followers set res_model = 'helpdesk.ticket' where res_model = 'project.task' and res_id in (select id from project_task where project_id in (:HELP_PID));

--> change attachment
Update ir_attachment set res_model = 'helpdesk.ticket' where res_model = 'project.task' and res_id in (select id from project_task where project_id in (:HELP_PID));

--> change activities
Update mail_activity set res_model = 'helpdesk.ticket' where res_model = 'project.task' and res_id in (select id from project_task where project_id in (:HELP_PID));

--> update next ids
select setval('helpdesk_ticket_id_seq',greatest((select last_value from helpdesk_ticket_id_seq),(select max(id) from project_task)));
select setval('helpdesk_team_id_seq',greatest((select last_value from helpdesk_team_id_seq),:HELP_PID));
select setval('helpdesk_tag_id_seq',greatest((select last_value from helpdesk_tag_id_seq),(select max(id) from project_tags)));
select setval('helpdesk_stage_id_seq',greatest((select last_value from helpdesk_stage_id_seq),(select max(id) from project_task_type)));

--> Deletion 
delete from project_task where project_id in (:HELP_PID);
delete from project_project where id in (:HELP_PID);

ROLLBACK

Quick impressions :)

Missing at first glance:

• cleanup of copied records (at least deleting the tasks to avoid people writing at the wrong place)
  => make sure to do this at the end to avoid any cascade deletion before reassigning everything
• reassignation of ir_attachments
• reassignation of followers
• reassigning xmlids of views to the new module
• declaring the new module and its deps (not sure if it should be done using the utils.py function of @KangOl or in some other way)
  => see e.g. odoo/saas-migration@5cd8950d4

Not sure:

• reassigning xmlids of fields to the new module (perhaps not needed, might be done automatically at registry reloading)

Not a problem:

• we'll probably have to migrate more than Help itself (e.g. PS Projects that interact with support), but since i's generic it's fine

Need to change the next sequence id for

• helpdesk.team
• helpdesk.ticket (we should keep numbering them without interruption so it should be set to 1800000 and some poussières)
• helpdesk.tag