update project_portfolio set json = convert_from(decode(portfolio, 'base64'), 'UTF8')::json ;
update project_portfolio set administrative_data    = json->'administrative_data';
update project_portfolio set governance_data        = json->'governance_data';
update project_portfolio set executive_summary      = json->'sections'->'executive_summary';
update project_portfolio set final_report_summary   = json->'sections'->'final_report_summary';
update project_portfolio set impact                 = json->'sections'->'impact';
update project_portfolio set objective              = json->'sections'->'objective';
update project_portfolio set title                  = json->'sections'->'title';

-- document
INSERT INTO DOCUMENT
            (id,
             title,
             abstract,
             doctype,
             repository,
             rights,
             pubyear)
SELECT Replace(Replace(projectid, '40|corda__h2020', '50|h2020_object'),
              '40|corda_______', '50|fp7___object')    AS id,
       'Objectives of project '
       ||( administrative_data ->> 'acronym' :: TEXT ) AS title,
       objective ->> 'text'                            AS abstract,
       'project_report'                                AS doctype,
       'CORDIS'                                        AS repository,
       'OPEN' :: TEXT                                  AS rights,
       administrative_data ->> 'date_to' :: TEXT       AS pubyear
FROM   project_portfolio
UNION ALL
SELECT Replace(Replace(projectid, '40|corda__h2020', '50|h2020summary'),
              '40|corda_______', '50|fp7__summary')    AS id,
       'Final report summary of project '
       ||( administrative_data ->> 'acronym' :: TEXT ) AS title,
       final_report_summary ->> 'text'                 AS abstract,
       'project_report'                                AS doctype,
       'CORDIS'                                        AS repository,
       'OPEN' :: TEXT                                  AS rights,
       administrative_data ->> 'date_to' :: TEXT       AS pubyear
FROM   project_portfolio
UNION ALL
SELECT Replace(Replace(projectid, '40|corda__h2020', '50|h2020___exec'),
              '40|corda_______', '50|fp7_____exec')    AS id,
       'Executive summary of project '
       ||( administrative_data ->> 'acronym' :: TEXT ) AS title,
       executive_summary ->> 'text'                    AS abstract,
       'project_report'                                AS doctype,
       'CORDIS'                                        AS repository,
       'OPEN' :: TEXT                                  AS rights,
       administrative_data ->> 'date_to' :: TEXT       AS pubyear
FROM   project_portfolio

-- doc_project
INSERT INTO doc_project
            (docid,
             projectid,
             inferred)
SELECT REPLACE(REPLACE(projectid, '40|corda__h2020', '50|h2020_object'),
              '40|corda_______', '50|fp7___object') AS docid,
       projectid,
       TRUE                                         AS inferred
FROM   project_portfolio
UNION ALL
SELECT REPLACE(REPLACE(projectid, '40|corda__h2020', '50|h2020summary'),
              '40|corda_______', '50|fp7__summary') AS docid,
       projectid,
       TRUE                                         AS inferred
FROM   project_portfolio
UNION ALL
SELECT REPLACE(REPLACE(projectid, '40|corda__h2020', '50|h2020___exec'),
              '40|corda_______', '50|fp7_____exec') AS docid,
       projectid,
       TRUE                                         AS inferred
FROM   project_portfolio


-- updates the project table with data from the project_portfolios
update project p set (total_cost,contribution,currency) = ((administrative_data->>'Total cost')::numeric, (administrative_data->>'contribution')::numeric, 'EURO'::text) from
project_portfolio pp where pp.projectid = p.id ;

update project_organization po set (contribution, currency) = (U.contribution, 'EURO'::text) from
(
    select projectid, '20|ec__________::'||MD5(o->>'pic') as orgid, (o->>'contribution')::numeric as contribution from
        (
            select projectid, json_array_elements(administrative_data->'coordinators') as o from project_portfolio
            union all
            select projectid, json_array_elements(administrative_data->'participants') as o from project_portfolio
        ) as T
) as U where po.orgid = U.orgid and po.projectid = U.projectid

-- include start/end dates from project portfolios
update project p set startdate = pp.administrative_data->>'date_from' from project_portfolio pp where p.startdate is null and p.id = pp.projectid ;
update project p set enddate = pp.administrative_data->>'date_to' from project_portfolio pp where p.enddate is null and p.id = pp.projectid ;