import type { NextApiRequest, NextApiResponse } from 'next';
import { getServerSession } from 'next-auth/next';
import { authOptions } from '../auth/[...nextauth]';
import mysql from 'mysql2/promise';

interface OrdersData {
  orders: Array<{
    id: number;
    orderNumber: string;
    date: string;
    total: number;
    status: string;
    items: number;
  }>;
}

interface ErrorResponse {
  error: string;
}

const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'perfume_shop',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse<OrdersData | ErrorResponse>
) {
  if (req.method !== 'GET') {
    return res.status(405).json({ error: 'Method not allowed' });
  }

  const session = await getServerSession(req, res, authOptions);

  if (!session?.user?.email) {
    return res.status(401).json({ error: 'Unauthorized' });
  }

  let connection: any = null;

  try {
    connection = await pool.getConnection();

    // Najdi user ID podle emailu
    const [userRows]: any = await connection.query(
      'SELECT id FROM users WHERE email = ?',
      [session.user.email]
    );

    if (!userRows || userRows.length === 0) {
      connection.release();
      return res.status(404).json({ error: 'User not found' });
    }

    const userId = userRows[0].id;

    // Nacti všechny objednávky s počtem položek
    const [orders]: any = await connection.query(
      `SELECT
        o.id,
        o.order_number,
        o.created_at,
        o.total_price,
        os.name as status_name,
        COUNT(oi.id) as items_count
       FROM orders o
       LEFT JOIN order_statuses os ON o.status_id = os.id
       LEFT JOIN order_items oi ON o.id = oi.order_id
       WHERE o.user_id = ?
       GROUP BY o.id
       ORDER BY o.created_at DESC`,
      [userId]
    );

    connection.release();

    const ordersData: OrdersData = {
      orders: (orders || []).map((order: any) => ({
        id: order.id,
        orderNumber: order.order_number,
        date: new Date(order.created_at).toLocaleDateString('cs-CZ', {
          day: '2-digit',
          month: '2-digit',
          year: 'numeric',
        }),
        total: order.total_price,
        status: order.status_name || 'Neznámý',
        items: order.items_count || 0,
      })),
    };

    return res.status(200).json(ordersData);
  } catch (error) {
    if (connection) {
      try {
        connection.release();
      } catch (e) {
      }
    }
    return res.status(500).json({ error: 'Internal server error' });
  }
}