CREATE TABLE IF NOT EXISTS schema_migrations (
  version character varying(255) NOT NULL
);
DELETE FROM schema_migrations WHERE rowid NOT IN (
  SELECT MIN(rowid) FROM schema_migrations
  GROUP BY version
);
CREATE UNIQUE INDEX IF NOT EXISTS schema_migrations_on_version ON schema_migrations (version);

CREATE TABLE IF NOT EXISTS accounts (
  id integer PRIMARY KEY NOT NULL,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  deleted_at timestamp with time zone,
  account_subscription_plan_id integer,
  name character varying(255) NOT NULL,
  slug character varying(255) NOT NULL,
  metadata json NOT NULL DEFAULT '{}',
  next_billing_at timestamp with time zone
);
INSERT INTO schema_migrations(version) VALUES('20250729101634') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS client_data (
  key character varying(64) NOT NULL,
  value blob
);
CREATE UNIQUE INDEX IF NOT EXISTS client_data_on_key ON client_data (key);

CREATE TABLE IF NOT EXISTS ships (
  id integer PRIMARY KEY NOT NULL,
  account_id integer NOT NULL,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  shard_id character varying(3) DEFAULT 'de1' NOT NULL,
  synced_at timestamp with time zone,

  client_token character varying(255),
  engine_count smallint NOT NULL DEFAULT 0,
  name character varying(255) NOT NULL,
  vessel_type smallint NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS index_ships_on_account_id ON ships (account_id);
INSERT INTO schema_migrations(version) VALUES('20241006220033') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS users (
  id integer PRIMARY KEY NOT NULL,
  account_id integer,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,
  activated_at timestamp with time zone,
  deleted_at timestamp with time zone,
  authentication_type integer,
  name character varying(255),
  email character varying(255) NOT NULL,
  shortname character varying(255)
);
CREATE INDEX IF NOT EXISTS index_users_on_authentication_type_email ON users (authentication_type, email);
INSERT INTO schema_migrations(version) VALUES('20250729010051') ON CONFLICT(version) DO NOTHING;
