from supabase import create_client, Client
import os
import uuid
from fractions import Fraction
import exifread
import uvicorn
from fastapi import FastAPI, UploadFile, File, HTTPException, Form, BackgroundTasks, Body, Query
from fastapi.middleware.cors import CORSMiddleware
from dotenv import load_dotenv
from ultralytics import YOLO
import cv2
import numpy as np
import tempfile
from datetime import datetime, date, timedelta, timezone
import logging
import random

logger = logging.getLogger("uvicorn.error")
load_dotenv()
model = YOLO('best.pt')
app = FastAPI()

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"]
)

SUPABASE_URL = os.getenv("SUPABASE_URL")
SUPABASE_KEY = os.getenv("SUPABASE_KEY")

if not SUPABASE_URL or not SUPABASE_KEY:
    raise ValueError("Supabase credentials not found.")
supabase: Client = create_client(SUPABASE_URL, SUPABASE_KEY)

# --- Helper Functions ---
def convert_to_decimal(coord):
    if coord and len(coord.values) >= 3:
        deg = float(coord.values[0])
        min = float(coord.values[1]) / 60
        sec = float(Fraction(str(coord.values[2]))) / 3600
        return deg + min + sec
    return 0.0

def get_gps_location(file_stream):
    tags = exifread.process_file(file_stream)
    gps_latitude = tags.get("GPS GPSLatitude")
    print(f"GPS GPSLatitude: {gps_latitude}")
    gps_latitude_ref = tags.get("GPS GPSLatitudeRef")
    print(f"GPS GPSLatitudeRef: {gps_latitude_ref}")
    gps_longitude = tags.get("GPS GPSLongitude")
    print(f"GPS GPSLongitude: {gps_longitude}")
    gps_longitude_ref = tags.get("GPS GPSLongitudeRef")
    print(f"GPS GPSLongitudeRef: {gps_longitude_ref}")
    if gps_latitude and gps_latitude_ref and gps_longitude and gps_longitude_ref:
        lat = convert_to_decimal(gps_latitude)
        lon = convert_to_decimal(gps_longitude)
        if gps_latitude_ref.values[0] != 'N': lat = -lat
        if gps_longitude_ref.values[0] != 'E': lon = -lon
        return lat, lon
    return None, None

def process_video_in_background(video_data: bytes, video_guid: str, filename: str, location_point: str , location: str , is_approved: int , getvariancevalue: str, category: str, city: str):
    bucket_name = 'pothole-images'
    try:
        supabase.table("videos").insert({"guid": video_guid, "video_name": filename, "status": "processing",
        "location": location_point, "location_text": location,
        "category": category, "approved": is_approved, "variance": getvariancevalue, "city": city}).execute()
    except Exception as e:
        print(f"DB Error on video insert: {str(e)}")
        return
    with tempfile.NamedTemporaryFile(delete=False, suffix=".mp4") as temp_video_file:
        temp_video_file.write(video_data)
        temp_path = temp_video_file.name
    cap = cv2.VideoCapture(temp_path)
    frame_number = 0
    first_frame_saved = False
    while cap.isOpened():
        ret, frame = cap.read()
        if not ret: break
        if frame_number % 90 == 0:
           
            results = model(frame, device='cpu')
            if len(results[0].boxes) > 0:
                annotated_frame = results[0].plot()
                is_success, buffer = cv2.imencode(".jpg", annotated_frame)
                if is_success:
                    first_frame_set = {}
                    frame_bytes = buffer.tobytes()
                    frame_path = f"{video_guid}/frame_{frame_number}.jpg"
                    try:
                        supabase.storage.from_(bucket_name).upload(path=frame_path, file=frame_bytes, file_options={"content-type": "image/jpeg"})
                        frame_url = supabase.storage.from_(bucket_name).get_public_url(frame_path)
                        supabase.table("detected_frames").insert({"video_guid": video_guid, "frame_image_url": frame_url, "frame_number": frame_number, "status": "In Progress"}).execute()
                        if not first_frame_saved:
                            supabase.table("videos").update({"image_url": frame_url
                            }).eq("guid", video_guid).execute()
                            first_frame_saved = True
                    
                    
                    except Exception as e:
                        print(f"Error saving frame {frame_number}: {str(e)}")
        frame_number += 1
    cap.release()
    os.remove(temp_path)
    supabase.table("videos").update({"status": "complete"}).eq("guid", video_guid).execute()

