import { Router, Request, Response } from 'express';
import { db } from '../database/connection';
import { users, orders, orderItems } from '../database/schema';
import { eq, desc, count, sql } from 'drizzle-orm';
import { authenticateUser, requireAdmin } from '../middleware/auth';
import { supabaseAdmin } from '../config/supabase';

const router = Router();

// GET /api/user/all - Get all users (admin only)
router.get('/all', authenticateUser, requireAdmin, async (req: Request, res: Response) => {
  try {
    const allUsers = await db
      .select({
        id: users.id,
        email: users.email,
        fullName: users.fullName,
        role: users.role,
        createdAt: users.createdAt,
      })
      .from(users)
      .orderBy(desc(users.createdAt));

    // Get order count for each user
    const usersWithStats = await Promise.all(
      allUsers.map(async (u) => {
        const [orderCount] = await db
          .select({ count: count() })
          .from(orders)
          .where(eq(orders.userId, u.id));

        const [lastOrder] = await db
          .select({ createdAt: orders.createdAt })
          .from(orders)
          .where(eq(orders.userId, u.id))
          .orderBy(desc(orders.createdAt))
          .limit(1);

        return {
          ...u,
          orders: orderCount.count,
          lastVisit: lastOrder?.createdAt || u.createdAt,
        };
      })
    );

    return res.json({ users: usersWithStats });
  } catch (error) {
    console.error('Error fetching users:', error);
    return res.status(500).json({ error: 'Failed to fetch users' });
  }
});

// GET /api/user/:id - Get specific user (admin only or own profile)
router.get('/:id', authenticateUser, async (req: Request, res: Response) => {
  try {
    const { id } = req.params;

    // Check if user is admin or requesting own profile
    if (req.user!.role !== 'admin' && req.user!.id !== id) {
      return res.status(403).json({ error: 'Unauthorized' });
    }

    const [userDetails] = await db
      .select({
        id: users.id,
        email: users.email,
        fullName: users.fullName,
        role: users.role,
        createdAt: users.createdAt,
      })
      .from(users)
      .where(eq(users.id, id));

    if (!userDetails) {
      return res.status(404).json({ error: 'User not found' });
    }

    return res.json({ user: userDetails });
  } catch (error) {
    console.error('Error fetching user:', error);
    return res.status(500).json({ error: 'Failed to fetch user' });
  }
});

// PUT /api/user/:id - Update user (admin only or own profile)
router.put('/:id', authenticateUser, async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    const { fullName, role, email } = req.body;

    // Check if user is admin or updating own profile
    if (req.user!.role !== 'admin' && req.user!.id !== id) {
      return res.status(403).json({ error: 'Unauthorized' });
    }

    // Build updates object
    const updates: any = { updatedAt: new Date() };
    if (fullName !== undefined) updates.fullName = fullName;
    if (email !== undefined) updates.email = email;
    
    // Only admins can change roles
    if (req.user!.role === 'admin' && role) {
      updates.role = role;
    }

    // Update database
    const [updatedUser] = await db
      .update(users)
      .set(updates)
      .where(eq(users.id, id))
      .returning();

    if (!updatedUser) {
      return res.status(404).json({ error: 'User not found' });
    }

    // Sync with Supabase Auth
    try {
      const authUpdates: any = {
        user_metadata: {
          full_name: updatedUser.fullName,
          role: updatedUser.role
        }
      };
      
      // If email was updated, also update in Supabase Auth
      if (email !== undefined) {
        authUpdates.email = email;
      }

      await supabaseAdmin.auth.admin.updateUserById(id, authUpdates);
      console.log(`✓ Synced user ${id} with Supabase Auth`);
    } catch (authError) {
      console.error('Warning: Failed to sync with Supabase Auth:', authError);
      // Continue anyway - database is updated
    }

    return res.json({ user: updatedUser });
  } catch (error) {
    console.error('Error updating user:', error);
    return res.status(500).json({ error: 'Failed to update user' });
  }
});

// DELETE /api/user/:id - Delete user (admin only)
router.delete('/:id', authenticateUser, requireAdmin, async (req: Request, res: Response) => {
  try {
    const { id } = req.params;

    // Don't allow deleting self
    if (req.user!.id === id) {
      return res.status(400).json({ error: 'Cannot delete your own account' });
    }

    // Delete user's orders first (cascade)
    await db.delete(orders).where(eq(orders.userId, id));
    
    // Delete the user from database
    await db.delete(users).where(eq(users.id, id));

    // Delete from Supabase Auth as well
    try {
      const { error: authError } = await supabaseAdmin.auth.admin.deleteUser(id);
      if (authError) {
        console.error('Warning: Failed to delete from Supabase Auth:', authError);
        // Continue anyway - database is cleaned up
      } else {
        console.log(`✓ Deleted user ${id} from Supabase Auth`);
      }
    } catch (authError) {
      console.error('Warning: Failed to delete from Supabase Auth:', authError);
      // Continue anyway - database is cleaned up
    }

    return res.json({ message: 'User deleted successfully' });
  } catch (error) {
    console.error('Error deleting user:', error);
    return res.status(500).json({ error: 'Failed to delete user' });
  }
});

export { router as userRoutes };
