main.py

import os
import time
import hmac
import html
import asyncio
import hashlib
import secrets
import sqlite3
import threading
from pathlib import Path
from datetime import datetime, timedelta
from typing import Optional
from urllib.parse import urlencode

import aiohttp
import requests
from fastapi import FastAPI, Request, HTTPException
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel

app = FastAPI(title="Domify Academy Backend", version="4.2")

app.add_middleware(
    CORSMiddleware,
    allow_origins=[
        "https://domify-academy.free.nf",
        "http://localhost:3000",
    ],
    allow_origin_regex=r"https://.*\.free\.nf",
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

DB_PATH = os.getenv("DB_PATH", "domify_users.db")
PUBLIC_BASE_URL = os.getenv("PUBLIC_BASE_URL", "https://domify-signup.hf.space").rstrip("/")
BREVO_API_KEY = os.getenv("BREVO_API_KEY", "")
BREVO_FROM_EMAIL = os.getenv("BREVO_FROM_EMAIL", "domifyacademy@gmail.com")
BREVO_FROM_NAME = os.getenv("BREVO_FROM_NAME", "Domify Academy")
LEMON_API_KEY = os.getenv("LEMON_API_KEY", "")
LEMON_WEBHOOK_SECRET = os.getenv("LEMON_WEBHOOK_SECRET", "")
APPS_SCRIPT_URL = os.getenv("APPS_SCRIPT_URL", "")
INTERNAL_API_TOKEN = os.getenv("INTERNAL_API_TOKEN", "")
ADMIN_API_TOKEN = os.getenv("ADMIN_API_TOKEN", "")

ADMIN_IPS = {
    "fe80::f26d:78ff:fe61:be53",
    "192.168.7.71",
    "10.92.98.240",
}

LEMON_PRODUCTS = {
    1519026: {"tier": "Professional", "days": 30},
    1519043: {"tier": "Master", "days": 30},
    1519056: {"tier": "Certificate", "days": 0},
}

TEMPLATE_DIR = Path("templates")
TEMPLATE_DIR.mkdir(exist_ok=True)
DB_LOCK = threading.Lock()
PROCESSING_TIMEOUT = timedelta(hours=1)


def now_iso() -> str:
    return datetime.utcnow().isoformat()


def add_days(days: int) -> str:
    return (datetime.utcnow() + timedelta(days=days)).isoformat()


def add_minutes(minutes: int) -> str:
    return (datetime.utcnow() + timedelta(minutes=minutes)).isoformat()


def parse_iso(value: Optional[str]) -> Optional[datetime]:
    if not value:
        return None
    try:
        return datetime.fromisoformat(value)
    except Exception:
        return None


def get_client_ip(request: Request) -> str:
    x_real_ip = request.headers.get("x-real-ip")
    if x_real_ip:
        return x_real_ip.strip()
    forwarded = request.headers.get("x-forwarded-for")
    if forwarded:
        return forwarded.split(",")[0].strip()
    return request.client.host if request.client else ""


def require_internal_token(request: Request) -> None:
    if not INTERNAL_API_TOKEN:
        raise HTTPException(status_code=500, detail="INTERNAL_API_TOKEN is not set")
    token = request.headers.get("x-api-token") or request.query_params.get("token")
    if token != INTERNAL_API_TOKEN:
        raise HTTPException(status_code=403, detail="Forbidden")


def get_db() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH, timeout=30, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    return conn


def init_db() -> None:
    conn = get_db()
    try:
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA synchronous=NORMAL;")

        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS users (
                user_id TEXT PRIMARY KEY,
                full_name TEXT,
                email TEXT UNIQUE,
                signup_date TEXT,
                country TEXT,
                source TEXT,
                ip TEXT,
                timestamp TEXT,
                tier TEXT DEFAULT 'Free',
                expiry TEXT,
                cert_paid INTEGER DEFAULT 0,
                cert_generated INTEGER DEFAULT 0
            )
            """
        )

        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS certificates (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                verification_code TEXT UNIQUE,
                user_name TEXT,
                user_email TEXT,
                user_id TEXT,
                tier TEXT,
                date TEXT,
                drive_url TEXT
            )
            """
        )

        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS email_subscribers (
                email TEXT PRIMARY KEY,
                name TEXT DEFAULT 'Subscriber',
                country TEXT,
                source TEXT,
                article TEXT,
                signup_date TEXT,
                last_sent_at TEXT,
                last_email_type TEXT,
                next_email_date TEXT,
                sequence_state TEXT DEFAULT 'welcome_pending',
                state_updated_at TEXT,
                unsubscribe INTEGER DEFAULT 0,
                unsubscribe_token TEXT
            )
            """
        )

        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS email_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                email TEXT,
                email_type TEXT,
                status TEXT DEFAULT 'sent',
                error TEXT,
                sent_at TEXT DEFAULT (datetime('now'))
            )
            """
        )

        conn.commit()
    finally:
        conn.close()


