-- alembic app database — initial schema.
-- Applied once at startup by app/db.py if schema_version < 1. Plain SQL
-- migrations, not the `alembic` Python migration tool (name collision
-- avoided deliberately — see docs/ARCHITECTURE.md).

CREATE TABLE IF NOT EXISTS schema_version (
  version INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS playlists (
  id            INTEGER PRIMARY KEY,
  name          TEXT UNIQUE NOT NULL,
  spotify_url   TEXT NOT NULL,
  active        BOOLEAN NOT NULL DEFAULT 1,
  no_m3u        BOOLEAN NOT NULL DEFAULT 0,
  cron_expr     TEXT,
  notes         TEXT,
  created_at    REAL NOT NULL,
  updated_at    REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS secrets (
  id                INTEGER PRIMARY KEY,
  scope             TEXT NOT NULL,
  key               TEXT NOT NULL,
  value_encrypted   BLOB NOT NULL,
  updated_at        REAL NOT NULL,
  UNIQUE(scope, key)
);

CREATE TABLE IF NOT EXISTS scheduled_jobs (
  id            INTEGER PRIMARY KEY,
  job_key       TEXT UNIQUE NOT NULL,
  job_type      TEXT NOT NULL,
  playlist_id   INTEGER REFERENCES playlists(id),
  cron_expr     TEXT NOT NULL,
  enabled       BOOLEAN NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS job_runs (
  id            INTEGER PRIMARY KEY,
  job_key       TEXT NOT NULL,
  started_at    REAL NOT NULL,
  finished_at   REAL,
  status        TEXT NOT NULL,
  exit_code     INTEGER,
  summary       TEXT,
  log_path      TEXT,
  triggered_by  TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS manual_fix_audit (
  id              INTEGER PRIMARY KEY,
  beets_item_id   INTEGER NOT NULL,
  file_path       TEXT NOT NULL,
  field           TEXT NOT NULL,
  old_value       TEXT,
  new_value       TEXT,
  changed_by      TEXT NOT NULL,
  changed_at      REAL NOT NULL,
  source          TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS manual_imports (
  id            INTEGER PRIMARY KEY,
  source_path   TEXT NOT NULL,
  playlist_name TEXT,
  imported_by   TEXT NOT NULL,
  imported_at   REAL NOT NULL,
  status        TEXT NOT NULL,
  job_run_id    INTEGER REFERENCES job_runs(id)
);

CREATE TABLE IF NOT EXISTS dedup_runs (
  id            INTEGER PRIMARY KEY,
  started_at    REAL NOT NULL,
  finished_at   REAL,
  mode          TEXT NOT NULL,
  groups_found  INTEGER,
  kept          INTEGER,
  deleted       INTEGER,
  log_path      TEXT
);

CREATE TABLE IF NOT EXISTS dedup_candidates (
  id                  INTEGER PRIMARY KEY,
  dedup_run_id        INTEGER NOT NULL REFERENCES dedup_runs(id),
  pass_name           TEXT NOT NULL,
  keep_path           TEXT NOT NULL,
  delete_path         TEXT NOT NULL,
  delete_size_bytes   INTEGER,
  confirmed           BOOLEAN NOT NULL DEFAULT 0,
  confirmed_by        TEXT,
  confirmed_at        REAL,
  applied             BOOLEAN NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS genre_runs (
  id            INTEGER PRIMARY KEY,
  started_at    REAL,
  finished_at   REAL,
  mode          TEXT,
  written       INTEGER,
  unchanged     INTEGER,
  no_match      INTEGER,
  no_meta       INTEGER,
  log_path      TEXT
);

CREATE TABLE IF NOT EXISTS genre_candidates (
  id            INTEGER PRIMARY KEY,
  genre_run_id  INTEGER NOT NULL REFERENCES genre_runs(id),
  artist        TEXT,
  title         TEXT,
  old_genre     TEXT,
  new_genre     TEXT
);

CREATE TABLE IF NOT EXISTS users (
  id            INTEGER PRIMARY KEY,
  oidc_sub      TEXT UNIQUE NOT NULL,
  email         TEXT,
  name          TEXT,
  last_login_at REAL
);

CREATE TABLE IF NOT EXISTS app_settings (
  key   TEXT PRIMARY KEY,
  value TEXT
);

INSERT INTO schema_version (version) SELECT 1 WHERE NOT EXISTS (SELECT 1 FROM schema_version);