Merge pull request #127 from Pear-Trading/master

Master merge for v0.10.10

.gitignore

@@ -2,9 +2,13 @@
 myapp.conf
 hypnotoad.pid
 *.db
+*.db-wal
+*.db-shm
+*.db-journal
 *~
 /images
 *.swp
+/upload
 
 cover_db/
 schema.png

.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

@@ -0,0 +1,13 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<module type="WEB_MODULE" version="4">
+  <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$" />
+    <orderEntry type="inheritedJdk" />
+    <orderEntry type="sourceFolder" forTests="false" />
+  </component>
+</module>

.idea/codeStyles/codeStyleConfig.xml

@@ -0,0 +1,5 @@
+<component name="ProjectCodeStyleConfiguration">
+  <state>
+    <option name="PREFERRED_PROJECT_CODE_STYLE" value="Default" />
+  </state>
+</component>

.idea/dataSources.xml

@@ -0,0 +1,20 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<project version="4">
+  <component name="DataSourceManagerImpl" format="xml" multifile-model="true">
+    <data-source source="LOCAL" name="foodloop" uuid="7c088e2a-6667-4259-a2b3-9f440345d008">
+      <driver-ref>sqlite.xerial</driver-ref>
+      <synchronize>true</synchronize>
+      <jdbc-driver>org.sqlite.JDBC</jdbc-driver>
+      <jdbc-url>jdbc:sqlite:$PROJECT_DIR$/foodloop.db</jdbc-url>
+      <driver-properties>
+        <property name="enable_load_extension" value="true" />
+      </driver-properties>
+    </data-source>
+    <data-source source="LOCAL" name="PostgreSQL foodloop@localhost" uuid="8161e393-4db4-4e03-aa8b-7a961f14a591">
+      <driver-ref>postgresql</driver-ref>
+      <synchronize>true</synchronize>
+      <jdbc-driver>org.postgresql.Driver</jdbc-driver>
+      <jdbc-url>jdbc:postgresql://localhost:5432/</jdbc-url>
+    </data-source>
+  </component>
+</project>

.idea/misc.xml

@@ -0,0 +1,6 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<project version="4">
+  <component name="ProjectRootManager">
+    <output url="file://$PROJECT_DIR$/out" />
+  </component>
+</project>

.idea/modules.xml

@@ -0,0 +1,8 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<project version="4">
+  <component name="ProjectModuleManager">
+    <modules>
+      <module fileurl="file://$PROJECT_DIR$/.idea/Foodloop-Server.iml" filepath="$PROJECT_DIR$/.idea/Foodloop-Server.iml" />
+    </modules>
+  </component>
+</project>

.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>

.idea/vcs.xml

@@ -0,0 +1,6 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<project version="4">
+  <component name="VcsDirectoryMappings">
+    <mapping directory="" vcs="Git" />
+  </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,8 +2,62 @@
 
 # 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
+
+# v0.10.9
+
+* Removed sector list from dashboard stats and swapped it for category list
+* Added fix to recurring transaction script
+
+# v0.10.8
+
+* Added yearly recurring payments
+
+# v0.10.7
+
+* Added `cron_daily` script for holding all daily cronjobs
+* **Admin Fix** Parse currency without a currency symbol on import
+* **Admin Fix** Fix large CSV issue on import
+* Use custom secrets for encryption
+* Made purchase categories easier to pull
+* Added dashboard data for getting essential for all purchases along with
+weekly and monthly view of category purchases
+* Amended tests where relevant
+
+# v0.10.6
+
+* Fixed organisation submission
+* Changed category listing code
+* Made transaction upload code more lenient
+* Added API ability to edit and delete transactions
+* Added test for above
+* Made test dumping more sane
+* Fixed quantised transaction calcuations for weeks on sqlite
+* Amended customer snippet, category list and customer stats tests
+
+# v0.10.5
+
+* **Admin Feature** Removed generic Transaction List, replaced with a new
+transaction statistic viewing list
+* **Admin Fix** Amended user view to have accordion
+
+# v0.10.4
+
 * Added API for category budget
 * Added working test for the new API
+* Added initial placeholder API for medals & user points being used in testing
+* Added initial schema for medals
+* Added essential flag to purchases in schema
+* Amended upload API to account for essential purchases
+* **Admin Feature** Added ability to view essential flag on purchases
+* Made fixes to category viewing API
+* Added schema for storing recurring purchases
+* Amended Upload code to allow for if purchases are recurring
+* Added script for checking recurring purchases and creating them if required
 
 # v0.10.3
 

README.md

