create table migrate_subcategory as  select id, create_date, create_uid, write_uid, write_date, upper(upper(trim(regexp_replace(name, E'[\\n\\r\\t. ]+', ' ', 'g')))) as name  from sub_categ;

create table migrate_vehicle_color as  select id, create_date, create_uid, write_uid, write_date, upper(trim(regexp_replace(name, E'[\\n\\r\\t. ]+', ' ', 'g'))) as name from vehicle_color;

create table migrate_fuel_type as  select id, create_date, create_uid, write_uid, write_date, upper(trim(regexp_replace(name, E'[\\n\\r\\t. ]+', ' ', 'g'))) as name from fuel_type;

create table migrate_vehicle_type as select id, create_date, create_uid, write_uid, write_date, upper(trim(regexp_replace(name, E'[\\n\\r\\t. ]+', ' ', 'g'))) as name from vehicle_type;

create table migrate_vehicle_make as select id, create_date, create_uid, write_uid, write_date, upper(trim(regexp_replace(name, E'[\\n\\r\\t. ]+', ' ', 'g'))) as name from vehicle_make;

create table migrate_model as select vm.id, upper(trim(regexp_replace(vm.name, E'[\\n\\r\\t. ]+', ' ', 'g'))) model, vmm.id make_id, upper(trim(regexp_replace(vmm.name, E'[\\n\\r\\t. ]+', ' ', 'g')))  make from vehicle_model vm , vehicle_make vmm where vm.make = vmm.id;

create table migrate_region as select id, create_date, create_uid, write_uid, write_date, upper(trim(regexp_replace(name, E'[\\n\\r\\t. ]+', ' ', 'g'))) as name, state from region_region;

create table migrate_district as select dd.id, upper(trim(regexp_replace(dd.name, E'[\\n\\r\\t. ]+', ' ', 'g'))) district, rr.id region_id, trim(regexp_replace(rr.name, E'[\\n\\r\\t. ]+', ' ', 'g')) region_name from district_district dd , region_region rr  where dd.region = rr.id;

create table migrate_tax_sticker as select lc.id, upper(trim(regexp_replace(lc.name, E'[\\n\\r\\t. ]+', ' ', 'g'))) sticker_number, is_assigned::text is_assigned ,  lc.quarter , qy.name tax_year
                    from label_code lc
                    join quarter_year qy on lc.year  = qy.id;

create table migrate_users as select ru.id, trim(ru.login) email, upper(trim(regexp_replace(dd.name, E'[\\n\\r\\t. ]+', ' ', 'g')))  district_name , rp.name first_name , rp.dob
                from res_users ru
                full outer join district_district dd on ru.location_id  = dd.id
                join res_partner rp on ru.partner_id  = rp.id;


create table invalid_vehicle as
select * from vehicle_vehicle vv  where trim(lower(license_plate))  in
(select upper(trim(regexp_replace(license_plate, E'[\\n\\r\\t. ]+', ' ', 'g'))) license_plate from vehicle_vehicle vv group by upper(trim(regexp_replace(license_plate, E'[\\n\\r\\t. ]+', ' ', 'g')))  having count(upper(trim(regexp_replace(license_plate, E'[\\n\\r\\t. ]+', ' ', 'g')))) > 1)
union
select * from vehicle_vehicle vv  where license_plate like '%.%'
union
select * from vehicle_vehicle vv  where length(license_plate) < 3
union
select * from vehicle_vehicle vv  where license_plate like '%0000%'
union
select * from vehicle_vehicle vv  where upper(trim(regexp_replace(chassis_no, E'[\\n\\r\\t. ]+', ' ', 'g'))) in (select upper(trim(regexp_replace(chassis_no, E'[\\n\\r\\t. ]+', ' ', 'g')))  as chassis_no from vehicle_vehicle group by upper(trim(regexp_replace(chassis_no, E'[\\n\\r\\t. ]+', ' ', 'g')))   having count(1) > 1)
union
select * from vehicle_vehicle vv  where upper(trim(regexp_replace(vin_number, E'[\\n\\r\\t. ]+', ' ', 'g'))) in (select upper(trim(regexp_replace(vin_number, E'[\\n\\r\\t. ]+', ' ', 'g')))  as vin_number from vehicle_vehicle group by upper(trim(regexp_replace(vin_number, E'[\\n\\r\\t. ]+', ' ', 'g')))   having count(1) > 1);


create table  valid_vehicle as
select * from vehicle_vehicle vv
where vv.id not in (select id from invalid_vehicle);



