import math
import os
from datetime import datetime, date, time
from sqlalchemy.orm import Session
from app.models.checkin_ftth import Checkin_ftth, DataPelangganFtth, FatBaru

def generate_no_activity(db3: Session, kode_sales: str) -> str:
    today = date.today()
    prefix = f"ACT-{today.strftime('%Y%m%d')}-{kode_sales}-"
    
    # Ambil semua no_activity yang sudah di-generate hari ini di data_pelanggan_ftth
    p_activities = db3.query(DataPelangganFtth.no_activity).filter(
        DataPelangganFtth.no_activity.like(f"{prefix}%")
    ).all()
    
    # Ambil semua no_activity yang sudah di-generate hari ini di checkins_ftth
    c_activities = db3.query(Checkin_ftth.no_activity).filter(
        Checkin_ftth.no_activity.like(f"{prefix}%")
    ).all()
    
    # Gabungkan semua list no_activity
    all_activities = [r[0] for r in p_activities] + [r[0] for r in c_activities]
    
    max_seq = 0
    for act in all_activities:
        try:
            parts = act.split("-")
            if parts:
                seq_num = int(parts[-1])
                if seq_num > max_seq:
                    max_seq = seq_num
        except (ValueError, IndexError):
            pass
            
    seq = max_seq + 1
    return f"{prefix}{seq:03d}"


def create_data_pelanggan(db3: Session, data: dict) -> DataPelangganFtth:
    pelanggan = DataPelangganFtth(**data)
    pelanggan.status = "pending"
    db3.add(pelanggan)
    db3.commit()
    db3.refresh(pelanggan)
    return pelanggan


def get_pending_list(db3: Session, kode_sales: str) -> list[DataPelangganFtth]:
    return db3.query(DataPelangganFtth).filter(
        DataPelangganFtth.kode_sales == kode_sales,
        DataPelangganFtth.status == "pending"
    ).order_by(DataPelangganFtth.tanggal.desc()).all()


def get_done_list(db3: Session, kode_sales: str) -> list[Checkin_ftth]:
    return db3.query(Checkin_ftth).filter(
        Checkin_ftth.kode_sales == kode_sales
    ).order_by(Checkin_ftth.tanggal_checkin.desc()).all()


def get_pending_by_no_activity(db3: Session, no_activity: str, kode_sales: str) -> DataPelangganFtth | None:
    return db3.query(DataPelangganFtth).filter(
        DataPelangganFtth.no_activity == no_activity,
        DataPelangganFtth.kode_sales == kode_sales,
        DataPelangganFtth.status == "pending"
    ).first()


def validate_radius(lat_sales: float, lon_sales: float, lat_fat: float, lon_fat: float) -> float:
    # Haversine formula to compute distance in meters
    R = 6371000.0  # Earth radius in meters
    phi1 = math.radians(lat_sales)
    phi2 = math.radians(lat_fat)
    delta_phi = math.radians(lat_fat - lat_sales)
    delta_lambda = math.radians(lon_fat - lon_sales)
    
    a = math.sin(delta_phi / 2.0) ** 2 + \
        math.cos(phi1) * math.cos(phi2) * \
        math.sin(delta_lambda / 2.0) ** 2
    c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a))
    return R * c


def merge_and_create_checkin(db3: Session, no_activity: str, kode_sales: str, form2_data: dict) -> Checkin_ftth:
    try:
        # 1. Ambil data pending, raise ValueError jika tidak ada
        pending = get_pending_by_no_activity(db3, no_activity, kode_sales)
        if not pending:
            raise ValueError(f"Data pending dengan no_activity {no_activity} tidak ditemukan.")
        
        status_pem = form2_data.get("status_pemesanan")
        now_dt = datetime.now()
        
        tgl_po = now_dt if status_pem == "PO" else None
        tgl_so = now_dt if status_pem == "SO" else None
        tgl_sa = now_dt if status_pem == "SA" else None
        tgl_pa = now_dt if status_pem == "PA" else None

        # 2. Insert baris baru ke checkins_ftth (gabungan data pending + form2_data)
        checkin = Checkin_ftth(
            no_activity=pending.no_activity,
            nama_pelanggan=pending.nama_pelanggan,
            nomor_pelanggan=pending.nomor_pelanggan,
            latitude_rumah=pending.latitude_rumah,
            longitude_rumah=pending.longitude_rumah,
            keterangan=pending.keterangan,
            kode_sales=pending.kode_sales,
            tanggal_form1=pending.tanggal,
            fat_code=form2_data["fat_code"],
            foto_fat=form2_data["foto_fat"],
            latitude_fat=form2_data["latitude_fat"],
            longitude_fat=form2_data["longitude_fat"],
            jarak_meter=form2_data["jarak_meter"],
            status_pemesanan=form2_data["status_pemesanan"],
            tanggal_po=tgl_po,
            tanggal_so=tgl_so,
            tanggal_sa=tgl_sa,
            tanggal_pa=tgl_pa,
            status_checkin="done"
        )
        db3.add(checkin)
        
        # 3. Hapus data pelanggan sementara (pending)
        db3.delete(pending)
        
        # 4. Commit dan refresh
        db3.commit()
        db3.refresh(checkin)
        return checkin
        
    except Exception as e:
        db3.rollback()
        raise e

