"""Lapisan akses data untuk aplikasi inventaris."""

import sqlite3
from pathlib import Path

NAMA_DATABASE = "inventaris.db"
BERKAS_SKEMA = Path(__file__).with_name("schema.sql")


def koneksi(db_path: str = NAMA_DATABASE) -> sqlite3.Connection:
    """Buka koneksi baru ke database inventaris."""
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA foreign_keys = ON")
    conn.row_factory = sqlite3.Row
    return conn


def inisialisasi(db_path: str = NAMA_DATABASE) -> None:
    """Terapkan schema.sql ke database. Aman dijalankan berulang."""
    conn = koneksi(db_path)
    try:
        conn.executescript(BERKAS_SKEMA.read_text(encoding="utf-8"))
        conn.commit()
    finally:
        conn.close()


def tambah_produk(nama: str, kategori: str, harga: int, stok: int,
                  db_path: str = NAMA_DATABASE) -> int:
    """Simpan produk baru, kembalikan id-nya."""
    sql = "INSERT INTO produk (nama, kategori, harga, stok) VALUES (?, ?, ?, ?)"
    conn = koneksi(db_path)
    try:
        kursor = conn.execute(sql, (nama, kategori, harga, stok))
        conn.commit()
        return int(kursor.lastrowid)
    finally:
        conn.close()


def lihat_produk(db_path: str = NAMA_DATABASE) -> list[sqlite3.Row]:
    """Ambil seluruh produk, urut berdasarkan nama."""
    conn = koneksi(db_path)
    try:
        return conn.execute("SELECT * FROM produk ORDER BY nama").fetchall()
    finally:
        conn.close()


def ambil_produk(produk_id: int,
                 db_path: str = NAMA_DATABASE) -> sqlite3.Row | None:
    """Ambil satu produk berdasarkan id; None bila tidak ada."""
    conn = koneksi(db_path)
    try:
        kursor = conn.execute("SELECT * FROM produk WHERE id = ?", (produk_id,))
        return kursor.fetchone()
    finally:
        conn.close()


def perbarui_harga(produk_id: int, harga_baru: int,
                   db_path: str = NAMA_DATABASE) -> bool:
    """Set harga produk menjadi nilai baru. False bila id tidak ada."""
    conn = koneksi(db_path)
    try:
        kursor = conn.execute(
            "UPDATE produk SET harga = ? WHERE id = ?", (harga_baru, produk_id)
        )
        conn.commit()
        return kursor.rowcount > 0
    finally:
        conn.close()


def perbarui_stok(produk_id: int, stok_baru: int,
                  db_path: str = NAMA_DATABASE) -> bool:
    """Set stok produk menjadi nilai baru. False bila id tidak ada."""
    conn = koneksi(db_path)
    try:
        kursor = conn.execute(
            "UPDATE produk SET stok = ? WHERE id = ?", (stok_baru, produk_id)
        )
        conn.commit()
        return kursor.rowcount > 0
    finally:
        conn.close()


def hapus_produk(produk_id: int, db_path: str = NAMA_DATABASE) -> bool:
    """Hapus produk beserta riwayatnya. False bila id tidak ada."""
    conn = koneksi(db_path)
    try:
        kursor = conn.execute("DELETE FROM produk WHERE id = ?", (produk_id,))
        conn.commit()
        return kursor.rowcount > 0
    finally:
        conn.close()