create table migrate_vehicle_unfiltered as select vv.*,
upper(trim(regexp_replace(vm.name, E'[\\n\\r\\t. ]+', ' ', 'g'))) model_name,
upper(trim(regexp_replace(vc.name, E'[\\n\\r\\t. ]+', ' ', 'g')))  vehicle_color,
upper(trim(regexp_replace(sc."name" , E'[\\n\\r\\t. ]+', ' ', 'g')))  sub_category_name ,
upper(trim(regexp_replace(vt."name" , E'[\\n\\r\\t. ]+', ' ', 'g')))  vehicle_type,
upper(trim(regexp_replace(ft."name" , E'[\\n\\r\\t. ]+', ' ', 'g'))) fuel_type_name,
yy.name vehicle_year
                from vehicle_vehicle vv
                full outer join quarter_year yy on vv.year = yy.id
                full outer join vehicle_model vm on vv.model = vm.id
                full outer join vehicle_color vc on vv.color = vc.id
                full outer join sub_categ sc on vv.sub_category  = sc.id
                full outer join vehicle_type vt on vv."type"  = vt.id
                full outer join fuel_type ft on vv.fuel_type  = ft.id;

create table migrate_vehicle as select vv.*
                from migrate_vehicle_unfiltered vv, quarter_year yy,vehicle_model vm, vehicle_color vc, sub_categ sc, vehicle_type vt , fuel_type ft
                where vv.model = vm.id
                and vv.color = vc.id
                and vv.sub_category  = sc.id
                and vv."type"  = vt.id
                and vv.year = yy.id
                and vv.fuel_type  = ft.id;

create table migrate_partner as select rp.*,
upper(trim(regexp_replace(hc1.name , E'[\\n\\r\\t. ]+', ' ', 'g'))) eye_color_name,
upper(trim(regexp_replace(hc2.name , E'[\\n\\r\\t. ]+', ' ', 'g'))) hair_color_name,
row_number() over() as new_id,
rs.name as state_name
                from res_partner rp
                full outer join human_color hc1 on hc1.id  = rp.eye_color
                full outer join human_color hc2 on hc2.id  = rp.hair_color
                full outer join res_country_state rs on rs.id = state_id
                where is_company = false
                order by rp.id;

create table migrate_company as select * from res_partner rp where is_company = TRUE order by id;



create table migrate_clear_customer_ownership as select id, purchase_price, cvo.partner_id, cvo.vehicle_id,
cvo.curr_ownership_status,
cvo.security_interest,
cvo.download, cvo.invoice_id
from customer_vehicle_ownership cvo
where curr_ownership_status = 'owner';


create table migrate_title_transactions as
select cvo.id,
cvo.purchase_price,
cvo.partner_id partner_id,
cvo.vehicle_id,
upper(cvo.curr_ownership_status) curr_ownership_status,
case cvo.security_interest when true then 'Y' else 'N' end security_interest,
case cvo.download when true then 'Y' else 'N' end download,
vv.taxbase, cvo.invoice_id,
row_number() over() as new_id,
row_number() over() as new_invoice_id,
concat(cvo.invoice_id,'-',cvo.id) as invoice_number,
concat(ai.number,'-',cvo.id)  as title_number,
ai.number as ai_number,
ai.amount_total,
ai.date_invoice,
ai.date_due,
ai.date_invoice create_date,
upper(ai.state) invoice_status
from customer_vehicle_ownership cvo
join vehicle_vehicle vv on cvo.vehicle_id = vv.id
join account_invoice ai on cvo.invoice_id  = ai.id
				where  ai.state  = 'paid'
				and curr_ownership_status='owner'
				order by cvo.id;


create table migrate_tax_transactions as select
vt.id,
(select count(id) as titlecount from migrate_title_transactions) + row_number() over() as new_id,
ai.id as invoice_number,
vt.vehicle vehicle_id,
tt.partner_id,
ai.date_due,
ai.date_invoice,
ai.date_invoice create_date,
tqc.id as quarter_id,
tqc.quarter ,
qy.name tax_year,
vt.tax_number vt_tax_number,
lc.name tax_number,
lc.id tax_sticker_id,
ai.id as invoice_id,
(select count(id) as titlecount from migrate_title_transactions) + row_number() over() as new_invoice_id,
upper(ai.state) as invoice_status,
ai.number ai_number,
ai.amount_total,
ai.write_date
from vehicle_tax vt
join account_invoice ai on vt.invoice_id = ai.id
join tax_quarter_color tqc on tqc.id = vt.quarterly_tax_id
join quarter_year qy on qy.id = tqc.year
full outer join label_code lc on lc.id = vt.label_id
join migrate_title_transactions tt on vt.vehicle = tt.vehicle_id
where ai.date_invoice  > '2022-12-27'
and ai.state='paid'
and qy.name::integer > 2022
order by tt.id;