init_db()


def load_template(name: str) -> str:
    path = TEMPLATE_DIR / f"{name}.html"
    if not path.exists():
        return f"<html><body><h1>Missing template: {html.escape(name)}</h1></body></html>"
    return path.read_text(encoding="utf-8")


def render_template(name: str, **values) -> str:
    text = load_template(name)
    for key, value in values.items():
        text = text.replace(f"{{{{{key}}}}}", str(value))
    return text


def build_url(path: str, **params) -> str:
    return f"{PUBLIC_BASE_URL}{path}?{urlencode(params)}"


def log_email(email: str, email_type: str, status: str = "sent", error: Optional[str] = None) -> None:
    conn = get_db()
    try:
        with DB_LOCK:
            conn.execute(
                "INSERT INTO email_log (email, email_type, status, error) VALUES (?, ?, ?, ?)",
                (email, email_type, status, error),
            )
            conn.commit()
    finally:
        conn.close()


def send_brevo_email_sync(to_email: str, to_name: str, subject: str, html_content: str) -> bool:
    if not BREVO_API_KEY:
        print("BREVO_API_KEY missing")
        return False

    payload = {
        "sender": {"name": BREVO_FROM_NAME, "email": BREVO_FROM_EMAIL},
        "to": [{"email": to_email, "name": to_name}],
        "subject": subject,
        "htmlContent": html_content,
        "replyTo": {"email": BREVO_FROM_EMAIL, "name": BREVO_FROM_NAME},
    }
    headers = {
        "accept": "application/json",
        "api-key": BREVO_API_KEY,
        "content-type": "application/json",
    }

    try:
        r = requests.post("https://api.brevo.com/v3/smtp/email", json=payload, headers=headers, timeout=20)
        print(f"Brevo [{r.status_code}] {to_email} -> {subject}")
        return r.status_code in (200, 201)
    except Exception as e:
        print(f"Brevo error: {e}")
        return False


async def send_brevo_email(to_email: str, to_name: str, subject: str, html_content: str) -> bool:
    return await asyncio.to_thread(send_brevo_email_sync, to_email, to_name, subject, html_content)


async def post_apps_script(payload: dict) -> bool:
    if not APPS_SCRIPT_URL:
        return False

    def _post() -> bool:
        try:
            r = requests.post(APPS_SCRIPT_URL, json=payload, timeout=20)
            print(f"Apps Script [{r.status_code}]")
            return r.ok
        except Exception as e:
            print(f"Apps Script error: {e}")
            return False

    return await asyncio.to_thread(_post)


def upsert_user(data: dict, client_ip: str = "") -> None:
    conn = get_db()
    try:
        with DB_LOCK:
            conn.execute(
                """
                INSERT INTO users (
                    user_id, full_name, email, signup_date, country, source, ip,
                    timestamp, tier, expiry, cert_paid, cert_generated
                )
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(email) DO UPDATE SET
                    user_id=excluded.user_id,
                    full_name=excluded.full_name,
                    signup_date=excluded.signup_date,
                    country=excluded.country,
                    source=excluded.source,
                    ip=excluded.ip,
                    timestamp=excluded.timestamp,
                    tier=excluded.tier,
                    expiry=excluded.expiry,
                    cert_paid=excluded.cert_paid,
                    cert_generated=excluded.cert_generated
                """,
                (
                    data["userId"],
                    data["fullName"],
                    data["email"],
                    data["signupDate"],
                    data.get("country", ""),
                    data.get("source", ""),
                    data.get("ip") or client_ip,
                    data.get("timestamp", now_iso()),
                    data.get("tier", "Free"),
                    data.get("expiry"),
                    int(bool(data.get("certPaid", False))),
                    int(bool(data.get("certGenerated", False))),
                ),
            )
            conn.commit()
    finally:
        conn.close()


