from sched import scheduler
from fastapi import FastAPI, Request, Form, Depends, HTTPException, Cookie, UploadFile, File, Query
from fastapi.responses import HTMLResponse, StreamingResponse, RedirectResponse, Response
from fastapi.templating import Jinja2Templates
from fastapi.staticfiles import StaticFiles
from sqlalchemy import Column, Integer, String, DateTime, Text, Float,ForeignKey, Boolean, create_engine, MetaData, select, and_, or_, desc, func
from sqlalchemy.ext.declarative import declarative_base
from databases import Database
from passlib.context import CryptContext
from jose import jwt, JWTError
from datetime import datetime, timedelta
from functools import wraps
import pytz
import random
import time
import smtplib
from email.mime.text import MIMEText
from email.message import EmailMessage
from apscheduler.schedulers.background import BackgroundScheduler
import shutil
import os
import csv
import io
import logging
import re
import uvicorn
from sqlalchemy.orm import relationship
from io import StringIO
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import Session
from fastapi import Depends

# DATABASE_URL = "postgresql+asyncpg://postgres:p4python@postgres.cqf3qroocfwm.us-east-1.rds.amazonaws.com:5432/Inhouse-Email"
DATABASE_URL = "postgresql+psycopg2://postgres:p4python@postgres.cqf3qroocfwm.us-east-1.rds.amazonaws.com:5432/Inhouse-Email"

SECRET_KEY = "YOUR_SECRET_KEY"
ALGORITHM = "HS256"
ACCESS_TOKEN_EXPIRE_MINUTES = 60
OTP_EXPIRY_SECONDS = 300

# DATABASE SETUP
engine = create_engine(DATABASE_URL, echo=True)

# Create a configured "Session" class
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
database = Database(DATABASE_URL)
metadata = MetaData()
Base = declarative_base()

pwd_context = CryptContext(schemes=["argon2"], deprecated="auto")

def hash_password(password: str):
    return pwd_context.hash(password)

def verify_password(password: str, hashed: str):
    return pwd_context.verify(password, hashed)

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


# engine = create_engine(DATABASE_URL.replace("+asyncpg", ""), echo=True)

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=True, index=True, nullable=False)
    email = Column(String, unique=True, nullable=False)
    image = Column(String, nullable=True)
    phone_number = Column(String, nullable=True)
    password = Column(String, nullable=False)

class EmailTemplate(Base):
    __tablename__ = "email_templates"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(100), nullable=False)
    subject = Column(String(200), nullable=False)
    body = Column(Text, nullable=False)



