import { Database } from "bun:sqlite";

// 初始化 SQLite 數據庫
const db = new Database("orders.db");

// 定義類型
interface Order {
  id?: number;
  customer_name: string;
  product_name: string;
  quantity: number;
  price: number;
  status?: string;
  created_at?: string;
}

// 準備 SQL 語句
const insertOrderStmt = db.prepare(`
  INSERT INTO orders (customer_name, product_name, quantity, price, status)
  VALUES ($customerName, $productName, $quantity, $price, $status)
`);

const getOrdersStmt = db.prepare(`
  SELECT * FROM orders
  WHERE ($customerName IS NULL OR customer_name LIKE $customerName)
    AND ($productName IS NULL OR product_name LIKE $productName)
    AND ($status IS NULL OR status = $status)
  ORDER BY created_at DESC
  LIMIT $limit OFFSET $offset
`);

const getOrderCountStmt = db.prepare(`
  SELECT COUNT(*) as count FROM orders
  WHERE ($customerName IS NULL OR customer_name LIKE $customerName)
    AND ($productName IS NULL OR product_name LIKE $productName)
    AND ($status IS NULL OR status = $status)
`);

// 創建服務器
const server = Bun.serve({
  port: 3000,
  async fetch(req) {
    const url = new URL(req.url);
    const path = url.pathname;
    const method = req.method;

    // CORS headers
    const headers = {
      "Content-Type": "application/json",
      "Access-Control-Allow-Origin": "*",
      "Access-Control-Allow-Methods": "GET, POST, PUT, DELETE, OPTIONS",
      "Access-Control-Allow-Headers": "Content-Type",
    };

    // 處理預檢請求
    if (method === "OPTIONS") {
      return new Response(null, { status: 204, headers });
    }

    try {
      if (path === "/api/orders" && method === "GET") {
        // 解析查詢參數
        const page = parseInt(url.searchParams.get("page") || "1");
        const pageSize = parseInt(url.searchParams.get("pageSize") || "10");
        const customerName = url.searchParams.get("customerName") || null;
        const productName = url.searchParams.get("productName") || null;
        const status = url.searchParams.get("status") || null;

        // 計算偏移量
        const offset = (page - 1) * pageSize;

        // 準備查詢參數
        const params: any = {
          $customerName: customerName ? `%${customerName}%` : null,
          $productName: productName ? `%${productName}%` : null,
          $status: status,
          $limit: pageSize,
          $offset: offset
        };

        // 查詢訂單列表
        const orders = getOrdersStmt.all(params) as Order[];

        // 獲取總數
        const countResult: any = getOrderCountStmt.get({
          $customerName: customerName ? `%${customerName}%` : null,
          $productName: productName ? `%${productName}%` : null,
          $status: status
        });

        const total = countResult.count;

        return Response.json({
          success: true,
          data: {
            orders,
            pagination: {
              page,
              pageSize,
              total,
              totalPages: Math.ceil(total / pageSize)
            }
          }
        }, { headers });
      } else if (path === "/api/orders" && method === "POST") {
        // 添加新訂單
        const body: any = await req.json();
        
        const order: Order = {
          customer_name: body.customer_name,
          product_name: body.product_name,
          quantity: body.quantity,
          price: body.price,
          status: body.status || 'pending'
        };

        // 驗證必需字段
        if (!order.customer_name || !order.product_name || order.quantity <= 0 || order.price <= 0) {
          return Response.json({
            success: false,
            message: "缺少必需字段或字段值無效"
          }, { 
            status: 400,
            headers 
          });
        }

        // 插入數據
        const result = insertOrderStmt.run({
          $customerName: order.customer_name,
          $productName: order.product_name,
          $quantity: order.quantity,
          $price: order.price,
          $status: order.status || 'pending'
        });

        return Response.json({
          success: true,
          message: "訂單創建成功",
          data: {
            id: result.lastInsertRowid as number,
            ...order
          }
        }, { headers });
      } else {
        return Response.json({
          success: false,
          message: "路由不存在"
        }, { 
          status: 404,
          headers 
        });
      }
    } catch (error: any) {
      console.error("服務器錯誤:", error);
      
      return Response.json({
        success: false,
        message: error.message || "服務器內部錯誤"
      }, { 
        status: 500,
        headers 
      });
    }
  }
});

console.log(`Server running at http://localhost:${server.port}`);