"""Verify the public SQL teaching example using only in-memory SQLite."""

from pathlib import Path
import sqlite3
import unittest


class InvoiceExampleTests(unittest.TestCase):
    def setUp(self):
        self.db = sqlite3.connect(":memory:")
        self.addCleanup(self.db.close)
        self.db.executescript(Path(__file__).with_name("invoice-check.sql").read_text())

    def result(self, invoice_id):
        return self.db.execute(
            "SELECT parsed_cents, difference_cents, check_result "
            "FROM invoice_check WHERE invoice_id = ?", (invoice_id,)
        ).fetchone()

    def test_mismatch_is_visible(self):
        self.assertEqual(self.result("EXAMPLE-A"), (9700, -300, "REVIEW_DIFFERENCE"))

    def test_matching_totals(self):
        self.assertEqual(self.result("EXAMPLE-B"), (15000, 0, "TOTALS_MATCH"))

    def test_missing_lines_remain_missing(self):
        self.assertEqual(self.result("EXAMPLE-C"), (None, None, "MISSING_LINES"))

    def test_reviewed_correction_resolves_difference(self):
        self.db.execute(
            "UPDATE invoice_line SET amount_cents = 4000 "
            "WHERE invoice_id = 'EXAMPLE-A' AND line_number = 2"
        )
        self.assertEqual(self.result("EXAMPLE-A"), (10000, 0, "TOTALS_MATCH"))

    def test_duplicate_line_key_is_rejected(self):
        with self.assertRaises(sqlite3.IntegrityError):
            self.db.execute("INSERT INTO invoice_line VALUES ('EXAMPLE-A', 1, 6000)")
        self.assertEqual(self.result("EXAMPLE-A"), (9700, -300, "REVIEW_DIFFERENCE"))

    def test_orphan_line_is_rejected(self):
        with self.assertRaises(sqlite3.IntegrityError):
            self.db.execute("INSERT INTO invoice_line VALUES ('UNKNOWN', 1, 100)")

    def test_fractional_cents_are_rejected(self):
        with self.assertRaises(sqlite3.IntegrityError):
            self.db.execute("INSERT INTO invoice_line VALUES ('EXAMPLE-C', 1, 12.5)")

    def test_zero_is_not_missing(self):
        self.db.execute("INSERT INTO invoice VALUES ('ZERO', 0)")
        self.assertEqual(self.result("ZERO"), (None, None, "MISSING_LINES"))
        self.db.execute("INSERT INTO invoice_line VALUES ('ZERO', 1, 0)")
        self.assertEqual(self.result("ZERO"), (0, 0, "TOTALS_MATCH"))


if __name__ == "__main__":
    unittest.main(verbosity=2)