def ensure_subscriber(email: str, name: str, country: str = "", source: str = "", article: str = "") -> str:
    conn = get_db()
    try:
        row = conn.execute(
            "SELECT unsubscribe_token FROM email_subscribers WHERE email = ?",
            (email,),
        ).fetchone()
        token = row["unsubscribe_token"] if row and row["unsubscribe_token"] else secrets.token_urlsafe(24)

        with DB_LOCK:
            if row:
                conn.execute(
                    """
                    UPDATE email_subscribers
                    SET name = ?, country = ?, source = ?, article = ?, unsubscribe_token = ?
                    WHERE email = ?
                    """,
                    (name, country, source, article, token, email),
                )
            else:
                conn.execute(
                    """
                    INSERT INTO email_subscribers (
                        email, name, country, source, article, signup_date,
                        last_sent_at, last_email_type, next_email_date,
                        sequence_state, state_updated_at, unsubscribe, unsubscribe_token
                    )
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?)
                    """,
                    (
                        email,
                        name,
                        country,
                        source,
                        article,
                        now_iso(),
                        None,
                        None,
                        add_minutes(1),
                        "welcome_pending",
                        now_iso(),
                        token,
                    ),
                )
            conn.commit()
        return token
    finally:
        conn.close()


def mark_subscriber_state(email: str, state: str, next_date: Optional[str], email_type: Optional[str] = None) -> None:
    conn = get_db()
    try:
        with DB_LOCK:
            conn.execute(
                """
                UPDATE email_subscribers
                SET sequence_state = ?, next_email_date = ?, last_sent_at = ?, last_email_type = ?, state_updated_at = ?
                WHERE email = ?
                """,
                (state, next_date, now_iso(), email_type, now_iso(), email),
            )
            conn.commit()
    finally:
        conn.close()


def mark_retry(email: str, expected_state: str, minutes: int = 30) -> None:
    conn = get_db()
    try:
        with DB_LOCK:
            conn.execute(
                """
                UPDATE email_subscribers
                SET sequence_state = ?, next_email_date = ?, state_updated_at = ?
                WHERE email = ?
                """,
                (expected_state, add_minutes(minutes), now_iso(), email),
            )
            conn.commit()
    finally:
        conn.close()


def processing_stale(row: sqlite3.Row) -> bool:
    updated = parse_iso(row["state_updated_at"])
    if not updated:
        return True
    return datetime.utcnow() - updated > PROCESSING_TIMEOUT


def claim_email_step(email: str, expected_state: str, processing_state: str, require_due: bool = True) -> Optional[sqlite3.Row]:
    conn = get_db()
    try:
        with DB_LOCK:
            row = conn.execute(
                "SELECT * FROM email_subscribers WHERE email = ?",
                (email,),
            ).fetchone()
            if not row or row["unsubscribe"]:
                return None

            current_state = row["sequence_state"] or "welcome_pending"

            if current_state == processing_state and processing_stale(row):
                conn.execute(
                    "UPDATE email_subscribers SET sequence_state = ?, state_updated_at = ? WHERE email = ?",
                    (expected_state, now_iso(), email),
                )
                conn.commit()
                row = conn.execute(
                    "SELECT * FROM email_subscribers WHERE email = ?",
                    (email,),
                ).fetchone()
                current_state = row["sequence_state"]

            if current_state != expected_state:
                return None

            if require_due and expected_state != "welcome_pending":
                due_dt = parse_iso(row["next_email_date"])
                if due_dt and datetime.utcnow() < due_dt:
                    return None

            conn.execute(
                """
                UPDATE email_subscribers
                SET sequence_state = ?, state_updated_at = ?, last_sent_at = ?
                WHERE email = ?
                """,
                (processing_state, now_iso(), now_iso(), email),
            )
            conn.commit()
            return row
    finally:
        conn.close()


