"""
RH Elektronik Backend API - MySQL Version
"""
from fastapi import FastAPI, APIRouter, HTTPException, Depends, status, UploadFile, File
from fastapi.security import HTTPBearer, HTTPAuthorizationCredentials
from dotenv import load_dotenv
from starlette.middleware.cors import CORSMiddleware
from sqlalchemy.orm import Session
from sqlalchemy import func as sql_func
import os
import logging
from pathlib import Path
from typing import List, Optional
import uuid
from datetime import datetime, timezone, timedelta
import jwt
from passlib.context import CryptContext
import shutil
import re

# Local imports
from database import get_db, engine, Base
from models import Admin, BlogPost, Service, Brand, Area, Contact, AboutCard, Product
from schemas import (
    AdminLogin, Token,
    BlogPostCreate, BlogPostUpdate, BlogPostResponse,
    ServiceCreate, ServiceResponse,
    BrandCreate, BrandResponse,
    AreaCreate, AreaResponse,
    ContactUpdate, ContactResponse,
    AboutCardCreate, AboutCardResponse,
    ProductCreate, ProductUpdate, ProductResponse
)

ROOT_DIR = Path(__file__).parent
load_dotenv(ROOT_DIR / '.env')

# Create database tables
Base.metadata.create_all(bind=engine)

# Security
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
security = HTTPBearer()
SECRET_KEY = os.environ.get('SECRET_KEY', 'rhelektronik-secret-key-2025-change-in-production')
ALGORITHM = "HS256"

# Create the main app
app = FastAPI(title="RH Elektronik API", version="2.0.0")
api_router = APIRouter(prefix="/api")

# Upload directory setup
UPLOAD_DIR = Path(__file__).parent.parent / "frontend" / "public" / "uploads"
UPLOAD_DIR.mkdir(parents=True, exist_ok=True)

# ==================== AUTH FUNCTIONS ====================

def verify_password(plain_password, hashed_password):
    return pwd_context.verify(plain_password, hashed_password)

def get_password_hash(password):
    return pwd_context.hash(password)

def create_access_token(data: dict):
    to_encode = data.copy()
    expire = datetime.now(timezone.utc) + timedelta(hours=24)
    to_encode.update({"exp": expire})
    encoded_jwt = jwt.encode(to_encode, SECRET_KEY, algorithm=ALGORITHM)
    return encoded_jwt

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

def generate_slug(title: str) -> str:
    """Generate URL-friendly slug from title"""
    slug = title.lower()
    slug = re.sub(r'[^a-z0-9\s-]', '', slug)
    slug = re.sub(r'[\s]+', '-', slug)
    slug = slug.strip('-')
    return slug[:100]

# ==================== AUTH ROUTES ====================

@api_router.post("/admin/login", response_model=Token)
def admin_login(credentials: AdminLogin, db: Session = Depends(get_db)):
    admin = db.query(Admin).filter(Admin.username == credentials.username).first()
    
    if not admin:
        raise HTTPException(status_code=401, detail="Invalid username or password")
    
    if not verify_password(credentials.password, admin.password_hash):
        raise HTTPException(status_code=401, detail="Invalid username or password")
    
    access_token = create_access_token(data={"sub": credentials.username})
    return {"access_token": access_token, "token_type": "bearer"}

@api_router.post("/admin/create-first-admin")
def create_first_admin(db: Session = Depends(get_db)):
    existing_admin = db.query(Admin).first()
    if existing_admin:
        raise HTTPException(status_code=400, detail="Admin already exists")
    
    admin = Admin(
        id=str(uuid.uuid4()),
        username="admin",
        password_hash=get_password_hash("admin123"),
        role="admin"
    )
    
    db.add(admin)
    db.commit()
    
    return {"message": "Admin created successfully", "username": "admin", "password": "admin123"}

# ==================== BLOG ROUTES ====================

@api_router.get("/blog", response_model=List[BlogPostResponse])
def get_blog_posts(status: Optional[str] = None, limit: int = 100, db: Session = Depends(get_db)):
    query = db.query(BlogPost)
    if status:
        query = query.filter(BlogPost.status == status)
    
    posts = query.order_by(BlogPost.created_at.desc()).limit(limit).all()
    return posts

@api_router.get("/blog/{post_id}", response_model=BlogPostResponse)
def get_blog_post(post_id: str, db: Session = Depends(get_db)):
    post = db.query(BlogPost).filter(BlogPost.id == post_id).first()
    if not post:
        # Try finding by slug
        post = db.query(BlogPost).filter(BlogPost.slug == post_id).first()
    if not post:
        raise HTTPException(status_code=404, detail="Blog post not found")
    
    # Increment view count
    post.views = (post.views or 0) + 1
    db.commit()
    db.refresh(post)
    
    return post

