"""College KT store: add colleges with their KT details, then search by name."""
import argparse
import sqlite3
import sys
from pathlib import Path

DB_PATH = Path(__file__).with_name("colleges.db")


def connect():
    conn = sqlite3.connect(DB_PATH)
    conn.execute("PRAGMA foreign_keys = ON")
    conn.executescript(
        """
        CREATE TABLE IF NOT EXISTS colleges (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE COLLATE NOCASE
        );
        CREATE TABLE IF NOT EXISTS kts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            college_id INTEGER NOT NULL REFERENCES colleges(id) ON DELETE CASCADE,
            title TEXT NOT NULL DEFAULT '',
            text TEXT NOT NULL
        );
        """
    )
    if "title" not in [r[1] for r in conn.execute("PRAGMA table_info(kts)")]:
        conn.execute("ALTER TABLE kts ADD COLUMN title TEXT NOT NULL DEFAULT ''")
    return conn


def add_college(name, kt):
    """Add the college if new, then attach the KT to it."""
    with connect() as conn:
        conn.execute("INSERT OR IGNORE INTO colleges (name) VALUES (?)", (name.strip(),))
        cid = conn.execute("SELECT id FROM colleges WHERE name = ?", (name.strip(),)).fetchone()[0]
        conn.execute("INSERT INTO kts (college_id, text) VALUES (?, ?)", (cid, kt.strip()))


def _rows(where, params):
    with connect() as conn:
        return conn.execute(
            "SELECT c.name, group_concat(k.text, char(10) || '       ') "
            "FROM colleges c LEFT JOIN kts k ON k.college_id = c.id "
            f"{where} GROUP BY c.id ORDER BY c.name",
            params,
        ).fetchall()


def search_colleges(query):
    return _rows("WHERE c.name LIKE ?", (f"%{query.strip()}%",))


def list_colleges():
    return _rows("", ())


def delete_college(name):
    with connect() as conn:
        return conn.execute("DELETE FROM colleges WHERE name = ?", (name.strip(),)).rowcount


def show(rows):
    if not rows:
        print("No matching college found.")
        return
    for name, kt in rows:
        print(f"\nCollege: {name}\nKT     : {kt}")


def interactive():
    menu = "\n1) Add college  2) Search college  3) List all  4) Delete  5) Exit"
    while True:
        print(menu)
        choice = input("Choose: ").strip()
        if choice == "1":
            name = input("College name: ").strip()
            kt = input("College KT: ").strip()
            if name and kt:
                add_college(name, kt)
                print("Saved.")
            else:
                print("Both name and KT are required.")
        elif choice == "2":
            show(search_colleges(input("Search college: ")))
        elif choice == "3":
            show(list_colleges())
        elif choice == "4":
            n = delete_college(input("Exact college name to delete: "))
            print("Deleted." if n else "Not found.")
        elif choice == "5":
            break


def main():
    p = argparse.ArgumentParser(description="Store and search college KT details.")
    sub = p.add_subparsers(dest="cmd")
    a = sub.add_parser("add", help="add or update a college")
    a.add_argument("name")
    a.add_argument("kt")
    s = sub.add_parser("search", help="search colleges by name (partial match)")
    s.add_argument("query")
    sub.add_parser("list", help="list all colleges")
    d = sub.add_parser("delete", help="delete a college by exact name")
    d.add_argument("name")
    args = p.parse_args()

    if args.cmd == "add":
        add_college(args.name, args.kt)
        print("Saved.")
    elif args.cmd == "search":
        show(search_colleges(args.query))
    elif args.cmd == "list":
        show(list_colleges())
    elif args.cmd == "delete":
        print("Deleted." if delete_college(args.name) else "Not found.")
    else:
        interactive()


if __name__ == "__main__":
    sys.exit(main())