"""
Clinic Bridge API - يشتغل على جهاز العيادة
يتصل بـ SQL Server ويوفر API للأيجنت
"""

from fastapi import FastAPI, HTTPException, Security, Depends
from fastapi.security.api_key import APIKeyHeader
from pydantic import BaseModel
from typing import Optional
from datetime import datetime, timedelta
import pyodbc
import os

app = FastAPI(title="Clinic Bridge API")

# ============================================================
# الإعدادات - غيّر هذه القيم
# ============================================================
DB_CONFIG = {
    "driver": "SQL Server",
    "server": r"192.168.7.32\EXPRESS",
    "database": "EXPRESS_DATA",
    "username": "SA",
    "password": "abc@123",
}

# API Key للأمان - غيّر هذه القيمة لكلمة سر قوية
API_KEY = os.environ.get("BRIDGE_API_KEY", "CHANGE_THIS_TO_STRONG_SECRET_KEY")
api_key_header = APIKeyHeader(name="X-API-Key")


# ============================================================
# دوال مساعدة
# ============================================================
def get_connection():
    conn_str = (
        f"DRIVER={{{DB_CONFIG['driver']}}};"
        f"SERVER={DB_CONFIG['server']};"
        f"DATABASE={DB_CONFIG['database']};"
        f"UID={DB_CONFIG['username']};"
        f"PWD={DB_CONFIG['password']};"
    )
    return pyodbc.connect(conn_str)


def verify_api_key(key: str = Security(api_key_header)):
    if key != API_KEY:
        raise HTTPException(status_code=403, detail="Invalid API Key")
    return key


def add_minutes_to_time(value: str, minutes: int = 15) -> str:
    value = value.strip()
    for fmt in ("%I:%M %p", "%H:%M"):
        try:
            parsed = datetime.strptime(value, fmt)
            break
        except ValueError:
            parsed = None
    if parsed is None:
        raise HTTPException(status_code=400, detail="صيغة الوقت غير صحيحة")
    return (parsed + timedelta(minutes=minutes)).strftime("%I:%M %p").lstrip("0")


def normalize_digits(value: str) -> str:
    return "".join(ch for ch in str(value or "") if ch.isdigit())


def normalize_name_for_match(value: str) -> str:
    value = str(value or "").strip().lower()
    return " ".join("".join(ch if ch.isalnum() else " " for ch in value).split())


def names_match(left: str, right: str) -> bool:
    left_norm = normalize_name_for_match(left)
    right_norm = normalize_name_for_match(right)
    if not left_norm or not right_norm:
        return False
    if left_norm == right_norm:
        return True
    return left_norm in right_norm or right_norm in left_norm


def phone_variants(value: str) -> list[str]:
    digits = normalize_digits(value)
    variants = {digits}
    if digits.startswith("966") and len(digits) >= 12:
        variants.add(digits[-9:])
        variants.add("0" + digits[-9:])
    elif digits.startswith("0") and len(digits) >= 10:
        variants.add(digits[-9:])
        variants.add("966" + digits[-9:])
    elif len(digits) == 9:
        variants.add("0" + digits)
        variants.add("966" + digits)
    return [variant for variant in variants if variant]


def quote_identifier(value: str) -> str:
    return "[" + value.replace("]", "]]") + "]"


PATIENT_FILE_COLUMNS = {"FILENO", "FILE_NO", "PATNO", "PAT_NO", "PATIENTNO", "PATIENT_NO"}
PATIENT_NAME_COLUMNS = {"NAME", "PNAME", "PATNAME", "PATIENTNAME", "PATIENT_NAME", "ENAME", "ANAME"}
PATIENT_MOBILE_COLUMNS = {"MOBIL", "MOBILE", "MOB", "PHONE", "TEL", "TELEPHONE", "GSM"}


