
create table paf_loc (
  loc_key varchar(10) primary key,
  log_name varchar(100) );
  
create table paf_pcs (
  outcode varchar(10),
  incode varchar(10),
  codetype varchar(1),
  ptn_fk varchar(10),
  loc_fk varchar(10),
  tfr_fk varchar(10),
  dep_tfr_key varchar(10),
  del_point_ct varchar(10),
  constraint primary key (outcode, incode),
  constraint postcode_idx unique ( outcode, incode )  );

create table paf_ppz (
  outcode varchar(10),
  incode varchar(10),
  codetype varchar(1),
  easting numeric(20,10),
  northing numeric(20,10),
  lat numeric(20,10),
  lng numeric(20,10),
  del_point_ct varchar(10),
  constraint primary key (outcode, incode) );
  
create table paf_ptn (
  ptn_key varchar(10) primary key,
  postal_area varchar(20),
  town_name varchar(50),
  county varchar(50),
  county_flag varchar(1),
  country_code varchar(1)
);

create table paf_tfr (
  tfr_key varchar(10) primary key,
  tfr_name varchar(80)
);

create table paf_country ( 
  code varchar(1) primary key,
  name varchar(50));

insert into paf_country values ( 'E', 'England' );
insert into paf_country values ( 'S', 'Scotland' );
insert into paf_country values ( 'W', 'Wales' );
insert into paf_country values ( 'N', 'Northern Ireland' );

load data local infile 'LOC_0909.CSV' into table paf_loc fields terminated by ',' enclosed by '"' lines terminated by '\r\n';
load data local infile 'PCS_0909.CSV' into table paf_pcs fields terminated by ',' enclosed by '"' lines terminated by '\r\n';
# Revised lat long table
load data local infile 'GBLL1003.CSV' into table paf_ppz fields terminated by ',' enclosed by '"' lines terminated by '\r\n';
load data local infile 'PTN_0909.CSV' into table paf_ptn fields terminated by ',' enclosed by '"' lines terminated by '\r\n';
load data local infile 'TFR_0909.CSV' into table paf_tfr fields terminated by ',' enclosed by '"' lines terminated by '\r\n';


Select paf_pcs.outcode, paf_pcs.incode, paf_tfr.tfr_name, paf_loc.log_name, paf_ptn.town_name, paf_ptn.county, paf_country.name
from   paf_pcs, paf_tfr, paf_loc, paf_ptn, paf_country
where  paf_pcs.tfr_fk = paf_tfr.tfr_key
  and  paf_pcs.loc_fk = paf_loc.loc_key
  and  paf_pcs.ptn_fk = paf_ptn.ptn_key
  and paf_ptn.country_code = paf_country.code
limit 10;

Select 'paf', 
       CONCAT('paf:pcs:',paf_pcs.outcode, paf_pcs.incode),
       CONCAT(paf_pcs.outcode, ' ',paf_pcs.incode),
       CONCAT_WS(', ', CONCAT(paf_pcs.outcode, ' ', paf_pcs.incode), paf_tfr.tfr_name, paf_loc.log_name, paf_ptn.town_name, paf_ptn.county, paf_country.name, 'UK'),
       CONCAT(paf_pcs.outcode, paf_pcs.incode),
       'imp'
from paf_pcs, paf_tfr, paf_loc, paf_ptn, paf_country
where paf_pcs.tfr_fk = paf_tfr.tfr_key
  and paf_pcs.loc_fk = paf_loc.loc_key
  and paf_pcs.ptn_fk = paf_ptn.ptn_key
  and paf_ptn.country_code = paf_country.code
limit 10;



# This section populates a table of thoroughfare instances (As opposed to thoroughfare names).

select 'Populating paf tfr instances';


create table paf_tfr_instances (
  tfr_inst_id varchar(30) primary key,
  name varchar(80),
  fqn varchar(200),
  outcode varchar(5),
  tfr_fk varchar(20),
  centroid_lat numeric(20,10),
  centroid_lon numeric(20,10)
);

create index tfr_outcode_tfr_idx on paf_tfr_instances ( tfr_fk, outcode );

insert into paf_tfr_instances ( tfr_inst_id, name, fqn, outcode, tfr_fk )
select distinct CONCAT_WS(':','paf:tfr',paf_pcs.outcode,paf_pcs.ptn_fk,paf_pcs.loc_fk,paf_pcs.tfr_fk), 
                paf_tfr.tfr_name,
                CONCAT_WS(', ', paf_tfr.tfr_name, paf_loc.log_name, paf_ptn.town_name, paf_ptn.county, paf_country.name, 'UK'),
                paf_pcs.outcode,
                paf_pcs.tfr_fk