def generate_fat_code(db3: Session) -> str:
    today = date.today()
    prefix = f"FAT-{today.strftime('%Y%m%d')}-"
    
    # Ambil semua fat_code yang sudah di-generate hari ini di fat_baru
    fats = db3.query(FatBaru.fat_code).filter(
        FatBaru.fat_code.like(f"{prefix}%")
    ).all()
    
    all_fats = [r[0] for r in fats]
    max_seq = 0
    for fat in all_fats:
        try:
            parts = fat.split("-")
            if parts:
                seq_num = int(parts[-1])
                if seq_num > max_seq:
                    max_seq = seq_num
        except (ValueError, IndexError):
            pass
            
    seq = max_seq + 1
    return f"{prefix}{seq:03d}"

def merge_and_create_checkin_new_fat(db3: Session, no_activity: str, kode_sales: str, new_fat_data: dict) -> tuple[Checkin_ftth, FatBaru]:
    try:
        # 1. Ambil data pending
        pending = get_pending_by_no_activity(db3, no_activity, kode_sales)
        if not pending:
            raise ValueError(f"Data pending dengan no_activity {no_activity} tidak ditemukan.")
        
        status_pem = new_fat_data.get("status_pemesanan")
        now_dt = datetime.now()
        
        tgl_po = now_dt if status_pem == "PO" else None
        tgl_so = now_dt if status_pem == "SO" else None
        tgl_sa = now_dt if status_pem == "SA" else None
        tgl_pa = now_dt if status_pem == "PA" else None

        # 2. Insert baris baru ke fat_baru
        fat_baru = FatBaru(
            fat_code=new_fat_data["fat_code"],
            tipe_fat=new_fat_data["tipe_fat"],
            longitude_fat=new_fat_data["longitude_fat"],
            latitude_fat=new_fat_data["latitude_fat"],
            slot_available=new_fat_data["slot_available"],
            slot_occupied=new_fat_data["slot_occupied"],
            status="diajukan",
            sales_code=kode_sales
        )
        db3.add(fat_baru)

        # 3. Insert baris baru ke checkins_ftth
        jarak = validate_radius(float(pending.latitude_rumah), float(pending.longitude_rumah), float(new_fat_data["latitude_fat"]), float(new_fat_data["longitude_fat"]))
        checkin = Checkin_ftth(
            no_activity=pending.no_activity,
            nama_pelanggan=pending.nama_pelanggan,
            nomor_pelanggan=pending.nomor_pelanggan,
            latitude_rumah=pending.latitude_rumah,
            longitude_rumah=pending.longitude_rumah,
            keterangan=pending.keterangan,
            kode_sales=pending.kode_sales,
            tanggal_form1=pending.tanggal,
            fat_code=new_fat_data["fat_code"],
            foto_fat="-",
            latitude_fat=new_fat_data["latitude_fat"],
            longitude_fat=new_fat_data["longitude_fat"],
            jarak_meter=jarak,
            status_pemesanan=new_fat_data["status_pemesanan"],
            tanggal_po=tgl_po,
            tanggal_so=tgl_so,
            tanggal_sa=tgl_sa,
            tanggal_pa=tgl_pa,
            status_checkin="done"
        )
        db3.add(checkin)
        
        # 4. Hapus data pelanggan sementara (pending)
        db3.delete(pending)
        
        # 5. Commit dan refresh
        db3.commit()
        db3.refresh(checkin)
        db3.refresh(fat_baru)
        return checkin, fat_baru
        
    except Exception as e:
        db3.rollback()
        raise e