def find_patient_columns(cursor):
    wanted = tuple(PATIENT_FILE_COLUMNS | PATIENT_NAME_COLUMNS | PATIENT_MOBILE_COLUMNS)
    placeholders = ",".join("?" for _ in wanted)
    cursor.execute(
        f"""
        SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE UPPER(COLUMN_NAME) IN ({placeholders})
        """,
        wanted,
    )

    tables = {}
    for schema, table, column in cursor.fetchall():
        key = (schema, table)
        tables.setdefault(key, {})[str(column).upper()] = str(column)

    candidates = []
    for (schema, table), columns in tables.items():
        file_col = next((columns[name] for name in PATIENT_FILE_COLUMNS if name in columns), None)
        name_col = next((columns[name] for name in PATIENT_NAME_COLUMNS if name in columns), None)
        mobile_col = next((columns[name] for name in PATIENT_MOBILE_COLUMNS if name in columns), None)
        if file_col and (name_col or mobile_col):
            candidates.append(
                {
                    "schema": str(schema),
                    "table": str(table),
                    "file_col": file_col,
                    "name_col": name_col,
                    "mobile_col": mobile_col,
                }
            )
    return candidates


def find_patient(cursor, mobile: str, patient_name: str = ""):
    variants = phone_variants(mobile)
    raw_name = patient_name.strip() if patient_name and patient_name.strip() else ""
    like_name = f"%{raw_name}%" if raw_name else None

    for candidate in find_patient_columns(cursor):
        schema = quote_identifier(candidate["schema"])
        table = quote_identifier(candidate["table"])
        file_col = quote_identifier(candidate["file_col"])
        name_col = quote_identifier(candidate["name_col"]) if candidate["name_col"] else None
        mobile_col = quote_identifier(candidate["mobile_col"]) if candidate["mobile_col"] else None

        select_name = name_col or "CAST(NULL AS NVARCHAR(255))"
        select_mobile = mobile_col or "CAST(NULL AS NVARCHAR(50))"
        rows = []

        if mobile_col and variants:
            placeholders = ",".join("?" for _ in variants)
            normalized_mobile = (
                f"REPLACE(REPLACE(REPLACE(REPLACE(CAST({mobile_col} AS NVARCHAR(50)), "
                "' ', ''), '-', ''), '+', ''), '.', '')"
            )
            cursor.execute(
                f"""
                SELECT TOP 5
                    CAST({file_col} AS NVARCHAR(50)) AS file_no,
                    CAST({select_name} AS NVARCHAR(255)) AS patient_name,
                    CAST({select_mobile} AS NVARCHAR(50)) AS mobile
                FROM {schema}.{table}
                WHERE {normalized_mobile} IN ({placeholders})
                ORDER BY {file_col} DESC
                """,
                variants,
            )
            rows = cursor.fetchall()

            if rows:
                if raw_name:
                    for row in rows:
                        if row[0] is not None and names_match(str(row[1] or ""), raw_name):
                            return {
                                "file_no": str(row[0]).strip(),
                                "patient_name": str(row[1]).strip() if row[1] is not None else "",
                                "mobile": str(row[2]).strip() if row[2] is not None else "",
                                "source_table": f"{candidate['schema']}.{candidate['table']}",
                                "match_strategy": "mobile_then_name",
                            }
                    continue

                row = rows[0]
                if row[0] is not None:
                    return {
                        "file_no": str(row[0]).strip(),
                        "patient_name": str(row[1]).strip() if row[1] is not None else "",
                        "mobile": str(row[2]).strip() if row[2] is not None else "",
                        "source_table": f"{candidate['schema']}.{candidate['table']}",
                        "match_strategy": "mobile_only",
                    }

        if name_col and like_name:
            cursor.execute(
                f"""
                SELECT TOP 1
                    CAST({file_col} AS NVARCHAR(50)) AS file_no,
                    CAST({select_name} AS NVARCHAR(255)) AS patient_name,
                    CAST({select_mobile} AS NVARCHAR(50)) AS mobile
                FROM {schema}.{table}
                WHERE CAST({name_col} AS NVARCHAR(255)) LIKE ?
                ORDER BY {file_col} DESC
                """,
                (like_name,),
            )
            row = cursor.fetchone()
            if row and row[0] is not None:
                return {
                    "file_no": str(row[0]).strip(),
                    "patient_name": str(row[1]).strip() if row[1] is not None else "",
                    "mobile": str(row[2]).strip() if row[2] is not None else "",
                    "source_table": f"{candidate['schema']}.{candidate['table']}",
                    "match_strategy": "name_only",
                }

    return None


# ============================================================
# Models
# ============================================================
class NewAppointment(BaseModel):
    patient_name: str
    mobile: str
    doctor_no: int
    doctor_name: str
    clinic_name: str
    clinic_no: int
    appointment_date: str  # YYYY-MM-DD
    appointment_time: str  # HH:MM
    duration_minutes: int = 15
    file_no: Optional[str] = None
    remark: Optional[str] = ""