async def send_sequence_email(
    email: str,
    name: str,
    template: str,
    subject: str,
    email_type: str,
    expected_state: str,
    processing_state: str,
    next_state: str,
    next_days: int,
    unsubscribe_token: str,
) -> bool:
    claimed = claim_email_step(email, expected_state, processing_state)
    if not claimed:
        return False

    unsubscribe_link = build_url("/unsubscribe", email=email, token=unsubscribe_token)
    html_body = render_template(
        template,
        name=name,
        email=email,
        unsubscribe_link=unsubscribe_link,
        base_url=PUBLIC_BASE_URL,
    )

    ok = await send_brevo_email(email, name, subject, html_body)
    if ok:
        log_email(email, email_type, "sent", None)
        mark_subscriber_state(email, next_state, add_days(next_days), email_type)
    else:
        log_email(email, email_type, "failed", "Brevo send failed")
        mark_retry(email, expected_state, 30)
    return ok


async def send_welcome_email(email: str, name: str, unsubscribe_token: str) -> bool:
    claimed = claim_email_step(email, "welcome_pending", "welcome_processing", require_due=False)
    if not claimed:
        return False

    unsubscribe_link = build_url("/unsubscribe", email=email, token=unsubscribe_token)
    html_body = render_template(
        "welcome",
        name=name,
        email=email,
        unsubscribe_link=unsubscribe_link,
        base_url=PUBLIC_BASE_URL,
    )

    subject = f"Welcome to Domify Academy, {name}! 🎓"
    ok = await send_brevo_email(email, name, subject, html_body)
    if ok:
        log_email(email, "welcome", "sent", None)
        mark_subscriber_state(email, "edu_day2_pending", add_days(2), "welcome")
    else:
        log_email(email, "welcome", "failed", "Brevo send failed")
        mark_retry(email, "welcome_pending", 30)
    return ok


async def process_subscriber(row: sqlite3.Row) -> None:
    email = row["email"]
    name = row["name"] or "Subscriber"
    token = row["unsubscribe_token"] or secrets.token_urlsafe(24)
    state = row["sequence_state"] or "welcome_pending"

    if state == "welcome_pending":
        await send_welcome_email(email, name, token)
        return

    if state == "edu_day2_pending":
        await send_sequence_email(
            email=email,
            name=name,
            template="educational",
            subject="The Cyber Trick People Miss Every Day",
            email_type="educational_day2",
            expected_state="edu_day2_pending",
            processing_state="edu_day2_processing",
            next_state="soft_pitch_pending",
            next_days=2,
            unsubscribe_token=token,
        )
        return

    if state == "soft_pitch_pending":
        await send_sequence_email(
            email=email,
            name=name,
            template="soft_pitch",
            subject="What You Get When You Go Beyond Free",
            email_type="soft_pitch",
            expected_state="soft_pitch_pending",
            processing_state="soft_pitch_processing",
            next_state="testimonial_pending",
            next_days=2,
            unsubscribe_token=token,
        )
        return

    if state == "testimonial_pending":
        await send_sequence_email(
            email=email,
            name=name,
            template="testimonial",
            subject="A Real Result From a Domify Student",
            email_type="testimonial",
            expected_state="testimonial_pending",
            processing_state="testimonial_processing",
            next_state="hard_pitch_pending",
            next_days=2,
            unsubscribe_token=token,
        )
        return

    if state == "hard_pitch_pending":
        await send_sequence_email(
            email=email,
            name=name,
            template="hard_pitch",
            subject="Unlock the Full Domify Academy Experience",
            email_type="hard_pitch",
            expected_state="hard_pitch_pending",
            processing_state="hard_pitch_processing",
            next_state="ongoing_educational",
            next_days=5,
            unsubscribe_token=token,
        )
        return

    if state == "ongoing_educational":
        await send_sequence_email(
            email=email,
            name=name,
            template="educational",
            subject="New Cyber Insight From Domify Academy",
            email_type="ongoing_educational",
            expected_state="ongoing_educational",
            processing_state="ongoing_educational_processing",
            next_state="ongoing_educational",
            next_days=5,
            unsubscribe_token=token,
        )
        return