def merge_and_join_new_fat(db3: Session, no_activity: str, kode_sales: str, fat_code: str, status_pemesanan: str) -> tuple[Checkin_ftth, FatBaru]:
    try:
        # 1. Ambil data pending
        pending = get_pending_by_no_activity(db3, no_activity, kode_sales)
        if not pending:
            raise ValueError(f"Data pending dengan no_activity {no_activity} tidak ditemukan.")
            
        # 2. Ambil data fat_baru
        fat_baru = db3.query(FatBaru).filter(FatBaru.fat_code == fat_code).first()
        if not fat_baru:
            raise ValueError(f"Data FAT Baru dengan kode {fat_code} tidak ditemukan.")
            
        # 3. Cek slot
        if fat_baru.slot_available <= 0:
            raise ValueError(f"Slot FAT {fat_code} sudah penuh.")
            
        # 4. Update slot fat_baru
        fat_baru.slot_available -= 1
        fat_baru.slot_occupied += 1
        
        status_pem = status_pemesanan
        now_dt = datetime.now()
        
        tgl_po = now_dt if status_pem == "PO" else None
        tgl_so = now_dt if status_pem == "SO" else None
        tgl_sa = now_dt if status_pem == "SA" else None
        tgl_pa = now_dt if status_pem == "PA" else None

        # 5. Insert baris baru ke checkins_ftth
        jarak = validate_radius(float(pending.latitude_rumah), float(pending.longitude_rumah), float(fat_baru.latitude_fat), float(fat_baru.longitude_fat))
        checkin = Checkin_ftth(
            no_activity=pending.no_activity,
            nama_pelanggan=pending.nama_pelanggan,
            nomor_pelanggan=pending.nomor_pelanggan,
            latitude_rumah=pending.latitude_rumah,
            longitude_rumah=pending.longitude_rumah,
            keterangan=pending.keterangan,
            kode_sales=pending.kode_sales,
            tanggal_form1=pending.tanggal,
            fat_code=fat_baru.fat_code,
            foto_fat="-",
            latitude_fat=fat_baru.latitude_fat,
            longitude_fat=fat_baru.longitude_fat,
            jarak_meter=jarak,
            status_pemesanan=status_pemesanan,
            tanggal_po=tgl_po,
            tanggal_so=tgl_so,
            tanggal_sa=tgl_sa,
            tanggal_pa=tgl_pa,
            status_checkin="done"
        )
        db3.add(checkin)
        
        # 6. Hapus data pelanggan sementara (pending)
        db3.delete(pending)
        
        # 7. Commit dan refresh
        db3.commit()
        db3.refresh(checkin)
        db3.refresh(fat_baru)
        return checkin, fat_baru
        
    except Exception as e:
        db3.rollback()
        raise e



def get_done_by_no_activity(db3: Session, no_activity: str, kode_sales: str) -> Checkin_ftth | None:
    return db3.query(Checkin_ftth).filter(
        Checkin_ftth.no_activity == no_activity,
        Checkin_ftth.kode_sales == kode_sales
    ).first()


def get_pemesanan_status_list(
    db3: Session,
    kode_sales: str,
    start_date: str = None,
    end_date: str = None,
    status_filter: str = None
) -> list[Checkin_ftth]:
    query = db3.query(Checkin_ftth).filter(Checkin_ftth.kode_sales == kode_sales)
    
    if start_date:
        try:
            parsed_start = datetime.strptime(start_date, "%Y-%m-%d").date()
            start_dt = datetime.combine(parsed_start, time.min)
            query = query.filter(Checkin_ftth.tanggal_checkin >= start_dt)
        except ValueError:
            pass
            
    if end_date:
        try:
            parsed_end = datetime.strptime(end_date, "%Y-%m-%d").date()
            end_dt = datetime.combine(parsed_end, time.max)
            query = query.filter(Checkin_ftth.tanggal_checkin <= end_dt)
        except ValueError:
            pass
            
    if status_filter:
        query = query.filter(Checkin_ftth.status_pemesanan == status_filter)
        
    return query.order_by(Checkin_ftth.tanggal_checkin.desc()).all()


