from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, DeclarativeBase
from dotenv import load_dotenv
import os

# Load the .env file so os.getenv() can read DB credentials
load_dotenv()

# ── Build the connection URL ─────────────────────────────────────────────
# Format: dialect+driver://user:password@host:port/database
# pymysql is the driver that talks to MySQL — it replaces JDBC in Java world
DATABASE_URL = (
    "mysql+pymysql://"
    + os.getenv("DB_USER")
    + ":"
    + os.getenv("DB_PASSWORD")
    + "@"
    + os.getenv("DB_HOST", "localhost")
    + ":"
    + os.getenv("DB_PORT", "3306")
    + "/"
    + os.getenv("DB_NAME")
)

# ── Create the engine ────────────────────────────────────────────────────
# The engine manages the connection pool — think of it as the DataSource
# bean in Spring Boot.
#
# echo=True prints every SQL query to the console (very useful while
# developing). Set echo=False before going to production.
engine = create_engine(
    DATABASE_URL,
    echo=True,
    pool_pre_ping=True,   # test connections before using them
    pool_recycle=3600,    # recycle connections after 1 hour
)

# ── Session factory ──────────────────────────────────────────────────────
# SessionLocal() creates a new database session on demand.
# autocommit=False means we control when to commit.
# autoflush=False means changes are not flushed to DB until we tell them to.
SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine,
)

# ── Base class for ORM models ────────────────────────────────────────────
# All model classes will inherit from Base.
# DeclarativeBase is the SQLAlchemy 2.0 replacement for declarative_base().
class Base(DeclarativeBase):
    pass

from __future__ import annotations
from typing import List, Optional
from sqlalchemy import String, Text, Integer, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database import Base


# ────────────────────────────────────────────────────────────────────────
# Subject — top level of the hierarchy (e.g. "Japan", "French", "Python")
# ────────────────────────────────────────────────────────────────────────
class Subject(Base):
    __tablename__ = "subjects"

    id:            Mapped[int]           = mapped_column(Integer, primary_key=True)
    name:          Mapped[str]           = mapped_column(String(100))
    slug:          Mapped[str]           = mapped_column(String(100), unique=True, index=True)
    description:   Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    icon:          Mapped[Optional[str]] = mapped_column(String(50), nullable=True)
    display_order: Mapped[int]           = mapped_column(Integer, default=0)

    # One Subject has many Subtopics
    subtopics: Mapped[List["Subtopic"]] = relationship(
        "Subtopic",
        back_populates="subject",
        order_by="Subtopic.display_order",
        cascade="all, delete-orphan",
    )

    def __repr__(self) -> str:
        return f"<Subject id={self.id} slug='{self.slug}'>"


# ────────────────────────────────────────────────────────────────────────
# Subtopic — second level (e.g. Subject "Japan" → Subtopic "Kanji")
# ────────────────────────────────────────────────────────────────────────
class Subtopic(Base):
    __tablename__ = "subtopics"

    id:            Mapped[int]           = mapped_column(Integer, primary_key=True)
    subject_id:    Mapped[int]           = mapped_column(ForeignKey("subjects.id"), index=True)
    name:          Mapped[str]           = mapped_column(String(100))
    slug:          Mapped[str]           = mapped_column(String(100), index=True)
    description:   Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    display_order: Mapped[int]           = mapped_column(Integer, default=0)

    # Many Subtopics belong to one Subject
    subject: Mapped["Subject"] = relationship("Subject", back_populates="subtopics")

    # One Subtopic has many Pages
    pages: Mapped[List["Page"]] = relationship(
        "Page",
        back_populates="subtopic",
        order_by="Page.display_order",
        cascade="all, delete-orphan",
    )

    def __repr__(self) -> str:
        return f"<Subtopic id={self.id} slug='{self.slug}'>"


# ────────────────────────────────────────────────────────────────────────
# Page — third level (e.g. Subtopic "Kanji" → Page "Kanji Tiles")
# ────────────────────────────────────────────────────────────────────────
class Page(Base):
    __tablename__ = "pages"

    id:            Mapped[int]           = mapped_column(Integer, primary_key=True)
    subtopic_id:   Mapped[int]           = mapped_column(ForeignKey("subtopics.id"), index=True)
    name:          Mapped[str]           = mapped_column(String(100))
    slug:          Mapped[str]           = mapped_column(String(100), index=True)
    display_order: Mapped[int]           = mapped_column(Integer, default=0)

    # Many Pages belong to one Subtopic
    subtopic: Mapped["Subtopic"] = relationship("Subtopic", back_populates="pages")

    # One Page has many PageContent rows (body sections)
    content_sections: Mapped[List["PageContent"]] = relationship(
        "PageContent",
        back_populates="page",
        order_by="PageContent.display_order",
        cascade="all, delete-orphan",
    )

    def __repr__(self) -> str:
        return f"<Page id={self.id} slug='{self.slug}'>"