from paf_pcs 
       join paf_tfr on paf_pcs.tfr_fk = paf_tfr.tfr_key
       left outer join paf_loc on paf_pcs.loc_fk = paf_loc.loc_key
       left outer join paf_ptn 
           left outer join paf_country on paf_ptn.country_code = paf_country.code
           on paf_pcs.ptn_fk = paf_ptn.ptn_key
       left outer join paf_ppz on (paf_pcs.outcode = paf_ppz.outcode and paf_pcs.incode = paf_ppz.incode ) ;


select 'populating tfr point table';

create table paf_tfr_point (
  tfr_inst_id varchar(30) primary key,
  point_incode varchar(10) );

insert into paf_tfr_point 
select tfr_inst_id, max(paf_pcs.incode)
from paf_tfr_instances, paf_pcs
where paf_tfr_instances.tfr_fk = paf_pcs.tfr_fk
and paf_tfr_instances.outcode = paf_pcs.outcode
group by tfr_inst_id;


# select pti.tfr_inst_id, pti.name, pti.outcode, avg(paf_ppz.lat), avg(paf_ppz.lng) 
# from paf_tfr_instances pti, paf_pcs, paf_ppz
# where paf_pcs.tfr_fk pti.tfr_fk
# and paf_pcs.outcode = pti.outcode
# and paf_pcs.outcode = paf_ppz.outcode
# and paf_pcs.incode = paf_ppz.incode;
 

select 'creating unified gaz';

create table paf_unified_gaz (
  auth varchar(10),
  auth_key varchar(40),
  type varchar(20),
  place_name varchar(60),
  fqn varchar(200),
  fqnidx varchar(200),
  aliases varchar(200),
  status varchar(10),
  northing numeric(20,10),
  easting numeric(20,10),
  lat numeric(20,10),
  lng numeric(20,10),
  constraint primary key (auth, auth_key) );


# Postcodes

select 'loading postcodes into unified table';

insert into paf_unified_gaz ( auth, auth_key, type, place_name, fqn, fqnidx, aliases, status, northing, easting, lat, lng )
Select 'paf', 
       CONCAT('paf:pcs:',paf_pcs.outcode, paf_pcs.incode),
       '1. postcode',
       CONCAT(paf_pcs.outcode, ' ',paf_pcs.incode),
       CONCAT_WS(', ', CONCAT(paf_pcs.outcode, ' ', paf_pcs.incode), paf_tfr.tfr_name, paf_loc.log_name, paf_ptn.town_name, paf_ptn.county, paf_country.name, 'UK'),
       CONCAT_WS(', ', CONCAT(paf_pcs.outcode, ' ', paf_pcs.incode), paf_tfr.tfr_name, paf_loc.log_name, paf_ptn.town_name, paf_ptn.county, paf_country.name, 'UK'),
       CONCAT_WS(', ', CONCAT(paf_pcs.outcode, paf_pcs.incode), paf_tfr.tfr_name, paf_loc.log_name, paf_ptn.town_name, paf_ptn.county, paf_country.name, 'UK'),
       'imp',
       paf_ppz.northing,
       paf_ppz.easting,
       paf_ppz.lat,
       paf_ppz.lng
from paf_pcs 
       left outer join paf_tfr on paf_pcs.tfr_fk = paf_tfr.tfr_key
       left outer join paf_loc on paf_pcs.loc_fk = paf_loc.loc_key
       left outer join paf_ptn 
           left outer join paf_country on paf_ptn.country_code = paf_country.code
           on paf_pcs.ptn_fk = paf_ptn.ptn_key
       left outer join paf_ppz on (paf_pcs.outcode = paf_ppz.outcode and paf_pcs.incode = paf_ppz.incode )
;

# Thoroughfares

select 'importing unified gaz records';
select count(*) from paf_unified_gaz;

insert into paf_unified_gaz ( auth, auth_key, type, place_name, fqn, aliases, status, northing, easting, lat, lng )
select 'paf',
       paf_tfr_instances.tfr_inst_id,
       '2. Thoroughfare',
       paf_tfr_instances.name,
       paf_tfr_instances.fqn,
       null,
       'imp',
       paf_ppz.northing,
       paf_ppz.easting,
       paf_ppz.lat,
       paf_ppz.lng
from paf_tfr_instances, paf_tfr_point, paf_ppz
where paf_tfr_instances.tfr_inst_id = paf_tfr_point.tfr_inst_id
and paf_tfr_instances.outcode = paf_ppz.outcode
and paf_tfr_point.point_incode = paf_ppz.incode;

# and paf_tfr_instances.tfr_inst_id='paf:tfr:S11:1196:00000:108491';