def update_checkin_status(
    db3: Session,
    no_activity: str,
    kode_sales: str,
    status_pemesanan: str,
    keterangan: str | None = None
) -> Checkin_ftth:
    checkin = db3.query(Checkin_ftth).filter(
        Checkin_ftth.no_activity == no_activity,
        Checkin_ftth.kode_sales == kode_sales
    ).first()
    
    if not checkin:
        # Periksa apakah data pelanggan ada di tabel data_pelanggan_ftth (form 1 / pending)
        pending = db3.query(DataPelangganFtth).filter(
            DataPelangganFtth.no_activity == no_activity,
            DataPelangganFtth.kode_sales == kode_sales
        ).first()
        if pending:
            raise ValueError("Data checkin belum selesai. Silakan isi Form 2 terlebih dahulu.")
        else:
            raise ValueError("Data customer tidak ditemukan. Sales wajib memulai dari Form 1 dan bertemu customer langsung.")
            
    # Validasi urutan status pemesanan: PO -> SO -> SA -> PA (hanya bisa berubah maju)
    status_order = ["PO", "SO", "SA", "PA"]
    current_status = checkin.status_pemesanan.upper()
    new_status = status_pemesanan.upper()
    
    if current_status in status_order:
        current_idx = status_order.index(current_status)
        new_idx = status_order.index(new_status)
        if new_idx <= current_idx:
            raise ValueError(
                f"Status pemesanan hanya bisa berubah maju mengikuti urutan PO -> SO -> SA -> PA. "
                f"Status saat ini ({current_status}) tidak dapat diubah menjadi ({new_status})."
            )

    checkin.status_pemesanan = status_pemesanan
    if keterangan is not None:
        checkin.keterangan = keterangan
        
    now_dt = datetime.now()
    if status_pemesanan == "PO":
        checkin.tanggal_po = now_dt
    elif status_pemesanan == "SO":
        checkin.tanggal_so = now_dt
    elif status_pemesanan == "SA":
        checkin.tanggal_sa = now_dt
    elif status_pemesanan == "PA":
        checkin.tanggal_pa = now_dt
        
    db3.commit()
    db3.refresh(checkin)
    return checkin

def get_nearby_fat_baru(db3: Session, lat_user: float, lon_user: float, max_radius_km: float = 5.0) -> list[dict]:
    all_fats = db3.query(FatBaru).all()
    nearby_fats = []
    
    max_radius_m = max_radius_km * 1000
    
    for fat in all_fats:
        try:
            jarak = validate_radius(lat_user, lon_user, float(fat.latitude_fat), float(fat.longitude_fat))
            if jarak <= max_radius_m:
                # Convert to dict and add jarak_meter
                fat_dict = {
                    "id": fat.id,
                    "fat_code": fat.fat_code,
                    "tipe_fat": fat.tipe_fat,
                    "longitude_fat": fat.longitude_fat,
                    "latitude_fat": fat.latitude_fat,
                    "slot_available": fat.slot_available,
                    "slot_occupied": fat.slot_occupied,
                    "date": fat.date,
                    "status": fat.status,
                    "sales_code": fat.sales_code,
                    "jarak_meter": jarak
                }
                nearby_fats.append(fat_dict)
        except (ValueError, TypeError):
            continue
            
    # Sort by nearest
    nearby_fats.sort(key=lambda x: x["jarak_meter"])
    return nearby_fats

def get_fat_baru_detail(db3: Session, fat_code: str) -> FatBaru | None:
    return db3.query(FatBaru).filter(FatBaru.fat_code == fat_code).first()

def get_checkins_by_fat_code(db3: Session, fat_code: str) -> list[Checkin_ftth]:
    return db3.query(Checkin_ftth).filter(Checkin_ftth.fat_code == fat_code).all()