def safe_date_converter(date_string):
    if not date_string:
        return datetime.min.replace(tzinfo=timezone.utc)
    try:
        dt = datetime.fromisoformat(date_string)
        if dt.tzinfo is None:
            return dt.replace(tzinfo=timezone.utc)
        return dt
    except (ValueError, TypeError):
        return datetime.min.replace(tzinfo=timezone.utc)

# --- API Endpoints ---
@app.get("/")
async def root():
    return {"message": "API is running"}

@app.get("/detailed_report_data")
async def get_detailed_report_data(category: str = Query(...)):
    try:
        # Fetch potholes reports for the specified category
        response_potholes = supabase.table("potholes").select("*").eq("category", category).order("created_at", desc=True).execute()

        # Fetch videos reports for the specified category
        response_videos = supabase.table("videos").select("*").eq("category", category).order("created_at", desc=True).execute()

        return response_potholes.data + response_videos.data
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Database error: {str(e)}")


@app.get("/get_pothole_reports")
async def get_pothole_reports():
    try:
        # Fetch all pothole reports
        reports_potholes_response = supabase.table("potholes").select("*").eq("category", "potholes").execute()

        # Fetch all videos reports
        reports_videos_response = supabase.table("videos").select("*").eq("category", "potholes").execute()

        return reports_potholes_response.data + reports_videos_response.data
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Database error: {str(e)}")

# Today's date range in UTC

@app.get("/get_today_processed_reports")
async def get_today_processed_reports():
    try:
        today = date.today()
        start_of_day = datetime.combine(today, datetime.min.time()).replace(tzinfo=timezone.utc)
        end_of_day = start_of_day + timedelta(days=1)

        # Fetch all pothole reports
        reports_response = supabase.table("potholes") \
            .select("*") \
            .gte("created_at", start_of_day.isoformat()) \
            .lt("created_at", end_of_day.isoformat()) \
            .execute()

        # Fetch all videos reports
        reports_videos_response = supabase.table("videos") \
            .select("*") \
            .gte("created_at", start_of_day.isoformat()) \
            .lt("created_at", end_of_day.isoformat()) \
            .execute()

        return reports_response.data + reports_videos_response.data
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Database error: {str(e)}")

@app.get("/get_total_processed_reports")
async def get_total_processed_reports():
    try:
        reports_potholes_response = supabase.table("potholes").select("*").order("created_at", desc=True).execute()
        reports_videos_response = supabase.table("videos").select("*").order("created_at", desc=True).execute()
        print(f"Total reports potholes fetched: {len(reports_potholes_response.data)}")
        print(f"Total reports videos fetched: {len(reports_videos_response.data)}")
        print(f"potholes Data : {reports_potholes_response.data}")
        print(f"Videos Data : {reports_videos_response.data}")
        # Extract data
        potholes_data = reports_potholes_response.data or []
        videos_data = reports_videos_response.data or []
        # Combine and sort by created_at
        all_data = potholes_data + videos_data
        all_data_sorted = sorted(all_data, key=lambda x: datetime.fromisoformat(x["created_at"].replace("Z", "")), reverse=True)
        # Take latest 4 records
        latest_4 = all_data_sorted[:4]
        return latest_4
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Database error: {str(e)}")        

@app.get("/report_summary")
async def get_report_summary():
    try:
        reports_response = supabase.table("potholes").select("category, approved").execute()
        all_reports = reports_response.data
        summary = {
            "billboard": {"total": 0, "approved": 0, "unapproved": 0, "damage": 0},
            "guardrails": {"total": 0, "missing": 0, "damaged": 0},
            "construction": {"total": 0},
            "potholes": {"total": 0, "approved": 0, "unapproved": 0}
        }
        for report in all_reports:
            category = report.get("category")
            is_approved = report.get("approved")
            if category in summary:
                summary[category]["total"] += 1
                if is_approved == 1:
                    summary[category]["approved"] += 1
                summary[category]["unapproved"] = summary[category]["total"] - summary[category]["approved"]
        return summary
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Database error: {str(e)}")

