Create schema and tables

DROP SCHEMA IF EXISTS addressbasepremium CASCADE;
COMMIT;
CREATE SCHEMA addressbasepremium;
COMMIT;


CREATE TABLE addressbasepremium.abp_blpu_record ( 
RECORD_IDENTIFIER bigint,
CHANGE_TYPE varchar(1) , 
PRO_ORDER integer , 
UPRN bigint NOT NULL, 
LOGICAL_STATUS integer , 
BLPU_STATE integer ,
BLPU_STATE_DATE date , 
PARENT_UPRN bigint , 
X_COORDINATE numeric(9,3) , 
Y_COORDINATE numeric(10,3) , 
RPC integer , 
LOCAL_CUSTODIAN_CODE integer , 
START_DATE date ,
END_DATE date , 
LAST_UPDATE_DATE date , 
ENTRY_DATE date , 
POSTAL_ADDRESS char(1), 
POSTCODE_LOCATOR char(8), 
MULTI_OCC_COUNT integer);
COMMIT;


CREATE TABLE addressbasepremium.abp_classification (
RECORD_IDENTIFIER bigint NOT NULL, 
CHANGE_TYPE varchar(1), 
PRO_ORDER integer, 
UPRN bigint NOT NULL,
CLASS_KEY varchar(14), 
CLASSIFICATION_CODE varchar(8), 
CLASS_SCHEME varchar(60), 
SCHEME_VERSION numeric(4,2), 
START_DATE date, 
END_DATE date,
LAST_UPDATE_DATE date, 
ENTRY_DATE date);
COMMIT;

CREATE TABLE addressbasepremium.abp_delivery_point ( 
RECORD_IDENTIFIER bigint, 
CHANGE_TYPE varchar(1),
PRO_ORDER integer, 
UPRN bigint, 
PARENT_ADDRESSABLE_UPRN bigint, 
RM_UDPRN bigint, 
ORGANISATION_NAME varchar(60), 
DEPARTMENT_NAME varchar(60), 
SUB_BUILDING_NAME varchar(60),
BUILDING_NAME varchar(60), 
BUILDING_NUMBER integer, 
DEPENDENT_THOROUGHFARE_NAME varchar(80), 
THROUGHFARE_NAME varchar(80),
DOUBLE_DEPENDENT_LOCALITY varchar(35), 
DEPENDENT_LOCALITY varchar(35),
POST_TOWN varchar(30), 
POSTCODE varchar(8), 
POSTCODE_TYPE varchar(5), 
WELSH_DEPENDENT_THOROUGHFARE varchar(80),
WELSH_THOROUGHFARE_NAME varchar(80),
WELSH_DOUBLE_DEPENDENT_LOCALITY varchar(35),
WELSH_DEPENDENT_LOCALITY varchar(35),
WELSH_POST_TOWN varchar(30), 
PO_BOX_NUMBER varchar(6), 
PROCESS_DATE date, 
START_DATE date, 
END_DATE date, 
LAST_UPDATE_DATE date,
ENTRY_DATE date);
COMMIT;

CREATE TABLE addressbasepremium.abp_crossref (
RECORD_IDENTIFIER bigint NOT NULL, 
CHANGE_TYPE varchar(1), 
PRO_ORDER integer, 
UPRN bigint NOT NULL,
XREF_KEY varchar(14), 
CROSS_REFERENCE varchar(20), 
VERSION integer, 
SOURCE varchar(6), 
START_DATE date, 
END_DATE date,
LAST_UPDATE_DATE date, 
ENTRY_DATE date);
COMMIT;

CREATE TABLE addressbasepremium.abp_lpi (
RECORD_IDENTIFIER bigint NOT NULL,
CHANGE_TYPE varchar(1),
PRO_ORDER integer, 
UPRN bigint NOT NULL DEFAULT '0', 
LPI_KEY varchar(14), 
LANGUAGE varchar(3),
LOGICAL_STATUS integer,
START_DATE date, 
END_DATE date, 
LAST_UPDATE_DATE date,
ENTRY_DATE date,
SAO_START_NUMBER integer,
SAO_START_SUFFIX varchar(2),
SAO_END_NUMBER integer,
SAO_END_SUFFIX varchar(2),
SAO_TEXT varchar(90), 
PAO_START_NUMBER integer, 
PAO_START_SUFFIX varchar(2),
PAO_END_NUMBER integer,
PAO_END_SUFFIX varchar(2), 
PAO_TEXT varchar(90),
USRN bigint, 
USRN_MATCH_INDICATOR varchar(1),
AREA_NAME varchar(35), 
LEVEL char(30),
OFFICIAL_FLAG char(1));
COMMIT;

CREATE TABLE addressbasepremium.abp_organisation ( 
RECORD_IDENTIFIER bigint NOT NULL,
CHANGE_TYPE varchar(1), 
PRO_ORDER integer,
UPRN bigint NOT NULL,
ORG_KEY varchar(14),
ORGANISATION varchar(120), 
LEGAL_NAME varchar(60), 
START_DATE date, 
END_DATE date, 
LAST_UPDATE_DATE date, 
ENTRY_DATE date);
COMMIT;

CREATE TABLE addressbasepremium.abp_street ( 
RECORD_IDENTIFIER bigint NOT NULL, 
CHANGE_TYPE varchar(1), 
PRO_ORDER integer, 
USRN bigint NOT NULL, 
RECORD_TYPE integer, 
SWA_ORG_REF_NAMING bigint, 
STATE integer, 
STATE_DATE date,
STREET_SURFACE integer,
STREET_CLASSIFICATION integer, 
VERSION integer, 
STREET_START_DATE date,
STREET_END_DATE date, 
LAST_UPDATE_DATE date, 
RECORD_ENTRY_DATE date, 
STREET_START_X numeric(9,3),
STREET_START_Y numeric(10,3),
STREET_END_X numeric(9,3),
STREET_END_Y numeric(10,3), 
STREET_TOLERANCE integer);
COMMIT;

CREATE TABLE addressbasepremium.abp_street_descriptor ( 
RECORD_IDENTIFIER bigint NOT NULL, 
CHANGE_TYPE varchar(1), 
PRO_ORDER integer, 
USRN bigint NOT NULL, 
STREET_DESCRIPTOR varchar(100), 
LOCALITY_NAME varchar(35),
TOWN_NAME varchar(30), 
ADMINISTRATIVE_AREA varchar(30), 
LANGUAGE varchar(3));
COMMIT;

CREATE TABLE addressbasepremium.abp_successor (
RECORD_IDENTIFIER bigint NOT NULL, 
CHANGE_TYPE varchar(1), 
PRO_ORDER integer, 
UPRN bigint NOT NULL, 
SUCC_KEY varchar(14),
START_DATE date, 
END_DATE date, 
LAST_UPDATE_DATE date,
ENTRY_DATE date, 
SUCCESSOR integer);
COMMIT;