Merge pull request #127 from Pear-Trading/master

Master merge for v0.10.10

.gitignore

@@ -4,6 +4,7 @@ hypnotoad.pid
 *.db
 *.db-wal
 *.db-shm
+*.db-journal
 *~
 /images
 *.swp

.idea/.gitignore

@@ -0,0 +1,7 @@
+# Default ignored files
+/workspace.xml
+/perl5local.xml
+
+# Datasource local storage ignored files
+/dataSources/
+/dataSources.local.xml

.idea/Foodloop-Server.iml

@@ -3,6 +3,7 @@
   <component name="NewModuleRootManager" inherit-compiler-output="true">
     <perl5>
       <path value="$MODULE_DIR$/lib" type="perl-library" />
+      <path value="$MODULE_DIR$/templates" type="mojo-template" />
     </perl5>
     <exclude-output />
     <content url="file://$MODULE_DIR$" />

.idea/sqldialects.xml

@@ -0,0 +1,7 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<project version="4">
+  <component name="SqlDialectMappings">
+    <file url="file://$PROJECT_DIR$/share/ddl/PostgreSQL" dialect="PostgreSQL" />
+    <file url="file://$PROJECT_DIR$/share/ddl/SQLite" dialect="SQLite" />
+  </component>
+</project>

.travis.yml

@@ -6,7 +6,7 @@ language: perl
 #  postgresql: "9.6"
 
 perl:
-  - "5.20"
+  - "5.26"
 env:
   - HARNESS_PERL_SWITCHES="-MDevel::Cover"
 install:

CHANGELOG.md

@@ -2,6 +2,8 @@
 
 # Next Release
 
+# v0.10.10
+
 * Added proper minion job support
 * **Admin Feature** Added importing of CSVs from Lancaster City Council
 * Added pagination support to searching of organisations during transaction submission in API

README.md

@@ -42,8 +42,32 @@ And then add the following to your configuration file:
   },
 ```
 
-This will then use an SQLite db for the minion backend, at minion.db
+This will then use an SQLite db for the minion backend, using `minion.db` as
+the database file. To start the minion itself, run:
 
+```
+./script/pear-local_loop minion worker
+```
+
+# Importing Ward Data
+
+To import ward data, get the ward data csv and then run the following command:
+
+```shell script
+./script/pear-local_loop minion job \
+  --enqueue 'csv_postcode_import' \
+  --args '[ "/path/to/ward/csv" ]'
+```
+
+# Setting up Entity Postcodes
+
+Assuming you have imported codepoint open, then to properly assign all
+ postcodes:
+ 
+```shell script
+./script/pear-local_loop minion job \
+  --enqueue entity_postcode_lookup
+```
 
 ## Example PostgreSQL setup
 
@@ -57,7 +81,46 @@ psql=# alter user minion with encrypted password 'abc123';
 psql=# grant all privileges on database localloop_minion to minion;
 ```
 
-# Dev notes
+# Development
+
+There are a couple of setup steps to getting a development environment ready.
+Use the corresponding instructions depending on what state your current setup
+is in.
+
+## First Time Setup
+
+First, decide if you're using SQLite or PostgreSQL locally. Development supports
+both, however production uses PostgreSQL. For this example we will use SQLite.
+As the default config is set up for this, no configuration changes are
+needed initially. So, first off, install dependencies:
+
+```shell script
+cpanm --installdeps . --with-feature=sqlite
+```
+
+Then install the database:
+
+```shell script
+./script/deploy_db install -c 'dbi:SQLite:dbname=foodloop.db'
+```
+
+Then set up the development users:
+
+```shell script
+./script/pear-local_loop dev_data --force
+```
+
+***Note: do NOT run that script on production.***
+
+Then you can start the application:
+
+```shell script
+morbo script/pear-local_loop -l http://*:3000
+```
+
+You can modify the host and port for listening as needed.
+
+# Old Docs
 
 ## Local test database
 

cpanfile

@@ -14,7 +14,6 @@ requires 'DBIx::Class::Schema::Loader';
 requires 'SQL::Translator';
 requires 'DateTime';
 requires 'DateTime::Format::Strptime', "1.73";
-requires 'DateTime::Format::SQLite';
 requires 'Try::Tiny';
 requires 'MooX::Options::Actions';
 requires 'Module::Runtime';
