# New DB — the FastAPI app's database
DB_HOST=localhost
DB_PORT=3306
DB_USER=new_db_user
DB_PASS=new_db_password
DB_NAME=osztromok_new

# Old DB — accessed via SSH tunnel on port 3307
OLD_DB_HOST=127.0.0.1
OLD_DB_PORT=3307
OLD_DB_USER=old_db_user
OLD_DB_PASS=old_db_password
OLD_DB_NAME=old_db_name

"""
Content migration script: old PHP CMS → FastAPI schema
═══════════════════════════════════════════════════════
Run once from the project root:
    python -m scripts.migrate

Prerequisites:
  - SSH tunnel open if old DB is remote (see Chapter 9 notes)
  - .env has OLD_DB_* and DB_* variables set
  - New DB tables already created (python -m scripts.create_tables)

The script is idempotent — safe to re-run. It skips records whose
slug already exists in the new database.
"""

import os
import sys
from pathlib import Path

# Ensure project root is on sys.path so we can import app.*
sys.path.insert(0, str(Path(__file__).parent.parent))

import pymysql
from dotenv import load_dotenv
from sqlalchemy import select

load_dotenv()

# ── New DB: import the FastAPI app's database and models ─────────────────
from app.database import Base, engine, SessionLocal, slugify
from app.models   import Subject, Subtopic, Page, PageContent


# ── Old DB: raw PyMySQL connection ───────────────────────────────────────
def old_db_conn():
    return pymysql.connect(
        host    = os.getenv("OLD_DB_HOST", "127.0.0.1"),
        port    = int(os.getenv("OLD_DB_PORT", "3306")),
        user    = os.getenv("OLD_DB_USER"),
        password= os.getenv("OLD_DB_PASS"),
        database= os.getenv("OLD_DB_NAME"),
        charset = "utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,  # rows as dicts
    )


# ── Helpers ──────────────────────────────────────────────────────────────
def get_slug(row: dict, name_col: str = "name") -> str:
    """Use the row's slug column if it exists, otherwise generate one."""
    return row.get("slug") or slugify(row[name_col])


def log(msg: str):
    print(msg, flush=True)


# ═══════════════════════════════════════════════════════════════════════════
# MIGRATE SUBJECTS
# ═══════════════════════════════════════════════════════════════════════════
def migrate_subjects(old, new_db) -> dict[int, int]:
    """Returns a mapping: old subject id → new subject id"""
    log("\n── Subjects ─────────────────────────────")
    id_map = {}

    with old.cursor() as cur:
        # ADAPT: change column names to match your old subjects table
        cur.execute("""
            SELECT
                id,
                name,                           -- ADAPT if different
                description,                    -- ADAPT: may be NULL
                sort_order  AS display_order,   -- ADAPT: rename here
                slug                            -- ADAPT: remove if no slug col
            FROM subjects
            ORDER BY sort_order
        """)
        rows = cur.fetchall()

    for row in rows:
        slug = get_slug(row)

        # Idempotent: skip if already migrated
        existing = new_db.execute(
            select(Subject).where(Subject.slug == slug)
        ).scalar_one_or_none()

        if existing:
            log(f"  SKIP  subject '{slug}' (already exists, id={existing.id})")
            id_map[row["id"]] = existing.id
        else:
            obj = Subject(
                name          = row["name"],
                slug          = slug,
                description   = row.get("description", "") or "",
                display_order = row.get("display_order", 0) or 0,
            )
            new_db.add(obj)
            new_db.flush()  # get the new id without committing yet
            id_map[row["id"]] = obj.id
            log(f"  ADD   subject '{slug}' (old id={row['id']} → new id={obj.id})")

    new_db.commit()
    return id_map