class ScheduledEmail(Base):
    __tablename__ = "scheduled_emails"

    id = Column(Integer, primary_key=True, index=True)
    to = Column(String, nullable=False)
    subject = Column(String, nullable=False)
    body = Column(Text, nullable=False)
    send_time = Column(String, nullable=False)
    sent = Column(Boolean, default=False)
    sent_at = Column(String, nullable=True)
    thread_id = Column(String, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class Lead(Base):
    __tablename__ = "leads"
    id = Column(Integer, primary_key=True, index=True)                  
    name = Column(String, nullable=False)
    email = Column(String, nullable=False)
    company = Column(String, nullable=True)
    status = Column(String, default="pending")
    email_sent_at = Column(DateTime, nullable=True)
    email_replied_at = Column(DateTime, nullable=True)
    auto_reply_sent_at = Column(DateTime, nullable=True)
    thread_id = Column(String, nullable=True)
    original_email_subject = Column(String, nullable=True)
    original_email_body = Column(Text, nullable=True)
    meeting_date = Column(DateTime, nullable=True)
    meeting_scheduled_at = Column(DateTime, nullable=True)
    last_followup_sent_at = Column(DateTime, nullable=True)
    meeting_status = Column(String, nullable=True)
    calendly_event_uri = Column(Text, nullable=True)
    email_classification = Column(String, nullable=True)  
    email_classification_confidence = Column(Float, nullable=True)
    email_classification_reason = Column(Text, nullable=True)
    is_out_of_office = Column(Boolean, default=False)
    ooo_reopen_date = Column(DateTime, nullable=True)
    ooo_detected_at = Column(DateTime, nullable=True)
    notes = Column(Text, nullable=True)
    reply_content = Column(Text, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
    

Base.metadata.create_all(bind=engine)

# FASTAPI APP
app = FastAPI()
templates = Jinja2Templates(directory="templates")
app.mount("/static", StaticFiles(directory="static"), name="static")

# STARTUP / SHUTDOWN
@app.on_event("startup")
async def startup():
    await database.connect()

@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()

# JWT TOKEN UTILITIES
def create_access_token(data: dict, expires_delta: int = ACCESS_TOKEN_EXPIRE_MINUTES):
    to_encode = data.copy()
    expire = datetime.utcnow() + timedelta(minutes=expires_delta)
    to_encode.update({"exp": expire})
    return jwt.encode(to_encode, SECRET_KEY, algorithm=ALGORITHM)

def get_current_user(token: str):
    try:
        payload = jwt.decode(token, SECRET_KEY, algorithms=[ALGORITHM])
        username = payload.get("sub")
        if username is None:
            raise HTTPException(status_code=401, detail="Invalid token")
        return username
    except JWTError:
        raise HTTPException(status_code=401, detail="Invalid token")


def login_required(func):
    @wraps(func)    
    async def wrapper(*args, **kwargs):

        request = kwargs.get("request")
        if request is None:
            request = args[0]  

        token = request.cookies.get("access_token")
        if not token:
            return RedirectResponse(url="/login", status_code=302)

        try:
            username = get_current_user(token)
            request.state.username = username
        except:
            return RedirectResponse(url="/login", status_code=302)

        return await func(*args, **kwargs)

    return wrapper


# OTP STORAGE
otp_store = {}  
print(otp_store,"otp_stor --------")

def no_cache_headers(response: Response):
    response.headers["Cache-Control"] = "no-store, no-cache, must-revalidate, max-age=0"
    response.headers["Pragma"] = "no-cache"
    response.headers["Expires"] = "0"
    return response

def send_email(to_email: str, subject: str, body: str):
    try:
        smtp_server = "smtp.gmail.com"
        smtp_port = 465 
        sender_email = "staffing@esferadigitalsolutions.com"
        sender_password = "ssoqykruruihvnwo"
        msg = MIMEText(body)
        msg["Subject"] = subject
        msg["From"] = sender_email
        msg["To"] = to_email

        server = smtplib.SMTP_SSL(smtp_server, smtp_port)
        server.login(sender_email, sender_password)
        server.send_message(msg)
        server.quit()
        print(f"Email sent to {to_email}")
    except Exception as e:
        print("Error sending email:", e)

@app.middleware("http")
async def check_session(request: Request, call_next):
    if request.url.path in ("/logout", "/dashboard/logout"):
        return await call_next(request)

    if request.url.path.startswith("/dashboard"):
        token = request.cookies.get("access_token")
        if not token:
            return RedirectResponse("/login")
        try:
            get_current_user(token)
        except:
            return RedirectResponse("/login")
    response = await call_next(request)
    return response

templates = Jinja2Templates(directory="templates")

scheduler = BackgroundScheduler()
scheduler.start()

from datetime import datetime

IST = pytz.timezone("Asia/Kolkata")

@app.get("/dashboard/email-scheduler/{lead_id}", response_class=HTMLResponse)
@login_required
async def email_scheduler_for_lead(request: Request, lead_id: int, db: Session = Depends(get_db)):
    lead = db.query(Lead).filter(Lead.id == lead_id).first()
    if not lead:
        return HTMLResponse("<h2>Lead not found</h2>", status_code=404)

    emails = db.query(ScheduledEmail).all()

    return templates.TemplateResponse(
        "email-scheduler.html",
        {
            "request": request,
            "emails": emails,
            "lead_id": lead.id,
            "to": lead.email,
            "subject": f"Proposal for {lead.name}",
            "body": lead.original_email_body or "",
            "send_time": ""  
        }
    )



IST = pytz.timezone("Asia/Kolkata")

@app.post("/dashboard/email-scheduler/{lead_id}", response_class=HTMLResponse)
@login_required
async def schedule_email_for_lead(
    request: Request,
    lead_id: int,
    to: str = Form(...),
    subject: str = Form(...),
    body: str = Form(...),
    send_time: str = Form(...),
    db: Session = Depends(get_db)
):
    ist_naive = datetime.fromisoformat(send_time)

    ist_dt = IST.localize(ist_naive)
    print(ist_dt,"ist_dt---------")

    now_ist = datetime.now(IST)
    if ist_dt <= now_ist:
        return HTMLResponse(
            "<h3>Please select a future time (IST)</h3>",
            status_code=400
        )

    email = ScheduledEmail(
        to=to,
        subject=subject,
        body=body,
        send_time=ist_dt.isoformat(),  
        sent=False
    )
    print(email,'---------------------------email')

    db.add(email)
    db.commit()
    db.refresh(email)

    scheduler.add_job(
        send_email_job,
        trigger="date",
        run_date=ist_dt,  
        args=[email.id],
        id=f"email_job_{email.id}",
        misfire_grace_time=300
    )

    lead = db.query(Lead).filter(Lead.id == lead_id).first()
    emails = db.query(ScheduledEmail).filter(
        ScheduledEmail.to == lead.email
    ).all()

    return templates.TemplateResponse(
        "email-scheduler.html",
        {
            "request": request,
            "emails": emails,
            "message": "Email scheduled successfully (IST)",
            "lead_id": lead.id,
            "to": lead.email,
            "subject": subject,
            "body": body,
            "send_time": ""
        }
    )

@app.on_event("startup")
def load_pending_emails():
    db = SessionLocal()
    try:
        pending_emails = db.query(ScheduledEmail).filter(
            ScheduledEmail.sent == False,
            ScheduledEmail.send_time > datetime.now(IST)
        ).all()

        for email in pending_emails:
            scheduler.add_job(
                send_email_job,
                trigger="date",
                run_date=email.send_time,
                args=[email.id],
                id=f"email_job_{email.id}",
                misfire_grace_time=300
            )

        print(f"Loaded {len(pending_emails)} pending emails (IST-safe)")
    finally:
        db.close()


def send_email_job(email_id: int):
    db = SessionLocal()
    try:
        email = db.query(ScheduledEmail).filter(ScheduledEmail.id == email_id).first()
        if not email or email.sent:
            return

        send_email(email.to, email.subject, email.body)

        email.sent = True
        email.sent_at = datetime.now(IST)
        print(email,)
        db.commit()
        print(f"Scheduled email {email.id} sent successfully.")
    except Exception as e:
        print(f"Error in scheduled email {email_id}: {e}")
    finally:
        db.close()


@app.get("/dashboard/email-templates", response_class=HTMLResponse)
async def get_email_templates(request: Request):
    query = select(EmailTemplate)
    email_templates = await database.fetch_all(query)
    current_user = await get_current_user_data(request)
    return templates.TemplateResponse("email-templates.html", {
        "request": request,
        "current_user": current_user,
        "email_templates": email_templates
    })

@app.post("/dashboard/email-templates")
async def add_email_template(
    request: Request,
    name: str = Form(...),
    subject: str = Form(...),
    body: str = Form(...)
):
    try:
        query = EmailTemplate.__table__.insert().values(
            name=name,
            subject=subject,
            body=body
        )
        await database.execute(query)

        email_templates = await database.fetch_all(
            select(EmailTemplate)
        )
        

        return templates.TemplateResponse(
            "email-templates.html",
            {
                "request": request,
                "email_templates": email_templates,
                "message": "Template saved successfully."
            }
        )

    except Exception as e:
        email_templates = await database.fetch_all(
            select(EmailTemplate)
        )

        return templates.TemplateResponse(
            "email-templates.html",
            {
                "request": request,
                "email_templates": email_templates,
                "error": str(e)
            }
        )


# LOGIN
@app.post("/login", response_class=HTMLResponse)
async def login(request: Request, email: str = Form(...), password: str = Form(...)):
    query = select(User).where(User.email == email)
    user = await database.fetch_one(query)
    if not user:
        return templates.TemplateResponse("login.html", {"request": request, "error": "Invalid email or password."})
    stored_password = user["password"]
    if not stored_password.startswith("$argon2"):
        new_hashed = hash_password(stored_password)
        await database.execute(
            User.__table__.update()
            .where(User.id == user["id"])
            .values(password=new_hashed)
        )
        stored_password = new_hashed
    if not verify_password(password, stored_password):
        return templates.TemplateResponse("login.html", {"request": request, "error": "Invalid email or password."})
    token = create_access_token({"sub": user["username"]})
    response = RedirectResponse(url="/dashboard", status_code=302)
    response.set_cookie(key="access_token", value=token, httponly=True, max_age=3600)
    return response

@app.get("/login.html")
async def login_html_redirect():
    return RedirectResponse(url="/login")

@app.get("/login", response_class=HTMLResponse)
async def root(request: Request):
    return templates.TemplateResponse("login.html", {"request": request})



@app.get("/", response_class=HTMLResponse)
async def root(request: Request):
    return templates.TemplateResponse("login.html", {"request": request})


@app.get("/dashboard/add-lead", response_class=HTMLResponse)
@login_required
async def add_lead_page(request: Request, success: int = 0):
    current_user = await get_current_user_data(request)
    return templates.TemplateResponse(
        "add_lead.html",
        {
            "current_user": current_user,
            "request": request,
            "success": success,
            "error": None
        }
    )


@app.post("/dashboard/add-lead", response_class=HTMLResponse)
@login_required
async def add_lead(request: Request, name: str = Form(...), email: str = Form(...), company: str = Form(None), status: str = Form(None), notes: str = Form(None)):
    existing_lead = await database.fetch_one(Lead.__table__.select().where(Lead.email == email))
    if existing_lead:
        return templates.TemplateResponse(
            "add_lead.html",
            {"request": request, "error": f"Lead with email '{email}' already exists."}
        )
    status = "pending"
    query = Lead.__table__.insert().values(name=name, email=email, company=company, status=status, notes=notes)
    await database.execute(query)
    return RedirectResponse(url="/dashboard/add-lead?success=1", status_code=302)

@app.post("dashboard/meetings", response_class=HTMLResponse)
@login_required
async def add_lead(
    request: Request, 
    name: str = Form(...), 
    email: str = Form(...), 
    company: str = Form(None), 
    status: str = Form(None), 
    notes: str = Form(None)
):
    existing_lead = await database.fetch_one(Lead.__table__.select().where(Lead.email == email))
    if existing_lead:
        return templates.TemplateResponse(
            "add_lead.html",
            {"request": request, "error": f"Lead with email '{email}' already exists."}
        )
    status = "pending"
    query = Lead.__table__.insert().values(
        name=name, email=email, company=company, status=status, notes=notes
    )
    await database.execute(query)
    return RedirectResponse(url="/dashboard/add-lead?success=1", status_code=302)

@app.get("/dashboard/meetings", response_class=HTMLResponse)
@login_required
async def dashboard_meetings(
    request: Request,
    query: str = None,
    page: int = Query(1, ge=1)
):
    query_stmt = (
        select(
            Lead.id,
            Lead.name,
            Lead.email,
            Lead.meeting_date,
            Lead.meeting_scheduled_at,
            Lead.last_followup_sent_at,
            Lead.meeting_status,
            Lead.calendly_event_uri

        )
        .where(Lead.meeting_date.isnot(None))
        .order_by(desc(Lead.meeting_scheduled_at))
    )

    if query:
        query = query.replace("+", " ")
        query = re.sub(r"\s+", " ", query).strip()
        if query:
            query_stmt = query_stmt.where(
                or_(
                    Lead.name.ilike(f"%{query}%"),
                    Lead.email.ilike(f"%{query}%"),
                    Lead.meeting_status.ilike(f"%{query}%")
                )
            )
    # Pagination
    page_size = 10
    offset = (page - 1) * page_size
    query_stmt_page = query_stmt.offset(offset).limit(page_size + 1)
    meetings = await database.fetch_all(query_stmt_page)
    has_next = len(meetings) > page_size
    meetings = meetings[:page_size]
    current_user = await get_current_user_data(request)
    return templates.TemplateResponse(
        "meeting.html",
        {
            "request": request,
            "current_user": current_user,
            "meetings": meetings,
            "search_query": query or "",
            "page": page,
            "has_next": has_next,
            "datetime": datetime
        }
    )

@app.get("/dashboard/leads", response_class=HTMLResponse)
@login_required
async def dashboard_leads(
    request: Request, 
    query: str = None, 
    action: str = Query(None), 
    page: int = Query(1, ge=1)  
):
    # --- Base query for leads ---
    query_stmt = select(Lead).order_by(desc(Lead.created_at))

    # --- Search filter ---
    if query:
        query = query.replace("+", " ")
        query = re.sub(r"\s+", " ", query).strip()
        if query != "":
            query_stmt = query_stmt.where(
                or_(
                    Lead.name.ilike(f"%{query}%"),
                    Lead.email.ilike(f"%{query}%"),
                    Lead.company.ilike(f"%{query}%"),
                    Lead.status.ilike(f"%{query}%"),
                    Lead.notes.ilike(f"%{query}%")
                )
            )

    # --- Export CSV ---
    if action == "export":
        leads = await database.fetch_all(query_stmt)
        def generate_csv():
            output = StringIO()
            writer = csv.writer(output)
            writer.writerow([
                "ID", "Name", "Email", "Company", "Status", "Notes", "Reply Content", "Created At"
            ])
            for lead in leads:
                lead = dict(lead)
                writer.writerow([
                    lead['id'],
                    lead['name'],
                    lead['email'],
                    lead['company'],
                    lead['status'],
                    lead['notes'],
                    lead.get('reply_content', '') or lead.get('replied_auto', ''),
                    lead['created_at']
                ])
            output.seek(0)
            return output
        return StreamingResponse(
            generate_csv(),
            media_type="text/csv",
            headers={"Content-Disposition": "attachment; filename=leads.csv"}
        )

    # --- Pagination ---
    page_size = 10
    offset = (page - 1) * page_size
    query_stmt_page = query_stmt.offset(offset).limit(page_size + 1)  
    leads = await database.fetch_all(query_stmt_page)
    has_next = len(leads) > page_size
    leads = leads[:page_size]

    # --- Current user ---
    current_user = await get_current_user_data(request)

    # --- Notifications: leads with reply_content or replied_auto ---
    # notif_stmt = (
    #     select(Lead)
    #     .where(
    #         or_(
    #             Lead.reply_content != None,
    #             Lead.status == "replied_auto"
    #         )
    #     )
    #     .order_by(desc(Lead.email_replied_at))
    #     # .limit(10)  
    # )
    # notif_leads = await database.fetch_all(notif_stmt)

    # --- Generate notification messages ---
    # notifications = []
    # for lead in notif_leads:
    #     msg_type = "replied_auto" if lead.status else "replied_auto"
    #     notifications.append({
    #         "message": f"The lead {lead.name or 'N/A'} with email {lead.email} has been {msg_type}."
    #     })

    return templates.TemplateResponse(
        "leads.html",
        {
            "request": request,
            "leads": leads,
            "current_user": current_user,
            "search_query": query or "",
            "page": page,
            "has_next": has_next,
            "datetime": datetime,
            "timedelta": timedelta,
            # "notifications": notifications 
        }
    )


@app.get("/dashboard/leads/{lead_id}/send-message", response_class=HTMLResponse)
async def get_send_message(request: Request, lead_id: int):
    # Fetch lead
    query = Lead.__table__.select().where(Lead.id == lead_id)
    lead = await database.fetch_one(query)
    if not lead:
        return HTMLResponse("<h2>Lead not found</h2>", status_code=404)

    # Fetch all email templates
    templates_query = select(EmailTemplate)
    email_templates = await database.fetch_all(templates_query)

    return templates.TemplateResponse("send-message.html", {
        "request": request,
        "lead": lead,
        "email_templates": email_templates,
        "template_count": len(email_templates)
    })


@app.post("/dashboard/leads/{lead_id}/send-message", response_class=HTMLResponse)
async def post_send_message(
    lead_id: int,
    request: Request,
    email: str = Form(...),
    cc: str = Form(None),
    subject: str = Form(...),
    body: str = Form(...)
):
    # Fetch lead
    query = Lead.__table__.select().where(Lead.id == lead_id)
    lead = await database.fetch_one(query)
    if not lead:
        return HTMLResponse("<h2>Lead not found</h2>", status_code=404)

    # Send email
    try:
        send_email(to_email=email, subject=subject, body=body)
        if cc:
            for cc_email in [e.strip() for e in cc.split(",")]:
                send_email(to_email=cc_email, subject=subject, body=body)
    except Exception as e:
        return HTMLResponse(f"<h2>Error sending email: {e}</h2>", status_code=500)

    # Update lead record
    update_query = Lead.__table__.update().where(Lead.id == lead_id).values(
        email_sent_at=datetime.utcnow(),
        original_email_subject=subject,
        original_email_body=body
    )
    await database.execute(update_query)

    return RedirectResponse(url="/dashboard/leads", status_code=302)




@app.get("/dashboard/leads/{lead_id}", response_class=HTMLResponse)
@login_required
async def dashboard_lead_details(request: Request, lead_id: int):
    query = select(Lead).where(Lead.id == lead_id)
    lead = await database.fetch_one(query)
    if not lead:
        return HTMLResponse("<h2>Lead not found</h2>", status_code=404)
    return templates.TemplateResponse("lead_details.html", {"request": request, "lead": lead})

@app.post("/dashboard/leads/{lead_id}/delete")
@login_required
async def dashboard_delete_lead(request: Request, lead_id: int):
    query = Lead.__table__.delete().where(Lead.id == lead_id)
    await database.execute(query)
    return RedirectResponse(url="/dashboard/leads", status_code=302)






@app.get("/dashboard/notifications", response_class=HTMLResponse)
@login_required
async def notifications_page(request: Request):

    current_user = await get_current_user_data(request)

    notif_stmt = (
        select(Lead)
        .where(
            or_(
                Lead.reply_content.isnot(None),
                Lead.status == "replied_auto"
            )
        )
        .order_by(desc(Lead.email_replied_at))
        # .limit(20)
    )

    leads = await database.fetch_all(notif_stmt)

    notifications = []
    for lead in leads:
        notifications.append({
            "message": f"📩 {lead.name or 'N/A'} ({lead.email}) replied"
        })

    return templates.TemplateResponse(
        "notifications.html",   
        {
            "request": request,
            "current_user": current_user,     
            "notifications": notifications,  
            "notification_count": len(notifications)
        }
    )

@app.get("/dashboard/send-proposal/{lead_id}")
async def send_proposal_form(request: Request, lead_id: int):
    # Fetch lead
    query = Lead.__table__.select().where(Lead.id == lead_id)
    lead = await database.fetch_one(query)
    if not lead:
        return HTMLResponse("<h2>Lead not found</h2>", status_code=404)

    # Fetch all email templates
    templates_query = select(EmailTemplate)
    email_templates = await database.fetch_all(templates_query)
    email_templates = [dict(t) for t in email_templates]  # Convert to dicts for Jinja

    return templates.TemplateResponse(
        "send_proposal.html",
        {
            "request": request,
            "lead": dict(lead),
            "email_templates": email_templates,
            "template_count": len(email_templates)
        }
    )

EMAIL_HOST='smtp.gmail.com'
EMAIL_PORT=465
EMAIL_HOST_USER='staffing@esferadigitalsolutions.com'
EMAIL_HOST_PASSWORD='ssoqykruruihvnwo'
templates = Jinja2Templates(directory="templates")

@app.post("/dashboard/send-proposal/{lead_id}", response_class=HTMLResponse)
async def send_proposal(
    request: Request,
    lead_id: int,
    email: str = Form(...),
    cc: str = Form(None),
    subject: str = Form(...),
    body: str = Form(...)
):
    lead = await database.fetch_one(
        Lead.__table__.select().where(Lead.id == lead_id)
    )

    if not lead:
        return templates.TemplateResponse(
            "send_proposal.html",
            {"request": request, "error": "Lead not found", "lead": None},
            status_code=404
        )
    msg = EmailMessage()
    msg["From"] = EMAIL_HOST_USER
    msg["To"] = email
    msg["Subject"] = subject
    msg["Reply-To"] = EMAIL_HOST_USER
    if cc:
        msg["Cc"] = cc
    msg.set_content(body)
    msg.add_alternative(body.replace("\n", "<br>"), subtype="html")
    try:
        with smtplib.SMTP_SSL(EMAIL_HOST, EMAIL_PORT) as server:
            server.login(EMAIL_HOST_USER, EMAIL_HOST_PASSWORD)
            server.send_message(msg)
    except Exception as e:
        return templates.TemplateResponse(
            "send_proposal.html",
            {
                "request": request,
                "lead": lead,
                "error": f"Email failed: {str(e)}"
            }
        )

    update_query = (
        Lead.__table__
        .update()
        .where(Lead.id == lead_id)
        .values(
            status="sent",
            original_email_body=body,
            email_sent_at=datetime.utcnow()
        )
    )

    await database.execute(update_query)

    updated_lead = await database.fetch_one(
        Lead.__table__.select().where(Lead.id == lead_id)
    )

    return templates.TemplateResponse(
        "send_proposal.html",
        {
            "request": request,
            "lead": updated_lead,
            "message": "Proposal sent successfully!"
        }
    )

@app.get("/dashboard/bulk-email")
# @login_required
async def bulk_email_form(request: Request):
    # Fetch leads if needed
    leads = await database.fetch_all(select(Lead))
    return templates.TemplateResponse("bulk-email.html", {"request": request, "leads": leads})


@app.post("/dashboard/bulk-email")
async def send_bulk_email(
    request: Request,
    lead_ids: list[int] = Form(...),  
    cc: str = Form(None),
    subject: str = Form(...),
    body: str = Form(...)
):
    # Fetch leads from DB
    leads = await database.fetch_all(
        Lead.__table__.select().where(Lead.id.in_(lead_ids))
    )
    if not leads:
        return templates.TemplateResponse(
            "bulk-email.html",
            {"request": request, "error": "No leads selected.", "leads": []}
        )

    errors = []

    for lead in leads:
        # Personalize body and subject
        personalized_body = body.replace("{{ lead.name }}", lead["name"] or "")
        personalized_subject = subject.replace("{{ lead.name }}", lead["name"] or "")

        # Prepare email message
        msg = EmailMessage()
        msg['From'] = EMAIL_HOST_USER
        msg['To'] = lead["email"]
        msg['Subject'] = personalized_subject

        if cc:
            cc_list = [e.strip() for e in cc.split(",") if e.strip()]
            if cc_list:
                msg['Cc'] = ", ".join(cc_list)

        msg.set_content(personalized_body)
        html_body = personalized_body.replace("\n", "<br>")
        msg.add_alternative(html_body, subtype="html")

        try:
            # Send email via SMTP
            with smtplib.SMTP_SSL(EMAIL_HOST, EMAIL_PORT) as server:
                server.login(EMAIL_HOST_USER, EMAIL_HOST_PASSWORD)
                server.send_message(msg)

            # Update lead record:
            # 1. Mark as sent
            # 2. Save original body & subject
            await database.execute(
                Lead.__table__.update()
                .where(Lead.id == lead["id"])
                .values(
                    status="sent",
                    email_sent_at=datetime.utcnow(),
                    original_email_body=personalized_body,
                    original_email_subject=personalized_subject
                )
            )

        except Exception as e:
            errors.append(f"{lead['email']}: {str(e)}")
            continue

    message = "Bulk emails sent successfully!" if not errors else None
    error = None if not errors else "Some emails failed: " + ", ".join(errors)

    return templates.TemplateResponse(
        "bulk-email.html",
        {"request": request, "message": message, "error": error, "leads": leads}
    )



@app.get("/dashboard", response_class=HTMLResponse)
@login_required
async def dashboard(request: Request, year: int | None = None):

    # =====================
    # YEAR HANDLING
    # =====================
    today = datetime.now()
    selected_year = year or today.year

    # If viewing past year, lock date to Dec 31
    if selected_year < today.year:
        reference_date = datetime(selected_year, 12, 31)
    else:
        reference_date = today

    # =====================
    # INIT VARIABLES
    # =====================
    daily_labels = []
    daily_data = []
    daily_sent_data = []
    daily_responses_data = []

    monthly_labels = []
    monthly_online_data = []
    monthly_offline_data = []
    monthly_responses_data = []

    # =====================
    # DASHBOARD CARDS
    # =====================
    ham_count = await database.fetch_val(
        select(func.count()).select_from(Lead)
        .where(Lead.email_classification == "ham")
    )

    spam_count = await database.fetch_val(
        select(func.count()).select_from(Lead)
        .where(Lead.email_classification == "spam")
    )

    undecided_count = await database.fetch_val(
        select(func.count()).select_from(Lead)
        .where(Lead.email_classification == "undecided")
    )
    total_leads = await database.fetch_val(
        select(func.count(Lead.id))  # count the primary key column
    )


    # total_leads = await database.fetch_val(
    #     select(func.count()).select_from(Lead)
    # )

    sent_leads = await database.fetch_val(
        select(func.count()).select_from(Lead)
        .where(or_(Lead.status == "sent", Lead.status == "replied_auto"))
    )

    meetings = await database.fetch_val(
        select(func.count()).select_from(Lead)
        .where(Lead.meeting_date.isnot(None))
    )

    replies = await database.fetch_val(
        select(func.count()).select_from(Lead)
        .where(Lead.reply_content.isnot(None))
    )

    first_day_of_month = datetime(
        selected_year,
        reference_date.month,
        1
    )

    new_leads = await database.fetch_val(
        select(func.count()).select_from(Lead)
        .where(Lead.created_at >= first_day_of_month)
    )

    # =====================
    # DAILY DATA (LAST 7 DAYS OF SELECTED YEAR)
    # =====================
    for i in range(6, -1, -1):
        day = reference_date - timedelta(days=i)

        start = day.replace(hour=0, minute=0, second=0, microsecond=0)
        end = day.replace(hour=23, minute=59, second=59, microsecond=999999)

        daily_labels.append(day.strftime("%d %b"))

        daily_data.append(
            await database.fetch_val(
                select(func.count()).select_from(Lead)
                .where(Lead.created_at.between(start, end))
            )
        )

        daily_sent_data.append(
            await database.fetch_val(
                select(func.count()).select_from(Lead)
                .where(Lead.email_sent_at.between(start, end))
            )
        )

        daily_responses_data.append(
            await database.fetch_val(
                select(func.count()).select_from(Lead)
                .where(Lead.email_replied_at.between(start, end))
            )
        )

    # =====================
    # MONTHLY DATA (JAN–DEC OF SELECTED YEAR)
    # =====================
    for month in range(1, 13):
        start = datetime(selected_year, month, 1)

        if month == 12:
            end = datetime(selected_year + 1, 1, 1)
        else:
            end = datetime(selected_year, month + 1, 1)

        monthly_labels.append(start.strftime("%b"))

        monthly_online_data.append(
            await database.fetch_val(
                select(func.count()).select_from(Lead)
                .where(Lead.created_at.between(start, end))
            )
        )

        monthly_offline_data.append(
            await database.fetch_val(
                select(func.count()).select_from(Lead)
                .where(Lead.email_sent_at.between(start, end))
            )
        )

        monthly_responses_data.append(
            await database.fetch_val(
                select(func.count()).select_from(Lead)
                .where(Lead.email_replied_at.between(start, end))
            )
        )
    current_user = await get_current_user_data(request)

    return templates.TemplateResponse(
        "dashboard.html",
        {
            "request": request,
            "current_user": current_user,
            "selected_year": selected_year,

            # Cards
            "total_leads": total_leads,
            "sent_leads": sent_leads,
            "meetings": meetings,
            "replies": replies,
            "new_leads": new_leads,

            # Daily charts
            "daily_labels": daily_labels,
            "daily_data": daily_data,
            "daily_sent_data": daily_sent_data,
            "daily_responses_data": daily_responses_data,

            # Monthly charts
            "monthly_labels": monthly_labels,
            "monthly_online_data": monthly_online_data,
            "monthly_offline_data": monthly_offline_data,
            "monthly_responses_data": monthly_responses_data,

            # Email classification
            "ham_count": ham_count,
            "spam_count": spam_count,
            "undecided_count": undecided_count
        }
    )
# FORGOT PASSWORD
@app.get("/forgot-password", response_class=HTMLResponse)
def forgot_password_page(request: Request):
    return templates.TemplateResponse("forgot_password.html", {"request": request, "error": None, "message": None})

@app.post("/forgot-password", response_class=HTMLResponse)
async def forgot_password(request: Request, email: str = Form(...)):
    query = select(User).where(User.email == email)
    user = await database.fetch_one(query)
    if not user:
        return templates.TemplateResponse("forgot_password.html", {"request": request, "error": "Email not found", "message": None})
    otp = str(random.randint(100000, 999999))
    otp_store[email] = {"otp": otp, "expires": time.time() + OTP_EXPIRY_SECONDS}
    send_email(email, "Your OTP for Password Reset", f"Your OTP is: {otp}")
    return templates.TemplateResponse("verify_otp.html", {"request": request, "email": email, "error": None})

@app.post("/verify-otp", response_class=HTMLResponse)
async def verify_otp(request: Request, email: str = Form(...), otp: str = Form(...)):
    if email not in otp_store:
        return templates.TemplateResponse("verify_otp.html", {"request": request, "email": email, "error": "No OTP found"})
    stored_otp = otp_store[email]["otp"]
    if time.time() > otp_store[email]["expires"]:
        otp_store.pop(email)
        return templates.TemplateResponse("verify_otp.html", {"request": request, "email": email, "error": "OTP expired"})
    if otp != stored_otp:
        return templates.TemplateResponse("verify_otp.html", {"request": request, "email": email, "error": "Invalid OTP"})

    return templates.TemplateResponse("reset_password.html", {"request": request, "email": email, "error": None})

@app.post("/reset-password", response_class=HTMLResponse)
async def reset_password(request: Request, email: str = Form(...), password: str = Form(...), confirm_password: str = Form(...)):
    if password != confirm_password:
        return templates.TemplateResponse("reset_password.html", {"request": request, "email": email, "error": "Passwords do not match"})
    hashed_password = hash_password(password)
    update_query = User.__table__.update().where(User.email == email).values(password=hashed_password)
    await database.execute(update_query)
    otp_store.pop(email, None)
    return RedirectResponse(url="/login", status_code=302)


@app.get("/dashboard/upload-csv", response_class=HTMLResponse)
@login_required
async def upload_csv_page(request: Request, success: int = 0):
    current_user = await get_current_user_data(request)
    return templates.TemplateResponse(
        "upload_csv.html",
        {
            "current_user": current_user,
            "request": request,
            "success": success
        }
    )


@app.post("/dashboard/upload-csv")
@login_required
async def upload_csv_file(request: Request, file: UploadFile = File(...)):
    if not file.filename.endswith(".csv"):
        return templates.TemplateResponse(
            "upload_csv.html",
            {
                "request": request,
                "error": "Only CSV files are allowed. Please upload a valid CSV file."
            }
        )

    content = await file.read()
    csv_text = content.decode("utf-8")
    file_io = io.StringIO(csv_text)
    reader = csv.DictReader(file_io)
    required_columns = {"email", "name", "company"}

    if not reader.fieldnames or not required_columns.issubset(reader.fieldnames):
        return templates.TemplateResponse(
            "upload_csv.html",
            {
                "request": request,
                "error": "Invalid CSV format! CSV must contain columns: name, email, company."
            }
        )

    # normal upload logic
    csv_emails = [row.get("email") for row in reader if row.get("email")]

    file_io.seek(0)
    reader = csv.DictReader(file_io)
    query = select(Lead.email).where(Lead.email.in_(csv_emails))
    existing_emails = {row["email"] for row in await database.fetch_all(query)}

    for row in reader:
        email = row.get("email")
        if not email or email in existing_emails:
            continue

        await database.execute(
            Lead.__table__.insert().values(
                name=row.get("name"),
                email=email,
                company=row.get("company"),
                status="pending"
            )
        )
    return RedirectResponse(url="/dashboard/upload-csv?success=1", status_code=302)

# CHANGE PASSWORD
@app.get("/dashboard/change-password", response_class=HTMLResponse)
async def change_password_page(request: Request, message: str = None, access_token: str = Cookie(None)):
    if not access_token:
        return RedirectResponse(url="/login")
    username = get_current_user(access_token)
    current_user = await get_current_user_data(request)
    return templates.TemplateResponse(
        "change_password.html",
        {
            "request": request,
            "current_user": current_user,
            "username": username,
            "error": None,
            "message": message
        }
    )

@app.post("/dashboard/change-password", response_class=HTMLResponse)
@login_required
async def change_password(
    request: Request,
    current_password: str = Form(...),
    new_password: str = Form(...),
    confirm_password: str = Form(...),
    access_token: str = Cookie(None)
):
    if not access_token:
        return RedirectResponse(url="/login")
    username = get_current_user(access_token)
    query = select(User).where(User.username == username)
    user = await database.fetch_one(query)

    if not verify_password(current_password, user["password"]):
        return templates.TemplateResponse("change_password.html", {"request": request, "username": username, "error": "Current password is incorrect."})

    if new_password != confirm_password:
        return templates.TemplateResponse("change_password.html", {"request": request, "username": username, "error": "Passwords do not match."})

    hashed_password = hash_password(new_password)
    update_query = User.__table__.update().where(User.username == username).values(password=hashed_password)
    await database.execute(update_query)

    return templates.TemplateResponse("change_password.html", {"request": request, "username": username, "error": None, "message": "Password changed successfully."})



@app.get("/dashboard/profile", response_class=HTMLResponse)
@login_required
async def profile_page(request: Request):
    username = request.state.username

    # Fetch user from DB
    user_row = await database.fetch_one(
        select(User).where(User.username == username)
    )
    # If user exists, build full user dict including new fields
    if user_row:
        user = {
            "username": user_row.username,
            "email": user_row.email,
            "phone_number": user_row.phone_number,
            "image": user_row.image
        }
    else:
        user = {"username": "", "email": "", "phone_number": "", "image": None}

    current_user = await get_current_user_data(request)

    return templates.TemplateResponse(
        "profile.html",
        {
            "request": request,
            "current_user": current_user,
            "user": user,
            "message": None,
            "error": None
        }
    )

UPLOAD_DIR = "static/uploads" 

@app.post("/dashboard/profile", response_class=HTMLResponse)
@login_required
async def update_profile(
    request: Request,
    username: str = Form(...),
    email: str = Form(...),
    phone_number: str = Form(None),
    image: UploadFile = File(None)
):
    current_username = request.state.username

    # Check if email is taken by another user
    existing_email = await database.fetch_one(
        select(User).where(and_(User.email == email, User.username != current_username))
    )
    if existing_email:
        return templates.TemplateResponse(
            "profile.html",
            {
                "request": request,
                "user": {"username": username, "email": email, "phone_number": phone_number, "image": None},
                "error": "Email already in use.",
                "message": None
            }
        )

    update_data = {"username": username, "email": email, "phone_number": phone_number}

    if image and image.filename:
        os.makedirs(UPLOAD_DIR, exist_ok=True)

        file_path = os.path.join(UPLOAD_DIR, image.filename)

        with open(file_path, "wb") as f:
            shutil.copyfileobj(image.file, f)

        update_data["image"] = f"/static/uploads/{image.filename}"

    await database.execute(
        User.__table__.update()
        .where(User.username == current_username)
        .values(**update_data)
    )

    # Prepare user dict for template
    updated_user = {
        "username": username,
        "email": email,
        "phone_number": phone_number,
        "image": update_data.get("image")
    }

    return templates.TemplateResponse(
        "profile.html",
        {
            "request": request,
            "user": updated_user,
            "message": "Profile updated successfully!",
            "error": None
        }
    )

async def get_current_user_data(request: Request):
    token = request.cookies.get("access_token")
    if not token:
        return None
    try:
        username = get_current_user(token)
    except:
        return None

    user_row = await database.fetch_one(select(User).where(User.username == username))
    if not user_row:
        return None
    return {
        "username": user_row.username,
        "email": user_row.email,
        "phone_number": user_row.phone_number,
        "image": user_row.image
    }

@app.get("/logout")
async def logout():
    response = RedirectResponse(url="/login", status_code=302)
    response.delete_cookie("access_token")
    return response

# RUN APP
if __name__ == "__main__":
    logging.getLogger("uvicorn.access").setLevel(logging.WARNING)
    uvicorn.run("main:app", host="0.0.0.0", port=8080, reload=True)