-- Migration 037: IT Glue sync tables (all prefixed itg_)
-- Full backup of IT Glue data synced from the API

-- ─── Reference / Lookup Tables ───────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_organization_types (
  id                BIGINT PRIMARY KEY,
  name              TEXT NOT NULL,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS itg_organization_statuses (
  id                BIGINT PRIMARY KEY,
  name              TEXT NOT NULL,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS itg_configuration_types (
  id                BIGINT PRIMARY KEY,
  name              TEXT NOT NULL,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS itg_configuration_statuses (
  id                BIGINT PRIMARY KEY,
  name              TEXT NOT NULL,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS itg_contact_types (
  id                BIGINT PRIMARY KEY,
  name              TEXT NOT NULL,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS itg_password_categories (
  id                BIGINT PRIMARY KEY,
  name              TEXT NOT NULL,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS itg_manufacturers (
  id                BIGINT PRIMARY KEY,
  name              TEXT NOT NULL,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS itg_models (
  id                BIGINT PRIMARY KEY,
  manufacturer_id   BIGINT,
  name              TEXT NOT NULL,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS itg_operating_systems (
  id                BIGINT PRIMARY KEY,
  name              TEXT NOT NULL,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS itg_platforms (
  id                BIGINT PRIMARY KEY,
  name              TEXT NOT NULL,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS itg_countries (
  id                BIGINT PRIMARY KEY,
  name              TEXT NOT NULL,
  iso_code          TEXT,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ─── Organizations ────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_organizations (
  id                        BIGINT PRIMARY KEY,
  name                      TEXT NOT NULL,
  short_name                TEXT,
  organization_type_id      BIGINT,
  organization_type_name    TEXT,
  organization_status_id    BIGINT,
  organization_status_name  TEXT,
  psa_integration           TEXT,
  psa_id                    TEXT,
  sync_active               BOOLEAN DEFAULT FALSE,
  primary_org               BOOLEAN DEFAULT FALSE,
  quick_notes               TEXT,
  description               TEXT,
  alert                     TEXT,
  parent_id                 BIGINT,
  created_at                TIMESTAMPTZ,
  updated_at                TIMESTAMPTZ,
  synced_at                 TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_itg_organizations_name ON itg_organizations(name);
CREATE INDEX IF NOT EXISTS idx_itg_organizations_type ON itg_organizations(organization_type_id);
CREATE INDEX IF NOT EXISTS idx_itg_organizations_status ON itg_organizations(organization_status_id);
CREATE INDEX IF NOT EXISTS idx_itg_organizations_updated ON itg_organizations(updated_at);

-- ─── Locations ────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_locations (
  id                BIGINT PRIMARY KEY,
  organization_id   BIGINT NOT NULL,
  organization_name TEXT,
  name              TEXT NOT NULL,
  primary_location  BOOLEAN DEFAULT FALSE,
  address_1         TEXT,
  address_2         TEXT,
  city              TEXT,
  region_name       TEXT,
  postal_code       TEXT,
  country_name      TEXT,
  phone             TEXT,
  fax               TEXT,
  notes             TEXT,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_itg_locations_org ON itg_locations(organization_id);

-- ─── Contacts ─────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_contacts (
  id                  BIGINT PRIMARY KEY,
  organization_id     BIGINT NOT NULL,
  organization_name   TEXT,
  first_name          TEXT,
  last_name           TEXT,
  name                TEXT,
  title               TEXT,
  contact_type_id     BIGINT,
  contact_type_name   TEXT,
  location_id         BIGINT,
  important           BOOLEAN DEFAULT FALSE,
  notes               TEXT,
  emails              JSONB DEFAULT '[]',
  phones              JSONB DEFAULT '[]',
  created_at          TIMESTAMPTZ,
  updated_at          TIMESTAMPTZ,
  synced_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_itg_contacts_org ON itg_contacts(organization_id);
CREATE INDEX IF NOT EXISTS idx_itg_contacts_name ON itg_contacts(last_name, first_name);

-- ─── Configurations ───────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_configurations (
  id                          BIGINT PRIMARY KEY,
  organization_id             BIGINT NOT NULL,
  organization_name           TEXT,
  name                        TEXT NOT NULL,
  hostname                    TEXT,
  primary_ip                  TEXT,
  mac_address                 TEXT,
  serial_number               TEXT,
  asset_tag                   TEXT,
  position                    TEXT,
  installed_by                TEXT,
  purchased_by                TEXT,
  notes                       TEXT,
  operating_system_notes      TEXT,
  warranty_expires_at         TIMESTAMPTZ,
  installed_at                TIMESTAMPTZ,
  purchased_at                TIMESTAMPTZ,
  end_of_life_at              TIMESTAMPTZ,
  configuration_type_id       BIGINT,
  configuration_type_name     TEXT,
  configuration_status_id     BIGINT,
  configuration_status_name   TEXT,
  manufacturer_id             BIGINT,
  manufacturer_name           TEXT,
  model_id                    BIGINT,
  model_name                  TEXT,
  operating_system_id         BIGINT,
  operating_system_name       TEXT,
  location_id                 BIGINT,
  contact_id                  BIGINT,
  rmm_id                      TEXT,
  rmm_integration_type        TEXT,
  created_at                  TIMESTAMPTZ,
  updated_at                  TIMESTAMPTZ,
  synced_at                   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_itg_configurations_org ON itg_configurations(organization_id);
CREATE INDEX IF NOT EXISTS idx_itg_configurations_hostname ON itg_configurations(hostname);
CREATE INDEX IF NOT EXISTS idx_itg_configurations_serial ON itg_configurations(serial_number);
CREATE INDEX IF NOT EXISTS idx_itg_configurations_name ON itg_configurations(name);
CREATE INDEX IF NOT EXISTS idx_itg_configurations_rmm ON itg_configurations(rmm_id);

-- ─── Configuration Interfaces ─────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_configuration_interfaces (
  id                  BIGINT PRIMARY KEY,
  configuration_id    BIGINT NOT NULL,
  organization_id     BIGINT,
  name                TEXT,
  ip_address          TEXT,
  mac_address         TEXT,
  primary_interface   BOOLEAN DEFAULT FALSE,
  notes               TEXT,
  created_at          TIMESTAMPTZ,
  updated_at          TIMESTAMPTZ,
  synced_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_itg_config_interfaces_config ON itg_configuration_interfaces(configuration_id);

-- ─── Flexible Asset Types ─────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_flexible_asset_types (
  id            BIGINT PRIMARY KEY,
  name          TEXT NOT NULL,
  description   TEXT,
  icon          TEXT,
  enabled       BOOLEAN DEFAULT TRUE,
  builtin       BOOLEAN DEFAULT FALSE,
  created_at    TIMESTAMPTZ,
  updated_at    TIMESTAMPTZ,
  synced_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ─── Flexible Asset Fields ────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_flexible_asset_fields (
  id                        BIGINT PRIMARY KEY,
  flexible_asset_type_id    BIGINT NOT NULL,
  name                      TEXT NOT NULL,
  kind                      TEXT,
  hint                      TEXT,
  decimals                  INT DEFAULT 0,
  tag_type                  TEXT,
  required                  BOOLEAN DEFAULT FALSE,
  use_for_title             BOOLEAN DEFAULT FALSE,
  expiration                BOOLEAN DEFAULT FALSE,
  show_in_list              BOOLEAN DEFAULT FALSE,
  created_at                TIMESTAMPTZ,
  updated_at                TIMESTAMPTZ,
  synced_at                 TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_itg_fa_fields_type ON itg_flexible_asset_fields(flexible_asset_type_id);

-- ─── Flexible Assets ──────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_flexible_assets (
  id                        BIGINT PRIMARY KEY,
  organization_id           BIGINT NOT NULL,
  organization_name         TEXT,
  flexible_asset_type_id    BIGINT NOT NULL,
  flexible_asset_type_name  TEXT,
  name                      TEXT,
  traits                    JSONB DEFAULT '{}',
  archived                  BOOLEAN DEFAULT FALSE,
  created_at                TIMESTAMPTZ,
  updated_at                TIMESTAMPTZ,
  synced_at                 TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_itg_flexible_assets_org ON itg_flexible_assets(organization_id);
CREATE INDEX IF NOT EXISTS idx_itg_flexible_assets_type ON itg_flexible_assets(flexible_asset_type_id);
CREATE INDEX IF NOT EXISTS idx_itg_flexible_assets_name ON itg_flexible_assets(name);
CREATE INDEX IF NOT EXISTS idx_itg_flexible_assets_updated ON itg_flexible_assets(updated_at);

-- ─── Password Folders ─────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_password_folders (
  id                BIGINT PRIMARY KEY,
  organization_id   BIGINT NOT NULL,
  organization_name TEXT,
  name              TEXT NOT NULL,
  inherited         BOOLEAN DEFAULT FALSE,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_itg_password_folders_org ON itg_password_folders(organization_id);

-- ─── Passwords ────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_passwords (
  id                      BIGINT PRIMARY KEY,
  organization_id         BIGINT NOT NULL,
  organization_name       TEXT,
  name                    TEXT NOT NULL,
  username                TEXT,
  password                TEXT,
  url                     TEXT,
  notes                   TEXT,
  password_category_id    BIGINT,
  password_category_name  TEXT,
  password_folder_id      BIGINT,
  autofill_selectors      TEXT,
  otp_enabled             BOOLEAN DEFAULT FALSE,
  archived                BOOLEAN DEFAULT FALSE,
  created_at              TIMESTAMPTZ,
  updated_at              TIMESTAMPTZ,
  synced_at               TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_itg_passwords_org ON itg_passwords(organization_id);
CREATE INDEX IF NOT EXISTS idx_itg_passwords_name ON itg_passwords(name);

-- ─── Documents ────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_documents (
  id                BIGINT PRIMARY KEY,
  organization_id   BIGINT NOT NULL,
  organization_name TEXT,
  name              TEXT NOT NULL,
  content           TEXT,
  draft             BOOLEAN DEFAULT FALSE,
  archived          BOOLEAN DEFAULT FALSE,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_itg_documents_org ON itg_documents(organization_id);

-- ─── Domains ──────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_domains (
  id                BIGINT PRIMARY KEY,
  organization_id   BIGINT NOT NULL,
  organization_name TEXT,
  name              TEXT NOT NULL,
  screenshot        TEXT,
  whois_updated_at  TIMESTAMPTZ,
  expires_at        TIMESTAMPTZ,
  registrar_name    TEXT,
  notes             TEXT,
  created_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ,
  synced_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_itg_domains_org ON itg_domains(organization_id);
CREATE INDEX IF NOT EXISTS idx_itg_domains_name ON itg_domains(name);
CREATE INDEX IF NOT EXISTS idx_itg_domains_expires ON itg_domains(expires_at);

-- ─── Expirations ──────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_expirations (
  id                      BIGINT PRIMARY KEY,
  organization_id         BIGINT NOT NULL,
  organization_name       TEXT,
  resource_id             BIGINT,
  resource_type           TEXT,
  resource_name           TEXT,
  expiration_type         TEXT,
  description             TEXT,
  expiration_date         TIMESTAMPTZ,
  notify                  BOOLEAN DEFAULT FALSE,
  created_at              TIMESTAMPTZ,
  updated_at              TIMESTAMPTZ,
  synced_at               TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_itg_expirations_org ON itg_expirations(organization_id);
CREATE INDEX IF NOT EXISTS idx_itg_expirations_date ON itg_expirations(expiration_date);

-- ─── Sync History ─────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS itg_sync_history (
  id              SERIAL PRIMARY KEY,
  sync_type       TEXT NOT NULL DEFAULT 'full',
  status          TEXT NOT NULL,
  triggered_by    TEXT DEFAULT 'system',
  started_at      TIMESTAMPTZ NOT NULL,
  completed_at    TIMESTAMPTZ,
  duration_ms     INTEGER,
  entities        JSONB DEFAULT '[]',
  error           TEXT,
  total_upserted  INTEGER DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_itg_sync_history_started ON itg_sync_history(started_at DESC);