@app.get("/latest_reports")
async def get_latest_reports():
    try:
        videos_response = supabase.table("videos").select("guid, video_name, created_at, status").order("created_at", desc=True).limit(30).execute()
        videos = videos_response.data
        for video in videos:
            video['type'] = 'video'
            first_frame_response = supabase.table("detected_frames").select("frame_image_url").eq("video_guid", video["guid"]).order("frame_number").limit(1).execute()
            video["thumbnail_url"] = first_frame_response.data[0]["frame_image_url"] if first_frame_response.data else None
        images_response = supabase.table("potholes").select("guid, image_name, image_url, created_at, status, category").order("created_at", desc=True).limit(30).execute()
        images = images_response.data
        for image in images:
            image['type'] = 'image'
            image['thumbnail_url'] = image.get('image_url') 
        all_reports = videos + images
        combined_reports = sorted(all_reports, key=lambda x: safe_date_converter(x.get('created_at')), reverse=True)
        return combined_reports[:30]
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"A severe database error occurred: {str(e)}")

@app.post("/upload")
async def upload_image(
    file: UploadFile = File(...), 
    location: str = Form(None),
    category: str = Form(...),
    city: str = Form(...)
):
    image_data = await file.read()
    with tempfile.NamedTemporaryFile(delete=False, suffix=".jpg") as temp_image_file:
        temp_image_file.write(image_data)
        temp_path = temp_image_file.name
    results = model(temp_path, device='cpu')
    annotated_image_array = results[0].plot()
    os.remove(temp_path)
    is_success, buffer = cv2.imencode(".jpg", annotated_image_array)
    if not is_success: raise HTTPException(status_code=500, detail="Failed to encode annotated image.")
    annotated_image_bytes = buffer.tobytes()
    bucket_name = 'pothole-images'
    uuid_path = f"annotated_{uuid.uuid4()}.jpg"
    try:
        supabase.storage.from_(bucket_name).upload(path=uuid_path, file=annotated_image_bytes, file_options={"content-type": "image/jpeg"})
        url = supabase.storage.from_(bucket_name).get_public_url(uuid_path)
    except Exception as e: raise HTTPException(status_code=500, detail=f"Storage Error: {str(e)}")
    
    await file.seek(0)
    lat, lon = get_gps_location(file.file)
    location_point = f"Point({lon:.6f} {lat:.6f})" if lat and lon else None    
    guid = str(uuid.uuid4())
    variance_levels = ["Low", "Mid", "High"]    
    getvariancevalue = random.choice(variance_levels) if category == "potholes" else None      
    try:        
        validation_response = supabase.table("validation").select("location_text").execute()
        approved_locations = {item['location_text'] for item in validation_response.data}
        is_approved = 1 if location in approved_locations else 0
        insert_data = {
            "guid": guid, 
            "image_name": file.filename, 
            "image_url": url, 
            "location": location_point, 
            "location_text": f"{lon:.6f} {lat:.6f}" if lat and lon else location or None, 
            "status": "In Progress",
            "category": category,
            "approved": is_approved,
            "variance": getvariancevalue,
            "city": city
        }
        
        response = supabase.table("potholes").insert(insert_data).execute()
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Database Error: {str(e)}")
        
    return {"message": "Upload successful", "guid": guid, "image_url": url, "db_response": response.data}

@app.patch("/report/{guid}/status")
async def update_report_status(guid: str, payload: dict = Body(...)):
    new_status = payload.get("status")
    if not new_status:
        raise HTTPException(status_code=400, detail="Status not provided.")
    try:
        report_res = supabase.table("potholes").select("status").eq("guid", guid).single().execute()
        current_status = report_res.data.get("status") if report_res.data else None
        final_status = 'In Progress' if current_status == new_status else new_status
        response = supabase.table("potholes").update({"status": final_status}).eq("guid", guid).execute()
        return {"message": "Status updated successfully", "data": response.data}
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Database Error: {str(e)}")

@app.patch("/frame/{frame_id}/status")
async def update_frame_status(frame_id: int, payload: dict = Body(...)):
    new_status = payload.get("status")
    if not new_status:
        raise HTTPException(status_code=400, detail="Status not provided.")
    try:
        frame_res = supabase.table("detected_frames").select("status").eq("id", frame_id).single().execute()
        current_status = frame_res.data.get("status") if frame_res.data else None
        final_status = 'In Progress' if current_status == new_status else new_status
        response = supabase.table("detected_frames").update({"status": final_status}).eq("id", frame_id).execute()
        return {"message": "Status updated successfully", "data": response.data}
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Database Error: {str(e)}")