async def process_all_due_subscribers() -> int:
    conn = get_db()
    try:
        rows = conn.execute(
            "SELECT * FROM email_subscribers WHERE unsubscribe = 0"
        ).fetchall()
    finally:
        conn.close()

    count = 0
    for row in rows:
        try:
            await process_subscriber(row)
            count += 1
        except Exception as e:
            print(f"Subscriber processing error ({row['email']}): {e}")
            log_email(row["email"], "processor", "failed", str(e))
    return count


class SignupRequest(BaseModel):
    userId: str
    fullName: str
    email: str
    signupDate: str
    country: str
    source: str
    ip: str
    timestamp: str
    tier: Optional[str] = "Free"
    expiry: Optional[str] = None
    certPaid: Optional[bool] = False
    certGenerated: Optional[bool] = False
    marketingOptIn: Optional[bool] = True
    article: Optional[str] = ""
    startEmailSequence: Optional[bool] = True


class EmailSignupRequest(BaseModel):
    email: str
    name: str = "Subscriber"
    country: str = ""
    source: str = ""
    article: str = ""
    startEmailSequence: bool = True


class CertificateStoreRequest(BaseModel):
    verification_code: str
    user_name: str
    user_email: str
    user_id: str
    tier: str
    date: str


class DriveUrlRequest(BaseModel):
    verification_code: str
    drive_url: str


@app.get("/")
def health():
    return {"status": "alive", "service": "Domify Academy Backend", "version": "4.2"}


@app.post("/signup")
async def signup(request: Request):
    try:
        data = await request.json()
        required = ["userId", "fullName", "email", "signupDate", "country", "source", "ip", "timestamp"]
        for field in required:
            if field not in data:
                raise HTTPException(status_code=400, detail=f"Missing field: {field}")

        upsert_user(data, client_ip=get_client_ip(request))

        marketing_opt_in = bool(data.get("marketingOptIn", True))
        start_email_sequence = bool(data.get("startEmailSequence", True))

        if marketing_opt_in and start_email_sequence:
            token = ensure_subscriber(
                email=data["email"],
                name=data["fullName"],
                country=data.get("country", ""),
                source=data.get("source", ""),
                article=data.get("article", ""),
            )
            await send_welcome_email(data["email"], data["fullName"], token)

        return {"status": "ok", "message": "User saved"}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@app.get("/user")
async def get_user(request: Request, email: Optional[str] = None, user_id: Optional[str] = None):
    admin_token = request.headers.get("x-admin-token") or request.query_params.get("admin_token")
    if ADMIN_API_TOKEN and admin_token == ADMIN_API_TOKEN:
        return {
            "userId": "admin",
            "name": "Administrator",
            "email": "domifyacademy@gmail.com",
            "tier": "Master",
            "expiry": (datetime.utcnow() + timedelta(days=365)).isoformat(),
            "certPaid": True,
            "certGenerated": False,
            "isAdmin": True,
        }

    client_ip = get_client_ip(request)

    conn = get_db()
    try:
        row = conn.execute(
            """
            SELECT user_id, full_name, email, tier, expiry, cert_paid, cert_generated
            FROM users
            WHERE email = ? OR user_id = ? OR ip = ?
            LIMIT 1
            """,
            (email, user_id, client_ip),
        ).fetchone()
    finally:
        conn.close()

    if not row:
        raise HTTPException(status_code=404, detail="User not found")

    return {
        "userId": row["user_id"],
        "name": row["full_name"],
        "email": row["email"],
        "tier": row["tier"],
        "expiry": row["expiry"],
        "certPaid": bool(row["cert_paid"]),
        "certGenerated": bool(row["cert_generated"]),
        "isAdmin": False,
    }


