import sqlite3

import pytest

from app import db, ids
from app.services import devices


def test_ids_are_unique_typed_and_ordered():
    """Все ID глобально уникальны: не повторяются, типы не пересекаются, внутри типа растут со временем."""
    seen, last = set(), {}
    for i in range(50_000):
        prefix = list(ids.PREFIXES)[i % len(ids.PREFIXES)]
        v = ids.new_id(prefix)
        assert v not in seen and v > last.get(prefix, "")
        assert ids.is_id(v, prefix) and not any(ids.is_id(v, o) for o in ids.PREFIXES if o != prefix)
        seen.add(v)
        last[prefix] = v
    with pytest.raises(LookupError):  # ID другого типа в пути/запросе отклоняется
        ids.check(ids.new_id("job"), "dev")
    with pytest.raises(LookupError):
        devices.get_device("1")  # числовые ID больше не существуют


LEGACY_DDL = """
CREATE TABLE devices (id INTEGER NOT NULL, name VARCHAR(64) NOT NULL, host VARCHAR(255) NOT NULL, port INTEGER NOT NULL,
  username VARCHAR(64) NOT NULL, password_enc TEXT NOT NULL, verify_tls BOOLEAN NOT NULL, created_at DATETIME NOT NULL,
  online BOOLEAN, status_json TEXT NOT NULL, status_at DATETIME, last_error TEXT, last_backup_requested_at DATETIME,
  use_tls BOOLEAN NOT NULL DEFAULT 1, group_id INTEGER, note TEXT, PRIMARY KEY (id), UNIQUE (name));
CREATE TABLE jobs (id INTEGER NOT NULL, device_id INTEGER, device_name VARCHAR(64) NOT NULL, type VARCHAR(32) NOT NULL,
  status VARCHAR(16) NOT NULL, message TEXT NOT NULL, created_at DATETIME NOT NULL, finished_at DATETIME, PRIMARY KEY (id));
CREATE TABLE backups (id INTEGER NOT NULL, device_id INTEGER NOT NULL, requested_at DATETIME NOT NULL, status VARCHAR(16) NOT NULL,
  key_binary VARCHAR(512), key_rsc VARCHAR(512), error TEXT, PRIMARY KEY (id),
  FOREIGN KEY(device_id) REFERENCES devices (id) ON DELETE CASCADE);
CREATE TABLE device_groups (id INTEGER NOT NULL, name VARCHAR(64) NOT NULL, PRIMARY KEY (id), UNIQUE (name));
"""


def test_migration_replaces_numeric_ids(tmp_path):
    """БД старой схемы: числовые ID заменяются на dev_/grp_/job_/bkp_, ссылки и порядок сохраняются, повтор — без эффекта."""
    path = tmp_path / "legacy.db"
    con = sqlite3.connect(path)
    con.executescript(LEGACY_DDL)
    con.executemany("INSERT INTO device_groups VALUES (?, ?)", [(1, "Alpha"), (2, "Beta")])
    for i, (name, grp) in enumerate((("r1", 1), ("r2", None)), start=1):
        con.execute("INSERT INTO devices (id, name, host, port, username, password_enc, verify_tls, created_at, status_json, use_tls, group_id)"
                    " VALUES (?,?,?,?,?,?,?,?,?,?,?)", (i, name, "10.0.0.%d" % i, 80, "u", "enc", 0, "2026-09-1%d 10:00:00.000000" % i, "{}", 0, grp))
    # задачи: две по r1 (id 1), одна по уже удалённому устройству (id 99)
    for i, dev in enumerate((1, 1, 99), start=1):
        con.execute("INSERT INTO jobs VALUES (?,?,?,?,?,?,?,?)", (i, dev, "n", "backup", "done", "ok", "2026-09-19 10:0%d:00.000000" % i, None))
    con.execute("INSERT INTO backups VALUES (1, 1, '2026-09-19 10:00:00.000000', 'done', 'backups/r1/a.backup', 'backups/r1/a.rsc', NULL)")
    con.execute("INSERT INTO backups VALUES (2, 99, '2026-09-19 10:05:00.000000', 'done', 'backups/gone/b.backup', NULL, NULL)")
    con.commit()
    con.close()

    db.init_db(f"sqlite:///{path}")
    con = sqlite3.connect(path)
    assert con.execute("PRAGMA user_version").fetchone()[0] == 2
    counts = {t: con.execute(f"SELECT count(*) FROM {t}").fetchone()[0] for t in ("device_groups", "devices", "jobs", "backups")}
    assert counts == {"device_groups": 2, "devices": 2, "jobs": 3, "backups": 2}
    alpha = con.execute("SELECT id FROM device_groups WHERE name='Alpha'").fetchone()[0]
    r1, r2 = (con.execute("SELECT id, group_id FROM devices WHERE name=?", (n,)).fetchone() for n in ("r1", "r2"))
    assert ids.is_id(r1[0], "dev") and r1[1] == alpha and r2[1] is None  # связь устройство → группа сохранена
    job_dev = [r[0] for r in con.execute("SELECT device_id FROM jobs ORDER BY id")]
    assert job_dev[0] == job_dev[1] == r1[0] and job_dev[2] not in (r1[0], r2[0])  # удалённое устройство — свой стабильный ID
    assert [r[0] for r in con.execute("SELECT id FROM jobs ORDER BY created_at")] == [r[0] for r in con.execute("SELECT id FROM jobs ORDER BY id")]
    bk = con.execute("SELECT device_id, device_name FROM backups ORDER BY requested_at").fetchall()
    assert bk[0] == (r1[0], "r1") and bk[1][0] == job_dev[2] and bk[1][1] == "gone"  # имя — из ключа S3
    every = [r[0] for t in ("device_groups", "devices", "jobs", "backups", "events") for r in con.execute(f"SELECT id FROM {t}")]
    assert len(every) == len(set(every)) and all(ids.is_id(i) for i in every)  # ни одного пересечения между таблицами
    assert con.execute("SELECT type FROM events").fetchall() == [("system.migrated",)]
    assert len(list(tmp_path.glob("legacy.db.bak-*"))) == 1  # копия БД перед миграцией
    con.close()
    db.init_db(f"sqlite:///{path}")  # повторный запуск ничего не меняет
    con = sqlite3.connect(path)
    assert con.execute("SELECT count(*) FROM events").fetchone()[0] == 1 and len(list(tmp_path.glob("legacy.db.bak-*"))) == 1
    con.close()


