PRAGMA journal_mode = TRUNCATE;
PRAGMA foreign_keys = 1;

create table PaMetadata (
  dbSchema varchar(100) not null,
  k varchar(100) not null,
  val varchar(250) not null,
  primary key (dbSchema, k)
);

insert into PaMetadata (dbSchema, k, val) values ('image-cache', 'dbSchemaVersion', '9');

create table PaImageCache (
  id integer not null primary key autoincrement,
  resourceVersion varchar(100) not null,
  mediaId varchar(36) not null,
  mTypeRSize varchar(100) not null,
  mediaType varchar(100) not null,
  rawWidth integer not null,
  rawHeight integer not null,
  weightB integer not null,
  slug varchar(200),
  modifiedAt timestamp not null default current_timestamp,
  binaryFile blob not null,
  unique (mediaId, mTypeRSize)
);

create index PaImageCache_mediaIdMediaType_idx on PaImageCache (mediaId, mediaType);
create index PaImageCache_modifiedAt_idx on PaImageCache (modifiedAt);

create table PaImageCacheOwner (
  imageCacheId integer not null references PaImageCache (id) on delete cascade,
  ownerHandle varchar(250) not null,
  rawResizeR varchar(50) not null,
  primary key (imageCacheId, ownerHandle, rawResizeR)
);

create index PaImageCacheOwner_owner_idx on PaImageCacheOwner (ownerHandle, rawResizeR);
