import json
import sqlite3

from services import homescreen_editor


def _make_db(path):
    conn = sqlite3.connect(path)
    conn.execute("CREATE TABLE Users (Id INTEGER PRIMARY KEY, Name TEXT, Guid TEXT)")
    conn.execute("CREATE TABLE UserSettingsKeys (UserSettingsKeyId INTEGER PRIMARY KEY, Name TEXT)")
    conn.execute("CREATE TABLE UserSettings (UserId INTEGER, UserSettingsKeyId INTEGER, Value TEXT)")
    conn.execute("INSERT INTO UserSettingsKeys (UserSettingsKeyId, Name) VALUES (1, 'homescreensettings')")
    conn.execute("INSERT INTO Users (Id, Name, Guid) VALUES (1, 'Alice', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')")
    conn.execute("INSERT INTO Users (Id, Name, Guid) VALUES (2, 'Bob', 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb')")
    conn.execute(
        "INSERT INTO UserSettings (UserId, UserSettingsKeyId, Value) VALUES (?, ?, ?)",
        (
            1,
            1,
            json.dumps(
                {
                    "Sections": [
                        {
                            "Id": "one",
                            "Name": "Watchlist",
                            "CustomName": "Watchlist",
                            "UserId": "WRONG",
                            "SectionType": "items",
                        }
                    ]
                }
            ),
        ),
    )
    conn.commit()
    conn.close()


def test_read_db_normalizes_section_user_ids(tmp_path):
    db_path = tmp_path / "users.db"
    _make_db(db_path)

    result = homescreen_editor.read_db(str(db_path))

    assert result["validation"]["userCount"] == 2
    alice = next(user for user in result["users"] if user["name"] == "Alice")
    assert alice["embyGuid"] == "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
    assert alice["sections"][0]["UserId"] == "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"


def test_write_db_persists_normalized_sections(tmp_path):
    db_path = tmp_path / "users.db"
    _make_db(db_path)

    payload = homescreen_editor.write_db(
        str(db_path),
        [
            {
                "userId": 2,
                "sections": [
                    {
                        "Id": "two",
                        "Name": "Recent",
                        "CustomName": "Recent",
                        "UserId": "SHOULD_BE_NORMALIZED",
                        "SectionType": "items",
                    }
                ],
            }
        ],
    )

    assert payload["ok"] is True
    conn = sqlite3.connect(db_path)
    value = conn.execute("SELECT Value FROM UserSettings WHERE UserId = 2").fetchone()[0]
    conn.close()
    parsed = json.loads(value)
    assert parsed["Sections"][0]["UserId"] == "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"


def test_generate_sql_includes_only_changed_users():
    original = [{"id": 1, "name": "Alice", "sections": [{"Id": "one"}]}]
    updated = [{"id": 1, "name": "Alice", "sections": [{"Id": "two"}]}]

    sql = homescreen_editor.generate_sql(updated, original)

    assert "Alice" in sql
    assert "UPDATE UserSettings" in sql
    assert "COMMIT;" in sql


def test_uploaded_db_becomes_active_source(tmp_path, monkeypatch):
    upload_dir = tmp_path / "uploads"
    state_path = tmp_path / "upload-state.json"
    monkeypatch.setattr(homescreen_editor, "HOMESCREEN_UPLOAD_DIR", upload_dir)
    monkeypatch.setattr(homescreen_editor, "HOMESCREEN_UPLOAD_STATE_PATH", state_path)

    meta = homescreen_editor.save_uploaded_db("users.db", b"sqlite-bytes")
    active = homescreen_editor.get_active_upload()
    resolved_path, resolved_meta = homescreen_editor.resolve_db_source()

    assert meta["upload_id"] == active["upload_id"]
    assert resolved_meta["upload_id"] == meta["upload_id"]
    assert resolved_path.endswith(f"{meta['upload_id']}.db")