# ═══════════════════════════════════════════════════════════════════════════
# MIGRATE SUBTOPICS
# ═══════════════════════════════════════════════════════════════════════════
def migrate_subtopics(old, new_db, subject_id_map: dict) -> dict[int, int]:
    log("\n── Subtopics ────────────────────────────")
    id_map = {}

    with old.cursor() as cur:
        # ADAPT: change subjects_id to whatever your FK column is named
        cur.execute("""
            SELECT
                id,
                subjects_id AS old_subject_id,  -- ADAPT
                name,
                description,
                sort_order  AS display_order,   -- ADAPT
                slug                            -- ADAPT: remove if absent
            FROM subtopics
            ORDER BY subjects_id, sort_order
        """)
        rows = cur.fetchall()

    for row in rows:
        new_subject_id = subject_id_map.get(row["old_subject_id"])
        if not new_subject_id:
            log(f"  WARN  subtopic id={row['id']}: parent subject not found, skipping")
            continue

        slug = get_slug(row)
        existing = new_db.execute(
            select(Subtopic).where(
                Subtopic.slug       == slug,
                Subtopic.subject_id == new_subject_id,
            )
        ).scalar_one_or_none()

        if existing:
            log(f"  SKIP  subtopic '{slug}'")
            id_map[row["id"]] = existing.id
        else:
            obj = Subtopic(
                subject_id    = new_subject_id,
                name          = row["name"],
                slug          = slug,
                description   = row.get("description", "") or "",
                display_order = row.get("display_order", 0) or 0,
            )
            new_db.add(obj)
            new_db.flush()
            id_map[row["id"]] = obj.id
            log(f"  ADD   subtopic '{slug}'")

    new_db.commit()
    return id_map


# ═══════════════════════════════════════════════════════════════════════════
# MIGRATE PAGES
# ═══════════════════════════════════════════════════════════════════════════
def migrate_pages(old, new_db, subtopic_id_map: dict) -> dict[int, int]:
    log("\n── Pages ────────────────────────────────")
    id_map = {}

    with old.cursor() as cur:
        # ADAPT: change subtopics_id to your FK column name
        cur.execute("""
            SELECT
                id,
                subtopics_id AS old_subtopic_id,  -- ADAPT
                name,
                description,
                sort_order   AS display_order,    -- ADAPT
                slug                              -- ADAPT: remove if absent
            FROM pages
            ORDER BY subtopics_id, sort_order
        """)
        rows = cur.fetchall()

    for row in rows:
        new_subtopic_id = subtopic_id_map.get(row["old_subtopic_id"])
        if not new_subtopic_id:
            log(f"  WARN  page id={row['id']}: parent subtopic not found, skipping")
            continue

        slug = get_slug(row)
        existing = new_db.execute(
            select(Page).where(
                Page.slug        == slug,
                Page.subtopic_id == new_subtopic_id,
            )
        ).scalar_one_or_none()

        if existing:
            log(f"  SKIP  page '{slug}'")
            id_map[row["id"]] = existing.id
        else:
            obj = Page(
                subtopic_id   = new_subtopic_id,
                name          = row["name"],
                slug          = slug,
                description   = row.get("description", "") or "",
                display_order = row.get("display_order", 0) or 0,
            )
            new_db.add(obj)
            new_db.flush()
            id_map[row["id"]] = obj.id
            log(f"  ADD   page '{slug}'")

    new_db.commit()
    return id_map


# ═══════════════════════════════════════════════════════════════════════════
# MIGRATE PAGE CONTENT SECTIONS
# ═══════════════════════════════════════════════════════════════════════════
def migrate_content(old, new_db, page_id_map: dict):
    log("\n── Content sections ─────────────────────")
    added = skipped = 0

    with old.cursor() as cur:
        # ADAPT: change pages_id and html_content to your column names
        cur.execute("""
            SELECT
                id,
                pages_id    AS old_page_id,  -- ADAPT
                html_content AS content,     -- ADAPT (may just be 'content')
                section_order AS display_order -- ADAPT
            FROM page_content
            ORDER BY pages_id, section_order
        """)
        rows = cur.fetchall()

    for row in rows:
        new_page_id = page_id_map.get(row["old_page_id"])
        if not new_page_id:
            skipped += 1
            continue

        # Idempotent check: count existing sections for this page
        # Content sections have no natural unique key, so we skip the whole
        # page's sections if any already exist (prevents duplicates on re-run)
        existing_count = new_db.execute(
            select(PageContent).where(PageContent.page_id == new_page_id)
        ).scalars().all()
        if existing_count:
            skipped += 1
            continue

        obj = PageContent(
            page_id       = new_page_id,
            content       = row["content"] or "",
            display_order = row.get("display_order", 0) or 0,
        )
        new_db.add(obj)
        added += 1

    new_db.commit()
    log(f"  Content: {added} added, {skipped} skipped")