class CancelAppointment(BaseModel):
    appointment_id: int
    cancel_reason: Optional[str] = ""


class RepairAppointment(BaseModel):
    appointment_id: int
    file_no: Optional[str] = None
    duration_minutes: int = 15


# ============================================================
# Endpoints
# ============================================================
@app.get("/health")
def health():
    return {"status": "ok", "time": datetime.now().isoformat()}


@app.get("/appointments/available")
def get_available_slots(
    doctor_no: int,
    appointment_date: str,  # YYYY-MM-DD
    api_key: str = Depends(verify_api_key)
):
    """جلب الأوقات المتاحة لدكتور في يوم معين"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute(
            """
            SELECT mtime, mtime1, name, Finshed
            FROM apnt
            WHERE dcrno = ? AND CAST(mdate AS DATE) = ? AND app_del_date IS NULL
            ORDER BY mtime
            """,
            (doctor_no, appointment_date),
        )

        booked = []
        for row in cursor.fetchall():
            booked.append(
                {
                    "time_from": row[0],
                    "time_to": row[1],
                    "patient_name": row[2],
                    "finished": row[3],
                }
            )

        conn.close()
        return {
            "date": appointment_date,
            "doctor_no": doctor_no,
            "booked_slots": booked,
            "total_booked": len(booked),
        }

    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@app.get("/appointments/list")
def get_appointments(
    doctor_no: int,
    appointment_date: str,  # YYYY-MM-DD
    api_key: str = Depends(verify_api_key)
):
    """جلب كل مواعيد دكتور في يوم معين"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute(
            """
            SELECT mnum, mdate, mtime, mtime1, name, fileno,
                   drname, cliname, Mobil, rem, Finshed, came
            FROM apnt
            WHERE dcrno = ? AND CAST(mdate AS DATE) = ? AND app_del_date IS NULL
            ORDER BY mtime
            """,
            (doctor_no, appointment_date),
        )

        appointments = []
        for row in cursor.fetchall():
            appointments.append(
                {
                    "id": row[0],
                    "date": str(row[1]),
                    "time_from": row[2],
                    "time_to": row[3],
                    "patient_name": row[4],
                    "file_no": row[5],
                    "doctor_name": row[6],
                    "clinic_name": row[7],
                    "mobile": row[8],
                    "remark": row[9],
                    "finished": row[10],
                    "came": row[11],
                }
            )

        conn.close()
        return {"appointments": appointments, "total": len(appointments)}

    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@app.get("/doctors")