# ────────────────────────────────────────────────────────────────────────
# PageContent — the actual HTML/text body of a page
# A page can have multiple content sections (e.g. intro + vocab table)
# ────────────────────────────────────────────────────────────────────────
class PageContent(Base):
    __tablename__ = "page_content"

    id:            Mapped[int]           = mapped_column(Integer, primary_key=True)
    page_id:       Mapped[int]           = mapped_column(ForeignKey("pages.id"), index=True)
    content_type:  Mapped[str]           = mapped_column(String(50), default="html")
    content:       Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    display_order: Mapped[int]           = mapped_column(Integer, default=0)

    # Many PageContent rows belong to one Page
    page: Mapped["Page"] = relationship("Page", back_populates="content_sections")

    def __repr__(self) -> str:
        return f"<PageContent id={self.id} type='{self.content_type}'>"

from typing import Generator
from sqlalchemy.orm import Session

# ── Database session dependency ──────────────────────────────────────────
# This is a FastAPI dependency. Route functions declare it like this:
#
#   @app.get("/example")
#   async def example(db: Session = Depends(get_db)):
#       subjects = db.query(Subject).all()
#       ...
#
# FastAPI calls get_db(), injects the session, runs the route, then the
# finally block closes the session. Every request gets a fresh session;
# no sessions are leaked. Compare to Spring's @Transactional annotation.
def get_db() -> Generator[Session, None, None]:
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

"""
Quick database connectivity and model test.
Run from the project root with the venv active:
  python test_db.py
"""
from app.database import SessionLocal, engine
from app.models import Subject, Subtopic, Page, PageContent
from sqlalchemy import select, text

def main():
    # ── 1. Raw connection test ─────────────────────────────────────
    print("\n=== Testing raw connection ===")
    with engine.connect() as conn:
        result = conn.execute(text("SELECT 1 + 1 AS answer"))
        row = result.fetchone()
        print(f"Connection OK — SELECT 1+1 = {row.answer}")

    db = SessionLocal()
    try:
        # ── 2. List all subjects ───────────────────────────────────────
        print("\n=== All subjects ===")
        subjects = (
            db.execute(select(Subject).order_by(Subject.display_order))
            .scalars()
            .all()
        )
        for s in subjects:
            print(f"  [{s.id:3}] {s.slug:20} {s.name}")

        # ── 3. Drill into one subject ──────────────────────────────────
        # Change "japan" to a slug that actually exists in your DB
        SLUG = "japan"
        print(f"\n=== Subtopics of '{SLUG}' ===")
        subject = db.execute(
            select(Subject).where(Subject.slug == SLUG)
        ).scalar_one_or_none()

        if subject:
            for st in subject.subtopics:
                print(f"  [{st.id:3}] {st.slug:25} {st.name}")
                for pg in st.pages:
                    print(f"         → {pg.slug:22} {pg.name}")
        else:
            print(f"  (no subject with slug '{SLUG}' found)")

        # ── 4. Fetch a specific page's content ────────────────────────
        # Change to a page slug that exists in your DB
        PAGE_SLUG = "kanji-tiles"
        print(f"\n=== Content sections for page '{PAGE_SLUG}' ===")
        page = db.execute(
            select(Page).where(Page.slug == PAGE_SLUG)
        ).scalar_one_or_none()

        if page:
            print(f"  Found page: {page.name} (id={page.id})")
            print(f"  Content sections: {len(page.content_sections)}")
            for c in page.content_sections:
                # Show just the first 80 characters of each content block
                preview = (c.content or "")[:80].replace("\n", " ")
                print(f"  [type={c.content_type}] {preview}...")
        else:
            print(f"  (no page with slug '{PAGE_SLUG}' found)")

        print("\nAll tests passed.")

    finally:
        db.close()


if __name__ == "__main__":
    main()

# Import models here so SQLAlchemy registers them with the metadata.
# Even if you don't use them directly in main.py, this ensures they are
# loaded before any code tries to query the database.
from app import models  # noqa: F401