# ═══════════════════════════════════════════════════════════════════════════
# MAIN
# ═══════════════════════════════════════════════════════════════════════════
def main():
    log("=== Content Migration: old CMS → FastAPI schema ===")

    # Ensure new DB tables exist (safe to call even if already created)
    Base.metadata.create_all(bind=engine)

    old    = old_db_conn()
    new_db = SessionLocal()

    try:
        subject_map  = migrate_subjects(old, new_db)
        subtopic_map = migrate_subtopics(old, new_db, subject_map)
        page_map     = migrate_pages(old, new_db, subtopic_map)
        migrate_content(old, new_db, page_map)
        log("\n✓ Migration complete.")
    except Exception as e:
        new_db.rollback()
        log(f"\n✗ Migration FAILED: {e}")
        raise
    finally:
        new_db.close()
        old.close()


if __name__ == "__main__":
    main()

"""
Route verification script — run after migration.
Hits every subject / subtopic / page URL and reports non-200 responses.

Usage:
    uvicorn app.main:app &          # start the server in the background
    python -m scripts.verify_routes
"""

import sys
from pathlib import Path
sys.path.insert(0, str(Path(__file__).parent.parent))

import httpx
from dotenv import load_dotenv
from sqlalchemy import select
from app.database import SessionLocal
from app.models   import Subject, Subtopic, Page

load_dotenv()

BASE_URL = "http://127.0.0.1:8000"


def check(client: httpx.Client, url: str, errors: list):
    try:
        r = client.get(url, follow_redirects=True)
        status = r.status_code
        icon   = "✓" if status == 200 else "✗"
        print(f"  {icon} {status}  {url}")
        if status != 200:
            errors.append((status, url))
    except Exception as e:
        print(f"  ✗ ERR  {url}  ({e})")
        errors.append(("ERR", url))


def main():
    db     = SessionLocal()
    errors = []

    with httpx.Client(base_url=BASE_URL, timeout=10) as client:
        print(f"Verifying routes against {BASE_URL}\n")

        subjects = db.execute(select(Subject)).scalars().all()

        for subject in subjects:
            print(f"\n[{subject.slug}]")
            check(client, f"/{subject.slug}", errors)

            subtopics = db.execute(
                select(Subtopic).where(Subtopic.subject_id == subject.id)
            ).scalars().all()

            for subtopic in subtopics:
                check(client, f"/{subject.slug}/{subtopic.slug}", errors)

                pages = db.execute(
                    select(Page).where(Page.subtopic_id == subtopic.id)
                ).scalars().all()

                for page in pages:
                    check(client, f"/{subject.slug}/{subtopic.slug}/{page.slug}", errors)

    db.close()
    print(f"\n{'─'*40}")
    if errors:
        print(f"✗  {len(errors)} problem(s) found:")
        for code, url in errors:
            print(f"   {code}  {url}")
        sys.exit(1)
    else:
        print("✓  All routes returned 200.")

# Already in old_db_conn() — make sure charset is utf8mb4 not latin1
pymysql.connect(
    ...,
    charset="utf8mb4",
    use_unicode=True,
)

# If the old DB has latin1 columns, force conversion on the cursor:
cur.execute("SET NAMES utf8mb4")

# Replace the default sqlalchemy.url with a reference to your .env
sqlalchemy.url = mysql+pymysql://%(DB_USER)s:%(DB_PASS)s@%(DB_HOST)s/%(DB_NAME)s

from app.database import Base
from app import models  # noqa — registers all models with Base

# Set target_metadata so Alembic can detect schema changes
target_metadata = Base.metadata