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 cruises (
  id integer PRIMARY KEY NOT NULL,
  account_id integer NOT NULL,
  ship_id integer NOT NULL,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,

  comment text,
  completed_at timestamp with time zone,
  imported_at timestamp with time zone,
  started_at timestamp with time zone,
  title character varying(255),
  data json NOT NULL DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS index_cruises_on_account_id_ship_id ON cruises (account_id, ship_id);
CREATE VIRTUAL TABLE IF NOT EXISTS cruises_fts USING fts5(comment, title, content='cruises', content_rowid='id');
CREATE TRIGGER IF NOT EXISTS cruises_fts_ai AFTER INSERT ON cruises BEGIN
  INSERT INTO cruises_fts(rowid, comment, title) VALUES (new.id, new.comment, new.title);
END;
CREATE TRIGGER IF NOT EXISTS cruises_fts_ad AFTER DELETE ON cruises BEGIN
  INSERT INTO cruises_fts(cruises_fts, rowid, comment, title) VALUES('delete', old.id, old.comment, old.title);
END;
CREATE TRIGGER IF NOT EXISTS cruises_fts_au AFTER UPDATE ON cruises BEGIN
  INSERT INTO cruises_fts(cruises_fts, rowid, comment, title) VALUES('delete', old.id, old.comment, old.title);
  INSERT INTO cruises_fts(rowid, comment, title) VALUES (new.id, new.comment, new.title);
END;
INSERT INTO schema_migrations(version) VALUES('20241006213643') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS days (
  id integer PRIMARY KEY NOT NULL,
  cruise_id integer NOT NULL,
  trip_id integer NOT NULL,
  account_id integer NOT NULL,
  ship_id integer NOT NULL,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,

  comment text,
  crew BLOB,
  day integer NOT NULL,
  end_location character varying(255),
  passengers BLOB,
  skipper integer, 
  start_location character varying(255),
  timezone integer NOT NULL,
  data json NOT NULL DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS index_days_on_cruise_id_trip_id_account_id_ship_id ON days (cruise_id, trip_id, account_id, ship_id);
CREATE VIRTUAL TABLE IF NOT EXISTS days_fts USING fts5(comment, end_location, start_location, content='days', content_rowid='id');
CREATE TRIGGER IF NOT EXISTS days_fts_ai AFTER INSERT ON days BEGIN
  INSERT INTO days_fts(rowid, comment, end_location, start_location) VALUES (new.id, new.comment, new.end_location, new.start_location);
END;
CREATE TRIGGER IF NOT EXISTS days_fts_ad AFTER DELETE ON days BEGIN
  INSERT INTO days_fts(days_fts, rowid, comment, end_location, start_location) VALUES('delete', old.id, old.comment, old.end_location, old.start_location);
END;
CREATE TRIGGER IF NOT EXISTS days_fts_au AFTER UPDATE ON days BEGIN
  INSERT INTO days_fts(days_fts, rowid, comment , end_location, start_location) VALUES('delete', old.id, old.comment, old.end_location, old.start_location);
  INSERT INTO days_fts(rowid, comment , end_location, start_location) VALUES (new.id, new.comment, new.end_location, new.start_location);
END;
INSERT INTO schema_migrations(version) VALUES('20241006213651') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS event_logs (
  id integer PRIMARY KEY NOT NULL,
  cruise_id integer,
  trip_id integer,
  day_id integer,
  log_id integer,
  account_id integer NOT NULL,
  ship_id integer,
  user_id integer,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,
  data json NOT NULL DEFAULT '{}',
  info character varying(255) NOT NULL,
  level integer,
  type character varying(255) NOT NULL
);

CREATE INDEX IF NOT EXISTS index_event_logs_on_account_id_ship_id_level_type  ON event_logs (account_id, ship_id, level, type);
INSERT INTO schema_migrations(version) VALUES('20260301165118') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS logs (
  id integer PRIMARY KEY NOT NULL,
  cruise_id integer NOT NULL,
  trip_id integer NOT NULL,
  day_id integer NOT NULL,
  account_id integer NOT NULL,
  ship_id integer NOT NULL,
  user_id integer,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,

  comment text,
  course real,
  depth real,
  distance real,
  engine_rpms BLOB,
  heading real,
  labels BLOB,
  lat double precision,
  lng double precision,
  log real,
  sog real,
  stw real,
  time double precision NOT NULL,
  time_utc double precision NOT NULL,
  timezone integer NOT NULL,
  data json NOT NULL DEFAULT '{}',

  current_direction character varying(255),
  current_speed real,
  humidity real,
  pressure real,
  sea_state character varying(255),
  temperature integer,
  weather integer,
  wind_direction real,
  wind_speed real,

  watch character varying(255),
  watch_comment text
);
CREATE INDEX IF NOT EXISTS index_logs_on_cruise_trip_day_account_ship_labels ON logs (cruise_id, trip_id, day_id, account_id, ship_id, labels);
CREATE VIRTUAL TABLE IF NOT EXISTS logs_fts USING fts5(comment, watch_comment, content='logs', content_rowid='id');
CREATE TRIGGER IF NOT EXISTS logs_fts_ai AFTER INSERT ON logs BEGIN
  INSERT INTO logs_fts(rowid, comment, watch_comment) VALUES (new.id, new.comment, new.watch_comment);
END;
CREATE TRIGGER IF NOT EXISTS logs_fts_ad AFTER DELETE ON logs BEGIN
  INSERT INTO logs_fts(logs_fts, rowid, comment, watch_comment) VALUES('delete', old.id, old.comment, old.watch_comment);
END;
CREATE TRIGGER IF NOT EXISTS logs_fts_au AFTER UPDATE ON logs BEGIN
  INSERT INTO logs_fts(logs_fts, rowid, comment, watch_comment) VALUES('delete', old.id, old.comment, old.watch_comment);
  INSERT INTO logs_fts(rowid, comment, watch_comment) VALUES (new.id, new.comment, new.watch_comment);
END;
INSERT INTO schema_migrations(version) VALUES('20241006213653') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS notes (
  id integer PRIMARY KEY NOT NULL,
  cruise_id integer,
  trip_id integer,
  day_id integer,
  log_id integer,
  account_id integer NOT NULL,
  ship_id integer NOT NULL,
  user_id integer,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,
  deleted_at timestamp with time zone,

  data json NOT NULL DEFAULT '{}',
  date character varying(255),
  note text,
  note_extra text,
  note_type integer NOT NULL,
  sort_order integer NOT NULL DEFAULT 0,
  title character varying(255)
);
CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(date, note, note_extra, title, content='notes', content_rowid='id');
CREATE TRIGGER IF NOT EXISTS notes_fts_ai AFTER INSERT ON notes BEGIN
  INSERT INTO notes_fts(rowid, date, note, note_extra, title) VALUES (new.id, new.date, new.note, new.note_extra, new.title);
END;
CREATE TRIGGER IF NOT EXISTS notes_fts_ad AFTER DELETE ON notes BEGIN
  INSERT INTO notes_fts(notes_fts, rowid, date, note, note_extra, title) VALUES('delete', old.id, old.date, old.note, old.note_extra, old.title);
END;
CREATE TRIGGER IF NOT EXISTS notes_fts_au AFTER UPDATE ON notes BEGIN
  INSERT INTO notes_fts(notes_fts, rowid, date, note, note_extra, title) VALUES('delete', old.id, old.date, old.note, old.note_extra, old.title);
  INSERT INTO notes_fts(rowid, date, note, note_extra, title) VALUES (new.id, new.date, new.note, new.note_extra, new.title);
END;
INSERT INTO schema_migrations(version) VALUES('20241027202624') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS note_attachments (
  id integer PRIMARY KEY NOT NULL,
  note_id integer NOT NULL,
  account_id integer NOT NULL,
  ship_id integer NOT NULL,
  user_id integer,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,
  deleted_at timestamp with time zone,

  checksum character varying(64) NOT NULL,
  content_type character varying(255) NOT NULL,
  data json NOT NULL DEFAULT '{}',
  date timestamp with time zone,
  file_data BLOB,
  file_name character varying(255) NOT NULL,
  file_type character varying(64) NOT NULL,
  size integer NOT NULL,
  url text
);
CREATE INDEX IF NOT EXISTS index_note_attachments_on_note_id_account_id_ship_id ON note_attachments (note_id, account_id, ship_id);
INSERT INTO schema_migrations(version) VALUES('20241108182149') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS settings (
  id integer PRIMARY KEY NOT NULL,
  account_id integer NOT NULL,
  ship_id integer NOT NULL,
  user_id integer NOT NULL,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,

  data json NOT NULL DEFAULT '{}',
  setting_type integer NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS index_settings_on_account_id_ship_id_user_id_setting_type ON settings (account_id, ship_id, user_id, setting_type);
INSERT INTO schema_migrations(version) VALUES('20250715164220') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS ship_users (
  id integer PRIMARY KEY NOT NULL,
  account_id integer NOT NULL,
  ship_id integer NOT NULL,
  user_id integer,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,
  deleted_at timestamp with time zone,

  active integer NOT NULL DEFAULT 0,
  data json NOT NULL DEFAULT '{}',
  name character varying(255),
  passcode character varying(255),
  ship_user_level integer NOT NULL DEFAULT 0,
  ship_user_crew_role integer NOT NULL DEFAULT 0,
  shortname character varying(255),
  token character varying(255)
);

CREATE INDEX IF NOT EXISTS index_ship_users_account_id_ship_id_user_id ON ship_users (account_id, ship_id, user_id);
INSERT INTO schema_migrations(version) VALUES('20250729121053') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS statuses (
  id integer PRIMARY KEY NOT NULL,
  cruise_id integer NOT NULL,
  trip_id integer NOT NULL,
  day_id integer NOT NULL,
  account_id integer NOT NULL,
  ship_id integer NOT NULL,
  user_id integer,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,
  deleted_at timestamp with time zone,

  end_log_id integer,
  start_log_id integer NOT NULL,

  data json NOT NULL DEFAULT '{}',
  item integer,
  name character varying(255),
  status_type integer NOT NULL,
  value integer NOT NULL
);

CREATE INDEX IF NOT EXISTS index_statuses_on_cruise_trip_day_account_ship_type_item_name ON statuses (cruise_id, trip_id, day_id, account_id, ship_id, deleted_at, status_type, item, name);
INSERT INTO schema_migrations(version) VALUES('20250305190234') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS stored_calculations (
  id integer PRIMARY KEY NOT NULL,
  account_id integer NOT NULL,
  ship_id integer NOT NULL,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,

  data text,
  source_id integer NOT NULL,
  source_type integer NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS index_stored_calculations_on_type__id_account_id_ship_id ON stored_calculations (source_type, source_id, account_id, ship_id);
INSERT INTO schema_migrations(version) VALUES('20241015120439') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS tracks (
  id integer PRIMARY KEY NOT NULL,
  cruise_id integer NOT NULL,
  trip_id integer,
  day_id integer,
  account_id integer NOT NULL,
  ship_id integer NOT NULL,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,

  ais_entity_id integer,
  depths BLOB,
  headings BLOB,
  lats BLOB,
  lngs BLOB,
  sogs BLOB,
  times_utc BLOB,
  track_type integer NOT NULL,
  wind_directions BLOB,
  wind_speeds BLOB
);
CREATE INDEX IF NOT EXISTS index_tracks_on_cruise_trip_day_account_ship ON tracks (cruise_id, trip_id, day_id, account_id, ship_id);
INSERT INTO schema_migrations(version) VALUES('20241103113809') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS trips (
  id integer PRIMARY KEY NOT NULL,
  cruise_id integer NOT NULL,
  account_id integer NOT NULL,
  ship_id integer NOT NULL,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,

  comment text,
  completed_at timestamp with time zone,
  imported_at timestamp with time zone,
  started_at timestamp with time zone,
  timezone integer NOT NULL,
  title character varying(255),
  data json NOT NULL DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS index_trips_on_cruise_id_account_id_ship_id ON trips (cruise_id, account_id, ship_id);
CREATE VIRTUAL TABLE IF NOT EXISTS trips_fts USING fts5(comment, title, content='trips', content_rowid='id');
CREATE TRIGGER IF NOT EXISTS trips_fts_ai AFTER INSERT ON trips BEGIN
  INSERT INTO trips_fts(rowid, comment, title) VALUES (new.id, new.comment, new.title);
END;
CREATE TRIGGER IF NOT EXISTS trips_fts_ad AFTER DELETE ON trips BEGIN
  INSERT INTO trips_fts(trips_fts, rowid, comment, title) VALUES('delete', old.id, old.comment, old.title);
END;
CREATE TRIGGER IF NOT EXISTS trips_fts_au AFTER UPDATE ON trips BEGIN
  INSERT INTO trips_fts(trips_fts, rowid, comment, title) VALUES('delete', old.id, old.comment, old.title);
  INSERT INTO trips_fts(rowid, comment, title) VALUES (new.id, new.comment, new.title);
END;
INSERT INTO schema_migrations(version) VALUES('20241006213649') ON CONFLICT(version) DO NOTHING;

CREATE TABLE IF NOT EXISTS waypoints (
  id integer PRIMARY KEY NOT NULL,
  cruise_id integer NOT NULL,
  trip_id integer NOT NULL,
  day_id integer NOT NULL,
  log_id integer NOT NULL,
  account_id integer NOT NULL,
  ship_id integer NOT NULL,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  synced_at timestamp with time zone,

  course real,
  delta integer NOT NULL,
  distance real NOT NULL,
  heading real,
  lat double precision,
  lng double precision,
  log real,
  time_utc integer NOT NULL
);
CREATE INDEX IF NOT EXISTS index_waypoints_on_cruise_trip_day_log_account_ship ON waypoints (cruise_id, trip_id, day_id, log_id, account_id, ship_id);
INSERT INTO schema_migrations(version) VALUES('20241031204337') ON CONFLICT(version) DO NOTHING;