@api_router.post("/blog", response_model=BlogPostResponse)
def create_blog_post(post: BlogPostCreate, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    slug = post.slug if post.slug else generate_slug(post.title)
    
    # Check for duplicate slug
    existing = db.query(BlogPost).filter(BlogPost.slug == slug).first()
    if existing:
        slug = f"{slug}-{str(uuid.uuid4())[:8]}"
    
    meta_desc = post.meta_description if post.meta_description else (post.excerpt[:160] if post.excerpt else "")
    
    blog_post = BlogPost(
        id=str(uuid.uuid4()),
        title=post.title,
        slug=slug,
        content=post.content,
        excerpt=post.excerpt,
        meta_description=meta_desc,
        meta_keywords=post.meta_keywords,
        author=post.author,
        featured_image=post.featured_image,
        status=post.status,
        views=0
    )
    
    db.add(blog_post)
    db.commit()
    db.refresh(blog_post)
    
    return blog_post

@api_router.put("/blog/{post_id}", response_model=BlogPostResponse)
def update_blog_post(post_id: str, post_update: BlogPostUpdate, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    post = db.query(BlogPost).filter(BlogPost.id == post_id).first()
    if not post:
        raise HTTPException(status_code=404, detail="Blog post not found")
    
    update_data = post_update.model_dump(exclude_unset=True)
    for key, value in update_data.items():
        if value is not None:
            setattr(post, key, value)
    
    db.commit()
    db.refresh(post)
    
    return post

@api_router.delete("/blog/{post_id}")
def delete_blog_post(post_id: str, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    post = db.query(BlogPost).filter(BlogPost.id == post_id).first()
    if not post:
        raise HTTPException(status_code=404, detail="Blog post not found")
    
    db.delete(post)
    db.commit()
    
    return {"message": "Blog post deleted successfully"}

# ==================== SERVICE ROUTES ====================

@api_router.get("/services", response_model=List[ServiceResponse])
def get_services(db: Session = Depends(get_db)):
    services = db.query(Service).order_by(Service.order_num).all()
    return services

@api_router.post("/services", response_model=ServiceResponse)
def create_service(service: ServiceCreate, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    service_obj = Service(
        id=str(uuid.uuid4()),
        title=service.title,
        description=service.description,
        icon=service.icon,
        order_num=service.order
    )
    db.add(service_obj)
    db.commit()
    db.refresh(service_obj)
    return service_obj

@api_router.put("/services/{service_id}", response_model=ServiceResponse)
def update_service(service_id: str, service_update: ServiceCreate, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    service = db.query(Service).filter(Service.id == service_id).first()
    if not service:
        raise HTTPException(status_code=404, detail="Service not found")
    
    service.title = service_update.title
    service.description = service_update.description
    service.icon = service_update.icon
    service.order_num = service_update.order
    
    db.commit()
    db.refresh(service)
    return service

@api_router.delete("/services/{service_id}")
def delete_service(service_id: str, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    service = db.query(Service).filter(Service.id == service_id).first()
    if not service:
        raise HTTPException(status_code=404, detail="Service not found")
    
    db.delete(service)
    db.commit()
    return {"message": "Service deleted successfully"}

# ==================== BRAND ROUTES ====================

@api_router.get("/brands", response_model=List[BrandResponse])
def get_brands(db: Session = Depends(get_db)):
    brands = db.query(Brand).order_by(Brand.order_num).all()
    return brands

@api_router.post("/brands", response_model=BrandResponse)
def create_brand(brand: BrandCreate, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    brand_obj = Brand(
        id=str(uuid.uuid4()),
        name=brand.name,
        logo_url=brand.logo_url,
        alt_text=brand.alt_text,
        order_num=brand.order
    )
    db.add(brand_obj)
    db.commit()
    db.refresh(brand_obj)
    return brand_obj

@api_router.put("/brands/{brand_id}", response_model=BrandResponse)
def update_brand(brand_id: str, brand_update: BrandCreate, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    brand = db.query(Brand).filter(Brand.id == brand_id).first()
    if not brand:
        raise HTTPException(status_code=404, detail="Brand not found")
    
    brand.name = brand_update.name
    brand.logo_url = brand_update.logo_url
    brand.alt_text = brand_update.alt_text
    brand.order_num = brand_update.order
    
    db.commit()
    db.refresh(brand)
    return brand

@api_router.delete("/brands/{brand_id}")
def delete_brand(brand_id: str, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    brand = db.query(Brand).filter(Brand.id == brand_id).first()
    if not brand:
        raise HTTPException(status_code=404, detail="Brand not found")
    
    db.delete(brand)
    db.commit()
    return {"message": "Brand deleted successfully"}

# ==================== AREA ROUTES ====================

@api_router.get("/areas", response_model=List[AreaResponse])
def get_areas(db: Session = Depends(get_db)):
    areas = db.query(Area).all()
    return areas

@api_router.post("/areas", response_model=AreaResponse)
def create_area(area: AreaCreate, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    area_obj = Area(
        id=str(uuid.uuid4()),
        province=area.province,
        cities=area.cities
    )
    db.add(area_obj)
    db.commit()
    db.refresh(area_obj)
    return area_obj

@api_router.put("/areas/{area_id}", response_model=AreaResponse)
def update_area(area_id: str, area_update: AreaCreate, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    area = db.query(Area).filter(Area.id == area_id).first()
    if not area:
        raise HTTPException(status_code=404, detail="Area not found")
    
    area.province = area_update.province
    area.cities = area_update.cities
    
    db.commit()
    db.refresh(area)
    return area

@api_router.delete("/areas/{area_id}")
def delete_area(area_id: str, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    area = db.query(Area).filter(Area.id == area_id).first()
    if not area:
        raise HTTPException(status_code=404, detail="Area not found")
    
    db.delete(area)
    db.commit()
    return {"message": "Area deleted successfully"}

# ==================== CONTACT ROUTES ====================

@api_router.get("/contact", response_model=ContactResponse)
def get_contact(db: Session = Depends(get_db)):
    contact = db.query(Contact).filter(Contact.id == "main").first()
    if not contact:
        # Return default
        return ContactResponse(
            id="main",
            phone="+62 815-5433-3316",
            email="cs.rhelektronik@gmail.com",
            address="CV RH Elektronik, Jl. Pasar Gn.Kalong 01/09 Tasikmalaya, Jawa Barat, Indonesia",
            hours="Senin - Sabtu, 08.00 - 16.00 WIB",
            map_embed=""
        )
    return contact

@api_router.put("/contact", response_model=ContactResponse)
def update_contact(contact_update: ContactUpdate, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    contact = db.query(Contact).filter(Contact.id == "main").first()
    
    if contact:
        update_data = contact_update.model_dump(exclude_unset=True)
        for key, value in update_data.items():
            if value is not None:
                setattr(contact, key, value)
    else:
        contact = Contact(
            id="main",
            phone=contact_update.phone or "",
            email=contact_update.email or "",
            address=contact_update.address or "",
            hours=contact_update.hours or "",
            map_embed=contact_update.map_embed or ""
        )
        db.add(contact)
    
    db.commit()
    db.refresh(contact)
    return contact

# ==================== ABOUT ROUTES ====================

@api_router.get("/about", response_model=List[AboutCardResponse])
def get_about_cards(db: Session = Depends(get_db)):
    cards = db.query(AboutCard).order_by(AboutCard.order_num).all()
    return cards

@api_router.post("/about", response_model=AboutCardResponse)
def create_about_card(card: AboutCardCreate, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    card_obj = AboutCard(
        id=str(uuid.uuid4()),
        title=card.title,
        description=card.description,
        order_num=card.order
    )
    db.add(card_obj)
    db.commit()
    db.refresh(card_obj)
    return card_obj

@api_router.put("/about/{card_id}", response_model=AboutCardResponse)
def update_about_card(card_id: str, card_update: AboutCardCreate, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    card = db.query(AboutCard).filter(AboutCard.id == card_id).first()
    if not card:
        raise HTTPException(status_code=404, detail="About card not found")
    
    card.title = card_update.title
    card.description = card_update.description
    card.order_num = card_update.order
    
    db.commit()
    db.refresh(card)
    return card

@api_router.delete("/about/{card_id}")
def delete_about_card(card_id: str, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    card = db.query(AboutCard).filter(AboutCard.id == card_id).first()
    if not card:
        raise HTTPException(status_code=404, detail="About card not found")
    
    db.delete(card)
    db.commit()
    return {"message": "About card deleted successfully"}

# ==================== PRODUCT ROUTES ====================

@api_router.get("/products", response_model=List[ProductResponse])
def get_products(category: Optional[str] = None, featured: Optional[bool] = None, limit: int = 100, db: Session = Depends(get_db)):
    query = db.query(Product)
    if category:
        query = query.filter(Product.category == category)
    if featured is not None:
        query = query.filter(Product.featured == featured)
    
    products = query.order_by(Product.created_at.desc()).limit(limit).all()
    return products

@api_router.get("/products/{product_id}", response_model=ProductResponse)
def get_product(product_id: str, db: Session = Depends(get_db)):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        # Try finding by slug
        product = db.query(Product).filter(Product.slug == product_id).first()
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")
    
    return product

@api_router.post("/products", response_model=ProductResponse)
def create_product(product: ProductCreate, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    slug = product.slug if product.slug else generate_slug(product.name)
    
    # Check for duplicate slug
    existing = db.query(Product).filter(Product.slug == slug).first()
    if existing:
        slug = f"{slug}-{str(uuid.uuid4())[:8]}"
    
    product_obj = Product(
        id=str(uuid.uuid4()),
        name=product.name,
        slug=slug,
        description=product.description,
        price=product.price,
        original_price=product.original_price,
        category=product.category,
        brand=product.brand,
        images=product.images,
        stock=product.stock,
        is_available=product.is_available,
        featured=product.featured
    )
    
    db.add(product_obj)
    db.commit()
    db.refresh(product_obj)
    
    return product_obj

@api_router.put("/products/{product_id}", response_model=ProductResponse)
def update_product(product_id: str, product_update: ProductUpdate, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")
    
    update_data = product_update.model_dump(exclude_unset=True)
    for key, value in update_data.items():
        if value is not None:
            setattr(product, key, value)
    
    db.commit()
    db.refresh(product)
    
    return product

@api_router.delete("/products/{product_id}")
def delete_product(product_id: str, db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")
    
    db.delete(product)
    db.commit()
    
    return {"message": "Product deleted successfully"}

# ==================== SITE STATS ====================

@api_router.get("/stats/public")
def get_public_stats(db: Session = Depends(get_db)):
    """Public stats for brand advertisers"""
    blog_count = db.query(BlogPost).filter(BlogPost.status == "published").count()
    product_count = db.query(Product).count()
    
    # Calculate total blog views
    total_views = db.query(sql_func.sum(BlogPost.views)).filter(BlogPost.status == "published").scalar() or 0
    
    # Estimate monthly visitors
    monthly_visitors = max(total_views // 3, 1000)
    
    return {
        "total_views": total_views,
        "blog_posts": blog_count,
        "products": product_count,
        "monthly_visitors": monthly_visitors,
        "avg_time_on_site": "3:45",
        "bounce_rate": "42%"
    }

# ==================== DASHBOARD STATS ====================

@api_router.get("/admin/stats")
def get_stats(db: Session = Depends(get_db), current_user: str = Depends(get_current_user)):
    blog_count = db.query(BlogPost).filter(BlogPost.status == "published").count()
    draft_count = db.query(BlogPost).filter(BlogPost.status == "draft").count()
    service_count = db.query(Service).count()
    brand_count = db.query(Brand).count()
    product_count = db.query(Product).count()
    
    total_views = db.query(sql_func.sum(BlogPost.views)).scalar() or 0
    
    return {
        "blog_posts": blog_count,
        "drafts": draft_count,
        "services": service_count,
        "brands": brand_count,
        "products": product_count,
        "total_views": total_views
    }

# ==================== IMAGE UPLOAD ====================

@api_router.post("/upload-image")
def upload_image(file: UploadFile = File(...), current_user: str = Depends(get_current_user)):
    """Upload image and return URL"""
    try:
        if not file.content_type.startswith("image/"):
            raise HTTPException(status_code=400, detail="File must be an image")
        
        file_extension = file.filename.split(".")[-1]
        unique_filename = f"{uuid.uuid4()}.{file_extension}"
        file_path = UPLOAD_DIR / unique_filename
        
        with open(file_path, "wb") as buffer:
            shutil.copyfileobj(file.file, buffer)
        
        image_url = f"/uploads/{unique_filename}"
        
        return {"url": image_url, "filename": unique_filename}
    
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Upload failed: {str(e)}")

# Include router
app.include_router(api_router)

app.add_middleware(
    CORSMiddleware,
    allow_credentials=True,
    allow_origins=os.environ.get('CORS_ORIGINS', '*').split(','),
    allow_methods=["*"],
    allow_headers=["*"],
)

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)
