--Create Schema

CREATE SCHEMA pschema;

-- Create tables

CREATE TABLE pschema.patients (
    id character varying(256) ENCODE lzo,
    birthdate date ENCODE az64,
    deathdate date ENCODE az64,
    ssn character varying(256) ENCODE lzo,
    passport character varying(256) ENCODE lzo,
    prefix character varying(256) ENCODE lzo,
    first character varying(256) ENCODE lzo,
    last character varying(256) ENCODE lzo,
    suffix character varying(256) ENCODE lzo,
    maiden character varying(256) ENCODE lzo,
    marital character varying(256) ENCODE lzo,
    race character varying(256) ENCODE lzo,
    ethnicity character varying(256) ENCODE lzo,
    gender character varying(256) ENCODE lzo,
    birthplace character varying(256) ENCODE lzo,
    address character varying(256) ENCODE lzo,
    city character varying(256) ENCODE lzo,
    state character varying(256) ENCODE lzo,
    county character varying(256) ENCODE lzo,
    zip integer ENCODE az64,
    healthcare_expenses double precision ENCODE raw,
    healthcare_coverage double precision ENCODE raw,
    phonenumber character varying(15) DEFAULT '111-111-1111':: character varying ENCODE lzo,
    drid character varying(10) ENCODE lzo
) DISTSTYLE AUTO;

CREATE TABLE pschema.conditions (
    start date ENCODE az64,
    stop date ENCODE az64,
    patient character varying(256) ENCODE lzo,
    encounter character varying(256) ENCODE lzo,
    code character varying(256) ENCODE lzo,
    description character varying(256) ENCODE lzo
) DISTSTYLE AUTO;

CREATE TABLE pschema.encounters (
    id character varying(256) ENCODE lzo,
    start date ENCODE az64,
    stop date ENCODE az64,
    patient character varying(256) ENCODE lzo,
    organization character varying(256) ENCODE lzo,
    provider character varying(256) ENCODE lzo,
    payer character varying(256) ENCODE lzo,
    encounterclass character varying(256) ENCODE lzo,
    code integer ENCODE az64,
    description character varying(256) ENCODE lzo,
    base_encounter_cost real ENCODE raw,
    total_claim_cost real ENCODE raw,
    payer_coverage real ENCODE raw,
    reasoncode integer ENCODE az64,
    reasondescription character varying(256) ENCODE lzo
) DISTSTYLE AUTO;

CREATE TABLE pschema.immunizations (
    date date ENCODE az64,
    patient character varying(256) ENCODE lzo,
    encounter character varying(256) ENCODE lzo,
    code integer ENCODE az64,
    description character varying(256) ENCODE lzo,
    base_cost real ENCODE raw
) DISTSTYLE AUTO;

--Load tables
NOTE: Ensure you have default role assigned to your Redshift cluster and it has access to read S3 files.  If not, please provide arn of a role which has S3 read access.

copy pschema.patients from 's3://aws-blogs-artifacts-public/artifacts/BDB-4459/patients.csv' iam_role default delimiter ',' region 'us-east-1';
copy pschema.conditions from 's3://aws-blogs-artifacts-public/artifacts/BDB-4459/conditions.csv' iam_role default delimiter ',' region 'us-east-1';
copy pschema.immunizations from 's3://aws-blogs-artifacts-public/artifacts/BDB-4459/immunizations.csv' iam_role default delimiter ',' region 'us-east-1';
copy pschema.encounters from 's3://aws-blogs-artifacts-public/artifacts/BDB-4459/encounters.csv' iam_role default delimiter ',' region 'us-east-1';