-- ******* START Bug upgrade
-- Create each of the bugs with the new API that creates a content
-- item, an initial revision, and populates the bt_bugs denormalization table
create or replace function inline_0 ()
returns integer as '
declare
  -- Project loop
  project_rec           record;
  v_workflow_id         integer;
  v_open_action_id      integer;

  -- Bug loop
  bug_rec               record;
  notifications_rec     record;
  v_state_id            integer;
  v_item_id             integer;
  v_new_bug_id          integer;
  v_severity_id         integer;
  v_priority_id         integer;
  v_bug_type_id         integer;
  v_bug_revision_id     integer;
begin

  for project_rec in select project_id from bt_projects
  loop

    -- Get the bug workflow id
    select workflow_id into v_workflow_id 
        from workflows 
        where short_name = ''bug''
        and object_id = project_rec.project_id;

    select action_id into v_open_action_id 
          from workflow_actions
          where workflow_id = v_workflow_id
            and short_name = ''open'';


    for bug_rec in select b.bug_id,
                          b.bug_number,
                          b.project_id,
                          b.component_id,
                          b.found_in_version,
                          b.fix_for_version,
                          b.fixed_in_version,
                          b.resolution,
                          b.summary,
                          b.user_agent,
                          b.severity,
                          b.priority,
                          b.bug_type,
                          cr.content,
                          cr.mime_type,
                          o.creation_user, 
                          o.creation_date,
                          o.creation_ip
                   from bt_bugs_temp b, 
                        acs_objects o,
                        workflow_cases c,
                        workflow_case_log wcl,
                        cr_items ci,
                        cr_revisions cr
                   where b.bug_id = o.object_id
                     and b.project_id = project_rec.project_id
                     and b.bug_id = c.object_id
                     and c.workflow_id = v_workflow_id
                     and wcl.case_id = c.case_id
                     and wcl.action_id = v_open_action_id
                     and ci.item_id = wcl.entry_id
                     and cr.revision_id = ci.live_revision
    loop

        -- Use a modified version of bt_bug__new that doesn''t
        -- create a new acs_object
        perform bt_bug__new (
            bug_rec.bug_id,     -- bug_id
            bug_rec.bug_number,     -- bug_number
            bug_rec.project_id,     -- package_id
            bug_rec.component_id,     -- component_id
            bug_rec.found_in_version,     -- found_in_version
            bug_rec.summary,     -- summary
            bug_rec.user_agent,     -- user_agent
            bug_rec.content,        -- comment_content
            bug_rec.mime_type,     -- comment_format
            bug_rec.creation_date,   -- creation_date
            bug_rec.creation_user,     -- creation_user
            bug_rec.creation_ip,     -- creation_ip
            ''bt_bug'',     -- item_subtype
            ''bt_bug_revision''      -- content_type               
        );

        -- Get the revision id
        select live_revision
        into   v_bug_revision_id
        from   cr_items
        where  item_id = bug_rec.bug_id;
        
        -- Update with fix_for_version, fixed_in_version, resolution
        update bt_bug_revisions
        set    fix_for_version = bug_rec.fix_for_version,
               fixed_in_version = bug_rec.fixed_in_version,
               resolution = bug_rec.resolution
        where  bug_revision_id = v_bug_revision_id;

        -- update the cache in the item
        update bt_bugs
        set    fix_for_version = bug_rec.fix_for_version,
               fixed_in_version = bug_rec.fixed_in_version,
               resolution = bug_rec.resolution
        where  bug_id = bug_rec.bug_id;

        -- Map severity
        select keyword_id 
        into   v_severity_id
        from   code_keyword_map_temp
        where  code_id = bug_rec.severity;

        perform content_keyword__item_assign (
            bug_rec.bug_id,
            v_severity_id,
            null,
            bug_rec.creation_user,
            bug_rec.creation_ip
        );

        -- Map priority
        select keyword_id 
        into   v_priority_id
        from   code_keyword_map_temp
        where  code_id = bug_rec.priority;

        perform content_keyword__item_assign (
            bug_rec.bug_id,
            v_priority_id,
            null,
            bug_rec.creation_user,
            bug_rec.creation_ip
        );
        
        -- Map bug type
        select keyword_id
        into   v_bug_type_id
        from   bug_type_keyword_map_temp
        where  project_id = bug_rec.project_id
        and    bug_type = bug_rec.bug_type;

        perform content_keyword__item_assign (
            bug_rec.bug_id,
            v_bug_type_id,
            null,
            bug_rec.creation_user,
            bug_rec.creation_ip
        );
        
    end loop;

  end loop;

  return 0;    
end;' language 'plpgsql';
select inline_0();
drop function inline_0();

-- ******* Drop tables no longer used
-- drop table bt_severity_codes;
-- drop table bt_priority_codes;

-- ******* Drop temporary upgrade tables
-- drop table code_keyword_map_temp;
-- drop table bug_type_keyword_map_temp;
-- drop table bt_bugs_temp;
-- drop table project_temp;

-- ******* Drop temporary upgrade functions
--drop function bt_bug__new_temp(
--    integer,     -- bug_id
--    integer,     -- bug_number
--    integer,     -- package_id
--    integer,     -- component_id
--    integer,     -- found_in_version
--    varchar,     -- summary
--    varchar,     -- user_agent
--    text,        -- comment_content
--    varchar,     -- comment_format
--    timestamp,   -- creation_date
--    integer,     -- creation_user
--    varchar,     -- creation_ip
--    varchar,     -- item_subtype
--    varchar      -- content_type
--);

drop function content_item__new_temp (varchar,integer,integer,varchar,timestamp with time zone,integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer);

drop function content_item__new_temp (varchar,integer,integer,varchar,timestamp with time zone,integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar);