import csv, io, os, shutil, sqlite3
from datetime import datetime, timezone
from fastapi import APIRouter, Request, Form, Depends, HTTPException
from fastapi.responses import HTMLResponse, RedirectResponse, StreamingResponse, FileResponse, Response
from fastapi.templating import Jinja2Templates
from sqlalchemy.exc import IntegrityError
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select
from app.core.database import get_db
from app.core.security import require_roles, get_current_user
from app.models.models import ODP, Customer, AuditLog, Voucher, VoucherAgent

router = APIRouter(dependencies=[Depends(require_roles("super_admin", "admin_cs"))])
BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
templates = Jinja2Templates(directory=os.path.join(BASE_DIR, "app", "templates"))
BACKUP_DIR = os.path.join(BASE_DIR, "backups")
os.makedirs(BACKUP_DIR, exist_ok=True)

@router.get('/odps', response_class=HTMLResponse)
async def odps(request: Request, db: AsyncSession = Depends(get_db)):
    rows=(await db.execute(select(ODP).order_by(ODP.code))).scalars().all()
    return templates.TemplateResponse(request=request,name='odps.html',context={'odps':rows})

def _coordinate(value: str, label: str, minimum: float, maximum: float) -> str:
    try:
        number = float(value.strip())
    except (TypeError, ValueError):
        raise HTTPException(422, f'{label} wajib berupa koordinat valid')
    if not minimum <= number <= maximum:
        raise HTTPException(422, f'{label} di luar rentang koordinat valid')
    return f'{number:.7f}'

@router.post('/odps')
async def add_odp(code:str=Form(...),name:str=Form(...),latitude:str=Form(...),longitude:str=Form(...),port_count:int=Form(8),db:AsyncSession=Depends(get_db),user=Depends(get_current_user)):
    code, name = code.strip(), name.strip()
    if not code or not name:
        raise HTTPException(422, 'Kode dan nama ODP wajib diisi')
    if not 1 <= port_count <= 1024:
        raise HTTPException(422, 'Jumlah port harus antara 1 dan 1024')
    latitude = _coordinate(latitude, 'Latitude', -90, 90)
    longitude = _coordinate(longitude, 'Longitude', -180, 180)
    row=ODP(code=code,name=name,latitude=latitude,longitude=longitude,port_count=port_count)
    db.add(row)
    try:
        await db.flush()
    except IntegrityError:
        await db.rollback()
        raise HTTPException(409, 'Kode ODP sudah digunakan')
    db.add(AuditLog(user_id=user.id,action='create',entity='odp',entity_id=row.id,detail=row.code)); await db.commit()
    return RedirectResponse('/odps',status_code=303)

@router.get('/audit', response_class=HTMLResponse)
async def audit(request:Request,db:AsyncSession=Depends(get_db)):
    rows=(await db.execute(select(AuditLog).order_by(AuditLog.id.desc()).limit(200))).scalars().all()
    return templates.TemplateResponse(request=request,name='audit.html',context={'logs':rows})

@router.get('/backup')
async def backup(user=Depends(get_current_user)):
    src=os.path.join(BASE_DIR,'billing.db'); name=f"billing-{datetime.now().strftime('%Y%m%d-%H%M%S')}.db"; dst=os.path.join(BACKUP_DIR,name); shutil.copy2(src,dst); return FileResponse(dst,filename=name)

@router.post('/restore')
async def restore(filename:str=Form(...),user=Depends(get_current_user)):
    safe=os.path.basename(filename); src=os.path.join(BACKUP_DIR,safe)
    if not safe.startswith('billing-') or not os.path.isfile(src): raise HTTPException(404,'Backup tidak ditemukan')
    shutil.copy2(src,os.path.join(BASE_DIR,'billing.db')); return RedirectResponse('/',status_code=303)

@router.get('/export/customers.csv')
async def export_customers(db:AsyncSession=Depends(get_db)):
    rows=(await db.execute(select(Customer).where(Customer.is_archived==False))).scalars().all(); out=io.StringIO(); w=csv.writer(out); w.writerow(['id','customer_code','name','phone','address','type','status','harga_paket']); [w.writerow([x.id,x.customer_code,x.name,x.phone,x.address,x.type,x.status,x.harga_paket]) for x in rows]; return StreamingResponse(iter([out.getvalue()]),media_type='text/csv',headers={'Content-Disposition':'attachment; filename=customers.csv'})

@router.get('/export/vouchers.csv')
async def export_vouchers(db:AsyncSession=Depends(get_db)):
    rows=(await db.execute(select(Voucher))).scalars().all(); out=io.StringIO(); w=csv.writer(out); w.writerow(['code','profile','price','status','expires_at','agent_id']); [w.writerow([x.code,x.profile,x.price,x.status,x.expires_at,x.agent_id]) for x in rows]; return StreamingResponse(iter([out.getvalue()]),media_type='text/csv',headers={'Content-Disposition':'attachment; filename=vouchers.csv'})

@router.get('/export/customers.pdf')
async def export_pdf(db:AsyncSession=Depends(get_db)):
    rows=(await db.execute(select(Customer).where(Customer.is_archived==False))).scalars().all()
    lines=['BestWeb Customers','',*[f'{x.name} | {x.phone} | {x.status}' for x in rows]]
    def esc(value): return str(value).replace('\\','\\\\').replace('(','\\(').replace(')','\\)')
    stream='BT /F1 10 Tf 50 760 Td '+ ' '.join(f'({esc(line)}) Tj 0 -16 Td' for line in lines) +' ET'
    objects=[b'<< /Type /Catalog /Pages 2 0 R >>',b'<< /Type /Pages /Kids [3 0 R] /Count 1 >>',b'<< /Type /Page /Parent 2 0 R /MediaBox [0 0 612 792] /Resources << /Font << /F1 5 0 R >> >> /Contents 4 0 R >>',f'<< /Length {len(stream.encode())} >>\\nstream\\n{stream}\\nendstream'.encode(),b'<< /Type /Font /Subtype /Type1 /BaseFont /Helvetica >>']
    pdf=bytearray(b'%PDF-1.4\\n'); offsets=[0]
    for i,obj in enumerate(objects,1): offsets.append(len(pdf)); pdf.extend(f'{i} 0 obj\\n'.encode()); pdf.extend(obj); pdf.extend(b'\\nendobj\\n')
    xref=len(pdf); pdf.extend(f'xref\\n0 {len(objects)+1}\\n0000000000 65535 f \\n'.encode()); pdf.extend(''.join(f'{o:010d} 00000 n \\n' for o in offsets[1:]).encode()); pdf.extend(f'trailer\\n<< /Size {len(objects)+1} /Root 1 0 R >>\\nstartxref\\n{xref}\\n%%EOF'.encode())
    return Response(bytes(pdf),media_type='application/pdf',headers={'Content-Disposition':'attachment; filename=customers.pdf'})