#!/usr/bin/env python3

from __future__ import annotations

import os
import sys

try:
    import pymysql
except ModuleNotFoundError as exc:  # pragma: no cover
    raise SystemExit(
        "PyMySQL is required. Run: python3 -m pip install pymysql"
    ) from exc


def connect(database: str):
    return pymysql.connect(
        host=os.getenv("MYSQL_HOST", "127.0.0.1"),
        port=int(os.getenv("MYSQL_PORT", "3306")),
        user=os.getenv("MYSQL_USER", "root"),
        password=os.getenv("MYSQL_PASSWORD", "123456"),
        database=database,
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
    )


def print_rows(title: str, rows: list[dict]) -> None:
    print(f"\n{title}")
    print("-" * len(title))
    if not rows:
        print("No rows returned.")
        return
    for row in rows:
        print(row)


def query_student_course(student_no: str) -> None:
    sql = """
        SELECT
            s.s_no,
            s.s_name,
            c.c_name,
            sc.semester,
            sc.grade
        FROM student AS s
        JOIN sc ON s.s_no = sc.s_no
        JOIN course AS c ON sc.c_no = c.c_no
        WHERE s.s_no = %s
        ORDER BY c.c_no
    """
    with connect("StudentCourse") as conn:
        with conn.cursor() as cursor:
            cursor.execute(sql, (student_no,))
            print_rows("StudentCourse query result", cursor.fetchall())


def query_supplier_by_city(city: str) -> None:
    sql = """
        SELECT SNO, SNAME, STATUS, CITY
        FROM S
        WHERE CITY = %s
        ORDER BY SNO
    """
    with connect("SPJ") as conn:
        with conn.cursor() as cursor:
            cursor.execute(sql, (city,))
            print_rows("SPJ query result", cursor.fetchall())


def main() -> int:
    query_student_course("20240001")
    query_supplier_by_city("上海")
    return 0


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