
create table migrate_users as select ru.id, trim(ru.login) email, null district_name, rp.name first_name , null  dob
                from res_users ru
                join res_partner rp on ru.partner_id  = rp.id;

                create or replace view registration_history_last as
select rp.id partner_id, max(rh.registration_date) registration_date, max (rh.expiration_date) expiration_date, max(rh.invoice_id) invoice_id
from res_partner rp
join registration_history rh  on rp.id = rh.partner_id
group by rp.id;

create or replace view registration_history_view as
select date(registration_date) registration_date, date(expiration_date) expiration_date, partner_id,
row_number() over() as new_id
from registration_history;


create table migrate_partner as
select rp.id, rp.commercial_partner_id , parent_id, rp.ownership , rp."function" occupation, rp.email , replace(rp.mobile, ' ', '')  mobile,
rp.name, rp.trademark_name , date(rp.birthdate) birthdate, date(rp.create_date) create_date , date(rp.write_date) write_date , rp.street , rp.city , rp.state, rp.zip, rc."name" place_of_birth,
rp.district_id , rd."name"  district,  rp.region_id , rp.is_moc_customer,
rp.tin, rp.d_loc ,
rp.agency_issued_license_id , ail."name" agency, rp.license agency_license_required, date(rp.license_expiration_date)  agency_license_expiration_date,
rh.registration_date registration_date , rh.expiration_date  expiration_date, rh.invoice_id invoice_id,
rp.industry_id, i.name industry,
rp.industry_type_id, it."name" license_type,
rp.type_orgz_id , to2."name"  company_type,
rp.res_class_id business_class_id, cn."name" business_class,
row_number() over() as new_id
from res_partner rp
left join registration_history_last rh  on rp.id = rh.partner_id
left join industry i on i.id = rp.industry_id
left join industry_type it on it.id = rp.industry_type_id
left join type_organization to2 on to2.id = rp.type_orgz_id
left join conf_new cn on cn.id = rp.res_class_id
left join res_district rd on rd.id = rp.district_id
left join res_country rc  on rc.id = rp.nationality_id
left join agency_issued_license ail ON  ail.id = rp.agency_issued_license_id
order by rp.id;
