---

import sqlite3
from datetime import date, timedelta

def create_tables(conn):
    cursor = conn.cursor()

    cursor.execute("PRAGMA foreign_keys = ON;")

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS authors (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            last_name TEXT NOT NULL,
            first_name TEXT NOT NULL,
            patronymic TEXT,
            year_of_birth INTEGER
        )
    ''')

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS subjects (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            subject TEXT NOT NULL UNIQUE
        )
    ''')

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS books (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            subject_id INTEGER,
            book_name TEXT NOT NULL,
            annotation TEXT,
            year_of_publication INTEGER,
            publisher TEXT,
            number_of_racks INTEGER,
            number_of_books INTEGER,
            books_in_use INTEGER,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE SET NULL
        )
    ''')


    cursor.execute('''
        CREATE TABLE IF NOT EXISTS customers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            last_name TEXT NOT NULL,
            first_name TEXT NOT NULL,
            patronymic TEXT,
            date_of_birth TEXT,
            phone_number TEXT,
            home_address TEXT
        )
    ''')

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS book_author (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            book_id INTEGER,
            author_id INTEGER,
            FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
            FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE,
            UNIQUE(book_id, author_id)
        )
    ''')

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS author_subject (
            author_id INTEGER,
            subject_id INTEGER,
            PRIMARY KEY (author_id, subject_id),
            FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
        )
    ''')

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS borrows (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            book_id INTEGER,
            customer_id INTEGER,
            date_of_issue TEXT NOT NULL,
            return_date TEXT,  -- NULL означает, что книга ещё не возвращена
            FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE RESTRICT,
            FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
        )
    ''')

    conn.commit()
    print("✅ Таблицы созданы (или уже существовали).")

def insert_test_data(conn):
    cursor = conn.cursor()

    cursor.execute("DELETE FROM borrows")
    cursor.execute("DELETE FROM book_author")
    cursor.execute("DELETE FROM author_subject")
    cursor.execute("DELETE FROM books")
    cursor.execute("DELETE FROM authors")
    cursor.execute("DELETE FROM customers")
    cursor.execute("DELETE FROM subjects")
    conn.commit()

    subjects = [
        ("Программирование",),
        ("Базы данных",),
        ("Фантастика",),
        ("История",)
    ]
    cursor.executemany("INSERT INTO subjects (subject) VALUES (?)", subjects)
    conn.commit()

    cursor.execute("SELECT id, subject FROM subjects")
    subjects_dict = {row[1]: row[0] for row in cursor.fetchall()}

    authors_data = [
        ("Мартин", "Роберт", "Ц.", 1952),
        ("Кнут", "Дональд", "Э.", 1938),
        ("Лукьяненко", "Сергей", "В.", 1968),
        ("Толстой", "Лев", "Н.", 1828),
    ]
    cursor.executemany('''
        INSERT INTO authors (last_name, first_name, patronymic, year_of_birth)
        VALUES (?, ?, ?, ?)
    ''', authors_data)
    conn.commit()

    cursor.execute("SELECT id, last_name, first_name FROM authors")
    authors = []
    for row in cursor.fetchall():
        authors.append({"id": row[0], "last_name": row[1], "first_name": row[2]})

    martin_id = next(a["id"] for a in authors if a["last_name"] == "Мартин")
    knuth_id  = next(a["id"] for a in authors if a["last_name"] == "Кнут")
    luk_id    = next(a["id"] for a in authors if a["last_name"] == "Лукьяненко")
    tolstoy_id= next(a["id"] for a in authors if a["last_name"] == "Толстой")

    cursor.execute("INSERT INTO author_subject (author_id, subject_id) VALUES (?, ?)",
                   (martin_id, subjects_dict["Программирование"]))
    cursor.execute("INSERT INTO author_subject (author_id, subject_id) VALUES (?, ?)",
                   (martin_id, subjects_dict["Базы данных"]))
    cursor.execute("INSERT INTO author_subject (author_id, subject_id) VALUES (?, ?)",
                   (knuth_id, subjects_dict["Программирование"]))
    cursor.execute("INSERT INTO author_subject (author_id, subject_id) VALUES (?, ?)",
                   (luk_id, subjects_dict["Фантастика"]))
    cursor.execute("INSERT INTO author_subject (author_id, subject_id) VALUES (?, ?)",
                   (tolstoy_id, subjects_dict["История"]))
    conn.commit()

    books_data = [
        ("Чистая архитектура", "О проектировании программных систем", 2017,
         "Питер", 1, 5, 0, subjects_dict["Программирование"]),
        ("Искусство программирования, т.1", "Фундаментальный труд", 1968,
         "Вильямс", 2, 3, 0, subjects_dict["Программирование"]),
        ("Дозоры", "Серия городского фэнтези", 1998,
         "АСТ", 1, 10, 0, subjects_dict["Фантастика"]),
        ("Война и мир", "Роман-эпопея", 1869,
         "Русский вестник", 3, 8, 0, subjects_dict["История"]),
    ]
    cursor.executemany('''
        INSERT INTO books (book_name, annotation, year_of_publication, publisher,
                           number_of_racks, number_of_books, books_in_use, subject_id)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    ''', books_data)
    conn.commit()

    cursor.execute("SELECT id, book_name FROM books")
    books = {row[1]: row[0] for row in cursor.fetchall()}

    book_author_data = [
        (books["Чистая архитектура"], martin_id),
        (books["Искусство программирования, т.1"], knuth_id),
        (books["Дозоры"], luk_id),
        (books["Война и мир"], tolstoy_id),
    ]
    cursor.executemany("INSERT INTO book_author (book_id, author_id) VALUES (?, ?)", book_author_data)
    conn.commit()

    customers_data = [
        ("Иванов", "Иван", "Иванович", "1990-05-12", "+7 123 456-78-90", "ул. Пушкина, д.10"),
        ("Петрова", "Анна", "Сергеевна", "1985-08-24", "+7 987 654-32-10", "пр. Ленина, 5"),
    ]
    cursor.executemany('''
        INSERT INTO customers (last_name, first_name, patronymic, date_of_birth, phone_number, home_address)
        VALUES (?, ?, ?, ?, ?, ?)
    ''', customers_data)
    conn.commit()

    cursor.execute("SELECT id, last_name FROM customers")
    customers = {row[1]: row[0] for row in cursor.fetchall()}

    today = date.today()
    borrows_data = [
        (books["Чистая архитектура"], customers["Иванов"], today - timedelta(days=5), None),        # не вернули
        (books["Искусство программирования, т.1"], customers["Петрова"], today - timedelta(days=2), today + timedelta(days=12)),
        (books["Дозоры"], customers["Иванов"], today - timedelta(days=30), today - timedelta(days=5)),  # уже вернул
    ]
    cursor.executemany('''
        INSERT INTO borrows (book_id, customer_id, date_of_issue, return_date)
        VALUES (?, ?, ?, ?)
    ''', borrows_data)
    conn.commit()

    cursor.execute('''
        UPDATE books SET books_in_use = (
            SELECT COUNT(*) FROM borrows
            WHERE borrows.book_id = books.id AND borrows.return_date IS NULL
        )
    ''')
    conn.commit()

    print("✅ Тестовые данные добавлены.")

