"""Tests on Lab 04's made-up shop database (rebuilt below) and the SQL gpt-5.4-nano actually wrote
in the recorded runs. Run: pytest test_sql_practice.py"""
import os
import sqlite3

from sql_practice import is_safe, run_read_only, same_result, unknown_values

SHOP = 'CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT, signup_date TEXT);\nINSERT INTO "customers" VALUES(1,\'Ana\',\'Germany\',\'2024-09-23\');\nINSERT INTO "customers" VALUES(2,\'Ben\',\'France\',\'2025-03-09\');\nINSERT INTO "customers" VALUES(3,\'Chloe\',\'France\',\'2025-01-15\');\nINSERT INTO "customers" VALUES(4,\'Dev\',\'Canada\',\'2024-09-13\');\nINSERT INTO "customers" VALUES(5,\'Emil\',\'Germany\',\'2025-12-11\');\nINSERT INTO "customers" VALUES(6,\'Fatima\',\'Germany\',\'2025-08-07\');\nINSERT INTO "customers" VALUES(7,\'Goran\',\'Germany\',\'2024-07-01\');\nINSERT INTO "customers" VALUES(8,\'Hana\',\'France\',\'2025-01-10\');\nINSERT INTO "customers" VALUES(9,\'Ivan\',\'UK\',\'2025-10-31\');\nINSERT INTO "customers" VALUES(10,\'Julia\',\'UK\',\'2024-06-28\');\nINSERT INTO "customers" VALUES(11,\'Kofi\',\'Canada\',\'2024-12-21\');\nINSERT INTO "customers" VALUES(12,\'Lena\',\'USA\',\'2025-12-11\');\nINSERT INTO "customers" VALUES(13,\'Marco\',\'USA\',\'2025-01-12\');\nINSERT INTO "customers" VALUES(14,\'Nadia\',\'Germany\',\'2025-03-12\');\nINSERT INTO "customers" VALUES(15,\'Omar\',\'Canada\',\'2024-11-11\');\nINSERT INTO "customers" VALUES(16,\'Priya\',\'USA\',\'2025-08-07\');\nINSERT INTO "customers" VALUES(17,\'Quinn\',\'France\',\'2025-03-12\');\nINSERT INTO "customers" VALUES(18,\'Rosa\',\'USA\',\'2025-01-07\');\nINSERT INTO "customers" VALUES(19,\'Sven\',\'Germany\',\'2024-09-13\');\nINSERT INTO "customers" VALUES(20,\'Tara\',\'Germany\',\'2025-06-25\');\nCREATE TABLE order_items (order_id INTEGER REFERENCES orders(id),\n                          product_id INTEGER REFERENCES products(id), quantity INTEGER);\nINSERT INTO "order_items" VALUES(1,8,2);\nINSERT INTO "order_items" VALUES(1,9,1);\nINSERT INTO "order_items" VALUES(1,2,3);\nINSERT INTO "order_items" VALUES(2,6,3);\nINSERT INTO "order_items" VALUES(2,10,1);\nINSERT INTO "order_items" VALUES(2,4,1);\nINSERT INTO "order_items" VALUES(3,4,1);\nINSERT INTO "order_items" VALUES(4,6,3);\nINSERT INTO "order_items" VALUES(4,4,2);\nINSERT INTO "order_items" VALUES(5,3,1);\nINSERT INTO "order_items" VALUES(5,9,2);\nINSERT INTO "order_items" VALUES(5,4,2);\nINSERT INTO "order_items" VALUES(6,9,1);\nINSERT INTO "order_items" VALUES(6,4,1);\nINSERT INTO "order_items" VALUES(6,6,1);\nINSERT INTO "order_items" VALUES(7,2,3);\nINSERT INTO "order_items" VALUES(7,4,3);\nINSERT INTO "order_items" VALUES(8,8,1);\nINSERT INTO "order_items" VALUES(8,3,1);\nINSERT INTO "order_items" VALUES(8,5,3);\nINSERT INTO "order_items" VALUES(9,10,3);\nINSERT INTO "order_items" VALUES(9,7,2);\nINSERT INTO "order_items" VALUES(10,9,2);\nINSERT INTO "order_items" VALUES(11,3,3);\nINSERT INTO "order_items" VALUES(12,10,2);\nINSERT INTO "order_items" VALUES(12,2,2);\nINSERT INTO "order_items" VALUES(13,9,3);\nINSERT INTO "order_items" VALUES(13,1,3);\nINSERT INTO "order_items" VALUES(14,7,2);\nINSERT INTO "order_items" VALUES(14,3,1);\nINSERT INTO "order_items" VALUES(15,9,1);\nINSERT INTO "order_items" VALUES(16,10,2);\nINSERT INTO "order_items" VALUES(16,4,1);\nINSERT INTO "order_items" VALUES(16,3,3);\nINSERT INTO "order_items" VALUES(17,8,1);\nINSERT INTO "order_items" VALUES(17,1,2);\nINSERT INTO "order_items" VALUES(18,4,3);\nINSERT INTO "order_items" VALUES(19,8,1);\nINSERT INTO "order_items" VALUES(19,2,1);\nINSERT INTO "order_items" VALUES(19,9,3);\nINSERT INTO "order_items" VALUES(20,5,3);\nINSERT INTO "order_items" VALUES(21,4,3);\nINSERT INTO "order_items" VALUES(21,5,3);\nINSERT INTO "order_items" VALUES(21,7,2);\nINSERT INTO "order_items" VALUES(22,4,1);\nINSERT INTO "order_items" VALUES(23,9,1);\nINSERT INTO "order_items" VALUES(23,4,1);\nINSERT INTO "order_items" VALUES(23,10,3);\nINSERT INTO "order_items" VALUES(24,1,2);\nINSERT INTO "order_items" VALUES(25,5,3);\nINSERT INTO "order_items" VALUES(26,3,1);\nINSERT INTO "order_items" VALUES(26,10,2);\nINSERT INTO "order_items" VALUES(26,8,2);\nINSERT INTO "order_items" VALUES(27,7,3);\nINSERT INTO "order_items" VALUES(27,8,1);\nINSERT INTO "order_items" VALUES(28,6,1);\nINSERT INTO "order_items" VALUES(28,2,1);\nINSERT INTO "order_items" VALUES(29,3,1);\nINSERT INTO "order_items" VALUES(29,7,2);\nINSERT INTO "order_items" VALUES(30,8,3);\nINSERT INTO "order_items" VALUES(31,9,1);\nINSERT INTO "order_items" VALUES(31,1,1);\nINSERT INTO "order_items" VALUES(31,2,2);\nINSERT INTO "order_items" VALUES(32,7,1);\nINSERT INTO "order_items" VALUES(33,7,2);\nINSERT INTO "order_items" VALUES(34,9,1);\nINSERT INTO "order_items" VALUES(34,8,2);\nINSERT INTO "order_items" VALUES(34,3,1);\nINSERT INTO "order_items" VALUES(35,9,1);\nINSERT INTO "order_items" VALUES(35,1,1);\nINSERT INTO "order_items" VALUES(35,6,3);\nINSERT INTO "order_items" VALUES(36,3,1);\nINSERT INTO "order_items" VALUES(36,1,1);\nINSERT INTO "order_items" VALUES(36,9,1);\nINSERT INTO "order_items" VALUES(37,7,1);\nINSERT INTO "order_items" VALUES(38,1,2);\nINSERT INTO "order_items" VALUES(38,10,3);\nINSERT INTO "order_items" VALUES(38,2,3);\nINSERT INTO "order_items" VALUES(39,5,3);\nINSERT INTO "order_items" VALUES(39,4,3);\nINSERT INTO "order_items" VALUES(40,7,3);\nINSERT INTO "order_items" VALUES(40,3,3);\nINSERT INTO "order_items" VALUES(41,2,2);\nINSERT INTO "order_items" VALUES(41,1,3);\nINSERT INTO "order_items" VALUES(42,9,1);\nINSERT INTO "order_items" VALUES(43,6,2);\nINSERT INTO "order_items" VALUES(44,5,1);\nINSERT INTO "order_items" VALUES(44,10,3);\nINSERT INTO "order_items" VALUES(44,9,3);\nINSERT INTO "order_items" VALUES(45,2,3);\nINSERT INTO "order_items" VALUES(45,11,3);\nINSERT INTO "order_items" VALUES(46,10,3);\nINSERT INTO "order_items" VALUES(46,4,2);\nINSERT INTO "order_items" VALUES(47,5,2);\nINSERT INTO "order_items" VALUES(47,9,1);\nINSERT INTO "order_items" VALUES(47,8,1);\nINSERT INTO "order_items" VALUES(48,1,2);\nINSERT INTO "order_items" VALUES(49,3,3);\nINSERT INTO "order_items" VALUES(49,8,3);\nINSERT INTO "order_items" VALUES(50,2,1);\nINSERT INTO "order_items" VALUES(51,6,3);\nINSERT INTO "order_items" VALUES(52,3,2);\nINSERT INTO "order_items" VALUES(52,1,2);\nINSERT INTO "order_items" VALUES(53,11,1);\nINSERT INTO "order_items" VALUES(54,7,1);\nINSERT INTO "order_items" VALUES(54,10,1);\nINSERT INTO "order_items" VALUES(54,3,1);\nINSERT INTO "order_items" VALUES(55,6,2);\nINSERT INTO "order_items" VALUES(56,2,2);\nINSERT INTO "order_items" VALUES(57,6,1);\nINSERT INTO "order_items" VALUES(57,5,1);\nINSERT INTO "order_items" VALUES(58,7,2);\nINSERT INTO "order_items" VALUES(59,6,2);\nINSERT INTO "order_items" VALUES(59,9,3);\nINSERT INTO "order_items" VALUES(60,2,2);\nINSERT INTO "order_items" VALUES(61,7,2);\nINSERT INTO "order_items" VALUES(61,6,3);\nINSERT INTO "order_items" VALUES(61,10,3);\nINSERT INTO "order_items" VALUES(62,4,3);\nINSERT INTO "order_items" VALUES(62,5,2);\nINSERT INTO "order_items" VALUES(62,1,1);\nINSERT INTO "order_items" VALUES(63,11,2);\nINSERT INTO "order_items" VALUES(63,4,1);\nINSERT INTO "order_items" VALUES(63,6,3);\nINSERT INTO "order_items" VALUES(64,5,3);\nINSERT INTO "order_items" VALUES(65,6,3);\nINSERT INTO "order_items" VALUES(65,7,2);\nINSERT INTO "order_items" VALUES(66,7,3);\nINSERT INTO "order_items" VALUES(67,3,2);\nINSERT INTO "order_items" VALUES(67,10,3);\nINSERT INTO "order_items" VALUES(67,5,1);\nINSERT INTO "order_items" VALUES(68,7,3);\nINSERT INTO "order_items" VALUES(69,11,3);\nINSERT INTO "order_items" VALUES(69,4,2);\nINSERT INTO "order_items" VALUES(70,5,3);\nINSERT INTO "order_items" VALUES(71,11,2);\nINSERT INTO "order_items" VALUES(72,4,2);\nINSERT INTO "order_items" VALUES(73,11,1);\nINSERT INTO "order_items" VALUES(73,10,3);\nINSERT INTO "order_items" VALUES(74,11,3);\nINSERT INTO "order_items" VALUES(75,4,3);\nINSERT INTO "order_items" VALUES(75,3,3);\nINSERT INTO "order_items" VALUES(76,4,1);\nINSERT INTO "order_items" VALUES(76,2,2);\nINSERT INTO "order_items" VALUES(76,8,3);\nINSERT INTO "order_items" VALUES(77,6,2);\nINSERT INTO "order_items" VALUES(77,8,3);\nINSERT INTO "order_items" VALUES(77,9,2);\nCREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),\n                     order_date TEXT, status TEXT);\nINSERT INTO "orders" VALUES(1,1,\'2025-02-06\',\'delivered\');\nINSERT INTO "orders" VALUES(2,1,\'2025-02-21\',\'cancelled\');\nINSERT INTO "orders" VALUES(3,1,\'2025-01-18\',\'delivered\');\nINSERT INTO "orders" VALUES(4,2,\'2025-09-12\',\'delivered\');\nINSERT INTO "orders" VALUES(5,2,\'2025-04-15\',\'pending\');\nINSERT INTO "orders" VALUES(6,2,\'2025-07-26\',\'cancelled\');\nINSERT INTO "orders" VALUES(7,2,\'2025-08-18\',\'shipped\');\nINSERT INTO "orders" VALUES(8,3,\'2025-09-28\',\'shipped\');\nINSERT INTO "orders" VALUES(9,3,\'2025-10-30\',\'pending\');\nINSERT INTO "orders" VALUES(10,3,\'2025-07-20\',\'delivered\');\nINSERT INTO "orders" VALUES(11,3,\'2025-03-03\',\'delivered\');\nINSERT INTO "orders" VALUES(12,3,\'2025-04-07\',\'cancelled\');\nINSERT INTO "orders" VALUES(13,3,\'2025-09-12\',\'pending\');\nINSERT INTO "orders" VALUES(14,5,\'2026-06-04\',\'delivered\');\nINSERT INTO "orders" VALUES(15,5,\'2026-04-25\',\'pending\');\nINSERT INTO "orders" VALUES(16,5,\'2026-05-13\',\'cancelled\');\nINSERT INTO "orders" VALUES(17,6,\'2025-08-08\',\'pending\');\nINSERT INTO "orders" VALUES(18,6,\'2026-01-12\',\'delivered\');\nINSERT INTO "orders" VALUES(19,6,\'2025-09-17\',\'delivered\');\nINSERT INTO "orders" VALUES(20,6,\'2026-04-08\',\'pending\');\nINSERT INTO "orders" VALUES(21,6,\'2026-03-12\',\'delivered\');\nINSERT INTO "orders" VALUES(22,7,\'2025-02-18\',\'delivered\');\nINSERT INTO "orders" VALUES(23,7,\'2024-12-22\',\'delivered\');\nINSERT INTO "orders" VALUES(24,7,\'2024-08-01\',\'delivered\');\nINSERT INTO "orders" VALUES(25,7,\'2024-08-07\',\'pending\');\nINSERT INTO "orders" VALUES(26,7,\'2025-03-07\',\'delivered\');\nINSERT INTO "orders" VALUES(27,8,\'2025-08-19\',\'delivered\');\nINSERT INTO "orders" VALUES(28,9,\'2025-12-21\',\'delivered\');\nINSERT INTO "orders" VALUES(29,9,\'2026-02-06\',\'pending\');\nINSERT INTO "orders" VALUES(30,9,\'2026-06-25\',\'delivered\');\nINSERT INTO "orders" VALUES(31,9,\'2025-12-21\',\'delivered\');\nINSERT INTO "orders" VALUES(32,9,\'2026-07-07\',\'shipped\');\nINSERT INTO "orders" VALUES(33,9,\'2026-01-24\',\'shipped\');\nINSERT INTO "orders" VALUES(34,10,\'2024-11-22\',\'shipped\');\nINSERT INTO "orders" VALUES(35,10,\'2024-07-28\',\'pending\');\nINSERT INTO "orders" VALUES(36,10,\'2025-02-28\',\'pending\');\nINSERT INTO "orders" VALUES(37,10,\'2024-08-02\',\'cancelled\');\nINSERT INTO "orders" VALUES(38,11,\'2025-04-27\',\'pending\');\nINSERT INTO "orders" VALUES(39,11,\'2025-10-07\',\'pending\');\nINSERT INTO "orders" VALUES(40,11,\'2025-05-31\',\'delivered\');\nINSERT INTO "orders" VALUES(41,11,\'2025-05-24\',\'shipped\');\nINSERT INTO "orders" VALUES(42,11,\'2025-10-06\',\'delivered\');\nINSERT INTO "orders" VALUES(43,12,\'2026-06-08\',\'delivered\');\nINSERT INTO "orders" VALUES(44,12,\'2026-03-02\',\'shipped\');\nINSERT INTO "orders" VALUES(45,13,\'2025-03-07\',\'delivered\');\nINSERT INTO "orders" VALUES(46,13,\'2025-04-02\',\'delivered\');\nINSERT INTO "orders" VALUES(47,13,\'2025-04-27\',\'cancelled\');\nINSERT INTO "orders" VALUES(48,13,\'2025-08-17\',\'delivered\');\nINSERT INTO "orders" VALUES(49,13,\'2025-03-20\',\'cancelled\');\nINSERT INTO "orders" VALUES(50,13,\'2025-08-19\',\'pending\');\nINSERT INTO "orders" VALUES(51,14,\'2025-05-28\',\'pending\');\nINSERT INTO "orders" VALUES(52,14,\'2025-12-20\',\'delivered\');\nINSERT INTO "orders" VALUES(53,14,\'2025-04-02\',\'delivered\');\nINSERT INTO "orders" VALUES(54,14,\'2025-05-04\',\'delivered\');\nINSERT INTO "orders" VALUES(55,14,\'2025-10-10\',\'delivered\');\nINSERT INTO "orders" VALUES(56,14,\'2025-07-18\',\'delivered\');\nINSERT INTO "orders" VALUES(57,15,\'2025-03-05\',\'delivered\');\nINSERT INTO "orders" VALUES(58,15,\'2024-11-24\',\'cancelled\');\nINSERT INTO "orders" VALUES(59,15,\'2025-04-03\',\'delivered\');\nINSERT INTO "orders" VALUES(60,15,\'2025-08-13\',\'delivered\');\nINSERT INTO "orders" VALUES(61,16,\'2025-08-27\',\'delivered\');\nINSERT INTO "orders" VALUES(62,16,\'2025-10-06\',\'shipped\');\nINSERT INTO "orders" VALUES(63,16,\'2026-05-01\',\'pending\');\nINSERT INTO "orders" VALUES(64,17,\'2025-08-20\',\'cancelled\');\nINSERT INTO "orders" VALUES(65,17,\'2025-08-18\',\'cancelled\');\nINSERT INTO "orders" VALUES(66,17,\'2025-12-21\',\'delivered\');\nINSERT INTO "orders" VALUES(67,17,\'2025-09-23\',\'cancelled\');\nINSERT INTO "orders" VALUES(68,17,\'2025-08-15\',\'delivered\');\nINSERT INTO "orders" VALUES(69,18,\'2025-09-03\',\'shipped\');\nINSERT INTO "orders" VALUES(70,18,\'2025-04-04\',\'cancelled\');\nINSERT INTO "orders" VALUES(71,18,\'2025-06-28\',\'delivered\');\nINSERT INTO "orders" VALUES(72,19,\'2024-11-28\',\'delivered\');\nINSERT INTO "orders" VALUES(73,19,\'2024-10-21\',\'shipped\');\nINSERT INTO "orders" VALUES(74,20,\'2026-01-16\',\'delivered\');\nINSERT INTO "orders" VALUES(75,20,\'2025-06-28\',\'delivered\');\nINSERT INTO "orders" VALUES(76,20,\'2026-02-18\',\'delivered\');\nINSERT INTO "orders" VALUES(77,20,\'2026-03-24\',\'pending\');\nCREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL);\nINSERT INTO "products" VALUES(1,\'Mechanical Keyboard\',\'accessories\',129.0);\nINSERT INTO "products" VALUES(2,\'Wireless Mouse\',\'accessories\',39.0);\nINSERT INTO "products" VALUES(3,\'USB-C Hub\',\'accessories\',59.0);\nINSERT INTO "products" VALUES(4,\'27-inch Monitor\',\'displays\',349.0);\nINSERT INTO "products" VALUES(5,\'34-inch Ultrawide\',\'displays\',649.0);\nINSERT INTO "products" VALUES(6,\'Laptop Stand\',\'furniture\',45.0);\nINSERT INTO "products" VALUES(7,\'Standing Desk\',\'furniture\',499.0);\nINSERT INTO "products" VALUES(8,\'Noise-Cancelling Headphones\',\'audio\',279.0);\nINSERT INTO "products" VALUES(9,\'Webcam 4K\',\'video\',159.0);\nINSERT INTO "products" VALUES(10,\'Desk Lamp\',\'furniture\',35.0);\nINSERT INTO "products" VALUES(11,\'Ergonomic Chair\',\'furniture\',399.0);'