@@ -22,3 +22,129 @@ cpanm --installdeps . --with-feature postgres
 PEAR_TEST_PG=1 prove -lr
 ```
 
+# Minion
+
+to set up minion support, you will need to create a database and user for
+minion to connect to. In production his should be a PostgreSQL database,
+however an SQLite db can be used in testing.
+
+To use the SQLite version, run the following commands:
+
+```
+cpanm --installdeps --with-feature sqlite .
+```
+
+And then add the following to your configuration file:
+
+```
+  minion => {
+    SQLite => 'sqlite: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
+
+```
+# Example commands - probably not the best ones
+# TODO come back and improve these with proper ownership and DDL rights
+sudo -u postgres createuser minion
+sudo -u postgres createdb localloop_minion
+sudo -u postgres psql
+psql=# alter user minion with encrypted password 'abc123';
+psql=# grant all privileges on database localloop_minion to minion;
+```
+
+# 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
+
+To install a local DB:
+
+```
+./script/deploy_db install -c 'dbi:SQLite:dbname=foodloop.db'
+```
+
+To do an upgrade of it after making DB changes to commit:
+
+```
+./script/deploy_db write_ddl -c 'dbi:SQLite:dbname=foodloop.db'
+./script/deploy_db upgrade -c 'dbi:SQLite:dbname=foodloop.db'
+```
+
+To redo leaderboards:
+
+```
+./script/pear-local_loop recalc_leaderboards
+```
+
+To serve a test version locally of the server:
+
+```
+morbo script/pear-local_loop
+```

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';
@@ -23,6 +22,8 @@ requires 'DBIx::Class::Fixtures';
 requires 'GIS::Distance';
 requires 'Text::CSV';
 requires 'Try::Tiny';
+requires 'Throwable::Error';
+requires 'Minion';
 
 on 'test' => sub {
   requires 'Test::More';
@@ -37,6 +38,13 @@ feature 'schema-graph', 'Draw diagrams of Schema' => sub {
 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

@@ -26,6 +26,7 @@ sub startup {
   $self->plugin('Config', {
     default => {
       storage_path => tempdir,
+      upload_path => $self->home->child('upload'),
       sessionTimeSeconds => 60 * 60 * 24 * 7,
       sessionTokenJsonName => 'session_key',
       sessionExpiresJsonName => 'sessionExpires',
@@ -34,6 +35,13 @@ sub startup {
   });
   my $config = $self->config;
 
+  if ( defined $config->{secret} ) {
+    $self->secrets([ $config->{secret} ]);
+  } elsif ( $self->mode eq 'production' ) {
+    # Just incase we end up in production and it hasnt been set!
+    $self->secrets([ Data::UUID->new->create() ]);
+  }
+
   push @{ $self->commands->namespaces }, __PACKAGE__ . '::Command';
 
   $self->plugin('Pear::LocalLoop::Plugin::BootstrapPagination', { bootstrap4 => 1 } );
@@ -42,6 +50,7 @@ sub startup {
   $self->plugin('Pear::LocalLoop::Plugin::Currency');
   $self->plugin('Pear::LocalLoop::Plugin::Postcodes');
   $self->plugin('Pear::LocalLoop::Plugin::TemplateHelpers');
+  $self->plugin('Pear::LocalLoop::Plugin::Minion');
 
   $self->plugin('Authentication' => {
     'load_user' => sub {
@@ -151,9 +160,12 @@ sub startup {
   $api->post('/stats')->to('api-stats#post_index');
   $api->post('/stats/category')->to('api-categories#post_category_list');
   $api->post('/stats/customer')->to('api-stats#post_customer');
+  $api->post('/stats/organisation')->to('api-stats#post_organisation');
   $api->post('/stats/leaderboard')->to('api-stats#post_leaderboards');
   $api->post('/stats/leaderboard/paged')->to('api-stats#post_leaderboards_paged');
   $api->post('/outgoing-transactions')->to('api-transactions#post_transaction_list_purchases');
+  $api->post('/recurring-transactions')->to('api-transactions#update_recurring');
+  $api->post('/recurring-transactions/delete')->to('api-transactions#delete_recurring');
 
 
   my $api_v1 = $api->under('/v1');
@@ -179,6 +191,15 @@ sub startup {
   $api_v1_org->post('/employee')->to('api-organisation#post_employee_read');
   $api_v1_org->post('/employee/add')->to('api-organisation#post_employee_add');
 
+  $api_v1_org->post('/external/transactions')->to('api-external#post_lcc_transactions');
+  $api_v1_org->post('/external/suppliers')->to('api-external#post_lcc_suppliers');
+  $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');
+
   my $api_v1_cust = $api_v1->under('/customer')->to('api-v1-customer#auth');
 
   $api_v1_cust->post('/graphs')->to('api-v1-customer-graphs#index');
@@ -187,6 +208,12 @@ sub startup {
 
   my $admin_routes = $r->under('/admin')->to('admin#under');
 
+  if ( defined $config->{minion} ) {
+    $self->plugin( 'Minion::Admin' => {
+      return_to => '/admin/home',
+      route => $admin_routes->any('/minion'),
+    } );
+  }
   $admin_routes->get('/home')->to('admin#home');
 
   $admin_routes->get('/tokens')->to('admin-tokens#index');
@@ -235,6 +262,13 @@ sub startup {
 
   $admin_routes->get('/import/:set_id/ignore/:value_id')->to('admin-import#ignore_value');
   $admin_routes->get('/import/:set_id/import')->to('admin-import#run_import');
+
+  $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');
 
 # $user_routes->get('/home')->to('root#home');
@@ -245,9 +279,9 @@ sub startup {
 #  $portal_api->post('/search')->to('api-upload#post_search');
 
   $self->hook( before_dispatch => sub {
-    my $self = shift;
+    my $c = shift;
 
-    $self->res->headers->header('Access-Control-Allow-Origin' => '*') if $self->app->mode eq 'development';
+    $c->res->headers->header('Access-Control-Allow-Origin' => '*') if $c->app->mode eq 'development';
   });
 
   $self->helper( copy_transactions_and_delete => sub {

lib/Pear/LocalLoop/Command/codepoint_open.pm

@@ -22,7 +22,12 @@ sub run {
 
   unless ( -d $output_dir ) {
     print "Unzipping code-point-open data\n" unless $quiet_mode;
-    system( 'unzip', '-q', $zip_file, '-d', $output_dir );
+    eval { system( 'unzip', '-q', $zip_file, '-d', $output_dir ) };
+    if ( my $err = $@ ) {
+        print "Error extracting zip: " . $err . "\n";
+        print "Manually create etc/code-point-open/codepo_gb directory and extract zip into it";
+        die;
+    }
   }
 
   my $cpo = Geo::UK::Postcode::CodePointOpen->new( path => $output_dir );

lib/Pear/LocalLoop/Command/recur_transactions.pm

@@ -0,0 +1,140 @@
+package Pear::LocalLoop::Command::recur_transactions;
+use Mojo::Base 'Mojolicious::Command';
+
+use Mojo::Util 'getopt';
+
+use DateTime;
+use DateTime::Format::Strptime;
+
+has description => 'Recur Transactions';
+
+has usage => sub { shift->extract_usage };
+
+sub run {
+  my ( $self, @args ) = @_;
+
+  my $app = $self->app;
+
+  getopt \@args,
+    'f|force' => \my $force,
+    'd|date=s' => \my $date;
+
+  unless ( defined $force ) {
+    say "Will not do anything without force option";
+    return;
+  }
+
+  my $date_formatter = DateTime::Format::Strptime->new(
+    pattern => '%Y-%m-%d'
+  );
+
+  my $datetime;
+
+  if ( defined $date ) {
+
+    $datetime = $date_formatter->parse_datetime($date);
+
+    unless ( defined $datetime ) {
+      say "Unrecognised date format, please use 'YYYY-MM-DD' Format";
+      return;
+    }
+  } else {
+    $datetime = DateTime->today;
+  }
+
+  my $match_date_day = $app->format_iso_date($datetime->clone->subtract( days => 1 ));
+  my $match_date_week = $app->format_iso_date($datetime->clone->subtract( weeks => 1 ));
+  my $match_date_fortnight = $app->format_iso_date($datetime->clone->subtract( weeks => 2 ));
+  my $match_date_month = $app->format_iso_date($datetime->clone->subtract( months => 1 ));
+  my $match_date_quarter = $app->format_iso_date($datetime->clone->subtract( months => 3));
+  my $match_date_year = $app->format_iso_date($datetime->clone->subtract( years => 1 ));
+
+  my $schema = $app->schema;
+  my $dtf = $schema->storage->datetime_parser;
+  my $recur_rs = $schema->resultset('TransactionRecurring');
+
+  for my $recur_result ( $recur_rs->all ) {
+
+    my $start_time_dt;
+    if ( defined $recur_result->last_updated ) {
+      $start_time_dt = $recur_result->last_updated;
+    } else {
+      $start_time_dt = $recur_result->start_time;
+    }
+    my $start_time = $app->format_iso_date($start_time_dt);
+    my $recurring_period = $recur_result->recurring_period;
+
+    if ( $recurring_period eq 'daily' ) {
+      next unless $start_time eq $match_date_day;
+      say "matched recurring transaction ID " . $recur_result->id . " to daily";
+    } elsif ( $recurring_period eq 'weekly' ) {
+      next unless $start_time eq $match_date_week;
+      say "matched recurring transaction ID " . $recur_result->id . " to weekly";
+    } elsif ( $recurring_period eq 'fortnightly' ) {
+      next unless $start_time eq $match_date_fortnight;
+      say "matched recurring transaction ID " . $recur_result->id . " to fortnightly";
+    } elsif ( $recurring_period eq 'monthly' ) {
+      next unless $start_time eq $match_date_month;
+      say "matched recurring transaction ID " . $recur_result->id . " to monthly";
+    } elsif ( $recurring_period eq 'quarterly' ) {
+      next unless $start_time eq $match_date_quarter;
+      say "matched recurring transaction ID " . $recur_result->id . " to quarterly";
+    } elsif ( $recurring_period eq 'yearly' ) {
+      next unless $start_time eq $match_date_year;
+      say "matched recurring transaction ID " . $recur_result->id . " to yearly";
+    } else {
+      say "Invalid recurring time period given";
+      return;
+    }
+
+    my $purchase_time = DateTime->new(
+      year   => $datetime->year,
+      month  => $datetime->month,
+      day    => $datetime->day,
+      hour   => $start_time_dt->hour,
+      minute => $start_time_dt->minute,
+      second => $start_time_dt->second,
+      time_zone => 'UTC',
+    );
+    my $category = $recur_result->category_id;
+    my $essential = $recur_result->essential;
+    my $distance = $recur_result->distance;
+
+    my $new_transaction = $schema->resultset('Transaction')->create({
+      buyer_id      => $recur_result->buyer_id,
+      seller_id     => $recur_result->seller_id,
+      value         => $recur_result->value,
+      purchase_time => $app->format_db_datetime($purchase_time),
+      distance      => $distance,
+      essential     => ( defined $essential ? $essential : 0 ),
+    });
+
+    unless ( defined $new_transaction ) {
+      say "Error Adding Transaction";
+      return;
+    }
+
+    if ( defined $category ) {
+      $schema->resultset('TransactionCategory')->create({
+        category_id => $category,
+        transaction_id => $new_transaction->id,
+      });
+    }
+
+    $recur_result->update({ last_updated => $purchase_time });
+
+  }
+}
+
+=head1 SYNOPSIS
+
+  Usage: APPLICATION recur_transactions [OPTIONS]
+
+  Options:
+
+    -f, --force   Actually insert the data
+    -d, --date    Date to recur the transactions on
+
+=cut
+
+1;

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

@@ -59,6 +59,7 @@ sub update {
   my $validation = $c->validation;
   $validation->required('id');
   $validation->required('category', 'trim')->like(qr/^[\w]*$/);
+  $validation->optional('line_icon');
 
   my $id = $c->param('id');
 
@@ -70,6 +71,7 @@ sub update {
     $category->update({
       id => $validation->param('id'),
       name => $validation->param('category'),
+      line_icon => (defined $validation->param('line_icon') ? $validation->param('line_icon') : undef ),
     });
     $c->flash( success => 'Category Updated' );
     $c->redirect_to( '/admin/categories/' . $validation->param('id') );

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

@@ -144,7 +144,8 @@ sub _csv_flash_error {
 
   $c->flash(
     error => $error,
-    csv_data => $c->param('csv'),
+    # If csv info is huge, this fails epically
+    #csv_data => $c->param('csv'),
     date_format => $c->param('date_format'),
   );
 }

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

@@ -0,0 +1,127 @@
+package Pear::LocalLoop::Controller::Admin::ImportFrom;
+use Mojo::Base 'Mojolicious::Controller';
+use Moo;
+use Try::Tiny;
+use Mojo::File qw/path/;
+
+sub index {
+  my $c = shift;
+  $c->stash->{org_entities} = [
+    map {
+      { id => $_->entity_id, name => $_->name }
+    } $c->schema->resultset('Organisation')->search({ name => { like => '%lancashire%' }}, { columns => [qw/ entity_id name / ]})
+  ];
+
+  $c->app->max_request_size(104857600);
+}
+
+sub post_suppliers {
+  my $c = shift;
+
+  unless ($c->param('suppliers_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('suppliers_csv');
+
+  my $filename = path($c->app->config->{upload_path}, time . 'suppliers.csv');
+
+  $file->move_to($filename);
+
+  my $job_id = $c->minion->enqueue('csv_supplier_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_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;
+
+  unless ($c->param('entity_id') ne '') {
+    $c->flash(error => "Please Choose an organisation");
+    return $c->redirect_to('/admin/import_from');
+  }
+
+  unless ($c->param('transactions_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('transactions_csv');
+
+  my $filename = path($c->app->config->{upload_path}, time . 'transactions.csv');
+
+  $file->move_to($filename);
+
+  my $job_id = $c->minion->enqueue('csv_transaction_import' => [ $filename, $c->param('entity_id') ]);
+
+  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 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

@@ -1,6 +1,8 @@
 package Pear::LocalLoop::Controller::Admin::Transactions;
 use Mojo::Base 'Mojolicious::Controller';
 
+use List::Util qw/ max sum /;
+
 has result_set => sub {
   my $c = shift;
   return $c->schema->resultset('Transaction');
@@ -9,15 +11,49 @@ has result_set => sub {
 sub index {
   my $c = shift;
 
-  my $transactions = $c->result_set->search(
-    undef, {
-      page => $c->param('page') || 1,
-      rows => 10,
-      order_by => { -desc => 'submitted_at' },
-    },
+  my $pending_transaction_rs = $c->schema->resultset('Organisation')->search({ pending => 1 })->entity->sales;
+
+  my $driver = $c->schema->storage->dbh->{Driver}->{Name};
+  my $week_transaction_rs = $c->schema->resultset('ViewQuantisedTransaction' . $driver)->search(
+    {},
+    {
+      select => [
+        { count => 'me.value', '-as' => 'count' },
+        { sum => 'me.value', '-as' => 'sum_value' },
+        'quantised_weeks',
+      ],
+      group_by => 'quantised_weeks',
+      order_by => { '-asc' => 'quantised_weeks' },
+    }
   );
+
+  my @all_weeks = $week_transaction_rs->all;
+  my $first_week_count = defined $all_weeks[0] ? $all_weeks[0]->get_column('count') || 0 : 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;
+
+  my $transaction_rs = $c->schema->resultset('Transaction');
+  my $value_rs_col = $transaction_rs->get_column('value');
+  my $max_value = $value_rs_col->max / 100000 || 0;
+  my $avg_value = sprintf( '%.2f', $value_rs_col->func('AVG') / 100000) || 0;
+  my $sum_value = $value_rs_col->sum / 100000 || 0;
+  my $count = $transaction_rs->count || 0;
+
+  my $placeholder = 'Placeholder';
   $c->stash(
-    transactions => $transactions,
+    placeholder => $placeholder,
+    pending_trans => $pending_transaction_rs->count,
+    weeks => {
+      first_count => $first_week_count,
+      second_count => $second_week_count,
+      first_value => $first_week_value,
+      second_value => $second_week_value,
+      max => $max_value,
+      avg => $avg_value,
+      sum => $sum_value,
+      count => $count,
+    },
   );
 }
 
@@ -66,4 +102,19 @@ if ( my $transaction = $c->result_set->find($id) ) {
   }
 }
 
+sub pg_or_sqlite {
+  my ( $c, $pg_sql, $sqlite_sql ) = @_;
+
+  my $driver = $c->schema->storage->dbh->{Driver}->{Name};
+
+  if ( $driver eq 'Pg' ) {
+    return \$pg_sql;
+  } elsif ( $driver eq 'SQLite' ) {
+    return \$sqlite_sql;
+  } else {
+    $c->app->log->warn('Unknown Driver Used');
+    return undef;
+  }
+}
+
 1;

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

@@ -14,7 +14,7 @@ sub post_category_list {
 
   my $dtf = $c->schema->storage->datetime_parser;
   my $driver = $c->schema->storage->dbh->{Driver}->{Name};
-  my $month_transaction_rs = $c->schema->resultset('ViewQuantisedTransactionCategory' . $driver)->search(
+  my $month_transaction_category_rs = $c->schema->resultset('ViewQuantisedTransactionCategory' . $driver)->search(
     {
       purchase_time => {
         -between => [
@@ -28,28 +28,36 @@ sub post_category_list {
       columns => [
         {
           quantised        => 'quantised_weeks',
-          value            => 'value',
+          value            => { sum => 'value' },
           category_id      => 'category_id',
-        }
+          essential        => 'essential',
+        },
       ],
-      group_by => [ qw/ category_id quantised_weeks / ],
-      order_by => { '-desc' => 'value' },
+      group_by => [ qw/ category_id quantised_weeks essential / ],
     }
   );
 
-  my $data = {};
+  my $data = { categories => {}, essentials => {} };
 
-  for ( $month_transaction_rs->all ) {
-    my $quantised = $c->db_datetime_parser->parse_datetime($_->get_column('quantised'));
+  my $category_list = $c->schema->resultset('Category')->as_hash;
+
+  for my $cat_trans ( $month_transaction_category_rs->all ) {
+    my $quantised = $c->db_datetime_parser->parse_datetime($cat_trans->get_column('quantised'));
     my $days = $c->format_iso_date( $quantised ) || 0;
-    my $category = $_->get_column('category_id') || 0;
-    my $value = ($_->get_column('value') || 0) / 100000;
-    $data->{$days} = [] unless exists $data->{$days};
-    push @{ $data->{$days} }, {
-      days => $days,
-      value => $value,
-      category => $category,
-    };
+    my $category = $cat_trans->get_column('category_id') || 0;
+    my $value = ($cat_trans->get_column('value') || 0) / 100000;
+    $data->{categories}->{$days}->{$category_list->{$category}} += $value;
+    next unless $cat_trans->get_column('essential');
+    $data->{essentials}->{$days}->{value} += $value;
+  }
+
+  for my $day ( keys %{ $data->{categories} } ) {
+    my @days = ( map{ {
+      days => $day,
+      value => $data->{categories}->{$day}->{$_},
+      category => $_,
+    } } keys %{ $data->{categories}->{$day} } );
+    $data->{categories}->{$day} = [ sort { $b->{value} <=> $a->{value} } @days ];
   }
 
   return $c->render(

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

@@ -0,0 +1,489 @@
+package Pear::LocalLoop::Controller::Api::External;
+use Mojo::Base 'Mojolicious::Controller';
+use Mojo::JSON;
+
+sub post_lcc_transactions {
+  my $c = shift;
+
+  my $user = $c->stash->{api_user};
+
+  # TODO Check the user is lancaster city council
+
+  my $validation = $c->validation;
+  $validation->input($c->stash->{api_json});
+  $validation->optional('page')->number;
+  $validation->optional('per_page')->number;
+  $validation->optional('search');
+
+  return $c->api_validation_error if $validation->has_error;
+
+  my $search_ref = { 'me.buyer_id' => $user->entity->id };
+  if ($validation->param('search')) {
+    $search_ref->{"organisation.name"} = { '-like' => join('', '%', $validation->param('search'), '%') };
+  }
+
+  my $lcc_transactions = $c->schema->resultset('Transaction')->search(
+    $search_ref,
+    {
+      page     => $validation->param('page') || 1,
+      rows     => $validation->param('per_page') || 10,
+      join     => [ 'transaction', 'organisation' ],
+      order_by => { -desc => 'transaction.purchase_time' },
+    });
+
+  # purchase_time needs timezone attached to it
+  my @transaction_list = (
+    map {{
+      transaction_external_id => $_->external_id,
+      seller                  => $_->transaction->seller->name,
+      net_value               => $_->transaction->meta->net_value,
+      gross_value             => $_->transaction->meta->gross_value,
+      sales_tax_value         => $_->transaction->meta->sales_tax_value,
+      purchase_time           => $c->format_iso_datetime($_->transaction->purchase_time),
+    }} $lcc_transactions->all
+  );
+
+  return $c->render(json => {
+    success      => Mojo::JSON->true,
+    transactions => \@transaction_list,
+    page_no      => $lcc_transactions->pager->total_entries,
+  });
+}
+
+sub post_lcc_suppliers {
+  my $c = shift;
+
+  my $user = $c->stash->{api_user};
+
+  # TODO give an error if user is not of Lancashire County Council
+
+  # my $is_lcc = $user->entity->organisation->count({ name => "Lancashire County Council" });
+
+  my $v = $c->validation;
+  $v->input($c->stash->{api_json});
+  $v->optional('page')->number;
+  $v->optional('sort_by');
+  $v->optional('sort_dir');
+  $v->optional('search');
+
+  my $order_by = [
+    { -asc => 'organisation.name' },
+  ];
+  if ($v->param('sort_by')) {
+    my %dirs = ('asc' => '-asc', 'desc' => '-desc');
+    my $dir = $dirs{$v->param('sort_dir')} // '-asc';
+    my %sorts = (
+      'name'     => 'organisation.name',
+      'postcode' => 'organisation.postcode',
+      'spend'    => 'total_spend',
+    );
+    my $sort = $sorts{$v->param('sort_by')} || 'organisation.name';
+    $order_by->[0] = { $dir => $sort };
+  }
+
+  return $c->api_validation_error if $v->has_error;
+
+  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' ],
+      group_by  => [ 'me.id', 'organisation.id' ],
+      '+select' => [
+        {
+          'sum' => 'sales.value',
+          '-as' => 'total_spend',
+        }
+      ],
+      '+as'     => [ 'total_spend' ],
+      page      => $v->param('page') || 1,
+      rows      => 10,
+      order_by  => $order_by,
+    }
+  );
+
+  my @supplier_list = (
+    map {{
+      entity_id => $_->id,
+      name      => $_->name,
+      street    => $_->organisation->street_name,
+      town      => $_->organisation->town,
+      postcode  => $_->organisation->postcode,
+      country   => $_->organisation->country,
+      spend     => ($_->get_column('total_spend') / 100000) // 0,
+    }} $lcc_suppliers->all
+  );
+
+  return $c->render(json => {
+    success   => Mojo::JSON->true,
+    suppliers => \@supplier_list,
+    page_no   => $lcc_suppliers->pager->total_entries,
+  });
+}
+
+sub post_year_spend {
+  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 $dtf = $c->schema->storage->datetime_parser;
+  my $driver = $c->schema->storage->dbh->{Driver}->{Name};
+  my $spend_rs = $c->schema->resultset('ViewQuantisedTransaction' . $driver)->search(
+    {
+      purchase_time => {
+        -between => [
+          $dtf->format_datetime($first),
+          $dtf->format_datetime($last),
+        ],
+      },
+      buyer_id      => $user->entity->id,
+    },
+    {
+      columns  => [
+        {
+          quantised   => 'quantised_days',
+          count       => \"COUNT(*)",
+          total_spend => { sum => 'value' },
+        }
+      ],
+      group_by => 'quantised_days',
+      order_by => { '-asc' => 'quantised_days' },
+    }
+  );
+
+  my @graph_data = (
+    map {{
+      count => $_->get_column('count'),
+      value => ($_->get_column('total_spend') / 100000) // 0,
+      date  => $_->get_column('quantised'),
+    }} $spend_rs->all,
+  );
+
+  return $c->render(json => {
+    success => Mojo::JSON->true,
+    data    => \@graph_data,
+  });
+}
+
+sub post_supplier_count {
+  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 $dtf = $c->schema->storage->datetime_parser;
+  my $driver = $c->schema->storage->dbh->{Driver}->{Name};
+  my $spend_rs = $c->schema->resultset('ViewQuantisedTransaction' . $driver)->search(
+    {
+      purchase_time => {
+        -between => [
+          $dtf->format_datetime($first),
+          $dtf->format_datetime($last),
+        ],
+      },
+      buyer_id      => $user->entity->id,
+    },
+    {
+      join     => { 'seller' => 'organisation' },
+      select   => [
+        { count => 'me.value', '-as' => 'count' },
+        { sum => 'me.value', '-as' => 'total_spend' },
+        'organisation.name',
+        'me.quantised_days',
+      ],
+      as       => [ qw/count total_spend name quantised_days/ ],
+      group_by => [ qw/me.quantised_days seller.id organisation.id/ ],
+      order_by => { '-asc' => 'me.quantised_days' },
+    }
+  );
+
+  my @graph_data = (
+    map {{
+      count  => $_->get_column('count'),
+      value  => ($_->get_column('total_spend') / 100000) // 0,
+      date   => $_->get_column('quantised_days'),
+      seller => $_->get_column('name'),
+    }} $spend_rs->all,
+  );
+
+  return $c->render(json => {
+    success => Mojo::JSON->true,
+    data    => \@graph_data,
+  });
+}
+
+sub post_supplier_history {
+  my $c = shift;
+
+  my $user = $c->stash->{api_user};
+
+  # Temporary date lock for dev data
+  my $last = DateTime->new(
+    year  => 2019,
+    month => 4,
+    day   => 1
+  );
+  my $first = $last->clone->subtract(years => 1);
+  my $second = $last->clone->subtract(months => 6);
+  my $third = $last->clone->subtract(months => 3);
+
+  my $dtf = $c->schema->storage->datetime_parser;
+  my $year_rs = $c->schema->resultset('Entity')->search(
+    {
+      'sales.purchase_time' => {
+        -between => [
+          $dtf->format_datetime($first),
+          $dtf->format_datetime($last),
+        ],
+      },
+      'sales.buyer_id'      => $user->entity->id,
+    },
+    {
+      join     => [ 'sales', 'organisation' ],
+      columns  => [
+        {
+          id          => 'me.id',
+          name        => 'organisation.name',
+          count       => \"COUNT(*)",
+          total_spend => { sum => 'sales.value' },
+        }
+      ],
+      group_by => [ 'me.id', 'organisation.id' ],
+      order_by => { '-asc' => 'organisation.name' },
+    }
+  );
+  my $half_year_rs = $c->schema->resultset('Entity')->search(
+    {
+      'sales.purchase_time' => {
+        -between => [
+          $dtf->format_datetime($second),
+          $dtf->format_datetime($last),
+        ],
+      },
+      'sales.buyer_id'      => $user->entity->id,
+    },
+    {
+      join     => [ 'sales', 'organisation' ],
+      columns  => [
+        {
+          id          => 'me.id',
+          name        => 'organisation.name',
+          count       => \"COUNT(*)",
+          total_spend => { sum => 'sales.value' },
+        }
+      ],
+      group_by => [ 'me.id', 'organisation.id' ],
+      order_by => { '-asc' => 'organisation.name' },
+    }
+  );
+  my $quarter_year_rs = $c->schema->resultset('Entity')->search(
+    {
+      'sales.purchase_time' => {
+        -between => [
+          $dtf->format_datetime($third),
+          $dtf->format_datetime($last),
+        ],
+      },
+      'sales.buyer_id'      => $user->entity->id,
+    },
+    {
+      join     => [ 'sales', 'organisation' ],
+      columns  => [
+        {
+          id          => 'me.id',
+          name        => 'organisation.name',
+          count       => \"COUNT(*)",
+          total_spend => { sum => 'sales.value' },
+        }
+      ],
+      group_by => [ 'me.id', 'organisation.id' ],
+      order_by => { '-asc' => 'organisation.name' },
+    }
+  );
+
+  my %data;
+  for my $row ($year_rs->all) {
+    $data{$row->get_column('id')} = {
+      id            => $row->get_column('id'),
+      name          => $row->get_column('name'),
+      quarter_count => 0,
+      quarter_total => 0,
+      half_count    => 0,
+      half_total    => 0,
+      year_count    => $row->get_column('count'),
+      year_total    => $row->get_column('total_spend') / 100000,
+    };
+  }
+
+  for my $row ($half_year_rs->all) {
+    $data{$row->get_column('id')} = {
+      id            => $row->get_column('id'),
+      name          => $row->get_column('name'),
+      quarter_count => 0,
+      quarter_total => 0,
+      half_count    => $row->get_column('count'),
+      half_total    => $row->get_column('total_spend') / 100000,
+      year_count    => 0,
+      year_total    => 0,
+      %{$data{$row->get_column('id')}},
+    };
+  }
+
+  for my $row ($quarter_year_rs->all) {
+    $data{$row->get_column('id')} = {
+      id            => $row->get_column('id'),
+      name          => $row->get_column('name'),
+      quarter_count => $row->get_column('count'),
+      quarter_total => $row->get_column('total_spend') / 100000,
+      half_count    => 0,
+      half_total    => 0,
+      year_count    => 0,
+      year_total    => 0,
+      %{$data{$row->get_column('id')}},
+    };
+  }
+
+  return $c->render(json => {
+    success => Mojo::JSON->true,
+    data    => [ values %data ],
+  });
+}
+
+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/Stats.pm

@@ -63,9 +63,11 @@ sub post_customer {
 
   my $entity = $c->stash->{api_user}->entity;
 
-  my $duration = DateTime::Duration->new( weeks => 7 );
+  my $purchase_rs = $entity->purchases;
+
+  my $duration_weeks = DateTime::Duration->new( weeks => 7 );
   my $end = DateTime->today;
-  my $start = $end->clone->subtract_duration( $duration );
+  my $start_weeks = $end->clone->subtract_duration( $duration_weeks );
 
   my $dtf = $c->schema->storage->datetime_parser;
   my $driver = $c->schema->storage->dbh->{Driver}->{Name};
@@ -73,7 +75,7 @@ sub post_customer {
     {
       purchase_time => {
         -between => [
-          $dtf->format_datetime($start),
+          $dtf->format_datetime($start_weeks),
           $dtf->format_datetime($end),
         ],
       },
@@ -92,7 +94,7 @@ sub post_customer {
   );
 
   my @all_weeks = $week_transaction_rs->all;
-  my $first = $all_weeks[0]->get_column('count') || 0;
+  my $first = defined $all_weeks[0] ? $all_weeks[0]->get_column('count') || 0 : 0;
   my $second = defined $all_weeks[1] ? $all_weeks[1]->get_column('count') || 0 : 0;
   my $max = max( map { $_->get_column('count') } @all_weeks );
   my $sum = sum( map { $_->get_column('count') } @all_weeks );
@@ -106,29 +108,267 @@ sub post_customer {
     count => $count,
   };
 
-  my $sectors = { sectors => [], purchases => [] };
+  my $data = { cat_total => {}, categories => {}, essentials => {}, cat_list => {} };
 
-  my $sector_purchase_rs = $entity->purchases->search({},
+  my $category_list = $c->schema->resultset('Category')->as_hash;
+
+  my $category_purchase_rs = $purchase_rs->search({},
   {
-    join => { 'seller' => 'organisation' },
+    join => 'category',
     columns => {
-      sector => "organisation.sector",
-      count            => \"COUNT(*)",
+      category_id => "category.category_id",
+      value       => { sum => 'value' },
     },
-    group_by => "organisation.sector",
-    order_by => { '-desc' => $c->pg_or_sqlite('count',"COUNT(*)",)},
+    group_by => "category.category_id",
   }
   );
 
-  for ( $sector_purchase_rs->all ) {
-    push @{ $sectors->{ sectors } }, $_->get_column('sector');
-    push @{ $sectors->{ purchases } }, ($_->get_column('count') || 0);
+  my %cat_total_list;
+
+  for ( $category_purchase_rs->all ) {
+    my $category = $_->get_column('category_id') || 0;
+    my $value = ($_->get_column('value') || 0) / 100000;
+
+    $cat_total_list{$category_list->{$category}} += $value;
+  }
+
+  my @cat_lists = map { { category => $_, value => $cat_total_list{$_},
+  icon => $c->schema->resultset('Category')->as_hash_name_icon->{$_} || 'question'} } sort keys %cat_total_list;
+  $data->{cat_list} = [ sort { $b->{value} <=> $a->{value} } @cat_lists ];
+
+  my $purchase_no_essential_rs = $purchase_rs->search({
+    "me.essential" => 1,
+  });
+
+  $data->{essentials} = {
+    purchase_no_total => $purchase_rs->count,
+    purchase_no_essential_total => $purchase_no_essential_rs->count,
+  };
+
+  my $duration_month = DateTime::Duration->new( days => 28 );
+  my $start_month = $end->clone->subtract_duration( $duration_month );
+  my $month_transaction_category_rs = $c->schema->resultset('ViewQuantisedTransactionCategory' . $driver)->search(
+    {
+      purchase_time => {
+        -between => [
+          $dtf->format_datetime($start_month),
+          $dtf->format_datetime($end),
+        ],
+      },
+      buyer_id => $entity->id,
+    },
+    {
+      columns => [
+        {
+          quantised        => 'quantised_weeks',
+          value            => { sum => 'value' },
+          category_id      => 'category_id',
+          essential        => 'essential',
+        },
+      ],
+      group_by => [ qw/ category_id quantised_weeks essential / ],
+    }
+  );
+
+  for my $cat_trans ( $month_transaction_category_rs->all ) {
+    my $quantised = $c->db_datetime_parser->parse_datetime($cat_trans->get_column('quantised'));
+    my $days = $c->format_iso_date( $quantised ) || 0;
+    my $category = $cat_trans->get_column('category_id') || 0;
+    my $value = ($cat_trans->get_column('value') || 0) / 100000;
+    $data->{cat_total}->{$category_list->{$category}} += $value;
+    $data->{categories}->{$days}->{$category_list->{$category}} += $value;
+    next unless $cat_trans->get_column('essential');
+    $data->{essentials}->{$days}->{value} += $value;
+  }
+
+  for my $day ( keys %{ $data->{categories} } ) {
+    my @days = ( map{ {
+      days => $day,
+      value => $data->{categories}->{$day}->{$_},
+      category => $_,
+    } } keys %{ $data->{categories}->{$day} } );
+    $data->{categories}->{$day} = [ sort { $b->{value} <=> $a->{value} } @days ];
   }
 
   return $c->render( json => {
     success => Mojo::JSON->true,
+    data => $data,
+    weeks => $weeks,
+  });
+}
+
+sub post_organisation {
+  my $c = shift;
+
+  my $entity = $c->stash->{api_user}->entity;
+
+  my $purchase_rs = $entity->purchases;
+
+  my $duration_weeks = DateTime::Duration->new( weeks => 7 );
+  my $end = DateTime->today;
+  my $start_weeks = $end->clone->subtract_duration( $duration_weeks );
+
+  my $dtf = $c->schema->storage->datetime_parser;
+  my $driver = $c->schema->storage->dbh->{Driver}->{Name};
+  my $week_transaction_rs = $c->schema->resultset('ViewQuantisedTransaction' . $driver)->search(
+    {
+      purchase_time => {
+        -between => [
+          $dtf->format_datetime($start_weeks),
+          $dtf->format_datetime($end),
+        ],
+      },
+      buyer_id => $entity->id,
+    },
+    {
+      columns => [
+        {
+          quantised        => 'quantised_weeks',
+          count            => \"COUNT(*)",
+        }
+      ],
+      group_by => 'quantised_weeks',
+      order_by => { '-asc' => 'quantised_weeks' },
+    }
+  );
+
+  my @all_weeks = $week_transaction_rs->all;
+  my $first = defined $all_weeks[0] ? $all_weeks[0]->get_column('count') || 0 : 0;
+  my $second = defined $all_weeks[1] ? $all_weeks[1]->get_column('count') || 0 : 0;
+  my $max = max( map { $_->get_column('count') } @all_weeks );
+  my $sum = sum( map { $_->get_column('count') } @all_weeks );
+  my $count = $week_transaction_rs->count;
+
+  my $weeks = {
+    first => $first,
+    second => $second,
+    max => $max,
+    sum => $sum,
+    count => $count,
+  };
+
+  my $data = {
+    cat_total => {},
+    categories => {},
+    essentials => {},
+    cat_list => {},
+    sector_monthly => {}
+  };
+
+  my $category_list = $c->schema->resultset('Category')->as_hash;
+
+  my $category_purchase_rs = $purchase_rs->search({},
+  {
+    join => 'category',
+    columns => {
+      category_id => "category.category_id",
+      value       => { sum => 'value' },
+    },
+    group_by => "category.category_id",
+  }
+  );
+
+  my %cat_total_list;
+
+  for ( $category_purchase_rs->all ) {
+    my $category = $_->get_column('category_id') || 0;
+    my $value = ($_->get_column('value') || 0) / 100000;
+
+    $cat_total_list{$category_list->{$category}} += $value;
+  }
+
+  my @cat_lists = map { { category => $_, value => $cat_total_list{$_},
+  icon => $c->schema->resultset('Category')->as_hash_name_icon->{$_} || 'question'} } sort keys %cat_total_list;
+  $data->{cat_list} = [ sort { $b->{value} <=> $a->{value} } @cat_lists ];
+
+  my $purchase_no_essential_rs = $purchase_rs->search({
+    "me.essential" => 1,
+  });
+
+  $data->{essentials} = {
+    purchase_no_total => $purchase_rs->count,
+    purchase_no_essential_total => $purchase_no_essential_rs->count,
+  };
+
+  my $duration_month = DateTime::Duration->new( days => 28 );
+  my $start_month = $end->clone->subtract_duration( $duration_month );
+  my $month_transaction_category_rs = $c->schema->resultset('ViewQuantisedTransactionCategory' . $driver)->search(
+    {
+      purchase_time => {
+        -between => [
+          $dtf->format_datetime($start_month),
+          $dtf->format_datetime($end),
+        ],
+      },
+      buyer_id => $entity->id,
+    },
+    {
+      columns => [
+        {
+          quantised        => 'quantised_weeks',
+          value            => { sum => 'value' },
+          category_id      => 'category_id',
+          essential        => 'essential',
+        },
+      ],
+      group_by => [ qw/ category_id quantised_weeks essential / ],
+    }
+  );
+
+  for my $cat_trans ( $month_transaction_category_rs->all ) {
+    my $quantised = $c->db_datetime_parser->parse_datetime($cat_trans->get_column('quantised'));
+    my $days = $c->format_iso_date( $quantised ) || 0;
+    my $category = $cat_trans->get_column('category_id') || 0;
+    my $value = ($cat_trans->get_column('value') || 0) / 100000;
+    $data->{cat_total}->{$category_list->{$category}} += $value;
+    $data->{categories}->{$days}->{$category_list->{$category}} += $value;
+    next unless $cat_trans->get_column('essential');
+    $data->{essentials}->{$days}->{value} += $value;
+  }
+
+  for my $day ( keys %{ $data->{categories} } ) {
+    my @days = ( map{ {
+      days => $day,
+      value => $data->{categories}->{$day}->{$_},
+      category => $_,
+    } } keys %{ $data->{categories}->{$day} } );
+    $data->{categories}->{$day} = [ sort { $b->{value} <=> $a->{value} } @days ];
+  }
+
+  # my $start_year_monthly = DateTime->now->truncate( to => 'year' );
+  # my $current_year_monthly = DateTime->now->add( months => 1, end_of_month => 'limit' );
+  # my $monthly_sector_transactions_rs = $c->schema->resultset('ViewQuantisedTransaction' . $driver)->search(
+  #   {
+  #     purchase_time => {
+  #       -between => [
+  #         $dtf->format_datetime($start_year_monthly),
+  #         $dtf->format_datetime($current_year_monthly),
+  #       ],
+  #     },
+  #     buyer_id => $entity->id,
+  #   },
+  #   {
+  #     columns => [
+  #       {
+  #         quantised        => 'quantised_months',
+  #         value            => { sum => 'value' },
+  #       },
+  #     ],
+  #     group_by => [ qw/ quantised_months / ],
+  #   }
+  # );
+  #
+  # for my $sector_transaction ( $monthly_sector_transactions_rs->all ) {
+  #   my $quantised = $c->db_datetime_parser->parse_datetime($cat_trans->get_column('quantised'));
+  #   my $months = $c->format_iso_date( $quantised ) || 0;
+  #   my $category = $cat_trans->get_column('category_id') || 0;
+  #   my $value = ($cat_trans->get_column('value') || 0) / 100000;
+  # }
+
+  return $c->render( json => {
+    success => Mojo::JSON->true,
+    data => $data,
     weeks => $weeks,
-    sectors => $sectors,
   });
 }
 

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

@@ -8,6 +8,21 @@ has error_messages => sub {
       required => { message => 'No email sent.', status => 400 },
       email => { message => 'Email is invalid.', status => 400 },
     },
+    value => {
+      required => { message => 'transaction amount is missing', status => 400 },
+      number => { message => 'transaction amount does not look like a number', status => 400 },
+      gt_num => { message => 'transaction amount cannot be equal to or less than zero', status => 400 },
+    },
+    apply_time => {
+      required => { message => 'purchase time is missing', status => 400 },
+      is_full_iso_datetime => { message => 'time is in incorrect format', status => 400 },
+    },
+    id => {
+      required => { message => 'Recurring Transaction not found', status => 400 },
+    },
+    category => {
+      in_resultset => { message => 'Category is invalid', status => 400 },
+    },
   };
 };
 
@@ -30,20 +45,135 @@ sub post_transaction_list_purchases {
     },
   );
 
-# purchase_time needs timezone attached to it
+  my $recurring_transactions = $c->schema->resultset('TransactionRecurring')->search({
+    buyer_id => $user->id,
+  });
+
+  # purchase_time needs timezone attached to it
   my @transaction_list = (
     map {{
       seller => $_->seller->name,
       value => $_->value / 100000,
       purchase_time => $c->format_iso_datetime($_->purchase_time),
+        ( $_->meta ? (
+          net_value => $_->meta->net_value / 100000,
+          sales_tax_value => $_->meta->sales_tax_value / 100000,
+          gross_value => $_->meta->gross_value / 100000,
+        ) : (
+          net_value => undef,
+          sales_tax_value => undef,
+          gross_value => undef,
+        )),
+
     }} $transactions->all
   );
 
+  my @recurring_transaction_list = (
+    map {{
+      id => $_->id,
+      seller => $_->seller->name,
+      value => $_->value / 100000,
+      start_time => $c->format_iso_datetime($_->start_time),
+      last_updated => $c->format_iso_datetime($_->last_updated) || undef,
+      essential => $_->essential,
+      category => $_->category_id || 0,
+      recurring_period => $_->recurring_period,
+    }} $recurring_transactions->all
+  );
+
   return $c->render( json => {
     success => Mojo::JSON->true,
     transactions => \@transaction_list,
+    recurring_transactions => \@recurring_transaction_list,
     page_no => $transactions->pager->total_entries,
   });
 }
 
+sub update_recurring {
+  my $c = shift;
+
+  my $user = $c->stash->{api_user};
+
+  my $validation = $c->validation;
+  $validation->input( $c->stash->{api_json} );
+  $validation->required('id');
+
+  return $c->api_validation_error if $validation->has_error;
+
+  my $id = $validation->param('id');
+
+  my $recur_transaction = $c->schema->resultset('TransactionRecurring')->find($id);
+  unless ( $recur_transaction ) {
+    return $c->render(
+      json => {
+        success => Mojo::JSON->false,
+        message => 'Error Finding Recurring Transaction',
+        error   => 'recurring_error',
+      },
+      status => 400,
+    );
+  }
+
+  $validation->required('recurring_period');
+  $validation->required('apply_time')->is_full_iso_datetime;
+  $validation->optional('category')->in_resultset( 'id', $c->schema->resultset('Category'));
+  $validation->optional('essential');
+  $validation->required('value');
+
+  return $c->api_validation_error if $validation->has_error;
+
+  my $apply_time = $c->parse_iso_datetime($validation->param('apply_time'));
+
+  $c->schema->storage->txn_do( sub {
+    $recur_transaction->update({
+      start_time       => $c->format_db_datetime($apply_time),
+      last_updated     => undef,
+      category_id      => $validation->param('category'),
+      essential        => $validation->param('essential'),
+      value            => $validation->param('value') * 100000,
+      recurring_period => $validation->param('recurring_period'),
+    });
+  });
+
+  return $c->render( json => {
+    success => Mojo::JSON->true,
+    message => 'Recurring Transaction Updated Successfully',
+  });
+
+}
+
+sub delete_recurring {
+  my $c = shift;
+
+  my $user = $c->stash->{api_user};
+
+  my $validation = $c->validation;
+  $validation->input( $c->stash->{api_json} );
+  $validation->required('id');
+
+  return $c->api_validation_error if $validation->has_error;
+
+  my $id = $validation->param('id');
+
+  my $recur_transaction = $c->schema->resultset('TransactionRecurring')->find($id);
+  unless ( $recur_transaction ) {
+    return $c->render(
+      json => {
+        success => Mojo::JSON->false,
+        message => 'Error Finding Recurring Transaction',
+        error   => 'recurring_error',
+      },
+      status => 400,
+    );
+  }
+
+  $recur_transaction->delete;
+
+  return $c->render( json => {
+    success => Mojo::JSON->true,
+    message => 'Recurring Transaction Deleted Successfully',
+  });
+
+}
+
 1;

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

@@ -108,6 +108,8 @@ sub post_upload {
   #Check a proper purchase time was submitted
   $validation->optional('purchase_time')->is_full_iso_datetime;
   $validation->optional('category')->in_resultset( 'id', $c->schema->resultset('Category'));
+  $validation->optional('essential');
+  $validation->optional('recurring');
 
   # First pass of required items
   return $c->api_validation_error if $validation->has_error;
@@ -121,7 +123,7 @@ sub post_upload {
     my $valid_org_rs = $c->schema->resultset('Organisation')->search({
       pending => 0,
       entity_id => { "!=" => $user->entity_id },
-     });
+    });
     $validation->required('organisation_id')->number->in_resultset( 'id', $valid_org_rs );
 
     return $c->api_validation_error if $validation->has_error;
@@ -145,7 +147,7 @@ sub post_upload {
     # Unknown Organisation
     $validation->required('organisation_name');
     $validation->optional('street_name');
-    $validation->required('town');
+    $validation->optional('town');
     $validation->optional('postcode')->postcode;
 
     return $c->api_validation_error if $validation->has_error;
@@ -184,6 +186,8 @@ sub post_upload {
   $purchase_time ||= DateTime->now();
   my $file = defined $upload ? $c->store_file_from_upload( $upload ) : undef;
   my $category = $validation->param('category');
+  my $essential = $validation->param('essential');
+  my $recurring_period = $validation->param('recurring');
   my $distance = $c->get_distance_from_coords( $user->entity->type_object, $organisation );
 
   my $new_transaction = $organisation->entity->create_related(
@@ -193,6 +197,7 @@ sub post_upload {
       value => $transaction_value * 100000,
       ( defined $file ? ( proof_image => $file ) : () ),
       purchase_time => $c->format_db_datetime($purchase_time),
+      essential => ( defined $essential ? $essential : 0 ),
       distance => $distance,
     }
   );
@@ -215,6 +220,19 @@ sub post_upload {
     });
   }
 
+  if ( defined $recurring_period ) {
+    $c->schema->resultset('TransactionRecurring')->create({
+      buyer => $user->entity,
+      seller => $organisation->entity,
+      value => $transaction_value * 100000,
+      start_time => $c->format_db_datetime($purchase_time),
+      essential => ( defined $essential ? $essential : 0 ),
+      distance => $distance,
+      category_id => ( defined $category ? $category : undef ),
+      recurring_period => $recurring_period,
+    });
+  }
+
   return $c->render( json => {
     success => Mojo::JSON->true,
     message => 'Upload Successful',
@@ -225,18 +243,12 @@ sub post_category {
   my $c = shift;
   my $self = $c;
 
-  my $categories = { ids => [], names => [] };
-
-  my $category_rs = $c->schema->resultset('Category');
-
-  for ( $category_rs->all ) {
-    push @{ $categories->{ ids } }, $_->get_column('id');
-    push @{ $categories->{ names } }, $_->get_column('name');
-  }
+  my $category_list = $c->schema->resultset('Category')->as_hash;
+  delete $category_list->{0};
 
   return $self->render( json => {
     success => Mojo::JSON->true,
-    categories => $categories,
+    categories => $category_list,
   });
 }
 
@@ -253,6 +265,7 @@ sub post_search {
   $validation->input( $c->stash->{api_json} );
 
   $validation->required('search_name');
+  $validation->optional('page')->number;
 
   return $c->api_validation_error if $validation->has_error;
 
@@ -264,6 +277,11 @@ sub post_search {
   my $valid_orgs_rs = $org_rs->search({
     pending => 0,
     entity_id => { "!=" => $user->entity_id },
+  },
+  {
+    page => $validation->param('page') || 1,
+    rows => 10,
+    order_by => { -desc => 'name' },
   })->search(
     \$search_stmt,
   );

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

@@ -7,6 +7,7 @@ sub index {
   my $entity = $c->stash->{api_user}->entity;
 
   my $purchase_rs = $entity->purchases;
+
   my $local_org_local_purchase = $purchase_rs->search({
     "me.distance" => { '<', 20000 },
     'organisation.is_local' => 1,
@@ -43,17 +44,17 @@ sub index {
   }
   );
 
-  my $data = {
+  my $local_all = {
     'Local shop local purchaser' => $local_org_local_purchase->count,
     'Local shop non-local purchaser' => $local_org_non_local_purchase->count,
     'Non-local shop local purchaser' => $non_local_org_local_purchase->count,
     'Non-local shop non-local purchaser' => $non_local_org_non_local_purchase->count,
   };
-  
+
   return $c->render(
     json => {
       success => Mojo::JSON->true,
-      pie => $data,
+      local_all => $local_all,
     }
   );
 

lib/Pear/LocalLoop/Controller/Api/V1/Organisation/Pies.pm

@@ -0,0 +1,63 @@
+package Pear::LocalLoop::Controller::Api::V1::Organisation::Pies;
+use Mojo::Base 'Mojolicious::Controller';
+
+sub index {
+  my $c = shift;
+
+  my $entity = $c->stash->{api_user}->entity;
+
+  my $purchase_rs = $entity->purchases;
+
+  my $local_org_local_purchase = $purchase_rs->search({
+    "me.distance" => { '<', 20000 },
+    'organisation.is_local' => 1,
+  },
+  {
+    join => { 'seller' => 'organisation' },
+  }
+  );
+
+  my $local_org_non_local_purchase = $purchase_rs->search({
+    "me.distance" => { '>=', 20000 },
+    'organisation.is_local' => 1,
+  },
+  {
+    join => { 'seller' => 'organisation' },
+  }
+  );
+
+  my $non_local_org_local_purchase = $purchase_rs->search({
+    "me.distance" => { '<', 20000 },
+    'organisation.is_local' => [0, undef],
+  },
+  {
+    join => { 'seller' => 'organisation' },
+  }
+  );
+
+  my $non_local_org_non_local_purchase = $purchase_rs->search({
+    "me.distance" => { '>=', 20000 },
+    'organisation.is_local' => [0, undef],
+  },
+  {
+    join => { 'seller' => 'organisation' },
+  }
+  );
+
+  my $local_all = {
+    'Local shop local purchaser' => $local_org_local_purchase->count,
+    'Local shop non-local purchaser' => $local_org_non_local_purchase->count,
+    'Non-local shop local purchaser' => $non_local_org_local_purchase->count,
+    'Non-local shop non-local purchaser' => $non_local_org_non_local_purchase->count,
+  };
+
+  return $c->render(
+    json => {
+      success => Mojo::JSON->true,
+      local_all => $local_all,
+    }
+  );
+
+}
+
+1;

lib/Pear/LocalLoop/Controller/Api/V1/Organisation/Snippets.pm

@@ -6,6 +6,10 @@ sub index {
 
   my $entity = $c->stash->{api_user}->entity;
   my $data = {
+    all_sales_count => 0,
+    all_sales_total => 0,
+    all_purchases_count => 0,
+    all_purchases_total => 0,
     this_month_sales_count => 0,
     this_month_sales_total => 0,
     this_month_purchases_count => 0,
@@ -25,6 +29,12 @@ sub index {
   my $week_ago = $today->clone->subtract( days => 7 );
   my $month_ago = $today->clone->subtract( days => 30 );
 
+  # TODO check that sales is doing the right thing here
+  my $all_sales = $entity->sales;
+  $data->{ all_sales_count } = $all_sales->count;
+  $data->{ all_sales_total } = $all_sales->get_column('value')->sum || 0;
+  $data->{ all_sales_total } /= 100000;
+
   my $today_sales = $entity->sales->search_between( $today, $now );
   $data->{ today_sales_count } = $today_sales->count;
   $data->{ today_sales_total } = $today_sales->get_column('value')->sum || 0;
@@ -40,6 +50,11 @@ sub index {
   $data->{ this_month_sales_total } = $month_sales->get_column('value')->sum || 0;
   $data->{ this_month_sales_total } /= 100000;
 
+  my $all_purchases = $entity->purchases;
+  $data->{ all_purchases_count } = $all_purchases->count;
+  $data->{ all_purchases_total } = $all_purchases->get_column('value')->sum || 0;
+  $data->{ all_purchases_total } /= 100000;
+
   my $today_purchases = $entity->purchases->search_between( $today, $now );
   $data->{ today_purchases_count } = $today_purchases->count;
   $data->{ today_purchases_total } = $today_purchases->get_column('value')->sum || 0;

lib/Pear/LocalLoop/Error.pm

@@ -0,0 +1,10 @@
+package Pear::LocalLoop::Error;
+use Moo;
+extends 'Throwable::Error';
+
+package Pear::LocalLoop::ImplementationError;
+use Moo;
+use namespace::clean;
+extends 'Pear::LocalLoop::Error';
+
+1;

lib/Pear/LocalLoop/Import/LCCCsv.pm

@@ -0,0 +1,23 @@
+package Pear::LocalLoop::Import::LCCCsv;
+use Moo;
+use Pear::LocalLoop::Error;
+
+has external_name => (
+  is      => 'ro',
+  default => 'LCC CSV',
+);
+
+has csv_required_columns => (
+  is      => 'lazy',
+  builder => sub {
+    Pear::LocalLoop::ImplementationError->throw("Must be implemented by child class");
+  },
+);
+
+with qw/
+  Pear::LocalLoop::Import::Role::ExternalName
+  Pear::LocalLoop::Import::Role::Schema
+  Pear::LocalLoop::Import::Role::CSV
+/;
+
+1;

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

@@ -0,0 +1,48 @@
+package Pear::LocalLoop::Import::LCCCsv::Suppliers;
+use Moo;
+
+extends qw/Pear::LocalLoop::Import::LCCCsv/;
+
+has '+csv_required_columns' => (
+  builder => sub { return [ qw/
+  supplier_id
+  name
+  / ]},
+);
+
+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 $addr2 = $row->{post_town};
+
+  my $address = ( defined $addr2 ? ( $row->{"address line 2"} . ' ' . $addr2) : $row->{"address line 2"} );
+
+  return if $self->external_result->organisations->find({external_id => $row->{supplier_id}});
+
+  $self->schema->resultset('Entity')->create({
+    type => 'organisation',
+    organisation => {
+      name => $row->{name},
+      street_name => $row->{"address line 1"},
+      town => $address,
+      postcode => $row->{post_code},
+      country => $row->{country_code},
+      external_reference => [ {
+        external_reference => $self->external_result,
+        external_id => $row->{supplier_id},
+      } ],
+    }
+  });
+}
+
+1;

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

@@ -0,0 +1,128 @@
+package Pear::LocalLoop::Import::LCCCsv::Transactions;
+use Moo;
+use DateTime;
+use DateTime::Format::Strptime;
+
+use Geo::UK::Postcode::Regex;
+
+extends qw/Pear::LocalLoop::Import::LCCCsv/;
+
+has target_entity_id => (
+  is       => 'ro',
+  required => 1,
+);
+
+has target_entity => (
+  is      => 'lazy',
+  builder => sub {
+    my $self = shift;
+    my $entity = $self->schema->resultset('Entity')->find($self->target_entity_id);
+    Pear::LocalLoop::Error->throw("Cannot find LCC Entity, did you pass the right id?") unless $entity;
+    return $entity;
+  },
+);
+
+has '+csv_required_columns' => (
+  builder => sub {return [ (
+    'transaction_id',
+    'supplier_id',
+    'net_amount',
+    'vat amount',
+    'gross_amount',
+  ) ]},
+);
+
+sub import_csv {
+  my ($self) = @_;
+
+  $self->check_headers;
+  my $lcc_org = $self->target_entity;
+
+  while ( my $row = $self->get_csv_line ) {
+    $self->_row_to_result($row, $lcc_org);
+  }
+}
+
+sub _row_to_result {
+  my ($self, $row, $lcc_org) = @_;
+
+  my $supplier_id = $row->{supplier_id};
+
+  my $organisation = $self->schema->resultset('Organisation')->find({
+    'external_reference.external_id' => $supplier_id
+  }, { join => 'external_reference' });
+
+  unless ($organisation) {
+    # 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(
+    pattern   => '%m/%d/%Y',
+    time_zone => 'Europe/London'
+  );
+  
+  my $paid_date = ( $row->{paid_date} ?
+    $date_formatter->parse_datetime($row->{paid_date}) :
+    $date_formatter->parse_datetime($row->{invoice_date}) );
+
+  my $gross_value = $row->{gross_amount};
+  $gross_value =~ s/,//g;
+  my $sales_tax_value = $row->{"vat amount"};
+  $sales_tax_value =~ s/,//g;
+  my $net_value = $row->{net_amount};
+  $net_value =~ s/,//g;
+
+  # TODO negative values are sometimes present
+  my $external_transaction = $self->external_result->update_or_create_related('transactions', { # This is a TransactionExternal result
+    external_id => $row->{transaction_id},
+  });
+
+  my $transaction_result = $external_transaction->update_or_create_related( 'transaction', {
+    seller        => $organisation->entity,
+    buyer         => $lcc_org,
+    purchase_time => $paid_date,
+    value         => $gross_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"}) : ()),
+  });
+}
+
+1;

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

@@ -0,0 +1,88 @@
+package Pear::LocalLoop::Import::Role::CSV;
+use strict;
+use warnings;
+use Moo::Role;
+use Text::CSV;
+use Try::Tiny;
+use Pear::LocalLoop::Error;
+
+requires 'csv_required_columns';
+
+has csv_file => (
+  is       => 'ro',
+  predicate => 1,
+);
+
+has csv_string => (
+  is => 'ro',
+  predicate => 1,
+);
+
+has csv_error => (
+  is => 'ro',
+  predicate => 1,
+);
+
+has _csv_filehandle => (
+  is      => 'lazy',
+  builder => sub {
+    my $self = shift;
+    my $fh;
+    if ( $self->has_csv_file ) {
+      open $fh, '<', $self->csv_file;
+    } elsif ( $self->has_csv_string ) {
+      my $string = $self->csv_string;
+      open $fh, '<', \$string;
+    } else {
+      die "Must provide csv_file or csv_string"
+    }
+    return $fh;
+  }
+);
+
+has text_csv_options => (
+  is      => 'lazy',
+  builder => sub {
+    return {
+      binary           => 1,
+      allow_whitespace => 1,
+    };
+  }
+);
+
+has _text_csv => (
+  is      => 'lazy',
+  builder => sub {
+    return Text::CSV->new(shift->text_csv_options);
+  }
+);
+
+has csv_data => (
+  is      => 'lazy',
+  builder => sub {
+    my $self = shift;
+    my $header_check = $self->check_headers;
+    return 0 unless $header_check;
+    return $self->_text_csv->getline_hr_all( $self->_csv_filehandle );
+  }
+);
+
+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;
+  my @headers;
+  @headers = $self->_text_csv->header( $self->_csv_filehandle );
+  my %header_map = ( map { $_ => 1 } @headers );
+  for my $req_header ( @$req_headers ) {
+    next if $header_map{$req_header};
+    die "Require header [" . $req_header . "]";
+  }
+  return 1;
+}
+
+1;

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

@@ -0,0 +1,19 @@
+package Pear::LocalLoop::Import::Role::ExternalName;
+use strict;
+use warnings;
+use Moo::Role;
+
+requires qw/
+  external_name
+  schema
+/;
+
+has external_result => (
+  is      => 'lazy',
+  builder => sub {
+    my $self = shift;
+    return $self->schema->resultset('ExternalReference')->find_or_create({ name => $self->external_name });
+  }
+);
+
+1;

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

@@ -0,0 +1,11 @@
+package Pear::LocalLoop::Import::Role::Schema;
+use strict;
+use warnings;
+use Moo::Role;
+
+has schema => (
+  is       => 'ro',
+  required => 1,
+);
+
+1;

lib/Pear/LocalLoop/Plugin/Currency.pm

@@ -9,6 +9,8 @@ sub register {
     my $value;
     if ( $currency_string =~ /^£([\d.]+)/ ) {
       $value = $1 * 1;
+    } elsif ( $currency_string =~ /^([\d.]+)/ ) {
+      $value = $1 * 1;
     }
     return $value;
   });

lib/Pear/LocalLoop/Plugin/Datetime.pm

@@ -66,6 +66,7 @@ sub register {
 
   $app->helper( format_iso_datetime => sub {
     my ( $c, $datetime_obj ) = @_;
+    return unless defined $datetime_obj;
     return $c->iso_datetime_parser->format_datetime(
       $datetime_obj,
     );

lib/Pear/LocalLoop/Plugin/Minion.pm

@@ -0,0 +1,40 @@
+package Pear::LocalLoop::Plugin::Minion;
+use Mojo::Base 'Mojolicious::Plugin';
+
+use Mojo::Loader qw/ find_modules load_class /;
+
+sub register {
+  my ( $plugin, $app, $cong ) = @_;
+
+  if ( defined $app->config->{minion} ) {
+    $app->log->debug('Setting up Minion tasks');
+    $app->plugin('Minion' => $app->config->{minion} );
+
+    $app->log->debug('Loaded Minion Job packages:');
+
+    my $job_namespace = __PACKAGE__ . '::Job';
+    my @modules = find_modules $job_namespace;
+    for my $package ( @modules ) {
+      my ( $job_name ) = $package =~ /${job_namespace}::(.*)$/;
+      $app->log->debug( $package );
+      if (my $e = load_class $package) {
+        die ref $e ? "Exception: $e" : "$package not found";
+      }
+      $app->minion->add_task(
+        $job_name => sub {
+          my ( $job, @args ) = @_;
+          my $job_runner = $package->new(
+            job => $job,
+          );
+          $job_runner->run( @args );
+        }
+      );
+    }
+    #  $app->minion->enqueue('test' => [ 'test arg 1', 'test_arg 2' ] );
+  } else {
+    $app->log->debug('No Minion Config');
+  }
+
+}
+
+1;

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

@@ -0,0 +1,12 @@
+package Pear::LocalLoop::Plugin::Minion::Job;
+use Mojo::Base -base;
+
+has [ qw/ job / ];
+
+has app => sub { shift->job->app };
+
+sub run {
+  die ( __PACKAGE__ . " must implement run sub" );
+}
+
+1;

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/Plugin/Minion/Job/csv_supplier_import.pm

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

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

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

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

@@ -0,0 +1,30 @@
+package Pear::LocalLoop::Plugin::Minion::Job::entity_postcode_lookup;
+use Mojo::Base 'Pear::LocalLoop::Plugin::Minion::Job';
+
+sub run {
+  my ( $self, $entity_id ) = @_;
+
+  my $entity_rs = $self->app->schema->resultset('Entity');
+  $entity_rs = $entity_rs->search({id => $entity_id }) if $entity_id;
+
+  while ( my $entity = $entity_rs->next ) {
+    my $obj = $entity->type_object;
+    next unless $obj;
+
+    my $postcode_obj = Geo::UK::Postcode::Regex->parse( $obj->postcode );
+
+    unless ( defined $postcode_obj && $postcode_obj->{non_geographical} ) {
+      my $pc_result = $self->app->schema->resultset('GbPostcode')->find({
+        incode => $postcode_obj->{incode},
+        outcode => $postcode_obj->{outcode},
+      });
+      if ( defined $pc_result ) {
+        $entity->update_or_create_related('postcode', {
+          gb_postcode => $pc_result,
+        });
+      }
+    }
+  }
+}
+
+1;

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

@@ -0,0 +1,12 @@
+package Pear::LocalLoop::Plugin::Minion::Job::leaderboards_recalc;
+use Mojo::Base 'Pear::LocalLoop::Plugin::Minion::Job';
+
+sub run {
+  my ( $self, @args ) = @_;
+
+  my $leaderboard_rs = $self->app->schema->resultset('Leaderboard');
+
+  $leaderboard_rs->recalculate_all;
+}
+
+1;

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

@@ -0,0 +1,13 @@
+package Pear::LocalLoop::Plugin::Minion::Job::test;
+use Mojo::Base 'Pear::LocalLoop::Plugin::Minion::Job';
+
+sub run {
+  my ( $self, @args ) = @_;
+
+  $self->job->app->log->debug( 'Testing Job' );
+  for my $arg ( @args ) {
+    $self->job->app->log->debug( $arg );
+  }
+}
+
+1;

lib/Pear/LocalLoop/Schema.pm

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

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

@@ -18,6 +18,12 @@ __PACKAGE__->add_columns(
     size => 255,
     is_nullable => 0,
   },
+  # See here for all possible options http://simplelineicons.com/
+  "line_icon" => {
+    data_type => "varchar",
+    size => 255,
+    is_nullable => 1,
+  },
 );
 
 __PACKAGE__->set_primary_key("id");

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

@@ -42,6 +42,11 @@ __PACKAGE__->might_have(
   "Pear::LocalLoop::Schema::Result::EntityAssociation" => "entity_id",
 );
 
+__PACKAGE__->might_have(
+  "postcode",
+  "Pear::LocalLoop::Schema::Result::EntityPostcode" => "entity_id",
+);
+
 __PACKAGE__->has_many(
   "purchases",
   "Pear::LocalLoop::Schema::Result::Transaction",

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/EntityPostcode.pm

@@ -0,0 +1,44 @@
+package Pear::LocalLoop::Schema::Result::EntityPostcode;
+
+use strict;
+use warnings;
+
+use base 'DBIx::Class::Core';
+
+__PACKAGE__->table('entities_postcodes');
+
+__PACKAGE__->add_columns(
+  outcode => {
+    data_type => 'char',
+    size => 4,
+    is_nullable => 0,
+  },
+  incode => {
+    data_type => 'char',
+    size => 3,
+    is_nullable => 0,
+  },
+  entity_id       => {
+    data_type      => 'integer',
+    is_nullable    => 0,
+    is_foreign_key => 1,
+  },
+);
+
+__PACKAGE__->set_primary_key(qw/ outcode incode entity_id /);
+
+__PACKAGE__->belongs_to(
+  "entity",
+  "Pear::LocalLoop::Schema::Result::Entity",
+  "entity_id",
+);
+
+__PACKAGE__->belongs_to(
+  "gb_postcode",
+  "Pear::LocalLoop::Schema::Result::GbPostcode",
+  {
+    "foreign.outcode" => "self.outcode",
+    "foreign.incode" => "self.incode",
+  },
+);
+1;

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

@@ -0,0 +1,39 @@
+package Pear::LocalLoop::Schema::Result::ExternalReference;
+
+use strict;
+use warnings;
+
+use base 'DBIx::Class::Core';
+
+__PACKAGE__->table("external_references");
+
+__PACKAGE__->add_columns(
+  "id"   => {
+    data_type         => "integer",
+    is_auto_increment => 1,
+    is_nullable       => 0,
+  },
+  "name" => {
+    data_type   => "varchar",
+    size        => 255,
+    is_nullable => 0,
+  },
+);
+
+__PACKAGE__->set_primary_key("id");
+
+__PACKAGE__->add_unique_constraint([ qw/name/ ]);
+
+__PACKAGE__->has_many(
+  'transactions',
+  "Pear::LocalLoop::Schema::Result::TransactionExternal",
+  { 'foreign.external_reference_id' => 'self.id' },
+);
+
+__PACKAGE__->has_many(
+  'organisations',
+  "Pear::LocalLoop::Schema::Result::OrganisationExternal",
+  { 'foreign.external_reference_id' => 'self.id' },
+);
+
+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/GlobalMedals.pm

@@ -14,8 +14,7 @@ __PACKAGE__->add_columns(
     is_nullable => 0,
   },
   "group_id" => {
-    data_type => "varchar",
-    size => 255,
+    data_type => "integer",
     is_nullable => 0,
   },
   "threshold" => {

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

@@ -14,13 +14,11 @@ __PACKAGE__->add_columns(
     is_nullable => 0,
   },
   "entity_id" => {
-    data_type => "varchar",
-    size => 255,
+    data_type => "integer",
     is_nullable => 0,
   },
   "group_id" => {
-    data_type => "varchar",
-    size => 255,
+    data_type => "integer",
     is_nullable => 0,
   },
   "total" => {

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

@@ -19,13 +19,11 @@ __PACKAGE__->add_columns(
     is_nullable => 0,
   },
   "entity_id" => {
-    data_type => "varchar",
-    size => 255,
+    data_type => "integer",
     is_nullable => 0,
   },
   "group_id" => {
-    data_type => "varchar",
-    size => 255,
+    data_type => "integer",
     is_nullable => 0,
   },
   "points" => {

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

@@ -14,8 +14,7 @@ __PACKAGE__->add_columns(
     is_nullable => 0,
   },
   "group_id" => {
-    data_type => "varchar",
-    size => 255,
+    data_type => "integer",
     is_nullable => 0,
   },
   "threshold" => {

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

@@ -14,13 +14,11 @@ __PACKAGE__->add_columns(
     is_nullable => 0,
   },
   "entity_id" => {
-    data_type => "varchar",
-    size => 255,
+    data_type => "integer",
     is_nullable => 0,
   },
   "group_id" => {
-    data_type => "varchar",
-    size => 255,
+    data_type => "integer",
     is_nullable => 0,
   },
   "total" => {

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

@@ -19,13 +19,11 @@ __PACKAGE__->add_columns(
     is_nullable => 0,
   },
   "entity_id" => {
-    data_type => "varchar",
-    size => 255,
+    data_type => "integer",
     is_nullable => 0,
   },
   "group_id" => {
-    data_type => "varchar",
-    size => 255,
+    data_type => "integer",
     is_nullable => 0,
   },
   "points" => {

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

@@ -10,76 +10,92 @@ __PACKAGE__->load_components("InflateColumn::DateTime", "FilterColumn");
 __PACKAGE__->table("organisations");
 
 __PACKAGE__->add_columns(
-  id => {
-    data_type => 'integer',
+  id              => {
+    data_type         => 'integer',
     is_auto_increment => 1,
-    is_nullable => 0,
+    is_nullable       => 0,
   },
-  entity_id => {
-    data_type => 'integer',
-    is_nullable => 0,
+  entity_id       => {
+    data_type      => 'integer',
+    is_nullable    => 0,
     is_foreign_key => 1,
   },
-  name => {
-    data_type => 'varchar',
-    size => 255,
+  name            => {
+    data_type   => 'varchar',
+    size        => 255,
     is_nullable => 0,
   },
-  street_name => {
-    data_type => 'text',
+  street_name     => {
+    data_type   => 'text',
     is_nullable => 1,
   },
-  town => {
-    data_type => 'varchar',
-    size => 255,
+  town            => {
+    data_type   => 'varchar',
+    size        => 255,
     is_nullable => 0,
   },
-  postcode => {
-    data_type => 'varchar',
-    size => 16,
+  postcode        => {
+    data_type   => 'varchar',
+    size        => 16,
     is_nullable => 1,
   },
-  country => {
-    data_type => 'varchar',
-    size => 255,
+  country         => {
+    data_type   => 'varchar',
+    size        => 255,
     is_nullable => 1,
   },
-  sector => {
-    data_type => 'varchar',
-    size => 1,
+  # Stores codes based on https://www.ons.gov.uk/methodology/classificationsandstandards/ukstandardindustrialclassificationofeconomicactivities/uksic2007
+  sector          => {
+    data_type   => 'varchar',
+    size        => 1,
     is_nullable => 1,
   },
-  pending => {
-    data_type => 'boolean',
-    default => \"false",
+  pending         => {
+    data_type   => 'boolean',
+    default_value => \"false",
     is_nullable => 0,
   },
-  is_local => {
-    data_type => 'boolean',
-    default => undef,
+  is_local        => {
+    data_type   => 'boolean',
+    default_value => undef,
     is_nullable => 1,
   },
-  is_fair => {
-    data_type => 'boolean',
-    default => undef,
+  is_fair         => {
+    data_type   => 'boolean',
+    default_value => undef,
     is_nullable => 1,
   },
   submitted_by_id => {
-    data_type => 'integer',
+    data_type   => 'integer',
     is_nullable => 1,
   },
-  latitude => {
-    data_type => 'decimal',
-    size => [8,5],
-    is_nullable => 1,
+  latitude        => {
+    data_type     => 'decimal',
+    size          => [ 8, 5 ],
+    is_nullable   => 1,
     default_value => undef,
   },
-  longitude => {
-    data_type => 'decimal',
-    size => [8,5],
-    is_nullable => 1,
+  longitude       => {
+    data_type     => 'decimal',
+    size          => [ 8, 5 ],
+    is_nullable   => 1,
     default_value => undef,
   },
+  type_id         => {
+    data_type      => 'integer',
+    is_nullable    => 1,
+    is_foreign_key => 1,
+  },
+  social_type_id  => {
+    data_type      => 'integer',
+    is_nullable    => 1,
+    is_foreign_key => 1,
+  },
+  is_anchor       => {
+    data_type     => 'boolean',
+    is_nullable   => 0,
+    default_value => \'FALSE',
+  }
 );
 
 __PACKAGE__->set_primary_key('id');
@@ -90,6 +106,24 @@ __PACKAGE__->belongs_to(
   "entity_id",
 );
 
+__PACKAGE__->belongs_to(
+  "organisation_type",
+  "Pear::LocalLoop::Schema::Result::OrganisationType",
+  "type_id",
+);
+
+__PACKAGE__->belongs_to(
+  "social_type",
+  "Pear::LocalLoop::Schema::Result::OrganisationSocialType",
+  "social_type_id",
+);
+
+__PACKAGE__->has_many(
+  "external_reference",
+  "Pear::LocalLoop::Schema::Result::OrganisationExternal",
+  { 'foreign.org_id' => 'self.id' },
+);
+
 __PACKAGE__->has_many(
   "payroll",
   "Pear::LocalLoop::Schema::Result::OrganisationPayroll",
@@ -98,32 +132,37 @@ __PACKAGE__->has_many(
 );
 
 __PACKAGE__->filter_column(
-  pending => {
+  pending   => {
     filter_to_storage => 'to_bool',
   },
-  is_local => {
+  is_local  => {
+    filter_to_storage => 'to_bool',
+  },
+  is_anchor => {
     filter_to_storage => 'to_bool',
   }
 );
 
 # Only works when calling ->deploy, but atleast helps for tests
 sub sqlt_deploy_hook {
-  my ( $source_instance, $sqlt_table ) = @_;
+  my ($source_instance, $sqlt_table) = @_;
   my $pending_field = $sqlt_table->get_field('pending');
-  if ( $sqlt_table->schema->translator->producer_type =~ /SQLite$/ ) {
+  if ($sqlt_table->schema->translator->producer_type =~ /SQLite$/) {
     $pending_field->{default_value} = 0;
-  } else {
+  }
+  else {
     $pending_field->{default_value} = \"false";
   }
 }
 
 sub to_bool {
-  my ( $self, $val ) = @_;
-  return if ! defined $val;
+  my ($self, $val) = @_;
+  return if !defined $val;
   my $driver_name = $self->result_source->schema->storage->dbh->{Driver}->{Name};
-  if ( $driver_name eq 'SQLite' ) {
+  if ($driver_name eq 'SQLite') {
     return $val ? 1 : 0;
-  } else {
+  }
+  else {
     return $val ? 'true' : 'false';
   }
 }

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

@@ -0,0 +1,49 @@
+package Pear::LocalLoop::Schema::Result::OrganisationExternal;
+
+use strict;
+use warnings;
+
+use base 'DBIx::Class::Core';
+
+__PACKAGE__->table("organisations_external");
+
+__PACKAGE__->add_columns(
+  "id"                    => {
+    data_type         => "integer",
+    is_auto_increment => 1,
+    is_nullable       => 0,
+  },
+  "org_id"                => {
+    data_type      => "integer",
+    is_foreign_key => 1,
+    is_nullable    => 0,
+  },
+  "external_reference_id" => {
+    data_type      => "integer",
+    is_foreign_key => 1,
+    is_nullable    => 0,
+  },
+  "external_id"           => {
+    data_type   => "varchar",
+    size        => 255,
+    is_nullable => 0,
+  }
+);
+
+__PACKAGE__->set_primary_key("id");
+
+__PACKAGE__->add_unique_constraint([ qw/external_reference_id external_id/ ]);
+
+__PACKAGE__->belongs_to(
+  "organisation",
+  "Pear::LocalLoop::Schema::Result::Organisation",
+  { 'foreign.id' => 'self.org_id' },
+);
+
+__PACKAGE__->belongs_to(
+  "external_reference",
+  "Pear::LocalLoop::Schema::Result::ExternalReference",
+  { 'foreign.id' => 'self.external_reference_id' },
+);
+
+1;

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

@@ -0,0 +1,39 @@
+package Pear::LocalLoop::Schema::Result::OrganisationSocialType;
+
+use strict;
+use warnings;
+
+use base 'DBIx::Class::Core';
+
+__PACKAGE__->table("organisation_social_types");
+
+__PACKAGE__->add_columns(
+  "id"   => {
+    data_type         => "integer",
+    is_auto_increment => 1,
+    is_nullable       => 0,
+  },
+  "key"  => {
+    data_type   => "varchar",
+    size        => 255,
+    is_nullable => 0,
+  },
+  "name" => {
+    data_type   => "varchar",
+    size        => 255,
+    is_nullable => 0,
+  }
+);
+
+__PACKAGE__->set_primary_key("id");
+
+__PACKAGE__->add_unique_constraint([ qw/key/ ]);
+
+__PACKAGE__->has_many(
+  "organisations",
+  "Pear::LocalLoop::Schema::Result::Organisation",
+  { 'foreign.social_type_id' => 'self.id' },
+);
+
+1;
+1;

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

@@ -0,0 +1,38 @@
+package Pear::LocalLoop::Schema::Result::OrganisationType;
+
+use strict;
+use warnings;
+
+use base 'DBIx::Class::Core';
+
+__PACKAGE__->table("organisation_types");
+
+__PACKAGE__->add_columns(
+  "id"   => {
+    data_type         => "integer",
+    is_auto_increment => 1,
+    is_nullable       => 0,
+  },
+  "key"  => {
+    data_type   => "varchar",
+    size        => 255,
+    is_nullable => 0,
+  },
+  "name" => {
+    data_type   => "varchar",
+    size        => 255,
+    is_nullable => 0,
+  }
+);
+
+__PACKAGE__->set_primary_key("id");
+
+__PACKAGE__->add_unique_constraint([ qw/key/ ]);
+
+__PACKAGE__->has_many(
+  "organisations",
+  "Pear::LocalLoop::Schema::Result::Organisation",
+  { 'foreign.type_id' => 'self.id' },
+);
+
+1;

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

@@ -48,6 +48,11 @@ __PACKAGE__->add_columns(
     is_nullable => 0,
     set_on_create => 1,
   },
+  "essential" => {
+    data_type => "boolean",
+    default_value => \"false",
+    is_nullable => 0,
+  },
   distance => {
     data_type => 'numeric',
     size => [15],
@@ -76,4 +81,26 @@ __PACKAGE__->might_have(
   "Pear::LocalLoop::Schema::Result::TransactionCategory" => "transaction_id",
 );
 
+__PACKAGE__->has_one(
+    "meta",
+    "Pear::LocalLoop::Schema::Result::TransactionMeta",
+    { 'foreign.transaction_id' => 'self.id' },
+);
+
+__PACKAGE__->has_many(
+    "external_reference",
+    "Pear::LocalLoop::Schema::Result::TransactionExternal",
+    { 'foreign.transaction_id' => 'self.id' },
+);
+
+sub sqlt_deploy_hook {
+  my ( $source_instance, $sqlt_table ) = @_;
+  my $pending_field = $sqlt_table->get_field('essential');
+  if ( $sqlt_table->schema->translator->producer_type =~ /SQLite$/ ) {
+    $pending_field->{default_value} = 0;
+  } else {
+    $pending_field->{default_value} = \"false";
+  }
+}
+
 1;

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

@@ -0,0 +1,49 @@
+package Pear::LocalLoop::Schema::Result::TransactionExternal;
+
+use strict;
+use warnings;
+
+use base 'DBIx::Class::Core';
+
+__PACKAGE__->table("transactions_external");
+
+__PACKAGE__->add_columns(
+  "id" => {
+    data_type         => "integer",
+    is_auto_increment => 1,
+    is_nullable       => 0,
+  },
+  "transaction_id"        => {
+    data_type      => "integer",
+    is_foreign_key => 1,
+    is_nullable    => 0,
+  },
+  "external_reference_id" => {
+    data_type      => "integer",
+    is_foreign_key => 1,
+    is_nullable    => 0,
+  },
+  "external_id" => {
+    data_type   => "varchar",
+    size        => 255,
+    is_nullable => 0,
+  }
+);
+
+__PACKAGE__->set_primary_key("id");
+
+__PACKAGE__->add_unique_constraint([ qw/external_reference_id external_id/ ]);
+
+__PACKAGE__->belongs_to(
+  "transaction",
+  "Pear::LocalLoop::Schema::Result::Transaction",
+  { 'foreign.id' => 'self.transaction_id' },
+);
+
+__PACKAGE__->belongs_to(
+  "external_reference",
+  "Pear::LocalLoop::Schema::Result::ExternalReference",
+  { 'foreign.id' => 'self.external_reference_id' },
+);
+
+1;

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

@@ -0,0 +1,81 @@
+package Pear::LocalLoop::Schema::Result::TransactionMeta;
+
+use strict;
+use warnings;
+
+use base 'DBIx::Class::Core';
+
+__PACKAGE__->table("transactions_meta");
+
+__PACKAGE__->add_columns(
+    "id"              => {
+        data_type         => "integer",
+        is_auto_increment => 1,
+        is_nullable       => 0,
+    },
+    "transaction_id"  => {
+        data_type      => "integer",
+        is_foreign_key => 1,
+        is_nullable    => 0,
+    },
+    "net_value"       => {
+        data_type   => "numeric",
+        size        => [ 100, 0 ],
+        is_nullable => 0,
+    },
+    "sales_tax_value" => {
+        data_type   => "numeric",
+        size        => [ 100, 0 ],
+        is_nullable => 0,
+    },
+    "gross_value"     => {
+        data_type   => "numeric",
+        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");
+
+__PACKAGE__->belongs_to(
+    "transaction",
+    "Pear::LocalLoop::Schema::Result::Transaction",
+    { 'foreign.id' => 'self.transaction_id' },
+);
+
+1;

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

@@ -0,0 +1,92 @@
+package Pear::LocalLoop::Schema::Result::TransactionRecurring;
+
+use strict;
+use warnings;
+
+use base 'DBIx::Class::Core';
+
+__PACKAGE__->load_components(qw/
+  InflateColumn::DateTime
+  TimeStamp
+/);
+
+__PACKAGE__->table("transaction_recurring");
+
+__PACKAGE__->add_columns(
+  "id" => {
+    data_type => "integer",
+    is_auto_increment => 1,
+    is_nullable => 0,
+  },
+  "buyer_id" => {
+    data_type => "integer",
+    is_foreign_key => 1,
+    is_nullable => 0,
+  },
+  "seller_id" => {
+    data_type => "integer",
+    is_foreign_key => 1,
+    is_nullable => 0,
+  },
+  "value" => {
+    data_type => "numeric",
+    size => [ 100, 0 ],
+    is_nullable => 0,
+  },
+  "start_time" => {
+    data_type => "datetime",
+    timezone => "UTC",
+    is_nullable => 0,
+  },
+  "last_updated" => {
+    data_type => "datetime",
+    timezone => "UTC",
+    is_nullable => 1,
+    datetime_undef_if_invalid => 1,
+  },
+  "essential" => {
+    data_type => "boolean",
+    default_value => \"false",
+    is_nullable => 0,
+  },
+  "distance" => {
+    data_type => 'numeric',
+    size => [15],
+    is_nullable => 1,
+  },
+  "category_id" => {
+    data_type => "integer",
+    is_nullable => 1,
+    is_foreign_key => 1,
+  },
+  "recurring_period" => {
+    data_type => "varchar",
+    size => 255,
+    is_nullable => 0,
+  },
+);
+
+__PACKAGE__->set_primary_key("id");
+
+__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" },
+);
+
+__PACKAGE__->belongs_to(
+  "category",
+  "Pear::LocalLoop::Schema::Result::Category",
+  "category_id",
+  { cascade_delete => 0 },
+);
+
+1;

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

@@ -10,17 +10,18 @@ __PACKAGE__->table('view_quantised_transactions');
 __PACKAGE__->result_source_instance->is_virtual(1);
 
 __PACKAGE__->result_source_instance->view_definition( qq/
-SELECT "transactions.value",
-       "transactions.distance",
-       "transactions.purchase_time",
-       "transactions.buyer_id",
-       "transactions.seller_id",
-       "transaction_category.category_id",
-       DATE_TRUNC('hour', "transactions.purchase_time") AS "quantised_hours",
-       DATE_TRUNC('day', "transactions.purchase_time") AS "quantised_days",
-       DATE_TRUNC('week', "transactions.purchase_time") AS "quantised_weeks"
+SELECT "transactions"."value",
+       "transactions"."distance",
+       "transactions"."purchase_time",
+       "transactions"."buyer_id",
+       "transactions"."seller_id",
+       "transactions"."essential",
+       "transaction_category"."category_id",
+       DATE_TRUNC('hour', "transactions"."purchase_time") AS "quantised_hours",
+       DATE_TRUNC('day', "transactions"."purchase_time") AS "quantised_days",
+       DATE_TRUNC('week', "transactions"."purchase_time") AS "quantised_weeks"
   FROM "transactions"
-LEFT JOIN "transaction_category" ON "transactions.id" = "transaction_category.transaction_id"
+LEFT JOIN "transaction_category" ON "transactions"."id" = "transaction_category"."transaction_id"
 /);
 
 1;

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

@@ -15,10 +15,11 @@ SELECT "transactions"."value",
        "transactions"."purchase_time",
        "transactions"."buyer_id",
        "transactions"."seller_id",
+       "transactions"."essential",
        "transaction_category"."category_id",
        DATETIME(STRFTIME('%Y-%m-%d %H:00:00',"transactions"."purchase_time")) AS "quantised_hours",
        DATETIME(STRFTIME('%Y-%m-%d 00:00:00',"transactions"."purchase_time")) AS "quantised_days",
-       DATETIME(STRFTIME('%Y-%m-%d 00:00:00',"transactions"."purchase_time", 'weekday 1')) AS "quantised_weeks"
+       DATETIME(STRFTIME('%Y-%m-%d 00:00:00',"transactions"."purchase_time", 'weekday 0','-6 days')) AS "quantised_weeks"
   FROM "transactions"
 LEFT JOIN "transaction_category" ON "transactions"."id" = "transaction_category"."transaction_id"
 /);

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

@@ -17,8 +17,23 @@ SELECT "value",
        "seller_id",
        DATE_TRUNC('hour', "purchase_time") AS "quantised_hours",
        DATE_TRUNC('day', "purchase_time") AS "quantised_days",
-       DATE_TRUNC('week', "purchase_time") AS "quantised_weeks"
+       DATE_TRUNC('week', "purchase_time") AS "quantised_weeks",
+       DATE_TRUNC('month', "purchase_time") AS "quantised_months"
   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

@@ -15,10 +15,26 @@ SELECT "value",
        "purchase_time",
        "buyer_id",
        "seller_id",
+       "sector",
        DATETIME(STRFTIME('%Y-%m-%d %H:00:00',"purchase_time")) AS "quantised_hours",
        DATETIME(STRFTIME('%Y-%m-%d 00:00:00',"purchase_time")) AS "quantised_days",
-       DATETIME(STRFTIME('%Y-%m-%d 00:00:00',"purchase_time", 'weekday 1')) AS "quantised_weeks"
+       DATETIME(STRFTIME('%Y-%m-%d 00:00:00',"purchase_time",'weekday 0','-6 days')) AS "quantised_weeks",
+       DATETIME(STRFTIME('%Y-%m-00 00:00:00',"purchase_time")) AS "quantised_months"
   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/ResultSet/Category.pm

@@ -0,0 +1,36 @@
+package Pear::LocalLoop::Schema::ResultSet::Category;
+
+use strict;
+use warnings;
+
+use base 'DBIx::Class::ResultSet';
+
+sub as_hash {
+  my ( $self ) = @_;
+
+  my %category_list = (
+    (
+      map {
+        $_->id => $_->name,
+      } $self->all
+    ),
+    0 => 'Uncategorised',
+  );
+  return \%category_list;
+}
+
+sub as_hash_name_icon {
+  my ( $self ) = @_;
+
+  my %category_list = (
+    (
+      map {
+        $_->name => $_->line_icon,
+      } $self->all
+    ),
+    0 => 'Uncategorised',
+  );
+  return \%category_list;
+}
+
+1;

lib/Test/Pear/LocalLoop.pm

@@ -114,6 +114,8 @@ sub dump_error {
     my $self = shift;
     if ( my $error = $self->tx->res->dom->at('pre[id="error"]') ) {
       diag $error->text;
+    } elsif ( my $route_error = $self->tx->res->dom->at('div[id="routes"] > p') ) {
+      diag $route_error->content;
     } else {
       diag $self->tx->res->to_string;
     }

pear-local_loop.conf

@@ -3,4 +3,7 @@
   user => undef,
   pass => undef,
   key => "a",
-}; 
+  minion => {
+    SQLite => 'sqlite:minion.db',
+  },
+};

pear-local_loop.development.conf

@@ -4,4 +4,7 @@
   user => undef,
   pass => undef,
   key => "a",
+  minion => {
+    SQLite => 'sqlite:minion.db',
+  },
 }; 

script/cron_daily

@@ -0,0 +1,10 @@
+#! /bin/bash
+
+# Scripts to run daily.
+# This will be run sometime between 2 & 3AM every morning.
+# If order matters, make sure they are in the right place.
+
+eval $(perl -I ~/perl5/lib/perl5/ -Mlocal::lib)
+
+MOJO_MODE=production ./script/pear-local_loop recur_transactions --force
+MOJO_MODE=production ./script/pear-local_loop recalc_leaderboards

script/deploy_db

@@ -5,7 +5,10 @@ use warnings;
 
 use FindBin qw/ $Bin /;
 use lib "$Bin/../lib";
+use lib "$Bin/..";
+use Devel::Dwarn;
 
+Dwarn $Bin;
 use Pear::LocalLoop::Schema::Script::DeploymentHandler;
 
 Pear::LocalLoop::Schema::Script::DeploymentHandler->new_with_actions(schema_class => 'Pear::LocalLoop::Schema');

script/recalc_leaderboards

@@ -1,5 +0,0 @@
-#! /bin/bash
-
-eval $(perl -I ~/perl5/lib/perl5/ -Mlocal::lib)
-
-MOJO_MODE=production ./script/pear-local_loop recalc_leaderboards

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

@@ -1,6 +1,6 @@
 -- 
 -- Created by SQL::Translator::Producer::PostgreSQL
--- Created on Wed Feb 21 12:51:23 2018
+-- Created on Fri Mar  9 17:43:36 2018
 -- 
 ;
 --

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

@@ -1,6 +1,6 @@
 -- 
 -- Created by SQL::Translator::Producer::PostgreSQL
--- Created on Wed Feb 21 12:51:23 2018
+-- Created on Fri Mar  9 17:43:36 2018
 -- 
 ;
 --
@@ -127,7 +127,7 @@ CREATE INDEX "entity_association_idx_entity_id" on "entity_association" ("entity
 --
 CREATE TABLE "global_medals" (
   "id" serial NOT NULL,
-  "group_id" character varying(255) NOT NULL,
+  "group_id" integer NOT NULL,
   "threshold" integer NOT NULL,
   "points" integer NOT NULL,
   PRIMARY KEY ("id")
@@ -152,7 +152,7 @@ CREATE INDEX "leaderboard_sets_idx_leaderboard_id" on "leaderboard_sets" ("leade
 --
 CREATE TABLE "org_medals" (
   "id" serial NOT NULL,
-  "group_id" character varying(255) NOT NULL,
+  "group_id" integer NOT NULL,
   "threshold" integer NOT NULL,
   "points" integer NOT NULL,
   PRIMARY KEY ("id")
@@ -194,6 +194,7 @@ CREATE TABLE "transactions" (
   "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")
 );
@@ -240,8 +241,8 @@ CREATE INDEX "feedback_idx_user_id" on "feedback" ("user_id");
 --
 CREATE TABLE "global_user_medal_progress" (
   "id" serial NOT NULL,
-  "entity_id" character varying(255) NOT NULL,
-  "group_id" character varying(255) NOT NULL,
+  "entity_id" integer NOT NULL,
+  "group_id" integer NOT NULL,
   "total" integer NOT NULL,
   PRIMARY KEY ("id")
 );
@@ -254,8 +255,8 @@ CREATE INDEX "global_user_medal_progress_idx_group_id" on "global_user_medal_pro
 --
 CREATE TABLE "global_user_medals" (
   "id" serial NOT NULL,
-  "entity_id" character varying(255) NOT NULL,
-  "group_id" character varying(255) 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,
@@ -284,8 +285,8 @@ CREATE INDEX "import_lookups_idx_set_id" on "import_lookups" ("set_id");
 --
 CREATE TABLE "org_user_medal_progress" (
   "id" serial NOT NULL,
-  "entity_id" character varying(255) NOT NULL,
-  "group_id" character varying(255) NOT NULL,
+  "entity_id" integer NOT NULL,
+  "group_id" integer NOT NULL,
   "total" integer NOT NULL,
   PRIMARY KEY ("id")
 );
@@ -298,8 +299,8 @@ CREATE INDEX "org_user_medal_progress_idx_group_id" on "org_user_medal_progress"
 --
 CREATE TABLE "org_user_medals" (
   "id" serial NOT NULL,
-  "entity_id" character varying(255) NOT NULL,
-  "group_id" character varying(255) 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,
@@ -342,6 +343,27 @@ CREATE TABLE "session_tokens" (
 );
 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: import_values
@@ -482,6 +504,18 @@ ALTER TABLE "organisation_payroll" ADD CONSTRAINT "organisation_payroll_fk_org_i
 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 "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;

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

@@ -0,0 +1,18 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Mon Jun 11 17:59:15 2018
+-- 
+;
+--
+-- 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/24/001-auto.sql

@@ -0,0 +1,544 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Mon Jun 11 17:59:14 2018
+-- 
+;
+--
+-- 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: 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),
+  PRIMARY KEY ("outcode", "incode")
+);
+
+;
+--
+-- 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: 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: 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: 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),
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "organisations_idx_entity_id" on "organisations" ("entity_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: 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: 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: 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: 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");
+
+;
+--
+-- 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 "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 "organisations" ADD CONSTRAINT "organisations_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+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 "organisation_payroll" ADD CONSTRAINT "organisation_payroll_fk_org_id" FOREIGN KEY ("org_id")
+  REFERENCES "organisations" ("id") DEFERRABLE;
+
+;
+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 "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 "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;
+
+;

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

@@ -0,0 +1,18 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Fri Jun 28 15:21:00 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/25/001-auto.sql

@@ -0,0 +1,562 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Fri Jun 28 15:21:00 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: 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),
+  PRIMARY KEY ("outcode", "incode")
+);
+
+;
+--
+-- 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: 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: 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: 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),
+  PRIMARY KEY ("id")
+);
+CREATE INDEX "organisations_idx_entity_id" on "organisations" ("entity_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: 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: 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: 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: 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");
+
+;
+--
+-- 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 "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 "organisations" ADD CONSTRAINT "organisations_fk_entity_id" FOREIGN KEY ("entity_id")
+  REFERENCES "entities" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+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 "organisation_payroll" ADD CONSTRAINT "organisation_payroll_fk_org_id" FOREIGN KEY ("org_id")
+  REFERENCES "organisations" ("id") DEFERRABLE;
+
+;
+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 "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 "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;
+
+;

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

@@ -0,0 +1,18 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Wed Jul  3 15:49:20 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/26/001-auto.sql

@@ -0,0 +1,656 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Wed Jul  3 15:49:20 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_postcodes
+--
+CREATE TABLE "gb_postcodes" (
+  "outcode" character(4) NOT NULL,
+  "incode" character(3) DEFAULT '' NOT NULL,
+  "latitude" numeric(7,5),
+  "longitude" numeric(7,5),
+  PRIMARY KEY ("outcode", "incode")
+);
+
+;
+--
+-- 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: 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: 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: 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: 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: 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: 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: 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 "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 "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_types" ("id") DEFERRABLE;
+
+;
+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 "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 "organisation_payroll" ADD CONSTRAINT "organisation_payroll_fk_org_id" FOREIGN KEY ("org_id")
+  REFERENCES "organisations" ("id") 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 "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") DEFERRABLE;
+
+;

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

@@ -0,0 +1,18 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Thu Jul  4 18:09:11 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/27/001-auto.sql

@@ -0,0 +1,656 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Thu Jul  4 18:09:10 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_postcodes
+--
+CREATE TABLE "gb_postcodes" (
+  "outcode" character(4) NOT NULL,
+  "incode" character(3) DEFAULT '' NOT NULL,
+  "latitude" numeric(7,5),
+  "longitude" numeric(7,5),
+  PRIMARY KEY ("outcode", "incode")
+);
+
+;
+--
+-- 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: 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: 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 "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 "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/28/001-auto-__VERSION.sql

@@ -0,0 +1,18 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Fri Jul 12 21:29:50 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/28/001-auto.sql

@@ -0,0 +1,677 @@
+-- 
+-- Created by SQL::Translator::Producer::PostgreSQL
+-- Created on Fri Jul 12 21:29:50 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_postcodes
+--
+CREATE TABLE "gb_postcodes" (
+  "outcode" character(4) NOT NULL,
+  "incode" character(3) DEFAULT '' NOT NULL,
+  "latitude" numeric(7,5),
+  "longitude" numeric(7,5),
+  PRIMARY KEY ("outcode", "incode")
+);
+
+;
+--
+-- 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: 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: 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: 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: 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 "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 "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 "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 "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/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/22-23/001-auto.sql

@@ -14,7 +14,7 @@ CREATE TABLE "global_medal_group" (
 ;
 CREATE TABLE "global_medals" (
   "id" serial NOT NULL,
-  "group_id" character varying(255) NOT NULL,
+  "group_id" integer NOT NULL,
   "threshold" integer NOT NULL,
   "points" integer NOT NULL,
   PRIMARY KEY ("id")
@@ -24,8 +24,8 @@ CREATE INDEX "global_medals_idx_group_id" on "global_medals" ("group_id");
 ;
 CREATE TABLE "global_user_medal_progress" (
   "id" serial NOT NULL,
-  "entity_id" character varying(255) NOT NULL,
-  "group_id" character varying(255) NOT NULL,
+  "entity_id" integer NOT NULL,
+  "group_id" integer NOT NULL,
   "total" integer NOT NULL,
   PRIMARY KEY ("id")
 );
@@ -35,8 +35,8 @@ CREATE INDEX "global_user_medal_progress_idx_group_id" on "global_user_medal_pro
 ;
 CREATE TABLE "global_user_medals" (
   "id" serial NOT NULL,
-  "entity_id" character varying(255) NOT NULL,
-  "group_id" character varying(255) 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,
@@ -56,7 +56,7 @@ CREATE TABLE "org_medal_group" (
 ;
 CREATE TABLE "org_medals" (
   "id" serial NOT NULL,
-  "group_id" character varying(255) NOT NULL,
+  "group_id" integer NOT NULL,
   "threshold" integer NOT NULL,
   "points" integer NOT NULL,
   PRIMARY KEY ("id")
@@ -66,8 +66,8 @@ CREATE INDEX "org_medals_idx_group_id" on "org_medals" ("group_id");
 ;
 CREATE TABLE "org_user_medal_progress" (
   "id" serial NOT NULL,
-  "entity_id" character varying(255) NOT NULL,
-  "group_id" character varying(255) NOT NULL,
+  "entity_id" integer NOT NULL,
+  "group_id" integer NOT NULL,
   "total" integer NOT NULL,
   PRIMARY KEY ("id")
 );
@@ -77,8 +77,8 @@ CREATE INDEX "org_user_medal_progress_idx_group_id" on "org_user_medal_progress"
 ;
 CREATE TABLE "org_user_medals" (
   "id" serial NOT NULL,
-  "entity_id" character varying(255) NOT NULL,
-  "group_id" character varying(255) 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,
@@ -87,6 +87,24 @@ CREATE TABLE "org_user_medals" (
 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");
 
+;
+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");
+
 ;
 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;
@@ -127,6 +145,21 @@ ALTER TABLE "org_user_medals" ADD CONSTRAINT "org_user_medals_fk_entity_id" FORE
 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 "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 ADD COLUMN essential boolean DEFAULT false NOT NULL;
+
 ;
 
 COMMIT;

share/ddl/PostgreSQL/upgrade/23-24/001-auto.sql

@@ -0,0 +1,12 @@
+-- Convert schema 'share/ddl/_source/deploy/23/001-auto.yml' to 'share/ddl/_source/deploy/24/001-auto.yml':;
+
+;
+BEGIN;
+
+;
+ALTER TABLE category ADD COLUMN line_icon character varying(255);
+
+;
+
+COMMIT;
+

share/ddl/PostgreSQL/upgrade/24-25/001-auto.sql

@@ -0,0 +1,24 @@
+-- Convert schema 'share/ddl/_source/deploy/24/001-auto.yml' to 'share/ddl/_source/deploy/25/001-auto.yml':;
+
+;
+BEGIN;
+
+;
+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");
+
+;
+ALTER TABLE "transactions_meta" ADD CONSTRAINT "transactions_meta_fk_transaction_id" FOREIGN KEY ("transaction_id")
+  REFERENCES "transactions" ("id") ON DELETE CASCADE DEFERRABLE;
+
+;
+
+COMMIT;
+

share/ddl/PostgreSQL/upgrade/25-26/001-auto.sql

@@ -0,0 +1,98 @@
+-- Convert schema 'share/ddl/_source/deploy/25/001-auto.yml' to 'share/ddl/_source/deploy/26/001-auto.yml':;
+
+;
+BEGIN;
+
+;
+CREATE TABLE "external_references" (
+  "id" serial NOT NULL,
+  "name" character varying(255) NOT NULL,
+  PRIMARY KEY ("id"),
+  CONSTRAINT "external_references_name" UNIQUE ("name")
+);
+
+;
+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")
+);
+
+;
+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")
+);
+
+;
+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");
+
+;
+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");
+
+;
+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") 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 organisations ADD COLUMN type_id integer;
+
+;
+ALTER TABLE organisations ADD COLUMN social_type_id integer;
+
+;
+ALTER TABLE organisations ADD COLUMN is_anchor boolean DEFAULT FALSE NOT NULL;
+
+;
+CREATE INDEX organisations_idx_type_id on organisations (type_id);
+
+;
+CREATE INDEX organisations_idx_social_type_id on organisations (social_type_id);
+
+;
+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_types (id) DEFERRABLE;
+
+;
+
+COMMIT;
+

share/ddl/PostgreSQL/upgrade/26-27/001-auto.sql

@@ -0,0 +1,23 @@
+-- Convert schema 'share/ddl/_source/deploy/26/001-auto.yml' to 'share/ddl/_source/deploy/27/001-auto.yml':;
+
+;
+BEGIN;
+
+;
+ALTER TABLE organisations DROP CONSTRAINT organisations_fk_social_type_id;
+
+;
+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 organisations_external DROP CONSTRAINT organisations_external_fk_org_id;
+
+;
+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;
+
+;
+
+COMMIT;
+