create table migrate_title_invoice_items as
select ail.name invoice_item , ail.price_unit , ail.price_subtotal, mt.new_invoice_id as invoice_id
from account_invoice_line ail,
migrate_title_transactions mt
where mt.invoice_id = ail.invoice_id;


create table migrate_tax_invoice_items as
select ail.name invoice_item , ail.price_unit , ail.price_subtotal, mt.new_invoice_id as invoice_id
from account_invoice_line ail,
migrate_tax_transactions mt
where mt.invoice_id = ail.invoice_id;


create table migrate_clean_payment as
select distinct ai.id invoice_id,
ap.id,
ap.state payment_status,
ap.payment_date ,
upper(regexp_replace(ap.air_bill_number, '[\\n\\r\\t.'' ]+', '', 'g')) air_bill_number ,
ai.product_type , ai.amount_total,
COALESCE(district, 1262, district) district_id,
ap.create_date, ai.write_uid
 from account_invoice ai,
    account_invoice_payment_rel aipr,
    account_payment ap,
    account_move_line aml
where ai.id = aipr.invoice_id
and aipr.payment_id  = ap.id
and aipr.payment_id = aml.payment_id
and aml.reconciled = true
and ap.state  = 'posted';


create table migrate_title_payment as select
row_number() over() as new_id,
mt.new_invoice_id as invoice_id,
ap.id,
ap.payment_status,
ap.payment_date ,
ap.air_bill_number ,
ap.product_type ,
ap.amount_total,
ap.create_date,
ap.write_uid,
trim(ru.login) email,
dd.district as district_name
            from migrate_clean_payment ap,
            migrate_district dd,
            res_users ru,
            migrate_title_transactions mt
            where ru.id = ap.write_uid
            and ap.district_id  = dd.id
            and ap.invoice_id = mt.invoice_id;

create table migrate_tax_payment as select
row_number() over() as new_id,
mt.new_invoice_id as invoice_id,
ap.id,
ap.payment_status,
ap.payment_date ,
ap.air_bill_number ,
ap.product_type ,
ap.amount_total,
ap.create_date,
ap.write_uid,
trim(ru.login) email,
dd.district as district_name
            from migrate_clean_payment ap,
            migrate_district dd,
            res_users ru,
            migrate_tax_transactions mt
            where ru.id = ap.write_uid
            and ap.district_id  = dd.id
            and ap.invoice_id = mt.invoice_id;

create index migrate_title_transactions_inv_idx on migrate_title_transactions(invoice_id);
create index migrate_title_invoice_items_inv_idx on migrate_title_invoice_items(invoice_id);
create index migrate_tax_invoice_items_inv_idx on migrate_tax_invoice_items(invoice_id);
create index migrate_title_payment_inv_idx on migrate_title_payment(invoice_id);
create index migrate_tax_payment_inv_idx on migrate_tax_payment(invoice_id);


update res_partner  set country_id  = 207;
update res_users set login = 'cartan1@puntlandgov.org' where id = 251;
update res_users set login = 'yasinjama001@puntlandgov.org' where id = 254;



create table invalid_vehicle as
select * from vehicle_vehicle vv  where trim(lower(license_plate))  in
(select trim(lower(license_plate)) license_plate from vehicle_vehicle vv group by  trim(lower(license_plate)) having count(trim(lower(license_plate))) > 1)
union
select * from vehicle_vehicle vv  where license_plate like '%.%'
union
select * from vehicle_vehicle vv  where length(license_plate) < 3
union
select * from vehicle_vehicle vv  where license_plate like '%0000%';



create table  valid_vehicle as
select * from vehicle_vehicle vv
where vv.id not in (select id from invalid_vehicle);




create table migrate_payment as select ai.id invoice_id, ap.id, ap.state payment_status, ap.payment_date , ap.air_bill_number ,  ai.product_type , ai.amount_total, trim(dd.name) district_name, trim(ru.login) email, ap.create_date
                                    from account_invoice ai , account_invoice_payment_rel aipr, account_payment ap, district_district dd, res_users ru, account_move_line aml
                                    where ai.id = aipr.invoice_id
                                    and aipr.payment_id  = ap.id
                                    and ru.id = ap.write_uid
                                    and ap.district  = dd.id
                                    and aipr.payment_id = aml.payment_id
                                    and aml.reconciled = true
                                    and ap.state  = 'posted';

create index migrate_payment_invoice_idx on migrate_payment(invoice_id);
create index account_invoice_line_inv_idx on account_invoice_line(invoice_id);