@app.post("/upload_video")
async def upload_video(background_tasks: BackgroundTasks, location: str = Form(None), category: str = Form(...), city: str = Form(...), file: UploadFile = File(...)):
    if not file.content_type.startswith('video/'):
        raise HTTPException(status_code=400, detail="File is not a video.")
    video_data = await file.read()
    video_guid = str(uuid.uuid4())
    lat, lon = get_gps_location(file.file)
    location_point = f"Point({lon:.6f} {lat:.6f})" if lat and lon else None   
    validation_response = supabase.table("validation").select("location_text").execute()
    approved_locations = {item['location_text'] for item in validation_response.data}
    is_approved = 1 if location in approved_locations else 0
    variance_levels = ["Low", "Mid", "High"]    
    getvariancevalue = random.choice(variance_levels) if category == "potholes" else None
    location =f"{lon:.6f} {lat:.6f}" if lat and lon else location or None
    print(f"Step1")
    print(f"location_point: {location_point}")
    print(f"location text: {location}")
    print(f"is approved : {is_approved}")
    print(f"variancevalue: {getvariancevalue}")
    print(f"category: {category}")
    print(f"Step2")
    print(f"city: {city}")
    background_tasks.add_task(process_video_in_background, video_data, video_guid, file.filename, location_point, location, is_approved, getvariancevalue, category, city)
    return {"message": "Video upload successful. Processing has started.", "video_guid": video_guid}

@app.get("/video_report/{guid}")
async def get_video_report(guid: str):
    try:
        video_response = supabase.table("videos").select("*").eq("guid", guid).single().execute()
        if not video_response.data: raise HTTPException(status_code=404, detail="Video report not found")
        frames_response = supabase.table("detected_frames").select("*").eq("video_guid", guid).order("frame_number", desc=True).limit(15).execute()
        return { "video_info": video_response.data, "detected_frames": frames_response.data }
    except Exception as e: raise HTTPException(status_code=500, detail=f"Database Error: {str(e)}")

@app.get("/frame/{frame_id}")
async def get_frame(frame_id: int):
    try:
        response = supabase.table("detected_frames").select("*, videos(*), status").eq("id", frame_id).single().execute()
        if not response.data: raise HTTPException(status_code=404, detail="Frame not found")
        return response.data
    except Exception as e: raise HTTPException(status_code=500, detail=f"Database Error: {str(e)}")

@app.get("/videos")
async def get_videos():
    try:
        videos_response = supabase.table("videos").select("*").order("created_at", desc=True).limit(30).execute()
        videos = videos_response.data
        video_count = len(videos)        
        for video in videos:
            first_frame_response = supabase.table("detected_frames").select("frame_image_url").eq("video_guid", video["guid"]).order("frame_number").limit(1).execute()
            video["thumbnail_url"] = first_frame_response.data[0]["frame_image_url"] if first_frame_response.data else None
        return videos
    except Exception as e: raise HTTPException(status_code=500, detail=f"Database Error: {str(e)}")

@app.get("/images") 
async def get_images():
    try:
        response = supabase.table("potholes").select("id, guid, image_name, image_url, created_at, status, location_text").order("created_at", desc=True).execute()
        return response.data
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Database Error: {str(e)}")

@app.get("/reports")
async def get_reports():
    try:
        response = supabase.table("potholes").select("id, guid, image_name, image_url, created_at, status").order("created_at", desc=True).limit(30).execute()
        return response.data
    except Exception as e: raise HTTPException(status_code=500, detail=f"Database Error: {str(e)}")

@app.get("/report/{guid}")
async def get_report(guid: str):
    try:
        response = supabase.table("potholes").select("guid, image_name, image_url, location, created_at, location_text, status, category").eq("guid", guid).single().execute()
        if not response.data: raise HTTPException(status_code=404, detail=f"Report with guid {guid} not found.")
        return response.data
    except Exception as e: raise HTTPException(status_code=500, detail=f"Database Error: {str(e)}")
    
@app.get("/get_billboard_stats")
async def get_billboard_stats():
    try:
       # Call the RPC function
       response = supabase.rpc("get_billboard_stats").execute()
       print("Response : billboard stats:", response.data)
       return response.data
    except Exception as e: raise HTTPException(status_code=500, detail=f"Database Error: {str(e)}")

@app.get("/get_pothole_stats")
async def get_pothole_stats():
    try:
       # Call the RPC function
       response = supabase.rpc("get_pothole_stats").execute()
       print("Response : pothole stats:", response.data)
       return response.data
    except Exception as e: raise HTTPException(status_code=500, detail=f"Database Error: {str(e)}")

if __name__ == "__main__":
    uvicorn.run(app, port=8000, host="127.0.0.1")