def example_queries(conn):
    cursor = conn.cursor()

    print("\n1. Все книги с авторами и тематиками:")
    cursor.execute('''
        SELECT b.book_name, 
               GROUP_CONCAT(a.last_name || ' ' || a.first_name, ', ') AS authors,
               s.subject
        FROM books b
        LEFT JOIN book_author ba ON b.id = ba.book_id
        LEFT JOIN authors a ON ba.author_id = a.id
        LEFT JOIN subjects s ON b.subject_id = s.id
        GROUP BY b.id
        ORDER BY b.book_name
    ''')
    for row in cursor.fetchall():
        print(f"   {row[0]} | Авторы: {row[1] if row[1] else '—'} | Тема: {row[2]}")

    print("\n2. Читатели и книги, которые они не вернули:")
    cursor.execute('''
        SELECT c.last_name || ' ' || c.first_name AS reader,
               GROUP_CONCAT(b.book_name, ', ') AS books_on_hand
        FROM customers c
        JOIN borrows br ON c.id = br.customer_id
        JOIN books b ON br.book_id = b.id
        WHERE br.return_date IS NULL
        GROUP BY c.id
    ''')
    rows = cursor.fetchall()
    if rows:
        for row in rows:
            print(f"   {row[0]}: {row[1]}")
    else:
        print("   (нет читателей с книгами на руках)")

    print("\n3. Книги, у которых истёк срок возврата (если return_date уже прошла, но книга не возвращена):")
    today_str = date.today().isoformat()
    cursor.execute('''
        SELECT b.book_name, c.last_name || ' ' || c.first_name AS reader,
               br.return_date
        FROM borrows br
        JOIN books b ON br.book_id = b.id
        JOIN customers c ON br.customer_id = c.id
        WHERE br.return_date IS NOT NULL 
          AND br.return_date < ? 
          AND br.return_date NOT NULL
    ''', (today_str,))
    overdue = cursor.fetchall()
    if overdue:
        for row in overdue:
            print(f"   {row[0]} | Читатель: {row[1]} | срок был {row[2]}")
    else:
        print("   (просроченных нет)")

    print("\n 4. Статистика: количество экземпляров книг по тематикам:")
    cursor.execute('''
        SELECT s.subject, SUM(b.number_of_books) AS total_books
        FROM subjects s
        LEFT JOIN books b ON s.id = b.subject_id
        GROUP BY s.id
        ORDER BY total_books DESC
    ''')
    for row in cursor.fetchall():
        print(f"   {row[0]}: {row[1] if row[1] else 0} экз.")

    print("\n 5. Топ-3 автора по количеству книг в каталоге:")
    cursor.execute('''
        SELECT a.last_name, a.first_name, COUNT(b.id) AS book_count
        FROM authors a
        LEFT JOIN book_author ba ON a.id = ba.author_id
        LEFT JOIN books b ON ba.book_id = b.id
        GROUP BY a.id
        ORDER BY book_count DESC
        LIMIT 3
    ''')
    for row in cursor.fetchall():
        print(f"   {row[0]} {row[1]} — {row[2]} книг")

def main():
    conn = sqlite3.connect("library.db")
    conn.row_factory = sqlite3.Row

    try:
        create_tables(conn)
        insert_test_data(conn)
        example_queries(conn)
    except Exception as e:
        print("Ошибка:", e)
        conn.rollback()
    finally:
        conn.close()
        print("\n Работа завершена. База данных 'library.db' сохранена.")

if __name__ == "__main__":
    main()