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

interface ProfileData {
  user: {
    id: string;
    firstName: string;
    lastName: string;
    email: string;
    isActive: boolean;
    memberSince: string;
    profilePicture?: string;
  };
  statistics: {
    totalOrders: number;
    lastOrder: {
      id: number;
      orderNumber: string;
      date: string;
      total: number;
    } | null;
    daysSinceLastOrder: number | null;
    newsletterSubscribed: boolean;
  };
}

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<ProfileData | ErrorResponse>
) {
  if (req.method !== 'GET') {
    return res.status(405).json({ error: 'Method not allowed' });
  }

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

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

  let userId = (session.user as any).id;

  if (!userId) {
    return res.status(401).json({ error: 'User ID not found in session' });
  }

  let connection: any = null;

  try {
    connection = await pool.getConnection();

    // Get user info
    const [users]: any = await connection.query(
      'SELECT id, first_name, last_name, email, is_active, created_at, profile_picture FROM users WHERE id = ?',
      [userId]
    );

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

    const user = users[0];

    // Get total orders count
    const [orderCounts]: any = await connection.query(
      'SELECT COUNT(*) as total FROM orders WHERE user_id = ?',
      [userId]
    );

    const totalOrders = orderCounts[0]?.total || 0;

    // Get last order
    let lastOrderData = null;
    let daysSinceLastOrder = null;

    const [lastOrders]: any = await connection.query(
      `SELECT id, order_number, total_price, created_at 
       FROM orders 
       WHERE user_id = ? 
       ORDER BY created_at DESC 
       LIMIT 1`,
      [userId]
    );

    if (lastOrders && lastOrders.length > 0) {
      const order = lastOrders[0];
      lastOrderData = {
        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,
      };

      // Calculate days since last order
      const lastOrderDate = new Date(order.created_at);
      const today = new Date();
      const diffTime = Math.abs(today.getTime() - lastOrderDate.getTime());
      daysSinceLastOrder = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
    }

    // Get newsletter subscription status
    let newsletterSubscribed = false;
    try {
      const [newsLetterRows]: any = await connection.query(
        'SELECT active FROM users_newsletter WHERE user_id = ? LIMIT 1',
        [userId]
      );

      if (newsLetterRows && newsLetterRows.length > 0) {
        newsletterSubscribed = newsLetterRows[0].active === 1;
      }
    } catch (error) {
      // Newsletter table might not exist, default to false
      newsletterSubscribed = false;
    }

    connection.release();

    const profileData: ProfileData = {
      user: {
        id: user.id.toString(),
        firstName: user.first_name,
        lastName: user.last_name,
        email: user.email,
        isActive: user.is_active === 1,
        memberSince: new Date(user.created_at).toLocaleDateString('cs-CZ', {
          day: '2-digit',
          month: 'long',
          year: 'numeric',
        }),
        profilePicture: user.profile_picture || undefined,
      },
      statistics: {
        totalOrders,
        lastOrder: lastOrderData,
        daysSinceLastOrder,
        newsletterSubscribed,
      },
    };

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