data/database.py

import sqlite3
from pathlib import Path


PROJECT_DIR = Path(__file__).resolve().parent
DATA_DIR = PROJECT_DIR / "data"
DATABASE_PATH = DATA_DIR / "bookstore.db"


def get_connection() -> sqlite3.Connection:
    """
    SQLite veritabanı bağlantısı oluşturur.

    row_factory sayesinde sorgu sonuçlarına hem indeksle
    hem de kolon adıyla erişilebilir.
    """

    DATA_DIR.mkdir(parents=True, exist_ok=True)

    connection = sqlite3.connect(DATABASE_PATH)
    connection.row_factory = sqlite3.Row

    # SQLite'ta foreign key kontrolleri bağlantı başına açılır.
    connection.execute("PRAGMA foreign_keys = ON")

    return connection


def initialize_database() -> None:
    """Kitap ve sipariş tablolarını oluşturur."""

    with get_connection() as connection:
        connection.executescript(
            """
            CREATE TABLE IF NOT EXISTS books (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                author TEXT NOT NULL,
                category TEXT NOT NULL,
                price REAL NOT NULL CHECK (price >= 0),
                stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,

                UNIQUE(title, author)
            );

            CREATE TABLE IF NOT EXISTS orders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                customer_name TEXT NOT NULL,
                book_id INTEGER NOT NULL,
                quantity INTEGER NOT NULL CHECK (quantity > 0),
                unit_price REAL NOT NULL CHECK (unit_price >= 0),
                total_price REAL NOT NULL CHECK (total_price >= 0),
                status TEXT NOT NULL DEFAULT 'Hazırlanıyor',
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,

                FOREIGN KEY (book_id)
                    REFERENCES books(id)
                    ON DELETE RESTRICT
            );

            CREATE INDEX IF NOT EXISTS idx_books_title
            ON books(title);

            CREATE INDEX IF NOT EXISTS idx_books_author
            ON books(author);

            CREATE INDEX IF NOT EXISTS idx_books_category
            ON books(category);

            CREATE INDEX IF NOT EXISTS idx_orders_customer_name
            ON orders(customer_name);
            """
        )

        connection.commit()


if __name__ == "__main__":
    initialize_database()

    print("Veritabanı başarıyla oluşturuldu.")
    print(f"Veritabanı yolu: {DATABASE_PATH}")