
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)  );

create table paf_ppz (
  outcode varchar(10),
  incode varchar(10),
  codetype varchar(1),
  easting varchar(10),
  northing varchar(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 'OSG_0909.CSV' into table loc fields terminated by ',' enclosed by '"' lines terminated by '\r\n'

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';
load data local infile 'PPZ_0909.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;



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

insert into unified_gaz ( auth, auth_key, type, place_name, fqn, aliases, status, northing, easting )
Select 'paf', 
       CONCAT('paf:pcs:',pcs.outcode, pcs.incode),
       '1. postcode',
       CONCAT(pcs.outcode, ' ',pcs.incode),
       CONCAT_WS(', ', CONCAT(pcs.outcode, ' ', pcs.incode), tfr.tfr_name, loc.log_name, ptn.town_name, ptn.county, country.name, 'UK'),
       CONCAT(pcs.outcode, pcs.incode),
       'imp',
       ppz.northing,
       ppz.easting
from   pcs, tfr, loc, ptn, country, ppz
where  pcs.tfr_fk = tfr.tfr_key
  and  pcs.loc_fk = loc.loc_key
  and  pcs.ptn_fk = ptn.ptn_key
  and  pcs.outcode = ppz.outcode
  and  pcs.incode = ppz.incode
  and ptn.country_code = country.code;