@@ -40,10 +39,12 @@ feature 'postgres', 'PostgreSQL Support' => sub {
   requires 'DBD::Pg';
   requires 'Test::PostgreSQL';
   requires 'Mojo::Pg';
+  requires 'DateTime::Format::Pg';
 };
 
 feature 'sqlite', 'SQLite Support' => sub {
   requires 'Minion::Backend::SQLite';
+  requires 'DateTime::Format::SQLite';
 };
 
 feature 'codepoint-open', 'Code Point Open manipulation' => sub {

lib/Pear/LocalLoop.pm

@@ -196,6 +196,7 @@ sub startup {
   $api_v1_org->post('/external/year_spend')->to('api-external#post_year_spend');
   $api_v1_org->post('/external/supplier_count')->to('api-external#post_supplier_count');
   $api_v1_org->post('/external/supplier_history')->to('api-external#post_supplier_history');
+  $api_v1_org->post('/external/lcc_tables')->to('api-external#post_lcc_table_summary');
 
   $api_v1_org->post('/pies')->to('api-v1-organisation-pies#index');
 
@@ -265,6 +266,8 @@ sub startup {
   $admin_routes->get('/import_from')->to('admin-import_from#index');
   $admin_routes->post('/import_from/suppliers')->to('admin-import_from#post_suppliers');
   $admin_routes->post('/import_from/transactions')->to('admin-import_from#post_transactions');
+  $admin_routes->post('/import_from/postcodes')->to('admin-import_from#post_postcodes');
+  $admin_routes->get('/import_from/org_search')->to('admin-import_from#org_search');
 
 #  my $user_routes = $r->under('/')->to('root#under');
 

lib/Pear/LocalLoop/Controller/Admin/ImportFrom.pm

@@ -9,7 +9,7 @@ sub index {
   $c->stash->{org_entities} = [
     map {
       { id => $_->entity_id, name => $_->name }
-    } $c->schema->resultset('Organisation')->all
+    } $c->schema->resultset('Organisation')->search({ name => { like => '%lancashire%' }}, { columns => [qw/ entity_id name / ]})
   ];
 
   $c->app->max_request_size(104857600);
@@ -43,6 +43,34 @@ sub post_suppliers {
   return $c->redirect_to('/admin/import_from');
 }
 
+sub post_postcodes {
+  my $c = shift;
+
+  unless ($c->param('postcodes_csv')) {
+    $c->flash(error => "No CSV file given");
+    return $c->redirect_to('/admin/import_from');
+  }
+
+  # Check file size
+  if ($c->req->is_limit_exceeded) {
+    $c->flash(error => "CSV file size is too large");
+    return $c->redirect_to('/admin/import_from');
+  }
+
+  my $file = $c->param('postcodes_csv');
+
+  my $filename = path($c->app->config->{upload_path}, time . 'postcodes.csv');
+
+  $file->move_to($filename);
+
+  my $job_id = $c->minion->enqueue('csv_postcode_import' => [ $filename ]);
+
+  my $job_url = $c->url_for("/admin/minion/jobs?id=$job_id")->to_abs;
+
+  $c->flash(success => "CSV import started, see status of minion job at: $job_url");
+  return $c->redirect_to('/admin/import_from');
+}
+
 sub post_transactions {
   my $c = shift;
 
@@ -76,4 +104,24 @@ sub post_transactions {
   return $c->redirect_to('/admin/import_from');
 }
 
+sub org_search {
+  my $c = shift;
+  my $term = $c->param('term');
+
+  my $rs = $c->schema->resultset('Organisation')->search(
+    { name => { like => $term . '%' } },
+    {
+      join    => 'entity',
+      columns => [ qw/ me.name entity.id / ]
+    },
+  );
+
+  my @results = ( map { {
+    label => $_->name,
+    value => $_->entity->id,
+  } } $rs->all);
+
+  $c->render( json => \@results );
+}
+
 1;

lib/Pear/LocalLoop/Controller/Admin/Transactions.pm

@@ -17,15 +17,10 @@ sub index {
   my $week_transaction_rs = $c->schema->resultset('ViewQuantisedTransaction' . $driver)->search(
     {},
     {
-      columns => [
+      select => [
-        {
+        { count => 'me.value', '-as' => 'count' },
-          quantised        => 'quantised_weeks',
+        { sum => 'me.value', '-as' => 'sum_value' },
-          count            => \"COUNT(*)",
+        'quantised_weeks',
-          sum_value        => $c->pg_or_sqlite(
-                                'SUM("me"."value")',
-                                'SUM("me"."value")',
-                              ),
-        }
       ],
       group_by => 'quantised_weeks',
       order_by => { '-asc' => 'quantised_weeks' },
@@ -33,8 +28,8 @@ sub index {
   );
 
   my @all_weeks = $week_transaction_rs->all;
-  my $first_week_count = $all_weeks[0]->get_column('count') || 0;
+  my $first_week_count = defined $all_weeks[0] ? $all_weeks[0]->get_column('count') || 0 : 0;
-  my $first_week_value = $all_weeks[0]->get_column('sum_value') / 100000 || 0;
+  my $first_week_value = defined $all_weeks[0] ? $all_weeks[0]->get_column('sum_value') / 100000 || 0 : 0;
   my $second_week_count = defined $all_weeks[1] ? $all_weeks[1]->get_column('count') || 0 : 0;
   my $second_week_value = defined $all_weeks[1] ? $all_weeks[1]->get_column('sum_value') / 100000 || 0 : 0;
 

lib/Pear/LocalLoop/Controller/Api/External.pm

@@ -64,6 +64,7 @@ sub post_lcc_suppliers {
   $v->optional('page')->number;
   $v->optional('sort_by');
   $v->optional('sort_dir');
+  $v->optional('search');
 
   my $order_by = [
     { -asc => 'organisation.name' },
@@ -85,6 +86,12 @@ sub post_lcc_suppliers {
   my $lcc_suppliers = $c->schema->resultset('Entity')->search(
     {
       'sales.buyer_id' => $user->entity->id,
+      ($v->param('search') ? (
+        '-or' => [
+          { 'organisation.name' => { 'like' => $v->param('search') . '%' } },
+          { 'organisation.postcode' => { 'like' => $v->param('search') . '%' } },
+        ]
+      ) : ()),
     },
     {
       join      => [ 'sales', 'organisation' ],
@@ -126,13 +133,15 @@ sub post_year_spend {
 
   my $user = $c->stash->{api_user};
 
-  # Temporary date lock for dev data
+  my $v = $c->validation;
-  my $last = DateTime->new(
+  $v->input($c->stash->{api_json});
-    year  => 2019,
+  $v->required('from');
-    month => 4,
+  $v->required('to');
-    day   => 1
+
-  );
+  return $c->api_validation_error if $v->has_error;
-  my $first = $last->clone->subtract(years => 1);
+
+  my $last = $c->parse_iso_date($v->param('to'));
+  my $first = $c->parse_iso_date($v->param('from'));
 
   my $dtf = $c->schema->storage->datetime_parser;
   my $driver = $c->schema->storage->dbh->{Driver}->{Name};
@@ -178,13 +187,15 @@ sub post_supplier_count {
 
   my $user = $c->stash->{api_user};
 
-  # Temporary date lock for dev data
+  my $v = $c->validation;
-  my $last = DateTime->new(
+  $v->input($c->stash->{api_json});
-    year  => 2019,
+  $v->required('from');
-    month => 4,
+  $v->required('to');
-    day   => 1
+
-  );
+  return $c->api_validation_error if $v->has_error;
-  my $first = $last->clone->subtract(years => 1);
+
+  my $last = $c->parse_iso_date($v->param('to'));
+  my $first = $c->parse_iso_date($v->param('from'));
 
   my $dtf = $c->schema->storage->datetime_parser;
   my $driver = $c->schema->storage->dbh->{Driver}->{Name};
@@ -199,40 +210,25 @@ sub post_supplier_count {
       buyer_id      => $user->entity->id,
     },
     {
-      columns  => [
+      join     => { 'seller' => 'organisation' },
-        'seller_id',
+      select   => [
-        {
+        { count => 'me.value', '-as' => 'count' },
-          quantised   => 'quantised_days',
+        { sum => 'me.value', '-as' => 'total_spend' },
-          count       => \"COUNT(*)",
+        'organisation.name',
-          total_spend => { sum => 'value' },
+        'me.quantised_days',
-        }
       ],
-      group_by => [ 'quantised_days', 'seller_id' ],
+      as       => [ qw/count total_spend name quantised_days/ ],
-      order_by => { '-asc' => 'quantised_days' },
+      group_by => [ qw/me.quantised_days seller.id organisation.id/ ],
+      order_by => { '-asc' => 'me.quantised_days' },
     }
   );
 
-  my $name_rs = $c->schema->resultset('Transaction')->search(
-    {
-      'me.buyer_id' => $user->entity->id,
-    },
-    {
-      join => { seller => 'organisation' },
-    }
-  );
-
-  my %name_map = (
-    map {
-      $_->seller->id => $_->seller->organisation->name,
-    } $name_rs->all
-  );
-
   my @graph_data = (
     map {{
       count  => $_->get_column('count'),
       value  => ($_->get_column('total_spend') / 100000) // 0,
-      date   => $_->get_column('quantised'),
+      date   => $_->get_column('quantised_days'),
-      seller => $name_map{ $_->get_column('seller_id') },
+      seller => $_->get_column('name'),
     }} $spend_rs->all,
   );
 
@@ -379,4 +375,115 @@ sub post_supplier_history {
   });
 }
 
+sub post_lcc_table_summary {
+  my $c = shift;
+
+  my $user = $c->stash->{api_user};
+
+  my $v = $c->validation;
+  $v->input($c->stash->{api_json});
+  $v->required('from');
+  $v->required('to');
+
+  return $c->api_validation_error if $v->has_error;
+
+  my $last = $c->parse_iso_date($v->param('to'));
+  my $first = $c->parse_iso_date($v->param('from'));
+
+  my $transaction_rs = $c->schema->resultset('Transaction');
+
+  my $dtf = $c->schema->storage->datetime_parser;
+  my $ward_transactions_rs = $transaction_rs->search(
+    {
+      purchase_time => {
+        -between => [
+          $dtf->format_datetime($first),
+          $dtf->format_datetime($last),
+        ],
+      },
+      buyer_id      => $user->entity->id,
+    },
+    {
+      join     => { seller => { postcode => { gb_postcode => 'ward' } } },
+      group_by => 'ward.id',
+      select   => [
+        { count => 'me.id', '-as' => 'count' },
+        { sum => 'me.value', '-as' => 'sum' },
+        'ward.ward'
+      ],
+      as       => [ qw/count sum ward_name/ ],
+    }
+  );
+
+  my $transaction_type_data = {};
+
+  my %meta_names = (
+    local_service            => "Local Services",
+    regional_service         => "Regional Services",
+    national_service         => "National Services",
+    private_household_rebate => "Private Household Rebates etc",
+    business_tax_and_rebate  => "Business Tax & Service Rebates",
+    stat_loc_gov             => "Statutory Loc Gov",
+    central_loc_gov          => "Central Gov HMRC",
+  );
+
+  for my $meta (qw/
+    local_service
+    regional_service
+    national_service
+    private_household_rebate
+    business_tax_and_rebate
+    stat_loc_gov
+    central_loc_gov
+  /) {
+    my $transaction_type_rs = $transaction_rs->search(
+      {
+        'me.purchase_time' => {
+          -between => [
+            $dtf->format_datetime($first),
+            $dtf->format_datetime($last),
+          ],
+        },
+        'me.buyer_id'      => $user->entity->id,
+        'meta.' . $meta    => 1,
+      },
+      {
+        join     => 'meta',
+        group_by => 'meta.' . $meta,
+        select   => [
+          { count => 'me.id', '-as' => 'count' },
+          { sum => 'me.value', '-as' => 'sum' },
+        ],
+        as       => [ qw/count sum/ ],
+      }
+    )->first;
+
+    $transaction_type_data->{$meta} = {
+      ($transaction_type_rs ? (
+        count => $transaction_type_rs->get_column('count'),
+        sum   => $transaction_type_rs->get_column('sum'),
+        type  => $meta_names{$meta},
+      ) : (
+        count => 0,
+        sum   => 0,
+        type  => $meta_names{$meta},
+      )),
+    }
+  }
+
+  my @ward_transaction_list = (
+    map {{
+      ward  => $_->get_column('ward_name') || "N/A",
+      sum   => $_->get_column('sum') / 100000,
+      count => $_->get_column('count'),
+    }} $ward_transactions_rs->all
+  );
+
+  return $c->render(json => {
+    success => Mojo::JSON->true,
+    wards   => \@ward_transaction_list,
+    types   => $transaction_type_data,
+  });
+}
+
 1;

lib/Pear/LocalLoop/Controller/Api/V1/Customer/Graphs.pm

@@ -20,20 +20,9 @@ sub index {
     avg_spend_last_week
     total_last_month
     avg_spend_last_month
-    total_duration
-    total_range
-    avg_spend_duration
-    avg_spend_range
   / );
 
   return $c->api_validation_error if $validation->has_error;
-  if ($validation->param('graph') == 'total_range' || $validation->param('graph') == 'avg_spend_range') {
-    $validation->required('start_date', 'end_date');
-  }
-
-  if ($validation->param('graph') == 'total_duration' || $validation->param('graph') == 'avg_spend_duration') {
-    $validation->required('duration');
-  }
 
   my $graph_sub = "graph_" . $validation->param('graph');
 
@@ -55,26 +44,15 @@ sub index {
 sub graph_total_last_week { return shift->_purchases_total_duration( 7 ) }
 sub graph_total_last_month { return shift->_purchases_total_duration( 30 ) }
 
-sub graph_total_duration {
-  my ( $c, $duration ) = @_;
-  return shift->__purchases_total_duration( $duration );
-}
-
-sub graph_total_range {
-  my ( $c, $start_date, $end_date ) = @_;
-  return shift->__purchases_total_duration( $duration, $start_date, $end_date );
-}
-
 sub _purchases_total_duration {
-  my ( $c, $day_duration, $start_date, $end_date ) = @_;
+  my ( $c, $day_duration ) = @_;
 
   my $duration = DateTime::Duration->new( days => $day_duration );
   my $entity = $c->stash->{api_user}->entity;
 
   my $data = { labels => [], data => [] };
 
-# if $start_date and $end_date are not present it will use $duration
+  my ( $start, $end ) = $c->_get_start_end_duration( $duration );
-  my ( $start, $end ) = $c->_get_start_end_duration( $duration, $start_date, $end_date );
 
   $data->{bounds} = {
     min => $c->format_iso_datetime( $start ),
@@ -103,26 +81,15 @@ sub _purchases_total_duration {
 sub graph_avg_spend_last_week { return shift->_purchases_avg_spend_duration( 7 ) }
 sub graph_avg_spend_last_month { return shift->_purchases_avg_spend_duration( 30 ) }
 
-sub graph_avg_spend_duration {
-  my ( $c, $duration ) = @_;
-  return shift->_purchases_avg_spend_duration( $duration );
-}
-
-sub graph_avg_spend_range {
-  my ( $c, $start_date, $end_date ) = @_;
-  return shift->_purchases_avg_spend_duration( $duration, $start_date, $end_date );
-}
-
 sub _purchases_avg_spend_duration {
-  my ( $c, $day_duration, $start_date, $end_date ) = @_;
+  my ( $c, $day_duration ) = @_;
 
   my $duration = DateTime::Duration->new( days => $day_duration );
   my $entity = $c->stash->{api_user}->entity;
 
   my $data = { labels => [], data => [] };
 
-# if $start_date and $end_date are not present it will use $duration
+  my ( $start, $end ) = $c->_get_start_end_duration( $duration );
-  my ( $start, $end ) = $c->_get_start_end_duration( $duration, $start_date, $end_date );
 
   $data->{bounds} = {
     min => $c->format_iso_datetime( $start ),
@@ -176,25 +143,9 @@ sub _purchases_avg_spend_duration {
 }
 
 sub _get_start_end_duration {
-  my ( $c, $duration, $start_date, $end_date ) = @_;
+  my ( $c, $duration ) = @_;
-  my $start;
+  my $end = DateTime->today;
-  my $end;
+  my $start = $end->clone->subtract_duration( $duration );
-
-  if ($end_date && $start_date) {
-    $start = DateTime->new(
-      year    => substr $start_date, 0, 4,
-      month   => substr $start_date, 4, 2,
-      days    => substr $start_date, 7, 2,
-    );
-    $end = DateTime->new(
-      year    => substr $end_date, 0, 4,
-      month   => substr $end_date, 4, 2,
-      days    => substr $end_date, 7, 2,
-    );
-  } else {
-    $end = $end->clone->subtract_duration( $duration );
-  }
-  
   return ( $start, $end );
 }
 

lib/Pear/LocalLoop/Import/LCCCsv/Postcodes.pm

@@ -0,0 +1,43 @@
+package Pear::LocalLoop::Import::LCCCsv::Postcodes;
+use Moo;
+
+use Geo::UK::Postcode::Regex;
+
+extends qw/Pear::LocalLoop::Import::LCCCsv/;
+
+has '+csv_required_columns' => (
+  builder => sub { return [ qw/
+  postcode
+  ward
+  / ]},
+);
+
+sub import_csv {
+  my ($self) = @_;
+
+  $self->check_headers;
+
+  while ( my $row = $self->get_csv_line ) {
+    $self->_row_to_result($row);
+  }
+}
+
+sub _row_to_result {
+  my ( $self, $row ) = @_;
+
+  my $postcode_obj = Geo::UK::Postcode::Regex->parse( $row->{postcode} );
+
+  my $ward = $self->schema->resultset('GbWard')->find_or_create(ward => $row->{ward});
+
+  my $postcode_r = $self->schema->resultset('GbPostcode')->find({
+    outcode => $postcode_obj->{outcode},
+    incode => $postcode_obj->{incode},
+  });
+
+  return unless $postcode_r;
+  return if $postcode_r->ward;
+
+  $postcode_r->update({ ward_id => $ward->id });
+}
+
+1;

lib/Pear/LocalLoop/Import/LCCCsv/Suppliers.pm

@@ -13,9 +13,9 @@ has '+csv_required_columns' => (
 sub import_csv {
   my ($self) = @_;
 
-  my $rows = $self->csv_data;
+  $self->check_headers;
 
-  foreach my $row ( @{$rows} ) {
+  while ( my $row = $self->get_csv_line ) {
     $self->_row_to_result($row);
   }
 }
@@ -37,7 +37,10 @@ sub _row_to_result {
       town => $address,
       postcode => $row->{post_code},
       country => $row->{country_code},
-      external_reference => [ { external_reference => $self->external_result, external_id => $row->{supplier_id} } ],
+      external_reference => [ {
+        external_reference => $self->external_result,
+        external_id => $row->{supplier_id},
+      } ],
     }
   });
 }

lib/Pear/LocalLoop/Import/LCCCsv/Transactions.pm

@@ -3,6 +3,8 @@ use Moo;
 use DateTime;
 use DateTime::Format::Strptime;
 
+use Geo::UK::Postcode::Regex;
+
 extends qw/Pear::LocalLoop::Import::LCCCsv/;
 
 has target_entity_id => (
@@ -33,10 +35,10 @@ has '+csv_required_columns' => (
 sub import_csv {
   my ($self) = @_;
 
-  my $rows = $self->csv_data;
+  $self->check_headers;
   my $lcc_org = $self->target_entity;
 
-  foreach my $row (@{$rows}) {
+  while ( my $row = $self->get_csv_line ) {
     $self->_row_to_result($row, $lcc_org);
   }
 }
@@ -51,7 +53,34 @@ sub _row_to_result {
   }, { join => 'external_reference' });
 
   unless ($organisation) {
-    Pear::LocalLoop::Error->throw("Cannot find an organisation with supplier_id $supplier_id");
+    # Pear::LocalLoop::Error->throw("Cannot find an organisation with supplier_id $supplier_id");
+
+    return unless $row->{'Company Name (WHO)'};
+
+    my $town = $row->{post_town};
+
+    unless ($town) {
+      my $postcode_obj = Geo::UK::Postcode::Regex->parse( $row->{post_code} );
+      $town = Geo::UK::Postcode::Regex->outcode_to_posttowns($postcode_obj->{outcode});
+      $town = $town->[0];
+    }
+
+    return if $self->external_result->organisations->find({external_id => $row->{supplier_id}});
+
+    $organisation = $self->schema->resultset('Entity')->create({
+      type => 'organisation',
+      organisation => {
+        name => $row->{'Company Name (WHO)'},
+        street_name => $row->{"address line 1"},
+        town => $town,
+        postcode => $row->{post_code},
+        country => $row->{country_code},
+        external_reference => [ {
+          external_reference => $self->external_result,
+          external_id => $row->{supplier_id},
+        } ],
+      }
+    });
   }
 
   my $date_formatter = DateTime::Format::Strptime->new(
@@ -59,9 +88,9 @@ sub _row_to_result {
     time_zone => 'Europe/London'
   );
   
-    my $paid_date = ( $row->{paid_date} ?
+  my $paid_date = ( $row->{paid_date} ?
-      $date_formatter->parse_datetime($row->{paid_date}) :
+    $date_formatter->parse_datetime($row->{paid_date}) :
-      $date_formatter->parse_datetime($row->{invoice_date}) );
+    $date_formatter->parse_datetime($row->{invoice_date}) );
 
   my $gross_value = $row->{gross_amount};
   $gross_value =~ s/,//g;
@@ -71,19 +100,28 @@ sub _row_to_result {
   $net_value =~ s/,//g;
 
   # TODO negative values are sometimes present
-  $self->external_result->find_or_create_related('transactions', {
+  my $external_transaction = $self->external_result->update_or_create_related('transactions', { # This is a TransactionExternal result
     external_id => $row->{transaction_id},
-    transaction => {
+  });
-      seller        => $organisation->entity,
+
-      buyer         => $lcc_org,
+  my $transaction_result = $external_transaction->update_or_create_related( 'transaction', {
-      purchase_time => $paid_date,
+    seller        => $organisation->entity,
-      value         => $gross_value * 100000,
+    buyer         => $lcc_org,
-      meta          => {
+    purchase_time => $paid_date,
-        gross_value     => $gross_value * 100000,
+    value         => $gross_value * 100000,
-        sales_tax_value => $sales_tax_value * 100000,
+  });
-        net_value       => $net_value * 100000,
+
-      },
+  my $meta_result = $transaction_result->update_or_create_related('meta', {
-    }
+    gross_value     => $gross_value * 100000,
+    sales_tax_value => $sales_tax_value * 100000,
+    net_value       => $net_value * 100000,
+    ($row->{"local service"} ? (local_service => $row->{"local service"}) : ()),
+    ($row->{"regional service"} ? (regional_service => $row->{"regional service"}) : ()),
+    ($row->{"national service"} ? (national_service => $row->{"national service"}) : ()),
+    ($row->{"private household rebate"} ? (private_household_rebate => $row->{"private household rebate"}) : ()),
+    ($row->{"business tax and rebate"} ? (business_tax_and_rebate => $row->{"business tax and rebate"}) : ()),
+    ($row->{"stat loc gov"} ? (stat_loc_gov => $row->{"stat loc gov"}) : ()),
+    ($row->{"central loc gov"} ? (central_loc_gov => $row->{"central loc gov"}) : ()),
   });
 }
 

lib/Pear/LocalLoop/Import/Role/CSV.pm

@@ -67,6 +67,11 @@ has csv_data => (
   }
 );
 
+sub get_csv_line {
+  my $self = shift;
+  return $self->_text_csv->getline_hr( $self->_csv_filehandle );
+}
+
 sub check_headers {
   my $self = shift;
   my $req_headers = $self->csv_required_columns;

lib/Pear/LocalLoop/Plugin/Minion/Job/csv_postcode_import.pm

@@ -0,0 +1,15 @@
+package Pear::LocalLoop::Plugin::Minion::Job::csv_postcode_import;
+use Mojo::Base 'Pear::LocalLoop::Plugin::Minion::Job';
+
+use Pear::LocalLoop::Import::LCCCsv::Postcodes;
+
+sub run {
+  my ( $self, $filename ) = @_;
+
+  my $csv_import = Pear::LocalLoop::Import::LCCCsv::Postcodes->new(
+    csv_file => $filename,
+    schema => $self->app->schema
+  )->import_csv;
+}
+
+1;

lib/Pear/LocalLoop/Schema.pm

@@ -6,7 +6,7 @@ use warnings;
 
 use base 'DBIx::Class::Schema';
 
-our $VERSION = 28;
+our $VERSION = 30;
 
 __PACKAGE__->load_namespaces;
 

lib/Pear/LocalLoop/Schema/Result/EntityAssociation.pm

@@ -20,12 +20,12 @@ __PACKAGE__->add_columns(
   },
   "lis" => {
     data_type => 'boolean',
-    default => undef,
+    default_value => undef,
     is_nullable => 1,
   },
   "esta" => {
     data_type => 'boolean',
-    default => undef,
+    default_value => undef,
     is_nullable => 1,
   },
 );

lib/Pear/LocalLoop/Schema/Result/GbPostcode.pm

@@ -31,8 +31,19 @@ __PACKAGE__->add_columns(
     is_nullable => 1,
     default_value => undef,
   },
+  ward_id => {
+    data_type => 'integer',
+    is_nullable => 1,
+    default_value => undef,
+  },
 );
 
 __PACKAGE__->set_primary_key(qw/ outcode incode /);
 
+__PACKAGE__->belongs_to(
+  "ward",
+  "Pear::LocalLoop::Schema::Result::GbWard",
+  "ward_id",
+);
+
 1;

lib/Pear/LocalLoop/Schema/Result/GbWard.pm

@@ -0,0 +1,32 @@
+package Pear::LocalLoop::Schema::Result::GbWard;
+
+use strict;
+use warnings;
+
+use base 'DBIx::Class::Core';
+
+__PACKAGE__->table('gb_wards');
+
+__PACKAGE__->add_columns(
+  id => {
+    data_type => "integer",
+    is_auto_increment => 1,
+    is_nullable => 0,
+  },
+  ward => {
+    data_type => 'varchar',
+    size => 100,
+    is_nullable => 0,
+  },
+);
+
+__PACKAGE__->set_primary_key(qw/ id /);
+
+__PACKAGE__->has_many(
+  "postcodes",
+  "Pear::LocalLoop::Schema::Result::GbPostcode",
+  { "foreign.ward_id" => "self.id" },
+  { cascade_copy => 0, cascade_delete => 0 },
+);
+
+1;

lib/Pear/LocalLoop/Schema/Result/Organisation.pm

@@ -52,17 +52,17 @@ __PACKAGE__->add_columns(
   },
   pending         => {
     data_type   => 'boolean',
-    default     => \"false",
+    default_value => \"false",
     is_nullable => 0,
   },
   is_local        => {
     data_type   => 'boolean',
-    default     => undef,
+    default_value => undef,
     is_nullable => 1,
   },
   is_fair         => {
     data_type   => 'boolean',
-    default     => undef,
+    default_value => undef,
     is_nullable => 1,
   },
   submitted_by_id => {

lib/Pear/LocalLoop/Schema/Result/TransactionMeta.pm

@@ -33,6 +33,41 @@ __PACKAGE__->add_columns(
         size        => [ 100, 0 ],
         is_nullable => 0,
     },
+    "local_service" => {
+      data_type => 'boolean',
+      default_value => \"false",
+      is_nullable => 0,
+    },
+    "regional_service" => {
+      data_type => 'boolean',
+      default_value => \"false",
+      is_nullable => 0,
+    },
+    "national_service" => {
+      data_type   => 'boolean',
+      default_value => \"false",
+      is_nullable => 0,
+    },
+    "private_household_rebate" => {
+      data_type   => 'boolean',
+      default_value => \"false",
+      is_nullable => 0,
+    },
+    "business_tax_and_rebate" => {
+      data_type   => 'boolean',
+      default_value => \"false",
+      is_nullable => 0,
+    },
+    "stat_loc_gov" => {
+      data_type   => 'boolean',
+      default_value => \"false",
+      is_nullable => 0,
+    },
+    "central_loc_gov" => {
+      data_type   => 'boolean',
+      default_value => \"false",
+      is_nullable => 0,
+    },
 );
 
 __PACKAGE__->set_primary_key("id");

lib/Pear/LocalLoop/Schema/Result/ViewQuantisedTransactionPg.pm

@@ -22,4 +22,18 @@ SELECT "value",
   FROM "transactions"
 /);
 
+__PACKAGE__->belongs_to(
+  "buyer",
+  "Pear::LocalLoop::Schema::Result::Entity",
+  { id => "buyer_id" },
+  { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" },
+);
+
+__PACKAGE__->belongs_to(
+  "seller",
+  "Pear::LocalLoop::Schema::Result::Entity",
+  { id => "seller_id" },
+  { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" },
+);
+
 1;

lib/Pear/LocalLoop/Schema/Result/ViewQuantisedTransactionSQLite.pm

@@ -23,4 +23,18 @@ SELECT "value",
   FROM "transactions"
 /);
 
+__PACKAGE__->belongs_to(
+  "buyer",
+  "Pear::LocalLoop::Schema::Result::Entity",
+  { id => "buyer_id" },
+  { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" },
+);
+
+__PACKAGE__->belongs_to(
+  "seller",
+  "Pear::LocalLoop::Schema::Result::Entity",
+  { id => "seller_id" },
+  { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" },
+);
+
 1;

share/ddl/PostgreSQL/deploy/29/001-auto-__VERSION.sql

@@ -0,0 +1,18 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Tue Aug 27 17:44:14 2019
+-- 
+;
+--
+-- Table: dbix_class_deploymenthandler_versions
+--
+CREATE TABLE "dbix_class_deploymenthandler_versions" (
+  "id" serial NOT NULL,
+  "version" character varying(50) NOT NULL,
+  "ddl" text,
+  "upgrade_sql" text,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "dbix_class_deploymenthandler_versions_version" UNIQUE ("version")
+);
+
+;

share/ddl/PostgreSQL/deploy/29/001-auto.sql

@@ -0,0 +1,693 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Tue Aug 27 17:44:13 2019
+-- 
+;
+--
+-- Table: account_tokens
+--
+CREATE TABLE "account_tokens" (
+  "id" serial NOT NULL,
+  "name" text NOT NULL,
+  "used" integer DEFAULT 0 NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "account_tokens_name" UNIQUE ("name")
+);
+
+;
+--
+-- Table: category
+--
+CREATE TABLE "category" (
+  "id" serial NOT NULL,
+  "name" character varying(255) NOT NULL,
+  "line_icon" character varying(255),
+  PRIMARY KEY ("id"),
+  CONSTRAINT "category_name" UNIQUE ("name")
+);
+
+;
+--
+-- Table: entities
+--
+CREATE TABLE "entities" (
+  "id" serial NOT NULL,
+  "type" character varying(255) NOT NULL,
+  PRIMARY KEY ("id")
+);
+
+;
+--
+-- Table: external_references
+--
+CREATE TABLE "external_references" (
+  "id" serial NOT NULL,
+  "name" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "external_references_name" UNIQUE ("name")
+);
+
+;
+--
+-- Table: gb_wards
+--
+CREATE TABLE "gb_wards" (
+  "id" serial NOT NULL,
+  "ward" character varying(100) NOT NULL,
+  PRIMARY KEY ("id")
+);
+
+;
+--
+-- Table: global_medal_group
+--
+CREATE TABLE "global_medal_group" (
+  "id" serial NOT NULL,
+  "group_name" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "global_medal_group_group_name" UNIQUE ("group_name")
+);
+
+;
+--
+-- Table: import_sets
+--
+CREATE TABLE "import_sets" (
+  "id" serial NOT NULL,
+  "date" timestamp NOT NULL,
+  PRIMARY KEY ("id")
+);
+
+;
+--
+-- Table: leaderboards
+--
+CREATE TABLE "leaderboards" (
+  "id" serial NOT NULL,
+  "name" character varying(255) NOT NULL,
+  "type" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "leaderboards_type" UNIQUE ("type")
+);
+
+;
+--
+-- Table: org_medal_group
+--
+CREATE TABLE "org_medal_group" (
+  "id" serial NOT NULL,
+  "group_name" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "org_medal_group_group_name" UNIQUE ("group_name")
+);
+
+;
+--
+-- Table: organisation_social_types
+--
+CREATE TABLE "organisation_social_types" (
+  "id" serial NOT NULL,
+  "key" character varying(255) NOT NULL,
+  "name" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "organisation_social_types_key" UNIQUE ("key")
+);
+
+;
+--
+-- Table: organisation_types
+--
+CREATE TABLE "organisation_types" (
+  "id" serial NOT NULL,
+  "key" character varying(255) NOT NULL,
+  "name" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "organisation_types_key" UNIQUE ("key")
+);
+
+;
+--
+-- Table: customers
+--
+CREATE TABLE "customers" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "display_name" character varying(255) NOT NULL,
+  "full_name" character varying(255) NOT NULL,
+  "year_of_birth" integer NOT NULL,
+  "postcode" character varying(16) NOT NULL,
+  "latitude" numeric(5,2),
+  "longitude" numeric(5,2),
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "customers_idx_entity_id" on "customers" ("entity_id");
+
+;
+--
+-- Table: entity_association
+--
+CREATE TABLE "entity_association" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "lis" boolean,
+  "esta" boolean,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "entity_association_idx_entity_id" on "entity_association" ("entity_id");
+
+;
+--
+-- Table: gb_postcodes
+--
+CREATE TABLE "gb_postcodes" (
+  "outcode" character(4) NOT NULL,
+  "incode" character(3) DEFAULT '' NOT NULL,
+  "latitude" numeric(7,5),
+  "longitude" numeric(7,5),
+  "ward_id" integer,
+  PRIMARY KEY ("outcode", "incode")
+);
+CREATE INDEX "gb_postcodes_idx_ward_id" on "gb_postcodes" ("ward_id");
+
+;
+--
+-- Table: global_medals
+--
+CREATE TABLE "global_medals" (
+  "id" serial NOT NULL,
+  "group_id" integer NOT NULL,
+  "threshold" integer NOT NULL,
+  "points" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "global_medals_idx_group_id" on "global_medals" ("group_id");
+
+;
+--
+-- Table: leaderboard_sets
+--
+CREATE TABLE "leaderboard_sets" (
+  "id" serial NOT NULL,
+  "leaderboard_id" integer NOT NULL,
+  "date" timestamp NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "leaderboard_sets_idx_leaderboard_id" on "leaderboard_sets" ("leaderboard_id");
+
+;
+--
+-- Table: org_medals
+--
+CREATE TABLE "org_medals" (
+  "id" serial NOT NULL,
+  "group_id" integer NOT NULL,
+  "threshold" integer NOT NULL,
+  "points" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "org_medals_idx_group_id" on "org_medals" ("group_id");
+
+;
+--
+-- Table: transactions
+--
+CREATE TABLE "transactions" (
+  "id" serial NOT NULL,
+  "buyer_id" integer NOT NULL,
+  "seller_id" integer NOT NULL,
+  "value" numeric(100,0) NOT NULL,
+  "proof_image" text,
+  "submitted_at" timestamp NOT NULL,
+  "purchase_time" timestamp NOT NULL,
+  "essential" boolean DEFAULT false NOT NULL,
+  "distance" numeric(15),
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "transactions_idx_buyer_id" on "transactions" ("buyer_id");
+CREATE INDEX "transactions_idx_seller_id" on "transactions" ("seller_id");
+
+;
+--
+-- Table: users
+--
+CREATE TABLE "users" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "email" text NOT NULL,
+  "join_date" timestamp NOT NULL,
+  "password" character varying(100) NOT NULL,
+  "is_admin" boolean DEFAULT false NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "users_email" UNIQUE ("email")
+);
+CREATE INDEX "users_idx_entity_id" on "users" ("entity_id");
+
+;
+--
+-- Table: feedback
+--
+CREATE TABLE "feedback" (
+  "id" serial NOT NULL,
+  "user_id" integer NOT NULL,
+  "submitted_at" timestamp NOT NULL,
+  "feedbacktext" text NOT NULL,
+  "app_name" character varying(255) NOT NULL,
+  "package_name" character varying(255) NOT NULL,
+  "version_code" character varying(255) NOT NULL,
+  "version_number" character varying(255) NOT NULL,
+  "actioned" boolean DEFAULT false NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "feedback_idx_user_id" on "feedback" ("user_id");
+
+;
+--
+-- Table: global_user_medal_progress
+--
+CREATE TABLE "global_user_medal_progress" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "group_id" integer NOT NULL,
+  "total" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "global_user_medal_progress_idx_entity_id" on "global_user_medal_progress" ("entity_id");
+CREATE INDEX "global_user_medal_progress_idx_group_id" on "global_user_medal_progress" ("group_id");
+
+;
+--
+-- Table: global_user_medals
+--
+CREATE TABLE "global_user_medals" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "group_id" integer NOT NULL,
+  "points" integer NOT NULL,
+  "awarded_at" timestamp NOT NULL,
+  "threshold" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "global_user_medals_idx_entity_id" on "global_user_medals" ("entity_id");
+CREATE INDEX "global_user_medals_idx_group_id" on "global_user_medals" ("group_id");
+
+;
+--
+-- Table: import_lookups
+--
+CREATE TABLE "import_lookups" (
+  "id" serial NOT NULL,
+  "set_id" integer NOT NULL,
+  "name" character varying(255) NOT NULL,
+  "entity_id" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "import_lookups_idx_entity_id" on "import_lookups" ("entity_id");
+CREATE INDEX "import_lookups_idx_set_id" on "import_lookups" ("set_id");
+
+;
+--
+-- Table: org_user_medal_progress
+--
+CREATE TABLE "org_user_medal_progress" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "group_id" integer NOT NULL,
+  "total" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "org_user_medal_progress_idx_entity_id" on "org_user_medal_progress" ("entity_id");
+CREATE INDEX "org_user_medal_progress_idx_group_id" on "org_user_medal_progress" ("group_id");
+
+;
+--
+-- Table: org_user_medals
+--
+CREATE TABLE "org_user_medals" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "group_id" integer NOT NULL,
+  "points" integer NOT NULL,
+  "awarded_at" timestamp NOT NULL,
+  "threshold" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "org_user_medals_idx_entity_id" on "org_user_medals" ("entity_id");
+CREATE INDEX "org_user_medals_idx_group_id" on "org_user_medals" ("group_id");
+
+;
+--
+-- Table: session_tokens
+--
+CREATE TABLE "session_tokens" (
+  "id" serial NOT NULL,
+  "token" character varying(255) NOT NULL,
+  "user_id" integer NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "session_tokens_token" UNIQUE ("token")
+);
+CREATE INDEX "session_tokens_idx_user_id" on "session_tokens" ("user_id");
+
+;
+--
+-- Table: transaction_recurring
+--
+CREATE TABLE "transaction_recurring" (
+  "id" serial NOT NULL,
+  "buyer_id" integer NOT NULL,
+  "seller_id" integer NOT NULL,
+  "value" numeric(100,0) NOT NULL,
+  "start_time" timestamp NOT NULL,
+  "last_updated" timestamp,
+  "essential" boolean DEFAULT false NOT NULL,
+  "distance" numeric(15),
+  "category_id" integer,
+  "recurring_period" character varying(255) NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "transaction_recurring_idx_buyer_id" on "transaction_recurring" ("buyer_id");
+CREATE INDEX "transaction_recurring_idx_category_id" on "transaction_recurring" ("category_id");
+CREATE INDEX "transaction_recurring_idx_seller_id" on "transaction_recurring" ("seller_id");
+
+;
+--
+-- Table: transactions_meta
+--
+CREATE TABLE "transactions_meta" (
+  "id" serial NOT NULL,
+  "transaction_id" integer NOT NULL,
+  "net_value" numeric(100,0) NOT NULL,
+  "sales_tax_value" numeric(100,0) NOT NULL,
+  "gross_value" numeric(100,0) NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "transactions_meta_idx_transaction_id" on "transactions_meta" ("transaction_id");
+
+;
+--
+-- Table: entities_postcodes
+--
+CREATE TABLE "entities_postcodes" (
+  "outcode" character(4) NOT NULL,
+  "incode" character(3) NOT NULL,
+  "entity_id" integer NOT NULL,
+  PRIMARY KEY ("outcode", "incode", "entity_id")
+);
+CREATE INDEX "entities_postcodes_idx_entity_id" on "entities_postcodes" ("entity_id");
+CREATE INDEX "entities_postcodes_idx_outcode_incode" on "entities_postcodes" ("outcode", "incode");
+
+;
+--
+-- Table: import_values
+--
+CREATE TABLE "import_values" (
+  "id" serial NOT NULL,
+  "set_id" integer NOT NULL,
+  "user_name" character varying(255) NOT NULL,
+  "purchase_date" timestamp NOT NULL,
+  "purchase_value" character varying(255) NOT NULL,
+  "org_name" character varying(255) NOT NULL,
+  "transaction_id" integer,
+  "ignore_value" boolean DEFAULT false NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "import_values_idx_set_id" on "import_values" ("set_id");
+CREATE INDEX "import_values_idx_transaction_id" on "import_values" ("transaction_id");
+
+;
+--
+-- Table: leaderboard_values
+--
+CREATE TABLE "leaderboard_values" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "set_id" integer NOT NULL,
+  "position" integer NOT NULL,
+  "value" numeric(100,0) NOT NULL,
+  "trend" integer DEFAULT 0 NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "leaderboard_values_entity_id_set_id" UNIQUE ("entity_id", "set_id")
+);
+CREATE INDEX "leaderboard_values_idx_entity_id" on "leaderboard_values" ("entity_id");
+CREATE INDEX "leaderboard_values_idx_set_id" on "leaderboard_values" ("set_id");
+
+;
+--
+-- Table: organisations
+--
+CREATE TABLE "organisations" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "name" character varying(255) NOT NULL,
+  "street_name" text,
+  "town" character varying(255) NOT NULL,
+  "postcode" character varying(16),
+  "country" character varying(255),
+  "sector" character varying(1),
+  "pending" boolean DEFAULT false NOT NULL,
+  "is_local" boolean,
+  "is_fair" boolean,
+  "submitted_by_id" integer,
+  "latitude" numeric(8,5),
+  "longitude" numeric(8,5),
+  "type_id" integer,
+  "social_type_id" integer,
+  "is_anchor" boolean DEFAULT FALSE NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "organisations_idx_entity_id" on "organisations" ("entity_id");
+CREATE INDEX "organisations_idx_type_id" on "organisations" ("type_id");
+CREATE INDEX "organisations_idx_social_type_id" on "organisations" ("social_type_id");
+
+;
+--
+-- Table: transaction_category
+--
+CREATE TABLE "transaction_category" (
+  "category_id" integer NOT NULL,
+  "transaction_id" integer NOT NULL,
+  CONSTRAINT "transaction_category_transaction_id" UNIQUE ("transaction_id")
+);
+CREATE INDEX "transaction_category_idx_category_id" on "transaction_category" ("category_id");
+CREATE INDEX "transaction_category_idx_transaction_id" on "transaction_category" ("transaction_id");
+
+;
+--
+-- Table: transactions_external
+--
+CREATE TABLE "transactions_external" (
+  "id" serial NOT NULL,
+  "transaction_id" integer NOT NULL,
+  "external_reference_id" integer NOT NULL,
+  "external_id" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "transactions_external_external_reference_id_external_id" UNIQUE ("external_reference_id", "external_id")
+);
+CREATE INDEX "transactions_external_idx_external_reference_id" on "transactions_external" ("external_reference_id");
+CREATE INDEX "transactions_external_idx_transaction_id" on "transactions_external" ("transaction_id");
+
+;
+--
+-- Table: organisation_payroll
+--
+CREATE TABLE "organisation_payroll" (
+  "id" serial NOT NULL,
+  "org_id" integer NOT NULL,
+  "submitted_at" timestamp NOT NULL,
+  "entry_period" timestamp NOT NULL,
+  "employee_amount" integer NOT NULL,
+  "local_employee_amount" integer NOT NULL,
+  "gross_payroll" numeric(100,0) NOT NULL,
+  "payroll_income_tax" numeric(100,0) NOT NULL,
+  "payroll_employee_ni" numeric(100,0) NOT NULL,
+  "payroll_employer_ni" numeric(100,0) NOT NULL,
+  "payroll_total_pension" numeric(100,0) NOT NULL,
+  "payroll_other_benefit" numeric(100,0) NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "organisation_payroll_idx_org_id" on "organisation_payroll" ("org_id");
+
+;
+--
+-- Table: organisations_external
+--
+CREATE TABLE "organisations_external" (
+  "id" serial NOT NULL,
+  "org_id" integer NOT NULL,
+  "external_reference_id" integer NOT NULL,
+  "external_id" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "organisations_external_external_reference_id_external_id" UNIQUE ("external_reference_id", "external_id")
+);
+CREATE INDEX "organisations_external_idx_external_reference_id" on "organisations_external" ("external_reference_id");
+CREATE INDEX "organisations_external_idx_org_id" on "organisations_external" ("org_id");
+
+;
+--
+-- Foreign Key Definitions
+--
+
+;
+ALTER TABLE "customers" ADD CONSTRAINT "customers_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "entity_association" ADD CONSTRAINT "entity_association_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "gb_postcodes" ADD CONSTRAINT "gb_postcodes_fk_ward_id" FOREIGN KEY ("ward_id")
+  REFERENCES "gb_wards" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "global_medals" ADD CONSTRAINT "global_medals_fk_group_id" FOREIGN KEY ("group_id")
+  REFERENCES "global_medal_group" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "leaderboard_sets" ADD CONSTRAINT "leaderboard_sets_fk_leaderboard_id" FOREIGN KEY ("leaderboard_id")
+  REFERENCES "leaderboards" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "org_medals" ADD CONSTRAINT "org_medals_fk_group_id" FOREIGN KEY ("group_id")
+  REFERENCES "org_medal_group" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "transactions" ADD CONSTRAINT "transactions_fk_buyer_id" FOREIGN KEY ("buyer_id")
+  REFERENCES "entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "transactions" ADD CONSTRAINT "transactions_fk_seller_id" FOREIGN KEY ("seller_id")
+  REFERENCES "entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "users" ADD CONSTRAINT "users_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "feedback" ADD CONSTRAINT "feedback_fk_user_id" FOREIGN KEY ("user_id")
+  REFERENCES "users" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "global_user_medal_progress" ADD CONSTRAINT "global_user_medal_progress_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "global_user_medal_progress" ADD CONSTRAINT "global_user_medal_progress_fk_group_id" FOREIGN KEY ("group_id")
+  REFERENCES "global_medal_group" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "global_user_medals" ADD CONSTRAINT "global_user_medals_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "global_user_medals" ADD CONSTRAINT "global_user_medals_fk_group_id" FOREIGN KEY ("group_id")
+  REFERENCES "global_medal_group" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "import_lookups" ADD CONSTRAINT "import_lookups_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION DEFERRABLE;
+
+;
+ALTER TABLE "import_lookups" ADD CONSTRAINT "import_lookups_fk_set_id" FOREIGN KEY ("set_id")
+  REFERENCES "import_sets" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "org_user_medal_progress" ADD CONSTRAINT "org_user_medal_progress_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "org_user_medal_progress" ADD CONSTRAINT "org_user_medal_progress_fk_group_id" FOREIGN KEY ("group_id")
+  REFERENCES "org_medal_group" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "org_user_medals" ADD CONSTRAINT "org_user_medals_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "org_user_medals" ADD CONSTRAINT "org_user_medals_fk_group_id" FOREIGN KEY ("group_id")
+  REFERENCES "org_medal_group" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "session_tokens" ADD CONSTRAINT "session_tokens_fk_user_id" FOREIGN KEY ("user_id")
+  REFERENCES "users" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "transaction_recurring" ADD CONSTRAINT "transaction_recurring_fk_buyer_id" FOREIGN KEY ("buyer_id")
+  REFERENCES "entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "transaction_recurring" ADD CONSTRAINT "transaction_recurring_fk_category_id" FOREIGN KEY ("category_id")
+  REFERENCES "category" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "transaction_recurring" ADD CONSTRAINT "transaction_recurring_fk_seller_id" FOREIGN KEY ("seller_id")
+  REFERENCES "entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "transactions_meta" ADD CONSTRAINT "transactions_meta_fk_transaction_id" FOREIGN KEY ("transaction_id")
+  REFERENCES "transactions" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "entities_postcodes" ADD CONSTRAINT "entities_postcodes_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "entities_postcodes" ADD CONSTRAINT "entities_postcodes_fk_outcode_incode" FOREIGN KEY ("outcode", "incode")
+  REFERENCES "gb_postcodes" ("outcode", "incode") DEFERRABLE;
+
+;
+ALTER TABLE "import_values" ADD CONSTRAINT "import_values_fk_set_id" FOREIGN KEY ("set_id")
+  REFERENCES "import_sets" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "import_values" ADD CONSTRAINT "import_values_fk_transaction_id" FOREIGN KEY ("transaction_id")
+  REFERENCES "transactions" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION DEFERRABLE;
+
+;
+ALTER TABLE "leaderboard_values" ADD CONSTRAINT "leaderboard_values_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "leaderboard_values" ADD CONSTRAINT "leaderboard_values_fk_set_id" FOREIGN KEY ("set_id")
+  REFERENCES "leaderboard_sets" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "organisations" ADD CONSTRAINT "organisations_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "organisations" ADD CONSTRAINT "organisations_fk_type_id" FOREIGN KEY ("type_id")
+  REFERENCES "organisation_types" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "organisations" ADD CONSTRAINT "organisations_fk_social_type_id" FOREIGN KEY ("social_type_id")
+  REFERENCES "organisation_social_types" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "transaction_category" ADD CONSTRAINT "transaction_category_fk_category_id" FOREIGN KEY ("category_id")
+  REFERENCES "category" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "transaction_category" ADD CONSTRAINT "transaction_category_fk_transaction_id" FOREIGN KEY ("transaction_id")
+  REFERENCES "transactions" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "transactions_external" ADD CONSTRAINT "transactions_external_fk_external_reference_id" FOREIGN KEY ("external_reference_id")
+  REFERENCES "external_references" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "transactions_external" ADD CONSTRAINT "transactions_external_fk_transaction_id" FOREIGN KEY ("transaction_id")
+  REFERENCES "transactions" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "organisation_payroll" ADD CONSTRAINT "organisation_payroll_fk_org_id" FOREIGN KEY ("org_id")
+  REFERENCES "organisations" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "organisations_external" ADD CONSTRAINT "organisations_external_fk_external_reference_id" FOREIGN KEY ("external_reference_id")
+  REFERENCES "external_references" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "organisations_external" ADD CONSTRAINT "organisations_external_fk_org_id" FOREIGN KEY ("org_id")
+  REFERENCES "organisations" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
+
+;

share/ddl/PostgreSQL/deploy/30/001-auto-__VERSION.sql

@@ -0,0 +1,18 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Mon Sep  2 13:06:08 2019
+-- 
+;
+--
+-- Table: dbix_class_deploymenthandler_versions
+--
+CREATE TABLE "dbix_class_deploymenthandler_versions" (
+  "id" serial NOT NULL,
+  "version" character varying(50) NOT NULL,
+  "ddl" text,
+  "upgrade_sql" text,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "dbix_class_deploymenthandler_versions_version" UNIQUE ("version")
+);
+
+;

share/ddl/PostgreSQL/deploy/30/001-auto.sql

@@ -0,0 +1,700 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Mon Sep  2 13:06:08 2019
+-- 
+;
+--
+-- Table: account_tokens
+--
+CREATE TABLE "account_tokens" (
+  "id" serial NOT NULL,
+  "name" text NOT NULL,
+  "used" integer DEFAULT 0 NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "account_tokens_name" UNIQUE ("name")
+);
+
+;
+--
+-- Table: category
+--
+CREATE TABLE "category" (
+  "id" serial NOT NULL,
+  "name" character varying(255) NOT NULL,
+  "line_icon" character varying(255),
+  PRIMARY KEY ("id"),
+  CONSTRAINT "category_name" UNIQUE ("name")
+);
+
+;
+--
+-- Table: entities
+--
+CREATE TABLE "entities" (
+  "id" serial NOT NULL,
+  "type" character varying(255) NOT NULL,
+  PRIMARY KEY ("id")
+);
+
+;
+--
+-- Table: external_references
+--
+CREATE TABLE "external_references" (
+  "id" serial NOT NULL,
+  "name" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "external_references_name" UNIQUE ("name")
+);
+
+;
+--
+-- Table: gb_wards
+--
+CREATE TABLE "gb_wards" (
+  "id" serial NOT NULL,
+  "ward" character varying(100) NOT NULL,
+  PRIMARY KEY ("id")
+);
+
+;
+--
+-- Table: global_medal_group
+--
+CREATE TABLE "global_medal_group" (
+  "id" serial NOT NULL,
+  "group_name" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "global_medal_group_group_name" UNIQUE ("group_name")
+);
+
+;
+--
+-- Table: import_sets
+--
+CREATE TABLE "import_sets" (
+  "id" serial NOT NULL,
+  "date" timestamp NOT NULL,
+  PRIMARY KEY ("id")
+);
+
+;
+--
+-- Table: leaderboards
+--
+CREATE TABLE "leaderboards" (
+  "id" serial NOT NULL,
+  "name" character varying(255) NOT NULL,
+  "type" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "leaderboards_type" UNIQUE ("type")
+);
+
+;
+--
+-- Table: org_medal_group
+--
+CREATE TABLE "org_medal_group" (
+  "id" serial NOT NULL,
+  "group_name" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "org_medal_group_group_name" UNIQUE ("group_name")
+);
+
+;
+--
+-- Table: organisation_social_types
+--
+CREATE TABLE "organisation_social_types" (
+  "id" serial NOT NULL,
+  "key" character varying(255) NOT NULL,
+  "name" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "organisation_social_types_key" UNIQUE ("key")
+);
+
+;
+--
+-- Table: organisation_types
+--
+CREATE TABLE "organisation_types" (
+  "id" serial NOT NULL,
+  "key" character varying(255) NOT NULL,
+  "name" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "organisation_types_key" UNIQUE ("key")
+);
+
+;
+--
+-- Table: customers
+--
+CREATE TABLE "customers" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "display_name" character varying(255) NOT NULL,
+  "full_name" character varying(255) NOT NULL,
+  "year_of_birth" integer NOT NULL,
+  "postcode" character varying(16) NOT NULL,
+  "latitude" numeric(5,2),
+  "longitude" numeric(5,2),
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "customers_idx_entity_id" on "customers" ("entity_id");
+
+;
+--
+-- Table: entity_association
+--
+CREATE TABLE "entity_association" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "lis" boolean,
+  "esta" boolean,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "entity_association_idx_entity_id" on "entity_association" ("entity_id");
+
+;
+--
+-- Table: gb_postcodes
+--
+CREATE TABLE "gb_postcodes" (
+  "outcode" character(4) NOT NULL,
+  "incode" character(3) DEFAULT '' NOT NULL,
+  "latitude" numeric(7,5),
+  "longitude" numeric(7,5),
+  "ward_id" integer,
+  PRIMARY KEY ("outcode", "incode")
+);
+CREATE INDEX "gb_postcodes_idx_ward_id" on "gb_postcodes" ("ward_id");
+
+;
+--
+-- Table: global_medals
+--
+CREATE TABLE "global_medals" (
+  "id" serial NOT NULL,
+  "group_id" integer NOT NULL,
+  "threshold" integer NOT NULL,
+  "points" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "global_medals_idx_group_id" on "global_medals" ("group_id");
+
+;
+--
+-- Table: leaderboard_sets
+--
+CREATE TABLE "leaderboard_sets" (
+  "id" serial NOT NULL,
+  "leaderboard_id" integer NOT NULL,
+  "date" timestamp NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "leaderboard_sets_idx_leaderboard_id" on "leaderboard_sets" ("leaderboard_id");
+
+;
+--
+-- Table: org_medals
+--
+CREATE TABLE "org_medals" (
+  "id" serial NOT NULL,
+  "group_id" integer NOT NULL,
+  "threshold" integer NOT NULL,
+  "points" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "org_medals_idx_group_id" on "org_medals" ("group_id");
+
+;
+--
+-- Table: transactions
+--
+CREATE TABLE "transactions" (
+  "id" serial NOT NULL,
+  "buyer_id" integer NOT NULL,
+  "seller_id" integer NOT NULL,
+  "value" numeric(100,0) NOT NULL,
+  "proof_image" text,
+  "submitted_at" timestamp NOT NULL,
+  "purchase_time" timestamp NOT NULL,
+  "essential" boolean DEFAULT false NOT NULL,
+  "distance" numeric(15),
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "transactions_idx_buyer_id" on "transactions" ("buyer_id");
+CREATE INDEX "transactions_idx_seller_id" on "transactions" ("seller_id");
+
+;
+--
+-- Table: users
+--
+CREATE TABLE "users" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "email" text NOT NULL,
+  "join_date" timestamp NOT NULL,
+  "password" character varying(100) NOT NULL,
+  "is_admin" boolean DEFAULT false NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "users_email" UNIQUE ("email")
+);
+CREATE INDEX "users_idx_entity_id" on "users" ("entity_id");
+
+;
+--
+-- Table: feedback
+--
+CREATE TABLE "feedback" (
+  "id" serial NOT NULL,
+  "user_id" integer NOT NULL,
+  "submitted_at" timestamp NOT NULL,
+  "feedbacktext" text NOT NULL,
+  "app_name" character varying(255) NOT NULL,
+  "package_name" character varying(255) NOT NULL,
+  "version_code" character varying(255) NOT NULL,
+  "version_number" character varying(255) NOT NULL,
+  "actioned" boolean DEFAULT false NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "feedback_idx_user_id" on "feedback" ("user_id");
+
+;
+--
+-- Table: global_user_medal_progress
+--
+CREATE TABLE "global_user_medal_progress" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "group_id" integer NOT NULL,
+  "total" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "global_user_medal_progress_idx_entity_id" on "global_user_medal_progress" ("entity_id");
+CREATE INDEX "global_user_medal_progress_idx_group_id" on "global_user_medal_progress" ("group_id");
+
+;
+--
+-- Table: global_user_medals
+--
+CREATE TABLE "global_user_medals" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "group_id" integer NOT NULL,
+  "points" integer NOT NULL,
+  "awarded_at" timestamp NOT NULL,
+  "threshold" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "global_user_medals_idx_entity_id" on "global_user_medals" ("entity_id");
+CREATE INDEX "global_user_medals_idx_group_id" on "global_user_medals" ("group_id");
+
+;
+--
+-- Table: import_lookups
+--
+CREATE TABLE "import_lookups" (
+  "id" serial NOT NULL,
+  "set_id" integer NOT NULL,
+  "name" character varying(255) NOT NULL,
+  "entity_id" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "import_lookups_idx_entity_id" on "import_lookups" ("entity_id");
+CREATE INDEX "import_lookups_idx_set_id" on "import_lookups" ("set_id");
+
+;
+--
+-- Table: org_user_medal_progress
+--
+CREATE TABLE "org_user_medal_progress" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "group_id" integer NOT NULL,
+  "total" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "org_user_medal_progress_idx_entity_id" on "org_user_medal_progress" ("entity_id");
+CREATE INDEX "org_user_medal_progress_idx_group_id" on "org_user_medal_progress" ("group_id");
+
+;
+--
+-- Table: org_user_medals
+--
+CREATE TABLE "org_user_medals" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "group_id" integer NOT NULL,
+  "points" integer NOT NULL,
+  "awarded_at" timestamp NOT NULL,
+  "threshold" integer NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "org_user_medals_idx_entity_id" on "org_user_medals" ("entity_id");
+CREATE INDEX "org_user_medals_idx_group_id" on "org_user_medals" ("group_id");
+
+;
+--
+-- Table: session_tokens
+--
+CREATE TABLE "session_tokens" (
+  "id" serial NOT NULL,
+  "token" character varying(255) NOT NULL,
+  "user_id" integer NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "session_tokens_token" UNIQUE ("token")
+);
+CREATE INDEX "session_tokens_idx_user_id" on "session_tokens" ("user_id");
+
+;
+--
+-- Table: transaction_recurring
+--
+CREATE TABLE "transaction_recurring" (
+  "id" serial NOT NULL,
+  "buyer_id" integer NOT NULL,
+  "seller_id" integer NOT NULL,
+  "value" numeric(100,0) NOT NULL,
+  "start_time" timestamp NOT NULL,
+  "last_updated" timestamp,
+  "essential" boolean DEFAULT false NOT NULL,
+  "distance" numeric(15),
+  "category_id" integer,
+  "recurring_period" character varying(255) NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "transaction_recurring_idx_buyer_id" on "transaction_recurring" ("buyer_id");
+CREATE INDEX "transaction_recurring_idx_category_id" on "transaction_recurring" ("category_id");
+CREATE INDEX "transaction_recurring_idx_seller_id" on "transaction_recurring" ("seller_id");
+
+;
+--
+-- Table: transactions_meta
+--
+CREATE TABLE "transactions_meta" (
+  "id" serial NOT NULL,
+  "transaction_id" integer NOT NULL,
+  "net_value" numeric(100,0) NOT NULL,
+  "sales_tax_value" numeric(100,0) NOT NULL,
+  "gross_value" numeric(100,0) NOT NULL,
+  "local_service" boolean DEFAULT false NOT NULL,
+  "regional_service" boolean DEFAULT false NOT NULL,
+  "national_service" boolean DEFAULT false NOT NULL,
+  "private_household_rebate" boolean DEFAULT false NOT NULL,
+  "business_tax_and_rebate" boolean DEFAULT false NOT NULL,
+  "stat_loc_gov" boolean DEFAULT false NOT NULL,
+  "central_loc_gov" boolean DEFAULT false NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "transactions_meta_idx_transaction_id" on "transactions_meta" ("transaction_id");
+
+;
+--
+-- Table: entities_postcodes
+--
+CREATE TABLE "entities_postcodes" (
+  "outcode" character(4) NOT NULL,
+  "incode" character(3) NOT NULL,
+  "entity_id" integer NOT NULL,
+  PRIMARY KEY ("outcode", "incode", "entity_id")
+);
+CREATE INDEX "entities_postcodes_idx_entity_id" on "entities_postcodes" ("entity_id");
+CREATE INDEX "entities_postcodes_idx_outcode_incode" on "entities_postcodes" ("outcode", "incode");
+
+;
+--
+-- Table: import_values
+--
+CREATE TABLE "import_values" (
+  "id" serial NOT NULL,
+  "set_id" integer NOT NULL,
+  "user_name" character varying(255) NOT NULL,
+  "purchase_date" timestamp NOT NULL,
+  "purchase_value" character varying(255) NOT NULL,
+  "org_name" character varying(255) NOT NULL,
+  "transaction_id" integer,
+  "ignore_value" boolean DEFAULT false NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "import_values_idx_set_id" on "import_values" ("set_id");
+CREATE INDEX "import_values_idx_transaction_id" on "import_values" ("transaction_id");
+
+;
+--
+-- Table: leaderboard_values
+--
+CREATE TABLE "leaderboard_values" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "set_id" integer NOT NULL,
+  "position" integer NOT NULL,
+  "value" numeric(100,0) NOT NULL,
+  "trend" integer DEFAULT 0 NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "leaderboard_values_entity_id_set_id" UNIQUE ("entity_id", "set_id")
+);
+CREATE INDEX "leaderboard_values_idx_entity_id" on "leaderboard_values" ("entity_id");
+CREATE INDEX "leaderboard_values_idx_set_id" on "leaderboard_values" ("set_id");
+
+;
+--
+-- Table: organisations
+--
+CREATE TABLE "organisations" (
+  "id" serial NOT NULL,
+  "entity_id" integer NOT NULL,
+  "name" character varying(255) NOT NULL,
+  "street_name" text,
+  "town" character varying(255) NOT NULL,
+  "postcode" character varying(16),
+  "country" character varying(255),
+  "sector" character varying(1),
+  "pending" boolean DEFAULT false NOT NULL,
+  "is_local" boolean,
+  "is_fair" boolean,
+  "submitted_by_id" integer,
+  "latitude" numeric(8,5),
+  "longitude" numeric(8,5),
+  "type_id" integer,
+  "social_type_id" integer,
+  "is_anchor" boolean DEFAULT FALSE NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "organisations_idx_entity_id" on "organisations" ("entity_id");
+CREATE INDEX "organisations_idx_type_id" on "organisations" ("type_id");
+CREATE INDEX "organisations_idx_social_type_id" on "organisations" ("social_type_id");
+
+;
+--
+-- Table: transaction_category
+--
+CREATE TABLE "transaction_category" (
+  "category_id" integer NOT NULL,
+  "transaction_id" integer NOT NULL,
+  CONSTRAINT "transaction_category_transaction_id" UNIQUE ("transaction_id")
+);
+CREATE INDEX "transaction_category_idx_category_id" on "transaction_category" ("category_id");
+CREATE INDEX "transaction_category_idx_transaction_id" on "transaction_category" ("transaction_id");
+
+;
+--
+-- Table: transactions_external
+--
+CREATE TABLE "transactions_external" (
+  "id" serial NOT NULL,
+  "transaction_id" integer NOT NULL,
+  "external_reference_id" integer NOT NULL,
+  "external_id" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "transactions_external_external_reference_id_external_id" UNIQUE ("external_reference_id", "external_id")
+);
+CREATE INDEX "transactions_external_idx_external_reference_id" on "transactions_external" ("external_reference_id");
+CREATE INDEX "transactions_external_idx_transaction_id" on "transactions_external" ("transaction_id");
+
+;
+--
+-- Table: organisation_payroll
+--
+CREATE TABLE "organisation_payroll" (
+  "id" serial NOT NULL,
+  "org_id" integer NOT NULL,
+  "submitted_at" timestamp NOT NULL,
+  "entry_period" timestamp NOT NULL,
+  "employee_amount" integer NOT NULL,
+  "local_employee_amount" integer NOT NULL,
+  "gross_payroll" numeric(100,0) NOT NULL,
+  "payroll_income_tax" numeric(100,0) NOT NULL,
+  "payroll_employee_ni" numeric(100,0) NOT NULL,
+  "payroll_employer_ni" numeric(100,0) NOT NULL,
+  "payroll_total_pension" numeric(100,0) NOT NULL,
+  "payroll_other_benefit" numeric(100,0) NOT NULL,
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "organisation_payroll_idx_org_id" on "organisation_payroll" ("org_id");
+
+;
+--
+-- Table: organisations_external
+--
+CREATE TABLE "organisations_external" (
+  "id" serial NOT NULL,
+  "org_id" integer NOT NULL,
+  "external_reference_id" integer NOT NULL,
+  "external_id" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "organisations_external_external_reference_id_external_id" UNIQUE ("external_reference_id", "external_id")
+);
+CREATE INDEX "organisations_external_idx_external_reference_id" on "organisations_external" ("external_reference_id");
+CREATE INDEX "organisations_external_idx_org_id" on "organisations_external" ("org_id");
+
+;
+--
+-- Foreign Key Definitions
+--
+
+;
+ALTER TABLE "customers" ADD CONSTRAINT "customers_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "entity_association" ADD CONSTRAINT "entity_association_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "gb_postcodes" ADD CONSTRAINT "gb_postcodes_fk_ward_id" FOREIGN KEY ("ward_id")
+  REFERENCES "gb_wards" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "global_medals" ADD CONSTRAINT "global_medals_fk_group_id" FOREIGN KEY ("group_id")
+  REFERENCES "global_medal_group" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "leaderboard_sets" ADD CONSTRAINT "leaderboard_sets_fk_leaderboard_id" FOREIGN KEY ("leaderboard_id")
+  REFERENCES "leaderboards" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "org_medals" ADD CONSTRAINT "org_medals_fk_group_id" FOREIGN KEY ("group_id")
+  REFERENCES "org_medal_group" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "transactions" ADD CONSTRAINT "transactions_fk_buyer_id" FOREIGN KEY ("buyer_id")
+  REFERENCES "entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "transactions" ADD CONSTRAINT "transactions_fk_seller_id" FOREIGN KEY ("seller_id")
+  REFERENCES "entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "users" ADD CONSTRAINT "users_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "feedback" ADD CONSTRAINT "feedback_fk_user_id" FOREIGN KEY ("user_id")
+  REFERENCES "users" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "global_user_medal_progress" ADD CONSTRAINT "global_user_medal_progress_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "global_user_medal_progress" ADD CONSTRAINT "global_user_medal_progress_fk_group_id" FOREIGN KEY ("group_id")
+  REFERENCES "global_medal_group" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "global_user_medals" ADD CONSTRAINT "global_user_medals_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "global_user_medals" ADD CONSTRAINT "global_user_medals_fk_group_id" FOREIGN KEY ("group_id")
+  REFERENCES "global_medal_group" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "import_lookups" ADD CONSTRAINT "import_lookups_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION DEFERRABLE;
+
+;
+ALTER TABLE "import_lookups" ADD CONSTRAINT "import_lookups_fk_set_id" FOREIGN KEY ("set_id")
+  REFERENCES "import_sets" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "org_user_medal_progress" ADD CONSTRAINT "org_user_medal_progress_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "org_user_medal_progress" ADD CONSTRAINT "org_user_medal_progress_fk_group_id" FOREIGN KEY ("group_id")
+  REFERENCES "org_medal_group" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "org_user_medals" ADD CONSTRAINT "org_user_medals_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "org_user_medals" ADD CONSTRAINT "org_user_medals_fk_group_id" FOREIGN KEY ("group_id")
+  REFERENCES "org_medal_group" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "session_tokens" ADD CONSTRAINT "session_tokens_fk_user_id" FOREIGN KEY ("user_id")
+  REFERENCES "users" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "transaction_recurring" ADD CONSTRAINT "transaction_recurring_fk_buyer_id" FOREIGN KEY ("buyer_id")
+  REFERENCES "entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "transaction_recurring" ADD CONSTRAINT "transaction_recurring_fk_category_id" FOREIGN KEY ("category_id")
+  REFERENCES "category" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "transaction_recurring" ADD CONSTRAINT "transaction_recurring_fk_seller_id" FOREIGN KEY ("seller_id")
+  REFERENCES "entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "transactions_meta" ADD CONSTRAINT "transactions_meta_fk_transaction_id" FOREIGN KEY ("transaction_id")
+  REFERENCES "transactions" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "entities_postcodes" ADD CONSTRAINT "entities_postcodes_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "entities_postcodes" ADD CONSTRAINT "entities_postcodes_fk_outcode_incode" FOREIGN KEY ("outcode", "incode")
+  REFERENCES "gb_postcodes" ("outcode", "incode") DEFERRABLE;
+
+;
+ALTER TABLE "import_values" ADD CONSTRAINT "import_values_fk_set_id" FOREIGN KEY ("set_id")
+  REFERENCES "import_sets" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "import_values" ADD CONSTRAINT "import_values_fk_transaction_id" FOREIGN KEY ("transaction_id")
+  REFERENCES "transactions" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION DEFERRABLE;
+
+;
+ALTER TABLE "leaderboard_values" ADD CONSTRAINT "leaderboard_values_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "leaderboard_values" ADD CONSTRAINT "leaderboard_values_fk_set_id" FOREIGN KEY ("set_id")
+  REFERENCES "leaderboard_sets" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+;
+ALTER TABLE "organisations" ADD CONSTRAINT "organisations_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "organisations" ADD CONSTRAINT "organisations_fk_type_id" FOREIGN KEY ("type_id")
+  REFERENCES "organisation_types" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "organisations" ADD CONSTRAINT "organisations_fk_social_type_id" FOREIGN KEY ("social_type_id")
+  REFERENCES "organisation_social_types" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "transaction_category" ADD CONSTRAINT "transaction_category_fk_category_id" FOREIGN KEY ("category_id")
+  REFERENCES "category" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "transaction_category" ADD CONSTRAINT "transaction_category_fk_transaction_id" FOREIGN KEY ("transaction_id")
+  REFERENCES "transactions" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "transactions_external" ADD CONSTRAINT "transactions_external_fk_external_reference_id" FOREIGN KEY ("external_reference_id")
+  REFERENCES "external_references" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "transactions_external" ADD CONSTRAINT "transactions_external_fk_transaction_id" FOREIGN KEY ("transaction_id")
+  REFERENCES "transactions" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "organisation_payroll" ADD CONSTRAINT "organisation_payroll_fk_org_id" FOREIGN KEY ("org_id")
+  REFERENCES "organisations" ("id") DEFERRABLE;
+
+;
+ALTER TABLE "organisations_external" ADD CONSTRAINT "organisations_external_fk_external_reference_id" FOREIGN KEY ("external_reference_id")
+  REFERENCES "external_references" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
+
+;
+ALTER TABLE "organisations_external" ADD CONSTRAINT "organisations_external_fk_org_id" FOREIGN KEY ("org_id")
+  REFERENCES "organisations" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
+
+;

share/ddl/PostgreSQL/upgrade/28-29/001-auto.sql

@@ -0,0 +1,26 @@
+-- Convert schema 'share/ddl/_source/deploy/28/001-auto.yml' to 'share/ddl/_source/deploy/29/001-auto.yml':;
+
+;
+BEGIN;
+
+;
+CREATE TABLE "gb_wards" (
+  "id" serial NOT NULL,
+  "ward" character varying(100) NOT NULL,
+  PRIMARY KEY ("id")
+);
+
+;
+ALTER TABLE gb_postcodes ADD COLUMN ward_id integer;
+
+;
+CREATE INDEX gb_postcodes_idx_ward_id on gb_postcodes (ward_id);
+
+;
+ALTER TABLE gb_postcodes ADD CONSTRAINT gb_postcodes_fk_ward_id FOREIGN KEY (ward_id)
+  REFERENCES gb_wards (id) DEFERRABLE;
+
+;
+
+COMMIT;
+

share/ddl/PostgreSQL/upgrade/29-30/001-auto.sql

@@ -0,0 +1,30 @@
+-- Convert schema 'share/ddl/_source/deploy/29/001-auto.yml' to 'share/ddl/_source/deploy/30/001-auto.yml':;
+
+;
+BEGIN;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN local_service boolean DEFAULT false NOT NULL;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN regional_service boolean DEFAULT false NOT NULL;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN national_service boolean DEFAULT false NOT NULL;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN private_household_rebate boolean DEFAULT false NOT NULL;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN business_tax_and_rebate boolean DEFAULT false NOT NULL;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN stat_loc_gov boolean DEFAULT false NOT NULL;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN central_loc_gov boolean DEFAULT false NOT NULL;
+
+;
+
+COMMIT;
+

share/ddl/SQLite/deploy/29/001-auto-__VERSION.sql

@@ -0,0 +1,18 @@
+-- 
+-- Created by SQL::Translator::Producer::SQLite
+-- Created on Tue Aug 27 17:44:14 2019
+-- 
+
+;
+BEGIN TRANSACTION;
+--
+-- Table: dbix_class_deploymenthandler_versions
+--
+CREATE TABLE dbix_class_deploymenthandler_versions (
+  id INTEGER PRIMARY KEY NOT NULL,
+  version varchar(50) NOT NULL,
+  ddl text,
+  upgrade_sql text
+);
+CREATE UNIQUE INDEX dbix_class_deploymenthandler_versions_version ON dbix_class_deploymenthandler_versions (version);
+COMMIT;

share/ddl/SQLite/deploy/29/001-auto.sql

@@ -0,0 +1,463 @@
+-- 
+-- Created by SQL::Translator::Producer::SQLite
+-- Created on Tue Aug 27 17:44:14 2019
+-- 
+
+;
+BEGIN TRANSACTION;
+--
+-- Table: account_tokens
+--
+CREATE TABLE account_tokens (
+  id INTEGER PRIMARY KEY NOT NULL,
+  name text NOT NULL,
+  used integer NOT NULL DEFAULT 0
+);
+CREATE UNIQUE INDEX account_tokens_name ON account_tokens (name);
+--
+-- Table: category
+--
+CREATE TABLE category (
+  id INTEGER PRIMARY KEY NOT NULL,
+  name varchar(255) NOT NULL,
+  line_icon varchar(255)
+);
+CREATE UNIQUE INDEX category_name ON category (name);
+--
+-- Table: entities
+--
+CREATE TABLE entities (
+  id INTEGER PRIMARY KEY NOT NULL,
+  type varchar(255) NOT NULL
+);
+--
+-- Table: external_references
+--
+CREATE TABLE external_references (
+  id INTEGER PRIMARY KEY NOT NULL,
+  name varchar(255) NOT NULL
+);
+CREATE UNIQUE INDEX external_references_name ON external_references (name);
+--
+-- Table: gb_wards
+--
+CREATE TABLE gb_wards (
+  id INTEGER PRIMARY KEY NOT NULL,
+  ward varchar(100) NOT NULL
+);
+--
+-- Table: global_medal_group
+--
+CREATE TABLE global_medal_group (
+  id INTEGER PRIMARY KEY NOT NULL,
+  group_name varchar(255) NOT NULL
+);
+CREATE UNIQUE INDEX global_medal_group_group_name ON global_medal_group (group_name);
+--
+-- Table: import_sets
+--
+CREATE TABLE import_sets (
+  id INTEGER PRIMARY KEY NOT NULL,
+  date datetime NOT NULL
+);
+--
+-- Table: leaderboards
+--
+CREATE TABLE leaderboards (
+  id INTEGER PRIMARY KEY NOT NULL,
+  name varchar(255) NOT NULL,
+  type varchar(255) NOT NULL
+);
+CREATE UNIQUE INDEX leaderboards_type ON leaderboards (type);
+--
+-- Table: org_medal_group
+--
+CREATE TABLE org_medal_group (
+  id INTEGER PRIMARY KEY NOT NULL,
+  group_name varchar(255) NOT NULL
+);
+CREATE UNIQUE INDEX org_medal_group_group_name ON org_medal_group (group_name);
+--
+-- Table: organisation_social_types
+--
+CREATE TABLE organisation_social_types (
+  id INTEGER PRIMARY KEY NOT NULL,
+  key varchar(255) NOT NULL,
+  name varchar(255) NOT NULL
+);
+CREATE UNIQUE INDEX organisation_social_types_key ON organisation_social_types (key);
+--
+-- Table: organisation_types
+--
+CREATE TABLE organisation_types (
+  id INTEGER PRIMARY KEY NOT NULL,
+  key varchar(255) NOT NULL,
+  name varchar(255) NOT NULL
+);
+CREATE UNIQUE INDEX organisation_types_key ON organisation_types (key);
+--
+-- Table: customers
+--
+CREATE TABLE customers (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  display_name varchar(255) NOT NULL,
+  full_name varchar(255) NOT NULL,
+  year_of_birth integer NOT NULL,
+  postcode varchar(16) NOT NULL,
+  latitude decimal(5,2),
+  longitude decimal(5,2),
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
+);
+CREATE INDEX customers_idx_entity_id ON customers (entity_id);
+--
+-- Table: entity_association
+--
+CREATE TABLE entity_association (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  lis boolean,
+  esta boolean,
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
+);
+CREATE INDEX entity_association_idx_entity_id ON entity_association (entity_id);
+--
+-- Table: gb_postcodes
+--
+CREATE TABLE gb_postcodes (
+  outcode char(4) NOT NULL,
+  incode char(3) NOT NULL DEFAULT '',
+  latitude decimal(7,5),
+  longitude decimal(7,5),
+  ward_id integer,
+  PRIMARY KEY (outcode, incode),
+  FOREIGN KEY (ward_id) REFERENCES gb_wards(id)
+);
+CREATE INDEX gb_postcodes_idx_ward_id ON gb_postcodes (ward_id);
+--
+-- Table: global_medals
+--
+CREATE TABLE global_medals (
+  id INTEGER PRIMARY KEY NOT NULL,
+  group_id integer NOT NULL,
+  threshold integer NOT NULL,
+  points integer NOT NULL,
+  FOREIGN KEY (group_id) REFERENCES global_medal_group(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX global_medals_idx_group_id ON global_medals (group_id);
+--
+-- Table: leaderboard_sets
+--
+CREATE TABLE leaderboard_sets (
+  id INTEGER PRIMARY KEY NOT NULL,
+  leaderboard_id integer NOT NULL,
+  date datetime NOT NULL,
+  FOREIGN KEY (leaderboard_id) REFERENCES leaderboards(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX leaderboard_sets_idx_leaderboard_id ON leaderboard_sets (leaderboard_id);
+--
+-- Table: org_medals
+--
+CREATE TABLE org_medals (
+  id INTEGER PRIMARY KEY NOT NULL,
+  group_id integer NOT NULL,
+  threshold integer NOT NULL,
+  points integer NOT NULL,
+  FOREIGN KEY (group_id) REFERENCES org_medal_group(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX org_medals_idx_group_id ON org_medals (group_id);
+--
+-- Table: transactions
+--
+CREATE TABLE transactions (
+  id INTEGER PRIMARY KEY NOT NULL,
+  buyer_id integer NOT NULL,
+  seller_id integer NOT NULL,
+  value numeric(100,0) NOT NULL,
+  proof_image text,
+  submitted_at datetime NOT NULL,
+  purchase_time datetime NOT NULL,
+  essential boolean NOT NULL DEFAULT false,
+  distance numeric(15),
+  FOREIGN KEY (buyer_id) REFERENCES entities(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  FOREIGN KEY (seller_id) REFERENCES entities(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX transactions_idx_buyer_id ON transactions (buyer_id);
+CREATE INDEX transactions_idx_seller_id ON transactions (seller_id);
+--
+-- Table: users
+--
+CREATE TABLE users (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  email text NOT NULL,
+  join_date datetime NOT NULL,
+  password varchar(100) NOT NULL,
+  is_admin boolean NOT NULL DEFAULT false,
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
+);
+CREATE INDEX users_idx_entity_id ON users (entity_id);
+CREATE UNIQUE INDEX users_email ON users (email);
+--
+-- Table: feedback
+--
+CREATE TABLE feedback (
+  id INTEGER PRIMARY KEY NOT NULL,
+  user_id integer NOT NULL,
+  submitted_at datetime NOT NULL,
+  feedbacktext text NOT NULL,
+  app_name varchar(255) NOT NULL,
+  package_name varchar(255) NOT NULL,
+  version_code varchar(255) NOT NULL,
+  version_number varchar(255) NOT NULL,
+  actioned boolean NOT NULL DEFAULT false,
+  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX feedback_idx_user_id ON feedback (user_id);
+--
+-- Table: global_user_medal_progress
+--
+CREATE TABLE global_user_medal_progress (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  group_id integer NOT NULL,
+  total integer NOT NULL,
+  FOREIGN KEY (entity_id) REFERENCES entities(id),
+  FOREIGN KEY (group_id) REFERENCES global_medal_group(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX global_user_medal_progress_idx_entity_id ON global_user_medal_progress (entity_id);
+CREATE INDEX global_user_medal_progress_idx_group_id ON global_user_medal_progress (group_id);
+--
+-- Table: global_user_medals
+--
+CREATE TABLE global_user_medals (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  group_id integer NOT NULL,
+  points integer NOT NULL,
+  awarded_at datetime NOT NULL,
+  threshold integer NOT NULL,
+  FOREIGN KEY (entity_id) REFERENCES entities(id),
+  FOREIGN KEY (group_id) REFERENCES global_medal_group(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX global_user_medals_idx_entity_id ON global_user_medals (entity_id);
+CREATE INDEX global_user_medals_idx_group_id ON global_user_medals (group_id);
+--
+-- Table: import_lookups
+--
+CREATE TABLE import_lookups (
+  id INTEGER PRIMARY KEY NOT NULL,
+  set_id integer NOT NULL,
+  name varchar(255) NOT NULL,
+  entity_id integer NOT NULL,
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  FOREIGN KEY (set_id) REFERENCES import_sets(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX import_lookups_idx_entity_id ON import_lookups (entity_id);
+CREATE INDEX import_lookups_idx_set_id ON import_lookups (set_id);
+--
+-- Table: org_user_medal_progress
+--
+CREATE TABLE org_user_medal_progress (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  group_id integer NOT NULL,
+  total integer NOT NULL,
+  FOREIGN KEY (entity_id) REFERENCES entities(id),
+  FOREIGN KEY (group_id) REFERENCES org_medal_group(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX org_user_medal_progress_idx_entity_id ON org_user_medal_progress (entity_id);
+CREATE INDEX org_user_medal_progress_idx_group_id ON org_user_medal_progress (group_id);
+--
+-- Table: org_user_medals
+--
+CREATE TABLE org_user_medals (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  group_id integer NOT NULL,
+  points integer NOT NULL,
+  awarded_at datetime NOT NULL,
+  threshold integer NOT NULL,
+  FOREIGN KEY (entity_id) REFERENCES entities(id),
+  FOREIGN KEY (group_id) REFERENCES org_medal_group(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX org_user_medals_idx_entity_id ON org_user_medals (entity_id);
+CREATE INDEX org_user_medals_idx_group_id ON org_user_medals (group_id);
+--
+-- Table: session_tokens
+--
+CREATE TABLE session_tokens (
+  id INTEGER PRIMARY KEY NOT NULL,
+  token varchar(255) NOT NULL,
+  user_id integer NOT NULL,
+  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX session_tokens_idx_user_id ON session_tokens (user_id);
+CREATE UNIQUE INDEX session_tokens_token ON session_tokens (token);
+--
+-- Table: transaction_recurring
+--
+CREATE TABLE transaction_recurring (
+  id INTEGER PRIMARY KEY NOT NULL,
+  buyer_id integer NOT NULL,
+  seller_id integer NOT NULL,
+  value numeric(100,0) NOT NULL,
+  start_time datetime NOT NULL,
+  last_updated datetime,
+  essential boolean NOT NULL DEFAULT false,
+  distance numeric(15),
+  category_id integer,
+  recurring_period varchar(255) NOT NULL,
+  FOREIGN KEY (buyer_id) REFERENCES entities(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  FOREIGN KEY (category_id) REFERENCES category(id),
+  FOREIGN KEY (seller_id) REFERENCES entities(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX transaction_recurring_idx_buyer_id ON transaction_recurring (buyer_id);
+CREATE INDEX transaction_recurring_idx_category_id ON transaction_recurring (category_id);
+CREATE INDEX transaction_recurring_idx_seller_id ON transaction_recurring (seller_id);
+--
+-- Table: transactions_meta
+--
+CREATE TABLE transactions_meta (
+  id INTEGER PRIMARY KEY NOT NULL,
+  transaction_id integer NOT NULL,
+  net_value numeric(100,0) NOT NULL,
+  sales_tax_value numeric(100,0) NOT NULL,
+  gross_value numeric(100,0) NOT NULL,
+  FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE
+);
+CREATE INDEX transactions_meta_idx_transaction_id ON transactions_meta (transaction_id);
+--
+-- Table: entities_postcodes
+--
+CREATE TABLE entities_postcodes (
+  outcode char(4) NOT NULL,
+  incode char(3) NOT NULL,
+  entity_id integer NOT NULL,
+  PRIMARY KEY (outcode, incode, entity_id),
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE,
+  FOREIGN KEY (outcode, incode) REFERENCES gb_postcodes(outcode, incode)
+);
+CREATE INDEX entities_postcodes_idx_entity_id ON entities_postcodes (entity_id);
+CREATE INDEX entities_postcodes_idx_outcode_incode ON entities_postcodes (outcode, incode);
+--
+-- Table: import_values
+--
+CREATE TABLE import_values (
+  id INTEGER PRIMARY KEY NOT NULL,
+  set_id integer NOT NULL,
+  user_name varchar(255) NOT NULL,
+  purchase_date datetime NOT NULL,
+  purchase_value varchar(255) NOT NULL,
+  org_name varchar(255) NOT NULL,
+  transaction_id integer,
+  ignore_value boolean NOT NULL DEFAULT false,
+  FOREIGN KEY (set_id) REFERENCES import_sets(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX import_values_idx_set_id ON import_values (set_id);
+CREATE INDEX import_values_idx_transaction_id ON import_values (transaction_id);
+--
+-- Table: leaderboard_values
+--
+CREATE TABLE leaderboard_values (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  set_id integer NOT NULL,
+  position integer NOT NULL,
+  value numeric(100,0) NOT NULL,
+  trend integer NOT NULL DEFAULT 0,
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  FOREIGN KEY (set_id) REFERENCES leaderboard_sets(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX leaderboard_values_idx_entity_id ON leaderboard_values (entity_id);
+CREATE INDEX leaderboard_values_idx_set_id ON leaderboard_values (set_id);
+CREATE UNIQUE INDEX leaderboard_values_entity_id_set_id ON leaderboard_values (entity_id, set_id);
+--
+-- Table: organisations
+--
+CREATE TABLE organisations (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  name varchar(255) NOT NULL,
+  street_name text,
+  town varchar(255) NOT NULL,
+  postcode varchar(16),
+  country varchar(255),
+  sector varchar(1),
+  pending boolean NOT NULL DEFAULT false,
+  is_local boolean,
+  is_fair boolean,
+  submitted_by_id integer,
+  latitude decimal(8,5),
+  longitude decimal(8,5),
+  type_id integer,
+  social_type_id integer,
+  is_anchor boolean NOT NULL DEFAULT FALSE,
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE,
+  FOREIGN KEY (type_id) REFERENCES organisation_types(id) ON DELETE CASCADE ON UPDATE CASCADE,
+  FOREIGN KEY (social_type_id) REFERENCES organisation_social_types(id) ON DELETE CASCADE ON UPDATE CASCADE
+);
+CREATE INDEX organisations_idx_entity_id ON organisations (entity_id);
+CREATE INDEX organisations_idx_type_id ON organisations (type_id);
+CREATE INDEX organisations_idx_social_type_id ON organisations (social_type_id);
+--
+-- Table: transaction_category
+--
+CREATE TABLE transaction_category (
+  category_id integer NOT NULL,
+  transaction_id integer NOT NULL,
+  FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE CASCADE,
+  FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE
+);
+CREATE INDEX transaction_category_idx_category_id ON transaction_category (category_id);
+CREATE INDEX transaction_category_idx_transaction_id ON transaction_category (transaction_id);
+CREATE UNIQUE INDEX transaction_category_transaction_id ON transaction_category (transaction_id);
+--
+-- Table: transactions_external
+--
+CREATE TABLE transactions_external (
+  id INTEGER PRIMARY KEY NOT NULL,
+  transaction_id integer NOT NULL,
+  external_reference_id integer NOT NULL,
+  external_id varchar(255) NOT NULL,
+  FOREIGN KEY (external_reference_id) REFERENCES external_references(id) ON DELETE CASCADE ON UPDATE CASCADE,
+  FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE ON UPDATE CASCADE
+);
+CREATE INDEX transactions_external_idx_external_reference_id ON transactions_external (external_reference_id);
+CREATE INDEX transactions_external_idx_transaction_id ON transactions_external (transaction_id);
+CREATE UNIQUE INDEX transactions_external_external_reference_id_external_id ON transactions_external (external_reference_id, external_id);
+--
+-- Table: organisation_payroll
+--
+CREATE TABLE organisation_payroll (
+  id INTEGER PRIMARY KEY NOT NULL,
+  org_id integer NOT NULL,
+  submitted_at datetime NOT NULL,
+  entry_period datetime NOT NULL,
+  employee_amount integer NOT NULL,
+  local_employee_amount integer NOT NULL,
+  gross_payroll numeric(100,0) NOT NULL,
+  payroll_income_tax numeric(100,0) NOT NULL,
+  payroll_employee_ni numeric(100,0) NOT NULL,
+  payroll_employer_ni numeric(100,0) NOT NULL,
+  payroll_total_pension numeric(100,0) NOT NULL,
+  payroll_other_benefit numeric(100,0) NOT NULL,
+  FOREIGN KEY (org_id) REFERENCES organisations(id)
+);
+CREATE INDEX organisation_payroll_idx_org_id ON organisation_payroll (org_id);
+--
+-- Table: organisations_external
+--
+CREATE TABLE organisations_external (
+  id INTEGER PRIMARY KEY NOT NULL,
+  org_id integer NOT NULL,
+  external_reference_id integer NOT NULL,
+  external_id varchar(255) NOT NULL,
+  FOREIGN KEY (external_reference_id) REFERENCES external_references(id) ON DELETE CASCADE ON UPDATE CASCADE,
+  FOREIGN KEY (org_id) REFERENCES organisations(id) ON DELETE CASCADE ON UPDATE CASCADE
+);
+CREATE INDEX organisations_external_idx_external_reference_id ON organisations_external (external_reference_id);
+CREATE INDEX organisations_external_idx_org_id ON organisations_external (org_id);
+CREATE UNIQUE INDEX organisations_external_external_reference_id_external_id ON organisations_external (external_reference_id, external_id);
+COMMIT;

share/ddl/SQLite/deploy/30/001-auto-__VERSION.sql

@@ -0,0 +1,18 @@
+-- 
+-- Created by SQL::Translator::Producer::SQLite
+-- Created on Mon Sep  2 13:06:08 2019
+-- 
+
+;
+BEGIN TRANSACTION;
+--
+-- Table: dbix_class_deploymenthandler_versions
+--
+CREATE TABLE dbix_class_deploymenthandler_versions (
+  id INTEGER PRIMARY KEY NOT NULL,
+  version varchar(50) NOT NULL,
+  ddl text,
+  upgrade_sql text
+);
+CREATE UNIQUE INDEX dbix_class_deploymenthandler_versions_version ON dbix_class_deploymenthandler_versions (version);
+COMMIT;

share/ddl/SQLite/deploy/30/001-auto.sql

@@ -0,0 +1,470 @@
+-- 
+-- Created by SQL::Translator::Producer::SQLite
+-- Created on Mon Sep  2 13:06:08 2019
+-- 
+
+;
+BEGIN TRANSACTION;
+--
+-- Table: account_tokens
+--
+CREATE TABLE account_tokens (
+  id INTEGER PRIMARY KEY NOT NULL,
+  name text NOT NULL,
+  used integer NOT NULL DEFAULT 0
+);
+CREATE UNIQUE INDEX account_tokens_name ON account_tokens (name);
+--
+-- Table: category
+--
+CREATE TABLE category (
+  id INTEGER PRIMARY KEY NOT NULL,
+  name varchar(255) NOT NULL,
+  line_icon varchar(255)
+);
+CREATE UNIQUE INDEX category_name ON category (name);
+--
+-- Table: entities
+--
+CREATE TABLE entities (
+  id INTEGER PRIMARY KEY NOT NULL,
+  type varchar(255) NOT NULL
+);
+--
+-- Table: external_references
+--
+CREATE TABLE external_references (
+  id INTEGER PRIMARY KEY NOT NULL,
+  name varchar(255) NOT NULL
+);
+CREATE UNIQUE INDEX external_references_name ON external_references (name);
+--
+-- Table: gb_wards
+--
+CREATE TABLE gb_wards (
+  id INTEGER PRIMARY KEY NOT NULL,
+  ward varchar(100) NOT NULL
+);
+--
+-- Table: global_medal_group
+--
+CREATE TABLE global_medal_group (
+  id INTEGER PRIMARY KEY NOT NULL,
+  group_name varchar(255) NOT NULL
+);
+CREATE UNIQUE INDEX global_medal_group_group_name ON global_medal_group (group_name);
+--
+-- Table: import_sets
+--
+CREATE TABLE import_sets (
+  id INTEGER PRIMARY KEY NOT NULL,
+  date datetime NOT NULL
+);
+--
+-- Table: leaderboards
+--
+CREATE TABLE leaderboards (
+  id INTEGER PRIMARY KEY NOT NULL,
+  name varchar(255) NOT NULL,
+  type varchar(255) NOT NULL
+);
+CREATE UNIQUE INDEX leaderboards_type ON leaderboards (type);
+--
+-- Table: org_medal_group
+--
+CREATE TABLE org_medal_group (
+  id INTEGER PRIMARY KEY NOT NULL,
+  group_name varchar(255) NOT NULL
+);
+CREATE UNIQUE INDEX org_medal_group_group_name ON org_medal_group (group_name);
+--
+-- Table: organisation_social_types
+--
+CREATE TABLE organisation_social_types (
+  id INTEGER PRIMARY KEY NOT NULL,
+  key varchar(255) NOT NULL,
+  name varchar(255) NOT NULL
+);
+CREATE UNIQUE INDEX organisation_social_types_key ON organisation_social_types (key);
+--
+-- Table: organisation_types
+--
+CREATE TABLE organisation_types (
+  id INTEGER PRIMARY KEY NOT NULL,
+  key varchar(255) NOT NULL,
+  name varchar(255) NOT NULL
+);
+CREATE UNIQUE INDEX organisation_types_key ON organisation_types (key);
+--
+-- Table: customers
+--
+CREATE TABLE customers (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  display_name varchar(255) NOT NULL,
+  full_name varchar(255) NOT NULL,
+  year_of_birth integer NOT NULL,
+  postcode varchar(16) NOT NULL,
+  latitude decimal(5,2),
+  longitude decimal(5,2),
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
+);
+CREATE INDEX customers_idx_entity_id ON customers (entity_id);
+--
+-- Table: entity_association
+--
+CREATE TABLE entity_association (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  lis boolean,
+  esta boolean,
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
+);
+CREATE INDEX entity_association_idx_entity_id ON entity_association (entity_id);
+--
+-- Table: gb_postcodes
+--
+CREATE TABLE gb_postcodes (
+  outcode char(4) NOT NULL,
+  incode char(3) NOT NULL DEFAULT '',
+  latitude decimal(7,5),
+  longitude decimal(7,5),
+  ward_id integer,
+  PRIMARY KEY (outcode, incode),
+  FOREIGN KEY (ward_id) REFERENCES gb_wards(id)
+);
+CREATE INDEX gb_postcodes_idx_ward_id ON gb_postcodes (ward_id);
+--
+-- Table: global_medals
+--
+CREATE TABLE global_medals (
+  id INTEGER PRIMARY KEY NOT NULL,
+  group_id integer NOT NULL,
+  threshold integer NOT NULL,
+  points integer NOT NULL,
+  FOREIGN KEY (group_id) REFERENCES global_medal_group(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX global_medals_idx_group_id ON global_medals (group_id);
+--
+-- Table: leaderboard_sets
+--
+CREATE TABLE leaderboard_sets (
+  id INTEGER PRIMARY KEY NOT NULL,
+  leaderboard_id integer NOT NULL,
+  date datetime NOT NULL,
+  FOREIGN KEY (leaderboard_id) REFERENCES leaderboards(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX leaderboard_sets_idx_leaderboard_id ON leaderboard_sets (leaderboard_id);
+--
+-- Table: org_medals
+--
+CREATE TABLE org_medals (
+  id INTEGER PRIMARY KEY NOT NULL,
+  group_id integer NOT NULL,
+  threshold integer NOT NULL,
+  points integer NOT NULL,
+  FOREIGN KEY (group_id) REFERENCES org_medal_group(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX org_medals_idx_group_id ON org_medals (group_id);
+--
+-- Table: transactions
+--
+CREATE TABLE transactions (
+  id INTEGER PRIMARY KEY NOT NULL,
+  buyer_id integer NOT NULL,
+  seller_id integer NOT NULL,
+  value numeric(100,0) NOT NULL,
+  proof_image text,
+  submitted_at datetime NOT NULL,
+  purchase_time datetime NOT NULL,
+  essential boolean NOT NULL DEFAULT false,
+  distance numeric(15),
+  FOREIGN KEY (buyer_id) REFERENCES entities(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  FOREIGN KEY (seller_id) REFERENCES entities(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX transactions_idx_buyer_id ON transactions (buyer_id);
+CREATE INDEX transactions_idx_seller_id ON transactions (seller_id);
+--
+-- Table: users
+--
+CREATE TABLE users (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  email text NOT NULL,
+  join_date datetime NOT NULL,
+  password varchar(100) NOT NULL,
+  is_admin boolean NOT NULL DEFAULT false,
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
+);
+CREATE INDEX users_idx_entity_id ON users (entity_id);
+CREATE UNIQUE INDEX users_email ON users (email);
+--
+-- Table: feedback
+--
+CREATE TABLE feedback (
+  id INTEGER PRIMARY KEY NOT NULL,
+  user_id integer NOT NULL,
+  submitted_at datetime NOT NULL,
+  feedbacktext text NOT NULL,
+  app_name varchar(255) NOT NULL,
+  package_name varchar(255) NOT NULL,
+  version_code varchar(255) NOT NULL,
+  version_number varchar(255) NOT NULL,
+  actioned boolean NOT NULL DEFAULT false,
+  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX feedback_idx_user_id ON feedback (user_id);
+--
+-- Table: global_user_medal_progress
+--
+CREATE TABLE global_user_medal_progress (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  group_id integer NOT NULL,
+  total integer NOT NULL,
+  FOREIGN KEY (entity_id) REFERENCES entities(id),
+  FOREIGN KEY (group_id) REFERENCES global_medal_group(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX global_user_medal_progress_idx_entity_id ON global_user_medal_progress (entity_id);
+CREATE INDEX global_user_medal_progress_idx_group_id ON global_user_medal_progress (group_id);
+--
+-- Table: global_user_medals
+--
+CREATE TABLE global_user_medals (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  group_id integer NOT NULL,
+  points integer NOT NULL,
+  awarded_at datetime NOT NULL,
+  threshold integer NOT NULL,
+  FOREIGN KEY (entity_id) REFERENCES entities(id),
+  FOREIGN KEY (group_id) REFERENCES global_medal_group(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX global_user_medals_idx_entity_id ON global_user_medals (entity_id);
+CREATE INDEX global_user_medals_idx_group_id ON global_user_medals (group_id);
+--
+-- Table: import_lookups
+--
+CREATE TABLE import_lookups (
+  id INTEGER PRIMARY KEY NOT NULL,
+  set_id integer NOT NULL,
+  name varchar(255) NOT NULL,
+  entity_id integer NOT NULL,
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  FOREIGN KEY (set_id) REFERENCES import_sets(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX import_lookups_idx_entity_id ON import_lookups (entity_id);
+CREATE INDEX import_lookups_idx_set_id ON import_lookups (set_id);
+--
+-- Table: org_user_medal_progress
+--
+CREATE TABLE org_user_medal_progress (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  group_id integer NOT NULL,
+  total integer NOT NULL,
+  FOREIGN KEY (entity_id) REFERENCES entities(id),
+  FOREIGN KEY (group_id) REFERENCES org_medal_group(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX org_user_medal_progress_idx_entity_id ON org_user_medal_progress (entity_id);
+CREATE INDEX org_user_medal_progress_idx_group_id ON org_user_medal_progress (group_id);
+--
+-- Table: org_user_medals
+--
+CREATE TABLE org_user_medals (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  group_id integer NOT NULL,
+  points integer NOT NULL,
+  awarded_at datetime NOT NULL,
+  threshold integer NOT NULL,
+  FOREIGN KEY (entity_id) REFERENCES entities(id),
+  FOREIGN KEY (group_id) REFERENCES org_medal_group(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX org_user_medals_idx_entity_id ON org_user_medals (entity_id);
+CREATE INDEX org_user_medals_idx_group_id ON org_user_medals (group_id);
+--
+-- Table: session_tokens
+--
+CREATE TABLE session_tokens (
+  id INTEGER PRIMARY KEY NOT NULL,
+  token varchar(255) NOT NULL,
+  user_id integer NOT NULL,
+  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX session_tokens_idx_user_id ON session_tokens (user_id);
+CREATE UNIQUE INDEX session_tokens_token ON session_tokens (token);
+--
+-- Table: transaction_recurring
+--
+CREATE TABLE transaction_recurring (
+  id INTEGER PRIMARY KEY NOT NULL,
+  buyer_id integer NOT NULL,
+  seller_id integer NOT NULL,
+  value numeric(100,0) NOT NULL,
+  start_time datetime NOT NULL,
+  last_updated datetime,
+  essential boolean NOT NULL DEFAULT false,
+  distance numeric(15),
+  category_id integer,
+  recurring_period varchar(255) NOT NULL,
+  FOREIGN KEY (buyer_id) REFERENCES entities(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  FOREIGN KEY (category_id) REFERENCES category(id),
+  FOREIGN KEY (seller_id) REFERENCES entities(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX transaction_recurring_idx_buyer_id ON transaction_recurring (buyer_id);
+CREATE INDEX transaction_recurring_idx_category_id ON transaction_recurring (category_id);
+CREATE INDEX transaction_recurring_idx_seller_id ON transaction_recurring (seller_id);
+--
+-- Table: transactions_meta
+--
+CREATE TABLE transactions_meta (
+  id INTEGER PRIMARY KEY NOT NULL,
+  transaction_id integer NOT NULL,
+  net_value numeric(100,0) NOT NULL,
+  sales_tax_value numeric(100,0) NOT NULL,
+  gross_value numeric(100,0) NOT NULL,
+  local_service boolean NOT NULL DEFAULT false,
+  regional_service boolean NOT NULL DEFAULT false,
+  national_service boolean NOT NULL DEFAULT false,
+  private_household_rebate boolean NOT NULL DEFAULT false,
+  business_tax_and_rebate boolean NOT NULL DEFAULT false,
+  stat_loc_gov boolean NOT NULL DEFAULT false,
+  central_loc_gov boolean NOT NULL DEFAULT false,
+  FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE
+);
+CREATE INDEX transactions_meta_idx_transaction_id ON transactions_meta (transaction_id);
+--
+-- Table: entities_postcodes
+--
+CREATE TABLE entities_postcodes (
+  outcode char(4) NOT NULL,
+  incode char(3) NOT NULL,
+  entity_id integer NOT NULL,
+  PRIMARY KEY (outcode, incode, entity_id),
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE,
+  FOREIGN KEY (outcode, incode) REFERENCES gb_postcodes(outcode, incode)
+);
+CREATE INDEX entities_postcodes_idx_entity_id ON entities_postcodes (entity_id);
+CREATE INDEX entities_postcodes_idx_outcode_incode ON entities_postcodes (outcode, incode);
+--
+-- Table: import_values
+--
+CREATE TABLE import_values (
+  id INTEGER PRIMARY KEY NOT NULL,
+  set_id integer NOT NULL,
+  user_name varchar(255) NOT NULL,
+  purchase_date datetime NOT NULL,
+  purchase_value varchar(255) NOT NULL,
+  org_name varchar(255) NOT NULL,
+  transaction_id integer,
+  ignore_value boolean NOT NULL DEFAULT false,
+  FOREIGN KEY (set_id) REFERENCES import_sets(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX import_values_idx_set_id ON import_values (set_id);
+CREATE INDEX import_values_idx_transaction_id ON import_values (transaction_id);
+--
+-- Table: leaderboard_values
+--
+CREATE TABLE leaderboard_values (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  set_id integer NOT NULL,
+  position integer NOT NULL,
+  value numeric(100,0) NOT NULL,
+  trend integer NOT NULL DEFAULT 0,
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  FOREIGN KEY (set_id) REFERENCES leaderboard_sets(id) ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+CREATE INDEX leaderboard_values_idx_entity_id ON leaderboard_values (entity_id);
+CREATE INDEX leaderboard_values_idx_set_id ON leaderboard_values (set_id);
+CREATE UNIQUE INDEX leaderboard_values_entity_id_set_id ON leaderboard_values (entity_id, set_id);
+--
+-- Table: organisations
+--
+CREATE TABLE organisations (
+  id INTEGER PRIMARY KEY NOT NULL,
+  entity_id integer NOT NULL,
+  name varchar(255) NOT NULL,
+  street_name text,
+  town varchar(255) NOT NULL,
+  postcode varchar(16),
+  country varchar(255),
+  sector varchar(1),
+  pending boolean NOT NULL DEFAULT false,
+  is_local boolean,
+  is_fair boolean,
+  submitted_by_id integer,
+  latitude decimal(8,5),
+  longitude decimal(8,5),
+  type_id integer,
+  social_type_id integer,
+  is_anchor boolean NOT NULL DEFAULT FALSE,
+  FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE,
+  FOREIGN KEY (type_id) REFERENCES organisation_types(id) ON DELETE CASCADE ON UPDATE CASCADE,
+  FOREIGN KEY (social_type_id) REFERENCES organisation_social_types(id) ON DELETE CASCADE ON UPDATE CASCADE
+);
+CREATE INDEX organisations_idx_entity_id ON organisations (entity_id);
+CREATE INDEX organisations_idx_type_id ON organisations (type_id);
+CREATE INDEX organisations_idx_social_type_id ON organisations (social_type_id);
+--
+-- Table: transaction_category
+--
+CREATE TABLE transaction_category (
+  category_id integer NOT NULL,
+  transaction_id integer NOT NULL,
+  FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE CASCADE,
+  FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE
+);
+CREATE INDEX transaction_category_idx_category_id ON transaction_category (category_id);
+CREATE INDEX transaction_category_idx_transaction_id ON transaction_category (transaction_id);
+CREATE UNIQUE INDEX transaction_category_transaction_id ON transaction_category (transaction_id);
+--
+-- Table: transactions_external
+--
+CREATE TABLE transactions_external (
+  id INTEGER PRIMARY KEY NOT NULL,
+  transaction_id integer NOT NULL,
+  external_reference_id integer NOT NULL,
+  external_id varchar(255) NOT NULL,
+  FOREIGN KEY (external_reference_id) REFERENCES external_references(id) ON DELETE CASCADE ON UPDATE CASCADE,
+  FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE ON UPDATE CASCADE
+);
+CREATE INDEX transactions_external_idx_external_reference_id ON transactions_external (external_reference_id);
+CREATE INDEX transactions_external_idx_transaction_id ON transactions_external (transaction_id);
+CREATE UNIQUE INDEX transactions_external_external_reference_id_external_id ON transactions_external (external_reference_id, external_id);
+--
+-- Table: organisation_payroll
+--
+CREATE TABLE organisation_payroll (
+  id INTEGER PRIMARY KEY NOT NULL,
+  org_id integer NOT NULL,
+  submitted_at datetime NOT NULL,
+  entry_period datetime NOT NULL,
+  employee_amount integer NOT NULL,
+  local_employee_amount integer NOT NULL,
+  gross_payroll numeric(100,0) NOT NULL,
+  payroll_income_tax numeric(100,0) NOT NULL,
+  payroll_employee_ni numeric(100,0) NOT NULL,
+  payroll_employer_ni numeric(100,0) NOT NULL,
+  payroll_total_pension numeric(100,0) NOT NULL,
+  payroll_other_benefit numeric(100,0) NOT NULL,
+  FOREIGN KEY (org_id) REFERENCES organisations(id)
+);
+CREATE INDEX organisation_payroll_idx_org_id ON organisation_payroll (org_id);
+--
+-- Table: organisations_external
+--
+CREATE TABLE organisations_external (
+  id INTEGER PRIMARY KEY NOT NULL,
+  org_id integer NOT NULL,
+  external_reference_id integer NOT NULL,
+  external_id varchar(255) NOT NULL,
+  FOREIGN KEY (external_reference_id) REFERENCES external_references(id) ON DELETE CASCADE ON UPDATE CASCADE,
+  FOREIGN KEY (org_id) REFERENCES organisations(id) ON DELETE CASCADE ON UPDATE CASCADE
+);
+CREATE INDEX organisations_external_idx_external_reference_id ON organisations_external (external_reference_id);
+CREATE INDEX organisations_external_idx_org_id ON organisations_external (org_id);
+CREATE UNIQUE INDEX organisations_external_external_reference_id_external_id ON organisations_external (external_reference_id, external_id);
+COMMIT;

share/ddl/SQLite/upgrade/28-29/001-auto.sql

@@ -0,0 +1,23 @@
+-- Convert schema 'share/ddl/_source/deploy/28/001-auto.yml' to 'share/ddl/_source/deploy/29/001-auto.yml':;
+
+;
+BEGIN;
+
+;
+CREATE TABLE gb_wards (
+  id INTEGER PRIMARY KEY NOT NULL,
+  ward varchar(100) NOT NULL
+);
+
+;
+ALTER TABLE gb_postcodes ADD COLUMN ward_id integer;
+
+;
+CREATE INDEX gb_postcodes_idx_ward_id ON gb_postcodes (ward_id);
+
+;
+
+;
+
+COMMIT;
+

share/ddl/SQLite/upgrade/29-30/001-auto.sql

@@ -0,0 +1,30 @@
+-- Convert schema 'share/ddl/_source/deploy/29/001-auto.yml' to 'share/ddl/_source/deploy/30/001-auto.yml':;
+
+;
+BEGIN;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN local_service boolean NOT NULL DEFAULT false;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN regional_service boolean NOT NULL DEFAULT false;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN national_service boolean NOT NULL DEFAULT false;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN private_household_rebate boolean NOT NULL DEFAULT false;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN business_tax_and_rebate boolean NOT NULL DEFAULT false;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN stat_loc_gov boolean NOT NULL DEFAULT false;
+
+;
+ALTER TABLE transactions_meta ADD COLUMN central_loc_gov boolean NOT NULL DEFAULT false;
+
+;
+
+COMMIT;
+

share/ddl/_source/deploy/29/001-auto-__VERSION.yml

@@ -0,0 +1,91 @@
+---
+schema:
+  procedures: {}
+  tables:
+    dbix_class_deploymenthandler_versions:
+      constraints:
+        - deferrable: 1
+          expression: ''
+          fields:
+            - id
+          match_type: ''
+          name: ''
+          on_delete: ''
+          on_update: ''
+          options: []
+          reference_fields: []
+          reference_table: ''
+          type: PRIMARY KEY
+        - deferrable: 1
+          expression: ''
+          fields:
+            - version
+          match_type: ''
+          name: dbix_class_deploymenthandler_versions_version
+          on_delete: ''
+          on_update: ''
+          options: []
+          reference_fields: []
+          reference_table: ''
+          type: UNIQUE
+      fields:
+        ddl:
+          data_type: text
+          default_value: ~
+          is_nullable: 1
+          is_primary_key: 0
+          is_unique: 0
+          name: ddl
+          order: 3
+          size:
+            - 0
+        id:
+          data_type: int
+          default_value: ~
+          is_auto_increment: 1
+          is_nullable: 0
+          is_primary_key: 1
+          is_unique: 0
+          name: id
+          order: 1
+          size:
+            - 0
+        upgrade_sql:
+          data_type: text
+          default_value: ~
+          is_nullable: 1
+          is_primary_key: 0
+          is_unique: 0
+          name: upgrade_sql
+          order: 4
+          size:
+            - 0
+        version:
+          data_type: varchar
+          default_value: ~
+          is_nullable: 0
+          is_primary_key: 0
+          is_unique: 1
+          name: version
+          order: 2
+          size:
+            - 50
+      indices: []
+      name: dbix_class_deploymenthandler_versions
+      options: []
+      order: 1
+  triggers: {}
+  views: {}
+translator:
+  add_drop_table: 0
+  filename: ~
+  no_comments: 0
+  parser_args:
+    sources:
+      - __VERSION
+  parser_type: SQL::Translator::Parser::DBIx::Class
+  producer_args: {}
+  producer_type: SQL::Translator::Producer::YAML
+  show_warnings: 0
+  trace: 0
+  version: 0.11024

share/ddl/_source/deploy/30/001-auto-__VERSION.yml

@@ -0,0 +1,91 @@
+---
+schema:
+  procedures: {}
+  tables:
+    dbix_class_deploymenthandler_versions:
+      constraints:
+        - deferrable: 1
+          expression: ''
+          fields:
+            - id
+          match_type: ''
+          name: ''
+          on_delete: ''
+          on_update: ''
+          options: []
+          reference_fields: []
+          reference_table: ''
+          type: PRIMARY KEY
+        - deferrable: 1
+          expression: ''
+          fields:
+            - version
+          match_type: ''
+          name: dbix_class_deploymenthandler_versions_version
+          on_delete: ''
+          on_update: ''
+          options: []
+          reference_fields: []
+          reference_table: ''
+          type: UNIQUE
+      fields:
+        ddl:
+          data_type: text
+          default_value: ~
+          is_nullable: 1
+          is_primary_key: 0
+          is_unique: 0
+          name: ddl
+          order: 3
+          size:
+            - 0
+        id:
+          data_type: int
+          default_value: ~
+          is_auto_increment: 1
+          is_nullable: 0
+          is_primary_key: 1
+          is_unique: 0
+          name: id
+          order: 1
+          size:
+            - 0
+        upgrade_sql:
+          data_type: text
+          default_value: ~
+          is_nullable: 1
+          is_primary_key: 0
+          is_unique: 0
+          name: upgrade_sql
+          order: 4
+          size:
+            - 0
+        version:
+          data_type: varchar
+          default_value: ~
+          is_nullable: 0
+          is_primary_key: 0
+          is_unique: 1
+          name: version
+          order: 2
+          size:
+            - 50
+      indices: []
+      name: dbix_class_deploymenthandler_versions
+      options: []
+      order: 1
+  triggers: {}
+  views: {}
+translator:
+  add_drop_table: 0
+  filename: ~
+  no_comments: 0
+  parser_args:
+    sources:
+      - __VERSION
+  parser_type: SQL::Translator::Parser::DBIx::Class
+  producer_args: {}
+  producer_type: SQL::Translator::Producer::YAML
+  show_warnings: 0
+  trace: 0
+  version: 0.11024

templates/admin/import/get_org.html.ep

@@ -1,4 +1,4 @@
-% layout 'admin_errors';
+% layout 'admin';
 % title 'Import';
 <div class="row">
   <div class="col-8">

templates/admin/import/get_user.html.ep

@@ -1,4 +1,4 @@
-% layout 'admin_errors';
+% layout 'admin';
 % title 'Import';
 <div class="row">
   <div class="col-8">

templates/admin/import_from/index.html.ep

@@ -1,17 +1,21 @@
 % layout 'admin';
 % title 'Import From';
 % content_for javascript => begin
+<script>
+$(function() {
+    $('#select-org').autocomplete({
+      source: '<%= url_for '/admin/import_from/org_search' %>',
+      minLength: 2,
+      select: function( event, ui ) {
+          console.log(ui);
+          $('#select-org').val(ui.item.label);
+          $('#select-org-id').val(ui.item.value);
+          return false;
+      }
+    });
+})
+</script>
 % end
-% if (my $error = flash 'error') {
-<div class="alert alert-danger" role="alert">
-  <strong>Error!</strong> <%= $error %>
-</div>
-% }
-% elsif (my $success = flash 'success') {
-<div class="alert alert-success" role="alert">
-  <strong>Success!</strong> <%= $success %>
-</div>
-% }
 <div class="row">
   <div class="col-12 mb-3">
     <h3 class="float-left">Import From</h3>
@@ -38,12 +42,8 @@
         <h4 class="card-title">LCC Procurement Import - Transactions</h4>
         <p>Expected headers at very least: "supplier_id", "transaction_id", "net_amount", "vat amount" , "gross_amount".</p>
         <form action="/admin/import_from/transactions" method="POST" enctype="multipart/form-data">
-          <select name="entity_id">
+          <input id="select-org" type="text">
-            <option>Select an Organisation</option>
+          <input id="select-org-id" name="entity_id" type="hidden">
-            <% for my $org ( @$org_entities ) { %>
-            <option value="<%= $org->{id}; %>"><%= $org->{name}; %></option>
-            <% } %>
-          </select><br/>
           <input type="file" name="transactions_csv" accept="text/csv">
           <input type="submit" value="Upload Transactions CSV">
         </form>

templates/admin/organisations/merge_detail.html.ep

@@ -1,4 +1,4 @@
-% layout 'admin_errors';
+% layout 'admin';
 % title 'Organisations';
 <div class="row">
   <div class="col-12">

templates/admin/organisations/merge_list.html.ep

@@ -1,4 +1,4 @@
-% layout 'admin_errors';
+% layout 'admin';
 % title 'Organisations';
 <div class="row">
   <div class="col-12">

templates/layouts/admin.html.ep

@@ -6,8 +6,13 @@
     <title>LocalLoop Admin - <%= title %></title>
 
     <!-- Bootstrap and jQuery js -->
-    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta/css/bootstrap.min.css" integrity="sha384-/Y6pD6FV/Vv2HJnA6t+vslU6fwYXjCFtcEpHbNJ0lyAFsXTsjBbfaDjzALeQsN6M" crossorigin="anonymous">
+    <link rel="stylesheet"
-
+          href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.css"
+          integrity="sha256-rByPlHULObEjJ6XQxW/flG2r+22R5dKiAoef+aXWfik=" crossorigin="anonymous" />
+    <link rel="stylesheet"
+          href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
+          integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T"
+          crossorigin="anonymous">
 
     %= stylesheet '/static/admin/css/main.css';
   </head>
@@ -59,6 +64,9 @@
           <li class="nav-item">
             <a class="nav-link<%= title eq 'Import' ? ' active' : '' %>" href="<%= url_for '/admin/import' %>">Import</a>
           </li>
+          <li class="nav-item">
+            <a class="nav-link" href="<%= url_for '/admin/minion' %>">Minion</a>
+          </li>
           <li class="nav-item">
             <a class="nav-link" href="<%= url_for '/admin/logout' %>">Logout</a>
           </li>
@@ -66,6 +74,19 @@
       </div>
     </nav>
     <div class="container">
+      % if ( my $f_error = flash 'error' ) {
+      <div class="alert alert-danger" role="alert">
+        <strong>Error!</strong> <%= $f_error %>
+      </div>
+      % } elsif ( my $s_error = stash 'error' ) {
+      <div class="alert alert-danger" role="alert">
+        <strong>Error!</strong> <%= $s_error %>
+      </div>
+      % } elsif ( my $success = flash 'success' ) {
+      <div class="alert alert-success" role="alert">
+        <strong>Success!</strong> <%= $success %>
+      </div>
+      % }
       <%= content %>
     </div>
     <div class="navbar bg-dark fixed-bottom">
@@ -73,10 +94,18 @@
         Version: <%= $c->config->{version} %>
       </span>
     </div>
-    <script src="https://code.jquery.com/jquery-3.1.1.slim.min.js" integrity="sha384-A7FZj7v+d/sdmMqp/nOQwliLvUsJfDHW+k9Omg/a/EheAdgtzNs3hpfag6Ed950n" crossorigin="anonymous"></script>
+    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"
-    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.5/umd/popper.min.js" integrity="sha256-jpW4gXAhFvqGDD5B7366rIPD7PDbAmqq4CO0ZnHbdM4=" crossorigin="anonymous"></script>
+            integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8="
-    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta/js/bootstrap.min.js" integrity="sha384-h0AbiXch4ZDo7tp9hKZ4TsHbi047NrKGLO3SEJAg45jXxnGIfYzk4Si90RDIqNm1" crossorigin="anonymous"></script>
+            crossorigin="anonymous"></script>
-
+    <script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"
+            integrity="sha256-KM512VNnjElC30ehFwehXjx1YCHPiQkOPmqnrWtpccM="
+            crossorigin="anonymous"></script>
+    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"
+            integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1"
+            crossorigin="anonymous"></script>
+    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
+            integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM"
+            crossorigin="anonymous"></script>
     %= content_for 'javascript';
   </body>
 </html>

templates/layouts/admin_errors.html.ep

@@ -1,89 +0,0 @@
-<!DOCTYPE html>
-<html>
-  <head>
-    <meta charset="UTF-8">
-    <meta name="viewport" content="width=device-width, initial-scale=1.0">
-    <title>LocalLoop Admin - <%= title %></title>
-
-    <!-- Bootstrap and jQuery js -->
-    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta/css/bootstrap.min.css" integrity="sha384-/Y6pD6FV/Vv2HJnA6t+vslU6fwYXjCFtcEpHbNJ0lyAFsXTsjBbfaDjzALeQsN6M" crossorigin="anonymous">
-
-
-    %= stylesheet '/static/admin/css/main.css';
-  </head>
-  <body>
-    <nav class="navbar navbar-expand-md fixed-top navbar-dark bg-danger">
-      <a class="navbar-brand" href="<%= url_for '/admin/home' %>">LocalLoop Admin</a>
-      <button class="navbar-toggler navbar-toggler-right"
-              type="button"
-              data-toggle="collapse"
-              data-target="#navbarNav"
-              aria-controls="navbarNav"
-              aria-expanded="false"
-              aria-label="Toggle navigation">
-        <span class="navbar-toggler-icon"></span>
-      </button>
-
-      <div class="collapse navbar-collapse" id="navbarNav">
-        <ul class="navbar-nav ml-auto">
-          <li class="nav-item dropdown">
-            <a class="nav-link dropdown-toggle" data-toggle="dropdown" href="#">
-              Reports
-            </a>
-            <div class="dropdown-menu">
-              <a class="dropdown-item" href="<%= url_for '/admin/reports/transactions' %>">Transactions (Hourly)</a>
-              <a class="dropdown-item" href="<%= url_for('/admin/reports/transactions')->query(scale =>'days') %>">Transactions (Daily)</a>
-            </div>
-          </li>
-          <li class="nav-item">
-            <a class="nav-link<%= title eq 'Feedback' ? ' active' : '' %>" href="<%= url_for '/admin/feedback' %>">Feedback</a>
-          </li>
-          <li class="nav-item">
-            <a class="nav-link<%= title eq 'Tokens' ? ' active' : '' %>" href="<%= url_for '/admin/tokens' %>">Tokens</a>
-          </li>
-          <li class="nav-item">
-            <a class="nav-link<%= title eq 'Transactions' ? ' active' : '' %>" href="<%= url_for '/admin/transactions' %>">Transactions</a>
-          </li>
-          <li class="nav-item">
-            <a class="nav-link<%= title eq 'Users' ? ' active' : '' %>" href="<%= url_for '/admin/users' %>">Users</a>
-          </li>
-          <li class="nav-item">
-            <a class="nav-link<%= title eq 'Organisations' ? ' active' : '' %>" href="<%= url_for '/admin/organisations' %>">Organisations</a>
-          </li>
-          <li class="nav-item">
-            <a class="nav-link<%= title eq 'Import' ? ' active' : '' %>" href="<%= url_for '/admin/import' %>">Import</a>
-          </li>
-          <li class="nav-item">
-            <a class="nav-link" href="<%= url_for '/admin/logout' %>">Logout</a>
-          </li>
-        </div>
-      </div>
-    </nav>
-    <div class="container">
-      % if ( my $f_error = flash 'error' ) {
-        <div class="alert alert-danger" role="alert">
-          <strong>Error!</strong> <%= $f_error %>
-        </div>
-        % } elsif ( my $s_error = stash 'error' ) {
-        <div class="alert alert-danger" role="alert">
-          <strong>Error!</strong> <%= $s_error %>
-        </div>
-      % } elsif ( my $success = flash 'success' ) {
-        <div class="alert alert-success" role="alert">
-          <strong>Success!</strong> <%= $success %>
-        </div>
-      % }
-      <%= content %>
-    </div>
-    <div class="navbar bg-dark fixed-bottom">
-      <span class="navbar-text ml-auto text-muted">
-        Version: <%= $c->config->{version} %>
-      </span>
-    </div>
-    <script src="https://code.jquery.com/jquery-3.1.1.slim.min.js" integrity="sha384-A7FZj7v+d/sdmMqp/nOQwliLvUsJfDHW+k9Omg/a/EheAdgtzNs3hpfag6Ed950n" crossorigin="anonymous"></script>
-    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.5/umd/popper.min.js" integrity="sha256-jpW4gXAhFvqGDD5B7366rIPD7PDbAmqq4CO0ZnHbdM4=" crossorigin="anonymous"></script>
-    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta/js/bootstrap.min.js" integrity="sha384-h0AbiXch4ZDo7tp9hKZ4TsHbi047NrKGLO3SEJAg45jXxnGIfYzk4Si90RDIqNm1" crossorigin="anonymous"></script>
-
-    %= content_for 'javascript';
-  </body>
-</html>

templates/root/index.html.ep

@@ -4,5 +4,7 @@
 %= javascript '/static/user/js/home.js';
 % end
 <div>
-  <h1>App currently in development, please come back later!</h1>
+  <h1>Local Loop API Server</h1>
+  <p>If you have arrived here, you're either a developer or something has gone wrong! Oops!</p>
+  <a href="/admin" class="btn btn-primary">Go to Admin Login</a>
 </div>