@app.post("/webhook/lemonsqueezy")
async def lemon_webhook(request: Request):
    try:
        if not LEMON_WEBHOOK_SECRET:
            raise HTTPException(status_code=500, detail="LEMON_WEBHOOK_SECRET is not set")

        body = await request.body()
        signature = request.headers.get("X-Signature")
        if not signature:
            return {"status": "ignored", "reason": "no signature"}

        expected = hmac.new(
            LEMON_WEBHOOK_SECRET.encode("utf-8"),
            body,
            hashlib.sha256,
        ).hexdigest()

        if not hmac.compare_digest(signature, expected):
            return {"status": "ignored", "reason": "invalid signature"}

        data = await request.json()
        event_name = data.get("meta", {}).get("event_name")
        if event_name != "order_created":
            return {"status": "ignored", "reason": f"event {event_name}"}

        order_data = data.get("data", {}).get("attributes", {})
        email = order_data.get("user_email")
        first_item = order_data.get("first_order_item", {})
        variant_id = first_item.get("variant_id")

        if not email or not variant_id:
            return {"status": "ignored", "reason": "missing email or variant"}

        try:
            variant_id = int(variant_id)
        except Exception:
            return {"status": "ignored", "reason": "bad variant"}

        product = LEMON_PRODUCTS.get(variant_id)
        if not product:
            return {"status": "ignored", "reason": f"unknown variant {variant_id}"}

        expiry = None
        if product["days"] > 0:
            expiry = (datetime.utcnow() + timedelta(days=product["days"])).isoformat()

        conn = get_db()
        try:
            with DB_LOCK:
                user = conn.execute("SELECT * FROM users WHERE email = ?", (email,)).fetchone()
                if user:
                    conn.execute(
                        "UPDATE users SET tier = ?, expiry = ?, cert_paid = ? WHERE email = ?",
                        (
                            product["tier"],
                            expiry,
                            1 if product["tier"] == "Master" else 0,
                            email,
                        ),
                    )
                else:
                    conn.execute(
                        """
                        INSERT INTO users (
                            user_id, full_name, email, signup_date, country, source, ip,
                            timestamp, tier, expiry, cert_paid, cert_generated
                        )
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                        """,
                        (
                            f"auto_{int(time.time())}",
                            email,
                            email,
                            now_iso(),
                            "",
                            "payment",
                            "",
                            now_iso(),
                            product["tier"],
                            expiry,
                            1 if product["tier"] == "Master" else 0,
                            0,
                        ),
                    )
                conn.commit()
        finally:
            conn.close()

        return {"status": "ok", "message": f"Updated {email} to {product['tier']}"}
    except HTTPException:
        raise
    except Exception as e:
        print(f"Webhook error: {e}")
        return {"status": "error", "reason": str(e)}


@app.post("/store-certificate-code")
async def store_certificate_code(request: Request):
    require_internal_token(request)
    try:
        data = await request.json()
        req = CertificateStoreRequest(**data)

        conn = get_db()
        try:
            with DB_LOCK:
                conn.execute(
                    """
                    INSERT INTO certificates (
                        verification_code, user_name, user_email, user_id, tier, date, drive_url
                    )
                    VALUES (?, ?, ?, ?, ?, ?, ?)
                    ON CONFLICT(verification_code) DO UPDATE SET
                        user_name=excluded.user_name,
                        user_email=excluded.user_email,
                        user_id=excluded.user_id,
                        tier=excluded.tier,
                        date=excluded.date,
                        drive_url=excluded.drive_url
                    """,
                    (
                        req.verification_code.upper(),
                        req.user_name,
                        req.user_email,
                        req.user_id,
                        req.tier,
                        req.date,
                        None,
                    ),
                )
                conn.commit()
        finally:
            conn.close()

        await post_apps_script(
            {
                "verification_code": req.verification_code.upper(),
                "user_name": req.user_name,
                "user_email": req.user_email,
                "user_id": req.user_id,
                "tier": req.tier,
                "date": req.date,
                "drive_url": "",
            }
        )
        return {"success": True, "message": "Certificate code stored"}
    except Exception as e:
        return {"success": False, "error": str(e)}


