<?php

// Use builders to avoid typos and such
use Elgg\Schema\Column;
use Elgg\Schema\Table;
use Elgg\Schema\Database;
use Elgg\Schema\Key;

return Database::tables([
  'access_collection_membership' => Table::columns([
    'user_guid'            => Column::references('entities', 'guid'),
    'access_collection_id' => Column::references('access_collections', 'id'),
  ])->primaryKey(['user_guid', 'access_collection_id']),
  
  'access_collections' => Table::columns([
    'id'         => Column::int()->autoIncrement(3),
    'name'       => Column::text(),
    'owner_guid' => Column::references('entities', 'guid')->index(),
    'site_guid'  => Column::references('entities', 'guid')->value(0)->index(),
  ])->primaryKey('id'),
  
  'annotations' => Table::columns([
    'id'           => Column::int()->autoIncrement(1),
    'entity_guid'  => Column::references('entities', 'guid')->index(),
    'name_id'      => Column::references('metastrings', 'id')->index(),
    'value_id'     => Column::references('metastrings', 'id')->index(),
    'value_type'   => Column::enum(['integer', 'text']),
    'owner_guid'   => Column::references('entities', 'guid')->index(),
    'access_id'    => Column::references('access_collections', 'id')->index(),
    'time_created' => Column::timestamp(),
    'is_enabled'   => Column::boolean()->value(true),
  ])->primaryKey('id'),
  
  'config' => Table::columns([
    'name'      => Column::varchar(255),
    'value'     => Column::text(),
    'site_guid' => Column::references('entities', 'guid'),
  ])->primaryKey(['name', 'site_guid']),
  
  'datalists' => Table::columns([
    'name'  => Column::varchar(255),
    'value' => Column::text(),
  ])->primaryKey('name'),

  'entities' => Table::columns([
    'guid'           => Column::bigint()->unsigned()->autoIncrement(1),
    'type'           => Column::enum(['object', 'user', 'group', 'site'])->index(),
    'subtype'        => Column::references('entity_subtypes', 'id')->nullable()->value(null)->index(),
    'owner_guid'     => Column::references('entities', 'guid')->index(),
    'site_guid'      => Column::references('entities', 'guid')->index(),
    'container_guid' => Column::references('entities', 'guid')->index(),
    'access_id'      => Column::references('access_collections', 'id')->index(),
    'time_created'   => Column::timestamp()->index(),
    'time_updated'   => Column::timestamp()->index(),
    'last_action'    => Column::timestamp()->value(0),
    'is_enabled'     => Column::boolean()->value(true),
  ])->primaryKey('guid'),

  'entity_relationships' => Table::columns([
    'id'           => Column::int()->autoIncrement(1),
    'guid_one'     => Column::references('entities', 'guid')->index(),
    'relationship' => Column::varchar(50)->index(),
    'guid_two'     => Column::references('entities', 'guid')->index(),
    'time_created' => Column::timestamp(),
  ])->primaryKey('id')->keys([
    'guid_one' => Key::unique(['guid_one', 'relationship', 'guid_two']),
  ]),
  
  'entity_subtypes' => Table::columns([
    'id'      => Column::int()->autoIncrement(1),
    'type'    => Column::enum(['object', 'user', 'group', 'site']),
    'subtype' => Column::varchar(255),
    'class'   => Column::varchar(50)->value(''),
  ])->primaryKey('id')->keys([
    'type' => Key::unique(['type', 'subtype']),
  ]),
  
  'groups_entity' => Table::columns([
    'guid'        => Column::references('entities', 'guid'),
    'name'        => Column::text()->index(),
    'description' => Column::text()->index(),
  ])->primaryKey('guid')->keys([
    'name_2' => Key::fulltext(['name', 'description']),
  ]),
  
  'hmac_cache' => Table::columns([
    'hmac' => Column::varchar(255),
    'ts'   => Column::timestamp()->index(),
  ])->primaryKey('hmac'),
  
  'metadata' => Table::columns([
    'id'           => Column::int()->autoIncrement(1),
    'entity_guid'  => Column::references('entities', 'guid')->index(),
    'name_id'      => Column::references('metastrings', 'id')->index(),
    'value_id'     => Column::references('metastrings', 'id')->index(),
    'value_type'   => Column::enum(['integer', 'text']),
    'owner_guid'   => Column::references('entities', 'guid')->index(),
    'access_id'    => Column::references('access_collections', 'id')->index(),
    'time_created' => Column::timestamp(),
    'is_enabled'   => Column::boolean()->value(true),
  ])->primaryKey('id'),
  
  'metastrings' => Table::columns([
    'id'     => Column::int()->autoIncrement(1),
    'string' => Column::text()->index(),
  ])->primaryKey('id'),
  
  'objects_entity' => Table::columns([
    'guid'  => Column::references('entities', 'guid'),
    'title' => Column::text(),
    'description' => Column::text(),
  ])->primaryKey('guid')->keys([
    'title' => Key::fulltext(['title', 'description']),
  ]),
  
  'private_settings' => Table::columns([
    'id'          => Column::int()->autoIncrement(1),
    'entity_guid' => Column::references('entities', 'guid'),
    'name'        => Column::varchar(128)->index(),
    'value'       => Column::text()->index(),
  ])->primaryKey('id')->keys([
    'entity_guid' => Key::unique(['entity_guid', 'name']),
  ]),
  
  'queue' => Table::columns([
    'id'        => Column::int()->autoIncrement(1),
    'name'      => Column::varchar(255)->index(),
    'data'      => Column::blob(),
    'timestamp' => Column::timestamp(),
    'worker'    => Column::varchar(32)->nullable(),
  ])->primaryKey('id')->keys([
    'retrieve' => Key::columns(['timestamp', 'worker']),
  ]),
  
  'river' => Table::columns([
    'id'            => Column::int()->autoIncrement(1),
    'type'          => Column::varchar(8)->index(),
    'subtype'       => Column::varchar(32),
    'action_type'   => Column::varchar(32)->index(),
    'access_id'     => Column::references('access_collections', 'id')->index(),
    'view'          => Column::text(),
    'subject_guid'  => Column::references('entities', 'guid')->index(),
    'object_guid'   => Column::references('entities', 'guid')->index(),
    'target_guid'   => Column::references('entities', 'guid')->index(),
    'annotation_id' => Column::references('annotations', 'id')->index(),
    'posted'        => Column::timestamp()->index(),
    'is_enabled'    => Column::boolean()->value(true),
  ])->primaryKey('id'),
  
  'sites_entity' => Table::columns([
    'guid'        => Column::references('entities', 'guid'),
    'name'        => Column::text(),
    'description' => Column::text(),
    'url'         => Column::varchar(255)->unique(),
  ])->primaryKey('guid')->keys([
    'name' => Key::fulltext(['name', 'description', 'url']),
  ]),
  
  'system_log' => Table::columns([
    'id'                => Column::int()->autoIncrement(1),
    'object_id'         => Column::int()->index(),
    'object_class'      => Column::varchar(50)->index(),
    'object_type'       => Column::varchar(50)->index(),
    'object_subtype'    => Column::varchar(50)->index(),
    'event'             => Column::varchar(50)->index(),
    'performed_by_guid' => Column::references('entities', 'guid')->onDelete('none')->index(),
    'owner_guid'        => Column::references('entities', 'guid')->onDelete('none'),
    'access_id'         => Column::references('access_collections', 'id')->onDelete('none')->index(),
    'is_enabled'        => Column::boolean()->value(true),
    'time_created'      => Column::timestamp(),
    'ip_address'        => Column::varchar(46),
  ])->primaryKey('id'),
  
  'users_apisessions' => Table::columns([
    'id'        => Column::int()->autoIncrement(1),
    'user_guid' => Column::references('entities', 'guid'),
    'site_guid' => Column::references('entities', 'guid'),
    'token'     => Column::varchar(40)->nullable(),
    'expires'   => Column::timestamp(),
  ])->primaryKey('id')->keys([
    'user_guid' => Key::unique(['user_guid', 'site_guid']),
  ]),
  
  'users_entity' => Table::columns([
    'guid'             => Column::references('entities', 'guid'),
    'name'             => Column::text()->fulltext(),
    'username'         => Column::varchar(128)->value('')->unique(),
    'password'         => Column::varchar(32)->value('')->comment('Legacy password hashes')->index(),
    'salt'             => Column::varchar(8)->value('')->comment('Legacy password salts'),
    // 255 is recommended by PHP.net to hold future hash formats 
    'password_hash'    => Column::varchar(255)->value(''),
    'email'            => Column::text()->index(),
    'language'         => Column::varchar(6)->value(''),
    'is_banned'        => Column::boolean()->value(false),
    'is_admin'         => Column::boolean()->value(false)->index(),
    'last_action'      => Column::timestamp()->value(0)->index(),
    'prev_last_action' => Column::timestamp()->value(0),
    'last_login'       => Column::timestamp()->value(0)->index(),
    'prev_last_login'  => Column::timestamp()->value(0),
  ])->primaryKey('guid')->keys([
    'name_2'       => Key::fulltext(['name', 'username']),
  ]),
  
  'users_remember_me_cookies' => Table::columns([
    'code'      => Column::varchar(32),
    'guid'      => Column::references('entities', 'guid'),
    'timestamp' => Column::timestamp()->index(),
  ])->primaryKey('code'),
  
  'users_sessions' => Table::columns([
    'session' => Column::varchar(255),
    'ts'      => Column::timestamp()->value(0)->index(),
    'data'    => Column::blob(),
  ])->primaryKey('session'),
]);