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

from pathlib import Path
import sqlite3
import unittest


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

    def result(self, store, month):
        return self.db.execute(
            "SELECT roas, roas_change_pct, check_result FROM roas_check WHERE store = ? AND month = ?",
            (store, month),
        ).fetchone()

    def test_roas_and_change_against_the_month_before(self):
        self.assertEqual(self.result("Store A", "2026-07"), (5.0, None, "OK"))
        self.assertEqual(self.result("Store A", "2026-08"), (4.5, -10.0, "OK"))

    def test_missing_month_is_not_compared(self):
        # Store B has no August row, so September has no month-over-month change.
        self.assertEqual(self.result("Store B", "2026-09"), (4.0, None, "OK"))

    def test_early_export_is_provisional(self):
        self.assertEqual(self.result("Store A", "2026-09"), (5.5, 22.2, "PROVISIONAL"))

    def test_low_confirmed_share_needs_a_check(self):
        # Store C: RM2,100 of RM2,600 attributed sales confirmed (81%); Store A's 96% is a normal cancellation gap.
        self.assertEqual(self.result("Store C", "2026-08"), (5.2, None, "CHECK_ATTRIBUTION"))
        self.assertEqual(self.result("Store B", "2026-07")[2], "OK")

    def test_zero_spend_has_no_roas(self):
        self.assertEqual(self.result("Store C", "2026-09"), (None, None, "NO_SPEND"))

    def test_missing_orders_are_not_zero(self):
        self.db.execute("INSERT INTO ad_month VALUES ('Store D', '2026-09', 10000, 30000, NULL, '2026-10-09')")
        self.assertEqual(self.result("Store D", "2026-09"), (3.0, None, "MISSING_ORDERS"))
        self.db.execute("UPDATE ad_month SET confirmed_cents = 0 WHERE store = 'Store D'")
        self.assertEqual(self.result("Store D", "2026-09"), (3.0, None, "CHECK_ATTRIBUTION"))

    def test_duplicate_store_month_is_rejected(self):
        with self.assertRaises(sqlite3.IntegrityError):
            self.db.execute("INSERT INTO ad_month VALUES ('Store A', '2026-08', 1, 1, 1, '2026-09-09')")

    def test_bad_month_and_fractional_cents_are_rejected(self):
        with self.assertRaises(sqlite3.IntegrityError):
            self.db.execute("INSERT INTO ad_month VALUES ('Store E', 'Sep 2026', 1, 1, 1, '2026-10-09')")
        with self.assertRaises(sqlite3.IntegrityError):
            self.db.execute("INSERT INTO ad_month VALUES ('Store E', '2026-09', 12.5, 1, 1, '2026-10-09')")


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