@app.post("/update-drive-url")
async def update_drive_url(request: Request):
    require_internal_token(request)
    try:
        data = await request.json()
        req = DriveUrlRequest(**data)

        conn = get_db()
        try:
            with DB_LOCK:
                conn.execute(
                    "UPDATE certificates SET drive_url = ? WHERE verification_code = ?",
                    (req.drive_url, req.verification_code.upper()),
                )
                row = conn.execute(
                    """
                    SELECT user_name, user_email, user_id, tier, date
                    FROM certificates
                    WHERE verification_code = ?
                    LIMIT 1
                    """,
                    (req.verification_code.upper(),),
                ).fetchone()
                conn.commit()
        finally:
            conn.close()

        if row:
            await post_apps_script(
                {
                    "verification_code": req.verification_code.upper(),
                    "user_name": row["user_name"],
                    "user_email": row["user_email"],
                    "user_id": row["user_id"],
                    "tier": row["tier"],
                    "date": row["date"],
                    "drive_url": req.drive_url,
                }
            )

        return {"success": True, "message": "Drive URL updated"}
    except Exception as e:
        return {"success": False, "error": str(e)}


@app.get("/verify-certificate")
async def verify_certificate(code: str, name: str):
    conn = get_db()
    try:
        row = conn.execute(
            """
            SELECT user_name, tier, date, drive_url
            FROM certificates
            WHERE verification_code = ? AND user_name = ?
            LIMIT 1
            """,
            (code.upper(), name),
        ).fetchone()
    finally:
        conn.close()

    if not row:
        return {"valid": False, "error": "No matching certificate found"}

    return {
        "valid": True,
        "name": row["user_name"],
        "tier": row["tier"],
        "date": row["date"],
        "imageUrl": row["drive_url"],
    }


@app.post("/new-subscriber")
async def new_subscriber(request: Request):
    try:
        data = await request.json()
        req = EmailSignupRequest(**data)

        token = ensure_subscriber(
            email=req.email,
            name=req.name,
            country=req.country,
            source=req.source,
            article=req.article,
        )

        if req.startEmailSequence:
            await send_welcome_email(req.email, req.name, token)

        return {"status": "ok", "message": "Subscriber saved"}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@app.get("/send-scheduled-emails")
async def send_scheduled_emails(request: Request):
    require_internal_token(request)
    count = await process_all_due_subscribers()
    return {"status": "ok", "subscribers_checked": count}


@app.get("/email-status")
async def email_status(request: Request, email: str):
    require_internal_token(request)
    conn = get_db()
    try:
        row = conn.execute(
            "SELECT * FROM email_subscribers WHERE email = ?",
            (email,),
        ).fetchone()
    finally:
        conn.close()

    if not row:
        raise HTTPException(status_code=404, detail="Email not found")

    return {
        "email": row["email"],
        "name": row["name"],
        "country": row["country"],
        "source": row["source"],
        "article": row["article"],
        "sequence_state": row["sequence_state"],
        "next_email_date": row["next_email_date"],
        "unsubscribe": bool(row["unsubscribe"]),
    }


@app.get("/unsubscribe")
async def unsubscribe(email: str, token: str):
    conn = get_db()
    try:
        row = conn.execute(
            "SELECT unsubscribe_token FROM email_subscribers WHERE email = ?",
            (email,),
        ).fetchone()
        if not row or row["unsubscribe_token"] != token:
            raise HTTPException(status_code=403, detail="Invalid unsubscribe link")
        with DB_LOCK:
            conn.execute("UPDATE email_subscribers SET unsubscribe = 1 WHERE email = ?", (email,))
            conn.commit()
    finally:
        conn.close()

    return {"status": "ok", "message": "You have been unsubscribed"}


@app.get("/test-email")
async def test_email(request: Request, to_email: str):
    require_internal_token(request)
    html_body = render_template(
        "welcome",
        name="Test User",
        email=to_email,
        unsubscribe_link=build_url("/unsubscribe", email=to_email, token="test"),
        base_url=PUBLIC_BASE_URL,
    )
    ok = await send_brevo_email(to_email, "Test User", "Domify Test Email", html_body)
    return {"success": ok}


async def keep_alive_self():
    while True:
        try:
            async with aiohttp.ClientSession() as session:
                async with session.get(PUBLIC_BASE_URL) as resp:
                    print(f"Self-ping {resp.status}")
        except Exception as e:
            print(f"Self-ping failed: {e}")
        await asyncio.sleep(300)


@app.on_event("startup")
async def startup_event():
    print("Domify Academy backend starting...")
    asyncio.create_task(keep_alive_self())
    print("Keep-alive started")