def update_checkin_new_fat(db3: Session, no_activity: str, kode_sales: str, new_fat_data: dict) -> tuple[Checkin_ftth, FatBaru]:
    try:
        checkin = db3.query(Checkin_ftth).filter(
            Checkin_ftth.no_activity == no_activity,
            Checkin_ftth.kode_sales == kode_sales
        ).first()
        if not checkin:
            raise ValueError(f"Data checkin dengan no_activity {no_activity} tidak ditemukan.")
            
        old_fat_code = checkin.fat_code
        if old_fat_code and old_fat_code.startswith("FAT-"):
            old_fat = db3.query(FatBaru).filter(FatBaru.fat_code == old_fat_code).first()
            if old_fat:
                old_fat.slot_available += 1
                old_fat.slot_occupied -= 1
                
        fat_baru = FatBaru(
            fat_code=new_fat_data["fat_code"],
            tipe_fat=new_fat_data["tipe_fat"],
            longitude_fat=new_fat_data["longitude_fat"],
            latitude_fat=new_fat_data["latitude_fat"],
            slot_available=new_fat_data["slot_available"],
            slot_occupied=new_fat_data["slot_occupied"],
            status="diajukan",
            sales_code=kode_sales
        )
        db3.add(fat_baru)
        
        jarak = validate_radius(float(checkin.latitude_rumah), float(checkin.longitude_rumah), float(new_fat_data["latitude_fat"]), float(new_fat_data["longitude_fat"]))
        
        # Hapus foto lama jika ada
        if checkin.foto_fat and checkin.foto_fat != "-":
            foto_path = checkin.foto_fat.lstrip("/")
            if os.path.exists(foto_path):
                try:
                    os.remove(foto_path)
                except Exception as e:
                    print(f"Failed to delete old photo {foto_path}: {e}")
                    
        checkin.foto_fat = "-"
        checkin.fat_code = new_fat_data["fat_code"]
        checkin.latitude_fat = new_fat_data["latitude_fat"]
        checkin.longitude_fat = new_fat_data["longitude_fat"]
        checkin.jarak_meter = jarak
        
        db3.commit()
        db3.refresh(checkin)
        db3.refresh(fat_baru)
        return checkin, fat_baru
    except Exception as e:
        db3.rollback()
        raise e

def update_checkin_join_fat(db3: Session, no_activity: str, kode_sales: str, fat_code: str) -> tuple[Checkin_ftth, FatBaru]:
    try:
        checkin = db3.query(Checkin_ftth).filter(
            Checkin_ftth.no_activity == no_activity,
            Checkin_ftth.kode_sales == kode_sales
        ).first()
        if not checkin:
            raise ValueError(f"Data checkin dengan no_activity {no_activity} tidak ditemukan.")
            
        if checkin.fat_code == fat_code:
            raise ValueError(f"Checkin sudah menggunakan FAT {fat_code}.")
            
        fat_baru = db3.query(FatBaru).filter(FatBaru.fat_code == fat_code).first()
        if not fat_baru:
            raise ValueError(f"Data FAT Baru dengan kode {fat_code} tidak ditemukan.")
            
        if fat_baru.slot_available <= 0:
            raise ValueError(f"Slot FAT {fat_code} sudah penuh.")
            
        old_fat_code = checkin.fat_code
        if old_fat_code and old_fat_code.startswith("FAT-"):
            old_fat = db3.query(FatBaru).filter(FatBaru.fat_code == old_fat_code).first()
            if old_fat:
                old_fat.slot_available += 1
                old_fat.slot_occupied -= 1
                
        fat_baru.slot_available -= 1
        fat_baru.slot_occupied += 1
        
        jarak = validate_radius(float(checkin.latitude_rumah), float(checkin.longitude_rumah), float(fat_baru.latitude_fat), float(fat_baru.longitude_fat))
        
        # Hapus foto lama jika ada
        if checkin.foto_fat and checkin.foto_fat != "-":
            foto_path = checkin.foto_fat.lstrip("/")
            if os.path.exists(foto_path):
                try:
                    os.remove(foto_path)
                except Exception as e:
                    print(f"Failed to delete old photo {foto_path}: {e}")
        
        checkin.foto_fat = "-"
        checkin.fat_code = fat_baru.fat_code
        checkin.latitude_fat = fat_baru.latitude_fat
        checkin.longitude_fat = fat_baru.longitude_fat
        checkin.jarak_meter = jarak
        
        db3.commit()
        db3.refresh(checkin)
        db3.refresh(fat_baru)
        return checkin, fat_baru
    except Exception as e:
        db3.rollback()
        raise e