"""
Database Service — SQLAlchemy with SQLite
Creates ALL tables (FYP1 + FYP2) on startup.
"""
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
import os
from pathlib import Path

from ..config import DATABASE_URL

_connect_args = {"check_same_thread": False} if DATABASE_URL.startswith("sqlite") else {}
engine = create_engine(DATABASE_URL, connect_args=_connect_args)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


async def init_db():
    # FYP1 models
    from ..models.models import User, Case, Lawyer, Appointment  # noqa
    # FYP2 models
    from ..models.fyp2_models import (                           # noqa
        Hearing, CourtFiling, CaseMessage, CaseDocument,
        DraftedPetition, LawyerNote, Notification, ChatMessage
    )
    Base.metadata.create_all(bind=engine)

    db = SessionLocal()
    try:
        if db.query(Lawyer).count() == 0:
            _seed_lawyers(db)
    finally:
        db.close()

    print("Database initialized - all tables ready.")


def _seed_lawyers(db):
    import openpyxl
    from ..models.models import Lawyer

    xlsx_path = str(Path(__file__).resolve().parent.parent / "data" / "Lawyers_Dataset.xlsx")
    if not os.path.exists(xlsx_path):
        print(f"Lawyers_Dataset.xlsx not found at {xlsx_path}")
        return

    wb = openpyxl.load_workbook(xlsx_path)
    ws = wb.active
    count = 0
    for row in ws.iter_rows(min_row=2, values_only=True):
        if not row[0]:
            continue
        lawyer = Lawyer(
            name=str(row[1]),
            gender=str(row[2]),
            city=str(row[3]),
            specialization=str(row[4]),
            experience_years=int(row[5]) if row[5] else 0,
            consultation_fee_pkr=float(row[6]) if row[6] else 0.0,
            rating=float(row[7]) if row[7] else 0.0,
            total_cases_handled=int(row[8]) if row[8] else 0,
            success_rate_percentage=float(row[9]) if row[9] else 0.0,
            law_firm_name=str(row[10]),
            contact_email=str(row[11]),
        )
        db.add(lawyer)
        count += 1
    db.commit()
    print(f"Seeded {count} lawyers.")