REVENUE_LAYOUT_ONLY = "SELECT\n  COALESCE(SUM(oi.quantity * p.price), 0) AS total_revenue_2025\nFROM orders o\nJOIN order_items oi ON oi.order_id = o.id\nJOIN products p ON p.id = oi.product_id\nWHERE o.status IN ('completed', 'shipped', 'paid')\n  AND o.order_date >= '2025-01-01'\n  AND o.order_date < '2026-01-01'"
REVENUE_WITH_NOTES = "SELECT\n  COALESCE(SUM(oi.quantity * p.price), 0) AS total_revenue_2025\nFROM orders o\nJOIN order_items oi ON oi.order_id = o.id\nJOIN products p ON p.id = oi.product_id\nWHERE o.status IN ('shipped', 'delivered')\n  AND o.order_date >= '2025-01-01'\n  AND o.order_date < '2026-01-01'"
REVENUE_RULE_IN_FULL = "SELECT\n  COALESCE(SUM(oi.quantity * p.price), 0) AS total_revenue_2025\nFROM orders o\nJOIN order_items oi ON oi.order_id = o.id\nJOIN products p ON p.id = oi.product_id\nWHERE o.status IN ('pending', 'shipped', 'delivered')\n  AND o.order_date >= '2025-01-01'\n  AND o.order_date < '2026-01-01'"
KEYBOARDS_LAYOUT_ONLY = "SELECT SUM(oi.quantity) AS mechanical_keyboards_sold\nFROM order_items oi\nJOIN products p ON p.id = oi.product_id\nJOIN orders o ON o.id = oi.order_id\nWHERE p.category = 'Mechanical Keyboards'\n  AND o.status IN ('completed', 'shipped', 'delivered')"
MOST_ORDERS = 'SELECT\n  c.id,\n  c.name,\n  COUNT(o.id) AS order_count\nFROM customers c\nJOIN orders o ON o.customer_id = c.id\nGROUP BY c.id, c.name\nORDER BY order_count DESC, c.id\nLIMIT 1'