def get_doctors(api_key: str = Depends(verify_api_key)):
    """جلب قائمة الأطباء"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute(
            """
            SELECT Did, DNAME, cliname, CLINO
            FROM DCR
            WHERE stop_dcr IS NULL OR stop_dcr = 0
            ORDER BY DNAME
            """
        )

        doctors = []
        for row in cursor.fetchall():
            doctors.append(
                {
                    "doctor_no": row[0],
                    "doctor_name": row[1],
                    "clinic_name": row[2],
                    "clinic_no": row[3],
                }
            )

        conn.close()
        return {"doctors": doctors}

    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@app.get("/patients/search")
def search_patients(
    mobile: str = "",
    patient_name: str = "",
    api_key: str = Depends(verify_api_key),
):
    """البحث عن ملف المريض من الجوال أو الاسم"""
    try:
        conn = get_connection()
        cursor = conn.cursor()
        patient = find_patient(cursor, mobile, patient_name)
        conn.close()
        return {"patient": patient, "found": patient is not None}

    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@app.post("/appointments/create")
def create_appointment(
    apnt: NewAppointment,
    api_key: str = Depends(verify_api_key)
):
    """إنشاء موعد جديد"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute(
            """
            SELECT COUNT(*) FROM apnt
            WHERE dcrno = ? AND CAST(mdate AS DATE) = ?
              AND mtime = ? AND app_del_date IS NULL
            """,
            (apnt.doctor_no, apnt.appointment_date, apnt.appointment_time),
        )

        if cursor.fetchone()[0] > 0:
            raise HTTPException(status_code=400, detail="هذا الوقت محجوز مسبقاً")

        appointment_time_to = add_minutes_to_time(apnt.appointment_time, apnt.duration_minutes)
        patient = None
        file_no = apnt.file_no
        if not file_no:
            patient = find_patient(cursor, apnt.mobile, apnt.patient_name)
            if patient:
                file_no = patient["file_no"]

        cursor.execute(
            """
            INSERT INTO apnt
            (mdate, mtime, mtime1, name, Mobil, fileno, dcrno, drname, cliname, clino, rem,
             Finshed, app_add_date, came, Wait)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, GETDATE(), 0, 0)
            """,
            (
                apnt.appointment_date,
                apnt.appointment_time,
                appointment_time_to,
                apnt.patient_name,
                apnt.mobile,
                file_no,
                apnt.doctor_no,
                apnt.doctor_name,
                apnt.clinic_name,
                apnt.clinic_no,
                apnt.remark,
            ),
        )

        conn.commit()

        cursor.execute("SELECT @@IDENTITY")
        new_id = cursor.fetchone()[0]

        conn.close()
        return {
            "success": True,
            "appointment_id": int(new_id),
            "file_no": file_no,
            "patient_match": patient,
            "message": (
                f"تم حجز الموعد بنجاح - {apnt.patient_name} "
                f"في {apnt.appointment_date} {apnt.appointment_time}"
            ),
        }

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@app.post("/appointments/repair-display")
def repair_appointment_display(
    repair: RepairAppointment,
    api_key: str = Depends(verify_api_key)
):
    """إصلاح ظهور الموعد بإضافة وقت النهاية ورقم الملف عند توفره"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute(
            """
            SELECT mnum, mtime, name, Mobil, fileno
            FROM apnt
            WHERE mnum = ? AND app_del_date IS NULL
            """,
            (repair.appointment_id,),
        )
        row = cursor.fetchone()
        if not row:
            conn.close()
            raise HTTPException(status_code=404, detail="الموعد غير موجود")

        file_no = repair.file_no
        patient = None
        if not file_no:
            patient = find_patient(cursor, row[3] or "", row[2] or "")
            if patient:
                file_no = patient["file_no"]

        appointment_time_to = add_minutes_to_time(str(row[1]), repair.duration_minutes)

        cursor.execute(
            """
            UPDATE apnt
            SET mtime1 = ?, fileno = COALESCE(?, fileno)
            WHERE mnum = ? AND app_del_date IS NULL
            """,
            (appointment_time_to, file_no, repair.appointment_id),
        )
        conn.commit()
        conn.close()

        return {
            "success": True,
            "appointment_id": repair.appointment_id,
            "mtime1": appointment_time_to,
            "file_no": file_no,
            "patient_match": patient,
        }

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@app.post("/appointments/cancel")
def cancel_appointment(
    payload: CancelAppointment,
    api_key: str = Depends(verify_api_key)
):
    """إلغاء موعد موجود عبر soft delete باستخدام app_del_date"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute(
            """
            SELECT mnum, mdate, mtime, name, drname, cliname, rem, app_del_date
            FROM apnt
            WHERE mnum = ?
            """,
            (payload.appointment_id,),
        )
        row = cursor.fetchone()
        if not row:
            conn.close()
            raise HTTPException(status_code=404, detail="الموعد غير موجود")

        if row[7] is not None:
            conn.close()
            raise HTTPException(status_code=400, detail="الموعد ملغي مسبقاً")

        old_remark = (row[6] or "").strip()
        cancel_note = (payload.cancel_reason or "").strip()
        merged_remark = old_remark
        if cancel_note:
            suffix = f"[CANCELLED {datetime.now().isoformat()}] {cancel_note}"
            merged_remark = f"{old_remark} | {suffix}" if old_remark else suffix

        cursor.execute(
            """
            UPDATE apnt
            SET app_del_date = GETDATE(),
                rem = ?
            WHERE mnum = ? AND app_del_date IS NULL
            """,
            (merged_remark, payload.appointment_id),
        )

        if cursor.rowcount == 0:
            conn.rollback()
            conn.close()
            raise HTTPException(status_code=400, detail="تعذر إلغاء الموعد")

        conn.commit()
        conn.close()
        return {
            "success": True,
            "appointment_id": int(row[0]),
            "message": "تم إلغاء الموعد بنجاح",
            "appointment": {
                "id": int(row[0]),
                "date": str(row[1]),
                "time_from": row[2],
                "patient_name": row[3],
                "doctor_name": row[4],
                "clinic_name": row[5],
            },
        }

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))