import os
import uuid
from datetime import datetime
from fastapi import UploadFile, HTTPException
from sqlalchemy.orm import Session
from sqlalchemy.orm import Session
from datetime import datetime
import uuid
from src.apps.datasource.models import Datasource
from src.apps.datasource.schemas import ExternalDatasourceCreate
from src.apps.stores.models import Store, Branch
from sqlalchemy.orm import Session
import pandas as pd
import httpx

UPLOAD_DIR = os.path.abspath("src/assets/uploads")


def save_csv_file(file: UploadFile) -> str:
    """Saves the uploaded CSV file to local storage and returns the file path."""
    os.makedirs(UPLOAD_DIR, exist_ok=True)

    if file.filename.split(".")[-1].lower() != "csv":
        raise HTTPException(status_code=400, detail="Only CSV files are allowed")

    file_path = os.path.join(UPLOAD_DIR, f"{uuid.uuid4()}.csv")

    with open(file_path, "wb") as buffer:
        buffer.write(file.file.read())

    return file_path


def create_datasource(db, store_id: int, branch_id: int, file_path: str):
    """Creates a datasource entry in the database and returns it."""
    snapshot_id = uuid.uuid4()

    datasource_entry = Datasource(
        store_id=store_id,
        branch_id=branch_id,
        source_type="csv",
        url=file_path,
        snapshot_id=snapshot_id,
        created_at=datetime.utcnow(),
        last_fetch_date=datetime.utcnow()
    )

    db.add(datasource_entry)
    db.commit()
    db.refresh(datasource_entry)

    return datasource_entry

async def process_csv_data(db, datasource_id: int, file_path: str, webhook_url: str):
    """Reads review data from an uploaded CSV file and sends it to a webhook."""
    try:
        # Validate datasource entry
        datasource_entry = db.query(Datasource).filter(
            Datasource.ds_id == datasource_id
        ).first()

        if not datasource_entry:
            raise HTTPException(status_code=404, detail="Datasource entry not found")

        # Ensure the file exists
        if not os.path.exists(file_path):
            raise HTTPException(status_code=400, detail=f"CSV file not found: {file_path}")

        # Read CSV file (Validation already done in upload_csv_file)
        df = pd.read_csv(file_path)
        reviews = df.to_dict(orient="records")

        # Prepare payload
        payload = {
            "store_id": datasource_entry.store_id,
            "branch_id": datasource_entry.branch_id,
            "datasource_id": datasource_id,
            "snapshot_id": str(datasource_entry.snapshot_id),
            "source_type": "csv",
            "reviews": reviews
        }

        # Send data to webhook
        async with httpx.AsyncClient() as client:
            response = await client.post(webhook_url, json=payload)
            response.raise_for_status()

        return {"message": "Reviews sent to webhook successfully"}

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



def create_external_data_source(data: ExternalDatasourceCreate, db: Session, user, snapshot_id: str):
    """Create or update an external datasource entry in the database."""
    
    # Validate Store
    store = db.query(Store).filter(Store.store_id == data.store_id, Store.user_id == user.user_id).first()
    if not store:
        raise HTTPException(
            status_code=403,
            detail={"status": False, "code": 403, "message": "Unauthorized access to this store"}
        )
    
    # Validate Branch
    branch = db.query(Branch).filter(
        Branch.branch_id == data.branch_id,
        Branch.store_id == data.store_id,
        Branch.user_id == user.user_id
    ).first()
    if not branch:
        raise HTTPException(
           status_code=403,
            detail={"status": False, "code": 403, "message": "Unauthorized access to this branch"}
        )

    # Check if the datasource already exists
    existing_entry = db.query(Datasource).filter(
        Datasource.store_id == data.store_id,
        Datasource.branch_id == data.branch_id,
        Datasource.source_type == data.source_type,
        Datasource.url == str(data.url)
    ).first()

    if existing_entry:
        # Update last_fetch_date and snapshot_id
        existing_entry.last_fetch_date = datetime.utcnow()
        existing_entry.snapshot_id = snapshot_id  # Ensure snapshot_id is updated
        db.commit()
        db.refresh(existing_entry)
        print(f"Updated existing datasource: {existing_entry.ds_id}, snapshot_id updated")
        return existing_entry  

    # Create a new datasource entry
    datasource_entry = Datasource(
        store_id=data.store_id,
        branch_id=data.branch_id,
        source_type=data.source_type,
        url=str(data.url),
        snapshot_id=snapshot_id,  # Ensure snapshot_id is set
        last_fetch_date=datetime.utcnow(),
        created_at=datetime.utcnow()
    )

    print(f"Creating new datasource entry: store_id={data.store_id}, branch_id={data.branch_id}")
    
    try:
        db.add(datasource_entry)
        db.commit()
        db.refresh(datasource_entry)
        return datasource_entry  
    except Exception as e:
        db.rollback()
        print(f"Database error: {e}")
        raise HTTPException(status_code=500, detail="Database insertion failed")