"""Uji otomatis untuk lapisan data aplikasi inventaris.

Jalankan dari folder proyek:
    python3 -m pytest test_database.py -v
"""

import sqlite3
from pathlib import Path

import pytest

import database

BERKAS_SKEMA = Path(database.__file__).with_name("schema.sql")


@pytest.fixture
def conn() -> sqlite3.Connection:
    """Database SQLite :memory: dengan skema dan data awal."""
    koneksi_uji = sqlite3.connect(":memory:")
    koneksi_uji.execute("PRAGMA foreign_keys = ON")
    koneksi_uji.row_factory = sqlite3.Row
    koneksi_uji.executescript(BERKAS_SKEMA.read_text(encoding="utf-8"))
    yield koneksi_uji
    koneksi_uji.close()


def test_inisialisasi_idempoten(tmp_path):
    """inisialisasi dua kali tidak menduplikasi tabel maupun data."""
    berkas_db = tmp_path / "uji.db"
    database.inisialisasi(str(berkas_db))
    database.inisialisasi(str(berkas_db))

    conn = sqlite3.connect(berkas_db)
    try:
        tabel = {
            row[0]
            for row in conn.execute(
                "SELECT name FROM sqlite_master WHERE type = 'table'"
            )
        }
        assert {"produk", "riwayat"} <= tabel
        jumlah = conn.execute("SELECT COUNT(*) FROM produk").fetchone()[0]
        assert jumlah == 6
    finally:
        conn.close()


def test_tambah_lalu_ambil(conn):
    id_baru = database.tambah_produk(conn, "Susu Kotak", "Minuman", 6000, 12)

    produk = database.ambil_produk(conn, id_baru)
    assert produk["nama"] == "Susu Kotak"
    assert produk["kategori"] == "Minuman"
    assert produk["harga"] == 6000
    assert produk["stok"] == 12


def test_nama_duplikat_ditolak(conn):
    with pytest.raises(sqlite3.IntegrityError):
        database.tambah_produk(conn, "Kopi Tubruk", "Minuman", 15000, 5)


@pytest.mark.parametrize(
    "nama, kategori, harga, stok",
    [
        ("", "Minuman", 1000, 5),
        ("   ", "Minuman", 1000, 5),
        ("Teh", "", 1000, 5),
        ("Teh", "Minuman", -1, 5),
        ("Teh", "Minuman", 1000, -5),
    ],
)
def test_validasi_produk_menolak_masukan_salah(nama, kategori, harga, stok):
    with pytest.raises(ValueError):
        database.validasi_produk(nama, kategori, harga, stok)


def test_sesuaikan_stok_mencatat_riwayat(conn):
    database.sesuaikan_stok(conn, 1, 5, "restock mingguan")

    produk = database.ambil_produk(conn, 1)
    assert produk["stok"] == 24 + 5

    riwayat = database.lihat_riwayat(conn, 1)
    assert len(riwayat) == 1
    assert riwayat[0]["perubahan"] == 5
    assert riwayat[0]["keterangan"] == "restock mingguan"


def test_sesuaikan_stok_negatif_dibatalkan(conn):
    with pytest.raises(sqlite3.IntegrityError):
        database.sesuaikan_stok(conn, 1, -9999, "salah input")

    produk = database.ambil_produk(conn, 1)
    assert produk["stok"] == 24

    assert len(database.lihat_riwayat(conn, 1)) == 0


def test_sesuaikan_stok_produk_hilang(conn):
    with pytest.raises(ValueError):
        database.sesuaikan_stok(conn, 999, 1, "uji")


def test_hapus_produk_menghapus_riwayat(conn):
    database.sesuaikan_stok(conn, 1, 5, "restock")
    assert len(database.lihat_riwayat(conn, 1)) == 1

    assert database.hapus_produk(conn, 1) is True
    assert database.ambil_produk(conn, 1) is None
    assert len(database.lihat_riwayat(conn, 1)) == 0


def test_perbarui_harga(conn):
    assert database.perbarui_harga(conn, 1, 17500) is True
    assert database.ambil_produk(conn, 1)["harga"] == 17500
    assert database.perbarui_harga(conn, 999, 17500) is False


def test_cari_produk_dengan_karakter_like(conn):
    database.tambah_produk(conn, "Coklat Batang 80%", "Makanan", 12000, 10)

    hasil = database.cari_produk(conn, "%")
    assert [row["nama"] for row in hasil] == ["Coklat Batang 80%"]


def test_filter_kategori(conn):
    hasil = database.filter_kategori(conn, "Minuman")
    assert [row["nama"] for row in hasil] == ["Kopi Tubruk", "Teh Celup"]


def test_laporan_stok_menipis(conn):
    hasil = database.laporan_stok_menipis(conn)
    assert [(row["nama"], row["stok"]) for row in hasil] == [
        ("Keripik Singkong", 2),
        ("Teh Celup", 3),
        ("Pulpen Gel", 4),
    ]


def test_nilai_inventaris_per_kategori(conn):
    hasil = database.nilai_inventaris_per_kategori(conn)
    assert [(row["kategori"], row["nilai_total"]) for row in hasil] == [
        ("Minuman", 384000),
        ("Alat Tulis", 214000),
        ("Makanan", 200000),
    ]


def test_top_produk(conn):
    hasil = database.top_produk(conn, jumlah=3)
    assert [(row["nama"], row["nilai"]) for row in hasil] == [
        ("Kopi Tubruk", 360000),
        ("Buku Tulis", 200000),
        ("Roti Tawar", 180000),
    ]