create type phtx.image_data_image_type as enum ('full', 'preview');
create type phtx.image_data_storage_type as enum ('local');
create type phtx.image_data_file_format as enum ('jpeg', 'jxl', 'x-canon-cr2');
create type phtx.image_field_type as enum ('text', 'integer', 'float', 'enumerated');
create type phtx.token_invalidation_reason as enum ('user', 'reuse_attempt');
create type phtx.token_scope as enum (
  -- Unsure if we want to do more than this later
  'photos:read',
  'photos:write',
  'photos:upload',
  'photos:delete',
  'photos:share',
  'albums:read',
  'albums:write',
  'albums:delete',
  'albums:share',
  'user:profile:read',
  'user:profile:write',
  'user:account:read',
  'user:account:write',
  'user:account:delete',
  'user:rules:read',
  'user:rules:write',
  'user:rules:delete',
  'admin:users:read',
  'admin:users:write',
  'admin:users:delete',
  'auth:clients:read',
  'auth:clients:register',
  'auth:clients:remove',
  'auth:refresh'
);

create table phtx."user" (
  user_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
  user_email phtx_ext.citext unique not null,
  user_username phtx_ext.citext unique not null,
  user_display_name text,
  user_password_hash text,
  user_time_created timestamptz not null default now(),
  user_time_password_changed timestamptz,
  user_time_email_confirmed timestamptz
);

create table phtx."user_token" (
  user_token_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
  user_token_hash text not null,
  user_token_issued timestamptz not null default now(),
  user_token_expires timestamptz not null default now() + '1 hour',
  user_token_invalidated timestamptz null default null,
  user_token_invalidation_reason phtx.token_invalidation_reason null default null,

  user_token_scopes phtx.token_scope[] not null default array[]::phtx.token_scope[],

  constraint user_token_invalidation_needs_reason check (
    (user_token_invalidated is null and user_token_invalidation_reason is null)
    or (user_token_invalidated is not null and user_token_invalidation_reason is not null)
  ),

  constraint refresh_token_no_other_scopes check (
    (array_position(user_token_scopes, 'auth:refresh') is null)
    or (array_length(user_token_scopes, 0) = 1)
  )
);

create table phtx."login_session" (
  login_session_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
  login_session_time_created timestamptz not null default now(),
  login_session_time_last_active timestamptz not null default now(),
  login_session_duration interval not null,
  login_session_time_logged_out timestamptz null,
  login_session_ip_address varchar(50)  not null,
  login_session_user_agent varchar(500) not null,
  login_session_referer    varchar(500) not null,
  login_session_user_uuid uuid not null references phtx."user" (user_uuid)
);

create table phtx."image" (
  image_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
  image_original_filename text not null,
  image_title text,
  image_description text,
  image_owner uuid not null references phtx."user" (user_uuid)
);

create table phtx."image_data" (
  image_data_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
  image_data_image uuid not null references phtx."image" (image_uuid),
  image_data_storage_type phtx.image_data_storage_type not null,
  image_data_image_type phtx.image_data_image_type not null,
  image_data_dimensions_width integer not null,
  image_data_dimensions_height integer not null,
  image_data_file_format phtx.image_data_file_format not null,
  
  -- Compound key to constrain specific storage types
  unique (image_data_uuid, image_data_storage_type)
);

create unique index "only_one_full_image" on phtx."image_data" (image_data_image)
  where (image_data_image_type = 'full');

create table phtx."image_data_local" (
  image_data_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
  image_data_storage_type phtx.image_data_storage_type check ( image_data_storage_type = 'local' ),

  image_data_file_path text not null,
  image_data_file_sha256 varchar(64),

  foreign key (image_data_uuid, image_data_storage_type) references phtx."image_data" (image_data_uuid, image_data_storage_type)
);

create table phtx."field" (
  field_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
  field_type phtx.image_field_type not null,
  field_user uuid references phtx."user" (user_uuid),

  -- Compound key to constrain image field types
  unique (field_uuid, field_type)
);

create table phtx."image_field" (
  image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
  image_field_type phtx.image_field_type not null,
  image_field_image uuid not null references phtx."image" (image_uuid),
  image_field_field uuid not null references phtx."field" (field_uuid),

  -- Constrain an image-field record so its type matches the referenced field definition
  foreign key (image_field_field, image_field_type) references phtx."field" (field_uuid, field_type),

  -- Compound key to constrain value types
  unique (image_field_uuid, image_field_type)
);

create table phtx."image_field_text" (
  image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
  image_field_type phtx.image_field_type not null check (image_field_type = 'text'),

  image_field_value text not null,

  foreign key (image_field_uuid, image_field_type) references phtx."image_field" (image_field_uuid, image_field_type)
);

create table phtx."image_field_integer" (
  image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
  image_field_type phtx.image_field_type not null check (image_field_type = 'integer'),

  image_field_value bigint not null,

  foreign key (image_field_uuid, image_field_type) references phtx."image_field" (image_field_uuid, image_field_type)
);

create table phtx."image_field_float" (
  image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
  image_field_type phtx.image_field_type not null check (image_field_type = 'float'),

  image_field_value double precision not null,

  foreign key (image_field_uuid, image_field_type) references phtx."image_field" (image_field_uuid, image_field_type)
);

create table phtx."image_field_enumerated" (
  image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
  image_field_type phtx.image_field_type not null check (image_field_type = 'integer'),

  image_field_value text not null,

  foreign key (image_field_uuid, image_field_type) references phtx."image_field" (image_field_uuid, image_field_type)
);