import { db,jsonError,requireAdmin,uid,sendCommunication } from "@/lib/server";
export async function GET(request:Request){if(!await requireAdmin(request))return jsonError("Unauthorized",401);const url=new URL(request.url),type=url.searchParams.get("type")||"dashboard",q=`%${(url.searchParams.get("q")||"").slice(0,80)}%`,status=url.searchParams.get("status")||"";if(type==="registrations"){const rows=await db().prepare("SELECT * FROM registrations WHERE (first_name||' '||last_name LIKE ? OR email LIKE ? OR registration_number LIKE ?) AND (?='' OR status=? OR payment_status=?) ORDER BY created_at DESC LIMIT 500").bind(q,q,q,status,status,status).all();return Response.json(rows.results)}if(type==="payments"){const rows=await db().prepare("SELECT p.*,r.registration_number,r.first_name||' '||r.last_name AS attendee_name,r.email FROM payments p LEFT JOIN registrations r ON r.id=p.registration_id WHERE (?='' OR p.status=?) AND (COALESCE(r.email,p.supporter_email,'') LIKE ? OR COALESCE(r.registration_number,p.provider_reference,'') LIKE ?) ORDER BY p.created_at DESC LIMIT 500").bind(status,status,q,q).all();return Response.json(rows.results)}if(type==="support"){const rows=await db().prepare("SELECT * FROM payments WHERE purpose='support' AND (?='' OR status=?) ORDER BY created_at DESC LIMIT 500").bind(status,status).all();return Response.json(rows.results)}if(type==="communications"){const rows=await db().prepare("SELECT * FROM communications ORDER BY created_at DESC LIMIT 300").all();return Response.json(rows.results)}const metrics=await db().prepare("SELECT (SELECT COUNT(*) FROM registrations) total_registrations,(SELECT COUNT(*) FROM registrations WHERE status='confirmed') confirmed,(SELECT COUNT(*) FROM registrations WHERE payment_status='successful') paid,(SELECT COUNT(*) FROM registrations WHERE payment_status IN ('unpaid','pending')) unpaid,(SELECT COUNT(*) FROM registrations WHERE date(created_at)=date('now')) today,(SELECT COUNT(*) FROM payments WHERE status='successful') successful_payments,(SELECT COUNT(*) FROM payments WHERE status='pending') pending_payments,(SELECT COUNT(*) FROM payments WHERE status='failed') failed_payments,(SELECT COUNT(*) FROM payments WHERE status='refunded') refunded_payments,(SELECT COALESCE(SUM(amount),0) FROM payments WHERE status='successful' AND purpose='registration') revenue,(SELECT COALESCE(SUM(amount),0) FROM payments WHERE status='successful' AND purpose='support') support_total").first();const recent=await db().prepare("SELECT registration_number,first_name,last_name,email,status,payment_status,created_at FROM registrations ORDER BY created_at DESC LIMIT 8").all();return Response.json({metrics,recent:recent.results})}
export async function PATCH(request:Request){const admin=await requireAdmin(request);if(!admin)return jsonError("Unauthorized",401);const x=await request.json() as {id?:string,status?:string,paymentStatus?:string,note?:string};if(!x.id)return jsonError("Record ID required");if(x.note){await db().prepare("INSERT INTO admin_notes(id,registration_id,admin_id,note) VALUES(?,?,?,?)").bind(uid("note_"),x.id,admin.id,x.note.slice(0,2000)).run();return Response.json({ok:true})}if(x.status)await db().prepare("UPDATE registrations SET status=?,updated_at=CURRENT_TIMESTAMP WHERE id=?").bind(x.status,x.id).run();if(x.paymentStatus)await db().prepare("UPDATE registrations SET payment_status=?,updated_at=CURRENT_TIMESTAMP WHERE id=?").bind(x.paymentStatus,x.id).run();return Response.json({ok:true})}
export async function POST(request:Request){if(!await requireAdmin(request))return jsonError("Unauthorized",401);const x=await request.json() as {channel?:string,audience?:string,subject?:string,body?:string,idempotencyKey?:string};if(!x.channel||!x.body||!x.idempotencyKey)return jsonError("Channel, message and send key are required.");const where=x.audience==="paid"?"payment_status='successful'":x.audience==="confirmed"?"status='confirmed'":x.audience==="unpaid"?"payment_status IN ('unpaid','pending')":"communication_consent=1";const recipients=await db().prepare(`SELECT id,email,phone,first_name FROM registrations WHERE ${where} LIMIT 1000`).all<Record<string,string>>();let sent=0,failed=0;for(const r of recipients.results){const to=x.channel==="email"?r.email:r.phone;const key=`${x.idempotencyKey}:${r.id}`;try{await db().prepare("INSERT INTO communications(id,registration_id,channel,recipient,subject,body,status,idempotency_key) VALUES(?,?,?,?,?,?,?,?)").bind(uid("com_"),r.id,x.channel,to,x.subject||null,x.body,"sending",key).run()}catch{continue}const result=await sendCommunication(x.channel,to,x.subject||"Conference Update",x.body.replaceAll("{{first_name}}",r.first_name));await db().prepare("UPDATE communications SET status=?,provider_message_id=?,error=?,sent_at=CASE WHEN ? THEN CURRENT_TIMESTAMP ELSE NULL END WHERE idempotency_key=?").bind(result.ok?"sent":"failed",result.id||null,result.error||null,result.ok?1:0,key).run();result.ok?sent++:failed++}return Response.json({sent,failed,total:recipients.results.length})}