LEGACY_DDL_NO_EXTRA_COLUMNS = """
CREATE TABLE devices (id INTEGER NOT NULL, name VARCHAR(64) NOT NULL, host VARCHAR(255) NOT NULL, port INTEGER NOT NULL,
  username VARCHAR(64) NOT NULL, password_enc TEXT NOT NULL, verify_tls BOOLEAN NOT NULL, created_at DATETIME NOT NULL,
  online BOOLEAN, status_json TEXT NOT NULL, status_at DATETIME, last_error TEXT, last_backup_requested_at DATETIME,
  PRIMARY KEY (id), UNIQUE (name));
CREATE TABLE jobs (id INTEGER NOT NULL, device_id INTEGER, device_name VARCHAR(64) NOT NULL, type VARCHAR(32) NOT NULL,
  status VARCHAR(16) NOT NULL, message TEXT NOT NULL, created_at DATETIME NOT NULL, finished_at DATETIME, PRIMARY KEY (id));
CREATE TABLE backups (id INTEGER NOT NULL, device_id INTEGER NOT NULL, requested_at DATETIME NOT NULL, status VARCHAR(16) NOT NULL,
  key_binary VARCHAR(512), key_rsc VARCHAR(512), error TEXT, PRIMARY KEY (id),
  FOREIGN KEY(device_id) REFERENCES devices (id) ON DELETE CASCADE);
CREATE TABLE device_groups (id INTEGER NOT NULL, name VARCHAR(64) NOT NULL, PRIMARY KEY (id), UNIQUE (name));
"""


def test_migration_adds_legacy_columns_before_id_migration(tmp_path):
    """Схема до появления групп/TLS-настроек/примечаний (без use_tls/group_id/note): колонки добавляются
    раньше миграции ID — _to_v1 читает их из старой таблицы."""
    path = tmp_path / "legacy2.db"
    con = sqlite3.connect(path)
    con.executescript(LEGACY_DDL_NO_EXTRA_COLUMNS)
    con.execute("INSERT INTO devices (id, name, host, port, username, password_enc, verify_tls, created_at, status_json)"
                " VALUES (1, 'r1', '10.0.0.1', 80, 'u', 'enc', 0, '2026-09-10 10:00:00.000000', '{}')")
    con.commit()
    con.close()

    db.init_db(f"sqlite:///{path}")
    con = sqlite3.connect(path)
    assert con.execute("PRAGMA user_version").fetchone()[0] == 2
    dev_id, use_tls, group_id, note = con.execute("SELECT id, use_tls, group_id, note FROM devices").fetchone()
    assert ids.is_id(dev_id, "dev") and (use_tls, group_id, note) == (1, None, None)  # значения по умолчанию
    con.close()