def shop():
    """A fresh copy of the shop database on disk, and a normal (writable) connection to it."""
    path = os.path.abspath("shop_practice.db")
    if os.path.exists(path):
        os.remove(path)
    db = sqlite3.connect(path)
    db.executescript(SHOP)
    db.commit()
    return path, db


def test_gate_1_only_select():
    assert is_safe("SELECT COUNT(*) FROM customers") == (True, "ok")
    assert is_safe("WITH t AS (SELECT 1) SELECT * FROM t;") == (True, "ok")
    assert is_safe("DELETE FROM orders\nWHERE status = 'cancelled'") == (False, "not a SELECT query")
    assert is_safe("UPDATE products\nSET price = 0") == (False, "not a SELECT query")
    assert is_safe("SELECT 1; DROP TABLE customers") == (False, "more than one statement")
    assert is_safe("SELECT * FROM orders WHERE 1 = 1 OR DELETE") == (False, "forbidden keyword: DELETE")


def test_grade_by_rows_not_text():
    path, db = shop()
    gold = [(35762.0,)]
    assert db.execute(REVENUE_LAYOUT_ONLY).fetchall() == [(8283.0,)]
    assert not same_result(gold, db.execute(REVENUE_LAYOUT_ONLY).fetchall())
    assert db.execute(REVENUE_WITH_NOTES).fetchall() == [(28188.0,)]
    assert same_result(gold, db.execute(REVENUE_RULE_IN_FULL).fetchall())
    # extra columns are fine; a LIMIT 1 that hides a four-way tie is not
    assert same_result([("Chloe",)], [(3, "Chloe", 6)])
    assert not same_result([("Chloe",), ("Ivan",), ("Marco",), ("Nadia",)], db.execute(MOST_ORDERS).fetchall())


def test_values_the_layout_cannot_show():
    path, db = shop()
    assert unknown_values(db, REVENUE_LAYOUT_ONLY) == ["completed", "paid"]
    assert unknown_values(db, KEYBOARDS_LAYOUT_ONLY) == ["Mechanical Keyboards", "completed"]
    assert unknown_values(db, REVENUE_RULE_IN_FULL) == []


def test_gate_2_read_only_connection():
    path, db = shop()
    assert run_read_only(path, "SELECT COUNT(*) FROM customers WHERE country = 'Germany'") == [(7,)]
    try:
        run_read_only(path, "DELETE FROM orders WHERE status = 'cancelled'")
    except sqlite3.OperationalError as e:
        assert "readonly" in str(e)
    else:
        assert False, "a DELETE on the read-only connection must fail"
    assert db.execute("SELECT COUNT(*) FROM orders").fetchone()[0] == 77