const express = require("express");
const mysql = require("mysql2/promise");
const bcrypt = require("bcryptjs");
const jwt = require("jsonwebtoken");
const nodemailer = require("nodemailer");
const cors = require("cors");
const rateLimit = require("express-rate-limit");
const crypto = require("crypto");
const multer = require("multer");
const path = require("path");
const fs = require("fs");
const { v4: uuidv4 } = require("uuid");

const app = express();
const PORT = 5000;
const JWT_SECRET = "assignment-help-hub-super-secret-key-2024";
const SALT_ROUNDS = 10;

// Create uploads directory if it doesn't exist
const uploadsDir = path.join(__dirname, "uploads");
if (!fs.existsSync(uploadsDir)) {
  fs.mkdirSync(uploadsDir, { recursive: true });
  console.log("✅ Uploads directory created");
}

// Middleware
app.use(
  cors({
    origin: [
      // Production URLs
      "https://assignmentshub.mathenoid.com",
      "https://www.assignmentshub.mathenoid.com",

      "http://localhost:3000",
      "http://localhost:5173",
      "http://localhost:8080",
      "http://localhost:8081",
    ],
    credentials: true,
     methods: ['GET', 'POST', 'PUT', 'DELETE', 'PATCH', 'OPTIONS'],
    allowedHeaders: ['Content-Type', 'Authorization', 'Origin', 'X-Requested-With', 'Accept'],
  })
);

// Serve static files from uploads directory
app.use("/uploads", express.static(path.join(__dirname, "uploads")));

app.use(express.json({ limit: "10mb" }));
app.use(express.urlencoded({ extended: true }));

// File upload configuration
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    // Create user-specific folder
    const userId = req.user ? req.user.id : "anonymous";
    const userUploadsDir = path.join(uploadsDir, userId.toString());

    if (!fs.existsSync(userUploadsDir)) {
      fs.mkdirSync(userUploadsDir, { recursive: true });
    }

    cb(null, userUploadsDir);
  },
  filename: (req, file, cb) => {
    // Generate unique filename with original extension
    const uniqueId = uuidv4();
    const ext = path.extname(file.originalname);
    const timestamp = Date.now();
    cb(null, `${timestamp}-${uniqueId}${ext}`);
  },
});

const fileFilter = (req, file, cb) => {
  // Allow most common file types
  const allowedTypes = [
    "application/pdf",
    "application/msword",
    "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
    "application/vnd.ms-excel",
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    "application/vnd.ms-powerpoint",
    "application/vnd.openxmlformats-officedocument.presentationml.presentation",
    "text/plain",
    "text/csv",
    "image/jpeg",
    "image/png",
    "image/gif",
    "image/webp",
    "video/mp4",
    "video/webm",
    "audio/mpeg",
    "audio/wav",
    "application/zip",
    "application/x-zip-compressed",
  ];

  if (allowedTypes.includes(file.mimetype)) {
    cb(null, true);
  } else {
    cb(
      new Error(
        `File type ${file.mimetype} not allowed. Please upload supported file types.`
      ),
      false
    );
  }
};

const upload = multer({
  storage: storage,
  fileFilter: fileFilter,
  limits: {
    fileSize: 10 * 1024 * 1024, // 10MB per file
    files: 10, // Maximum 10 files
  },
});

// Rate limiting
const authLimiter = rateLimit({
  windowMs: 15 * 60 * 1000, // 15 minutes
  max: 500, // limit each IP to 50 requests per windowMs
  message: {
    error: "Too many authentication attempts, please try again later.",
  },
  standardHeaders: true,
  legacyHeaders: false,
});

const generalLimiter = rateLimit({
  windowMs: 15 * 60 * 1000,
  max: 100,
  message: { error: "Too many requests, please try again later." },
});

// app.use("/api/auth", authLimiter);
// app.use("/api", generalLimiter);

// Database connection
const dbConfig = {
  host: "127.0.0.1",
  user: "root",
  password: "", // Update this with your MySQL password if needed
  database: "assignments_help_hub",
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
};

const pool = mysql.createPool(dbConfig);

// Email configuration (using Gmail SMTP)
const emailTransporter = nodemailer.createTransport({
  service: "gmail",
  host: "smtp.gmail.com",
  port: 587,
  secure: false,
  auth: {
    user: "assignmenthelphub.service@gmail.com",
    pass: "hhbs zgsb mhhc mxrr",
  },
  tls: {
    rejectUnauthorized: false,
  },
});

// Verify email connection
emailTransporter.verify((error, success) => {
  if (error) {
    console.log("❌ Email service error:", error);
  } else {
    console.log("✅ Email service ready");
  }
});

// Utility functions
const generateRandomPassword = () => {
  const chars =
    "ABCDEFGHJKMNPQRSTUVWXYZabcdefghijkmnpqrstuvwxyz23456789!@#$%&*";
  let password = "";
  for (let i = 0; i < 12; i++) {
    password += chars.charAt(Math.floor(Math.random() * chars.length));
  }
  return password;
};

const createToken = (user) => {
  return jwt.sign(
    {
      id: user.id,
      username: user.username,
      email: user.email,
      role: user.role,
    },
    JWT_SECRET,
    { expiresIn: "7d" }
  );
};

const authenticateToken = async (req, res, next) => {
  const authHeader = req.headers["authorization"];
  const token = authHeader && authHeader.split(" ")[1];

  if (!token) {
    return res.status(401).json({ error: "Access denied. No token provided." });
  }

  try {
    const decoded = jwt.verify(token, JWT_SECRET);
    req.user = decoded;
    next();
  } catch (error) {
    res.status(403).json({ error: "Invalid token." });
  }
};

// File upload utility function
const generateFileUrl = (req, filename, userId) => {
  const baseUrl = `${req.protocol}://${req.get("host")}`;
  return `${baseUrl}/uploads/${userId}/${filename}`;
};

// Delete file utility
const deleteFile = async (filePath) => {
  try {
    if (fs.existsSync(filePath)) {
      fs.unlinkSync(filePath);
      console.log(`🗑️ Deleted file: ${filePath}`);
    }
  } catch (error) {
    console.error(`❌ Error deleting file ${filePath}:`, error);
  }
};

// Email template for new chat messages
const createNewMessageEmail = (
  recipientName,
  senderName,
  assignmentTitle,
  messageContent
) => ({
  subject: `💬 New Message: ${assignmentTitle}`,
  html: `
    <!DOCTYPE html>
    <html>
    <head>
      <style>
        body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; line-height: 1.6; color: #333; max-width: 600px; margin: 0 auto; }
        .header { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 30px; text-align: center; border-radius: 10px 10px 0 0; }
        .content { padding: 30px; background: #f9f9f9; }
        .message-box { background: white; border: 1px solid #e0e0e0; padding: 20px; margin: 20px 0; border-radius: 8px; }
        .footer { background: #333; color: white; padding: 20px; text-align: center; border-radius: 0 0 10px 10px; }
        .emoji { font-size: 1.5em; margin-right: 10px; }
        .btn { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 12px 24px; text-decoration: none; border-radius: 25px; font-weight: bold; display: inline-block; margin: 20px 0; }
      </style>
    </head>
    <body>
      <div class="header">
        <h1><span class="emoji">💬</span>New Message Received</h1>
        <p>You have a new message about your assignment</p>
      </div>
      <div class="content">
        <h2>Hello ${recipientName},</h2>
        <p><strong>${senderName}</strong> has sent you a new message regarding the assignment:</p>

        <div class="message-box">
          <h3>"${assignmentTitle}"</h3>
          <p style="margin: 15px 0; padding: 15px; background: #f0f8ff; border-radius: 5px; border-left: 4px solid #667eea;">
            ${messageContent}
          </p>
        </div>

        <div style="text-align: center;">
          <a href="https://assignmentshub.mathenoid.com/assignments/${assignmentTitle
            .toLowerCase()
            .replace(/\s+/g, "-")}/chat" class="btn">
            <span class="emoji">📩</span>View & Reply
          </a>
        </div>

        <p style="font-size: 0.9em; color: #666; margin-top: 20px;">
          This is an automated notification. Please don't reply to this email.
        </p>
      </div>
      <div class="footer">
        <p><span class="emoji">🎓</span>Assignment Help Hub - Connecting students and experts</p>
        <p><small>© 2025 Assignment Help Hub. All rights reserved.</small></p>
      </div>
    </body>
    </html>
  `,
});

// Enhanced email templates
const createWelcomeEmail = (name, role) => ({
  subject: "🎉 Welcome to Assignment Help Hub - Your Journey Begins!",
  html: `
    <!DOCTYPE html>
    <html>
    <head>
      <style>
        body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; line-height: 1.6; color: #333; max-width: 600px; margin: 0 auto; }
        .header { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 30px; text-align: center; border-radius: 10px 10px 0 0; }
        .content { padding: 30px; background: #f9f9f9; }
        .highlight { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 15px; border-radius: 8px; margin: 20px 0; text-align: center; }
        .features { display: flex; flex-wrap: wrap; gap: 20px; margin: 20px 0; }
        .feature { flex: 1; min-width: 200px; padding: 15px; background: white; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
        .footer { background: #333; color: white; padding: 20px; text-align: center; border-radius: 0 0 10px 10px; }
        .emoji { font-size: 1.5em; margin-right: 10px; }
      </style>
    </head>
    <body>
      <div class="header">
        <h1><span class="emoji">🎓</span>Assignment Help Hub</h1>
        <p>Where Excellence Meets Education</p>
      </div>
      <div class="content">
        <h2>Welcome aboard, ${name}! 🚀</h2>
        <p>Congratulations! You've successfully joined Assignment Help Hub as a <strong>${role}</strong>. We're thrilled to have you as part of our growing community of learners and educators.</p>
        
        <div class="highlight">
          <h3>🌟 Your ${
            role === "student" ? "Learning" : "Teaching"
          } Adventure Starts Now!</h3>
        </div>

        ${
          role === "student"
            ? `
        <div class="features">
          <div class="feature">
            <h4><span class="emoji">📚</span>Expert Help</h4>
            <p>Connect with qualified administrators for personalized assignment assistance</p>
          </div>
          <div class="feature">
            <h4><span class="emoji">💬</span>Real-time Chat</h4>
            <p>Communicate directly with experts through our secure messaging system</p>
          </div>
          <div class="feature">
            <h4><span class="emoji">📊</span>Track Progress</h4>
            <p>Monitor your assignments and deadlines with our intuitive dashboard</p>
          </div>
        </div>
        `
            : `
        <div class="features">
          <div class="feature">
            <h4><span class="emoji">👥</span>Help Students</h4>
            <p>Make a difference by providing expert guidance to students</p>
          </div>
          <div class="feature">
            <h4><span class="emoji">💰</span>Earn Rewards</h4>
            <p>Get compensated for your expertise and time</p>
          </div>
          <div class="feature">
            <span class="emoji">🔒</span>Secure Platform</h4>
            <p>Work in a safe, professional environment with verified users</p>
          </div>
        </div>
        `
        }

        <p><strong>What's next?</strong></p>
        <ul>
          <li>Complete your profile setup</li>
          <li>${
            role === "student"
              ? "Browse available administrators"
              : "Review pending assignments"
          }</li>
          <li>Start your first ${
            role === "student" ? "assignment request" : "project"
          }</li>
        </ul>

        <div style="text-align: center; margin: 30px 0;">
           <a href="https://assignmentshub.mathenoid.com" style="background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 15px 30px; text-decoration: none; border-radius: 25px; font-weight: bold; display: inline-block;">
            🚀 Get Started Now
          </a>
        </div>
      </div>
      <div class="footer">
        <p><span class="emoji">💡</span>Need help? Contact us at support@assignmenthelphub.com</p>
        <p><small>© 2025 Assignment Help Hub. Empowering education, one assignment at a time.</small></p>
      </div>
    </body>
    </html>
  `,
});

const createPasswordResetEmail = (name, newPassword) => ({
  subject: "🔐 Assignment Help Hub - Secure Password Reset Complete",
  html: `
    <!DOCTYPE html>
    <html>
    <head>
      <style>
        body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; line-height: 1.6; color: #333; max-width: 600px; margin: 0 auto; }
        .header { background: linear-gradient(135deg, #ff6b6b 0%, #ee5a24 100%); color: white; padding: 30px; text-align: center; border-radius: 10px 10px 0 0; }
        .content { padding: 30px; background: #f9f9f9; }
        .password-box { background: #fff; border: 3px solid #ff6b6b; padding: 20px; margin: 20px 0; border-radius: 10px; text-align: center; }
        .warning { background: #fff3cd; border-left: 5px solid #ffc107; padding: 15px; margin: 20px 0; border-radius: 5px; }
        .security-tips { background: white; padding: 20px; border-radius: 8px; margin: 20px 0; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
        .footer { background: #333; color: white; padding: 20px; text-align: center; border-radius: 0 0 10px 10px; }
        .emoji { font-size: 1.5em; margin-right: 10px; }
        .btn { background: linear-gradient(135deg, #ff6b6b 0%, #ee5a24 100%); color: white; padding: 15px 30px; text-decoration: none; border-radius: 25px; font-weight: bold; display: inline-block; margin: 20px 0; }
      </style>
    </head>
    <body>
      <div class="header">
        <h1><span class="emoji">🔐</span>Password Reset Successful</h1>
        <p>Your security is our priority</p>
      </div>
      <div class="content">
        <h2>Hello ${name},</h2>
        <p>We've successfully reset your Assignment Help Hub password as requested. Your account security is important to us!</p>
        
        <div class="password-box">
          <h3><span class="emoji">🔑</span>Your New Temporary Password</h3>
          <div style="font-family: 'Courier New', monospace; font-size: 24px; font-weight: bold; color: #ff6b6b; letter-spacing: 2px; margin: 15px 0;">
            ${newPassword}
          </div>
          <p><small>This password is randomly generated and secure</small></p>
        </div>

        <div class="warning">
          <h4><span class="emoji">⚠️</span>Important Security Notice</h4>
          <p><strong>Please change this password immediately after logging in!</strong> This temporary password should only be used for your first login.</p>
        </div>

        <div class="security-tips">
          <h4><span class="emoji">🛡️</span>Password Security Best Practices</h4>
          <ul>
            <li><strong>Use a unique password</strong> - Don't reuse passwords from other accounts</li>
            <li><strong>Make it strong</strong> - Include uppercase, lowercase, numbers, and symbols</li>
            <li><strong>Keep it private</strong> - Never share your password with anyone</li>
            <li><strong>Use a password manager</strong> - Consider using tools like LastPass or 1Password</li>
            <li><strong>Enable notifications</strong> - Monitor your account for unusual activity</li>
          </ul>
        </div>

        <div style="text-align: center;">
          <a href="https://assignmentshub.mathenoid.com" class="btn">
        <span class="emoji">🚀</span>Login & Change Password Now
      </a>
        </div>

        <div style="background: #e8f4fd; border-left: 5px solid #2196F3; padding: 15px; margin: 20px 0; border-radius: 5px;">
          <h4><span class="emoji">🤔</span>Didn't request this reset?</h4>
          <p>If you didn't request a password reset, please contact our security team immediately at <strong>security@assignmenthelphub.com</strong>. Your account may have been compromised.</p>
        </div>

        <p><strong>Need assistance?</strong> Our support team is here to help 24/7!</p>
      </div>
      <div class="footer">
        <p><span class="emoji">🔒</span>This is an automated security message from Assignment Help Hub</p>
        <p><small>© 2025 Assignment Help Hub. Protecting your academic journey.</small></p>
        <p><small>If you have security concerns, contact: security@assignmenthelphub.com</small></p>
      </div>
    </body>
    </html>
  `,
});

// Routes

// Health check
app.get("/api/health", (req, res) => {
  res.json({
    status: "OK",
    timestamp: new Date().toISOString(),
    service: "Assignment Help Hub API",
    version: "1.0.0",
  });
});

// Register endpoint
app.post("/api/auth/register", async (req, res) => {
  const { username, email, password, name, role, paymentMethods } = req.body;

  try {
    // Validation
    if (!username || !email || !password || !name || !role) {
      return res.status(400).json({
        error: "All fields are required",
        details: "Please provide username, email, password, name, and role",
      });
    }
    if (password.length < 6) {
      return res.status(400).json({
        error: "Password too weak",
        details: "Password must be at least 6 characters long",
      });
    }
    if (!["student", "admin"].includes(role)) {
      return res.status(400).json({
        error: "Invalid role",
        details: 'Role must be either "student" or "admin"',
      });
    }
    // Email validation
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    if (!emailRegex.test(email)) {
      return res.status(400).json({
        error: "Invalid email format",
        details: "Please provide a valid email address",
      });
    }
    // Payment methods validation for admins
    if (role === "admin") {
      if (!paymentMethods || paymentMethods.length === 0) {
        return res.status(400).json({
          error: "Payment methods required",
          details: "Admins must provide at least one payment method",
        });
      }
      for (const method of paymentMethods) {
        if (!method.name || !method.type || !method.details) {
          return res.status(400).json({
            error: "Invalid payment method",
            details: "Each payment method must have a name, type, and details",
          });
        }
      }
    }

    const connection = await pool.getConnection();
    try {
      // Check if username already exists in both tables
      const [studentCheck] = await connection.execute(
        "SELECT username FROM students WHERE username = ?",
        [username]
      );
      const [adminCheck] = await connection.execute(
        "SELECT username FROM admins WHERE username = ?",
        [username]
      );
      if (studentCheck.length > 0 || adminCheck.length > 0) {
        return res.status(409).json({
          error: "Username already exists",
          details: "Please choose a different username",
        });
      }
      // Check if email already exists in both tables
      const [studentEmailCheck] = await connection.execute(
        "SELECT email FROM students WHERE email = ?",
        [email]
      );
      const [adminEmailCheck] = await connection.execute(
        "SELECT email FROM admins WHERE email = ?",
        [email]
      );
      if (studentEmailCheck.length > 0 || adminEmailCheck.length > 0) {
        return res.status(409).json({
          error: "Email already registered",
          details: "This email is already associated with an account",
        });
      }

      // Hash password
      const hashedPassword = await bcrypt.hash(password, SALT_ROUNDS);

      await connection.beginTransaction();

      let insertResult;
      if (role === "student") {
        [insertResult] = await connection.execute(
          "INSERT INTO students (username, email, password, joining_date) VALUES (?, ?, ?, NOW())",
          [username, email, hashedPassword]
        );
      } else {
        [insertResult] = await connection.execute(
          "INSERT INTO admins (username, email, password, joining_date) VALUES (?, ?, ?, NOW())",
          [username, email, hashedPassword]
        );
      }

      const userId = insertResult.insertId;

      // Insert payment methods for admins
      if (role === "admin" && paymentMethods) {
        for (const method of paymentMethods) {
          await connection.execute(
            "INSERT INTO payment_methods (admin_id, name, type, details, isAvailable) VALUES (?, ?, ?, ?, 1)",
            [userId, method.name, method.type, method.details]
          );
        }
      }

      await connection.commit();

      // Create user object
      const user = {
        id: userId.toString(),
        username,
        email,
        role,
        name,
        createdAt: new Date(),
      };

      // Generate token
      const token = createToken(user);

      // Send welcome email
      try {
        const welcomeEmail = createWelcomeEmail(name, role);
        await emailTransporter.sendMail({
          from: '"Assignment Help Hub" <assignmenthelphub.service@gmail.com>',
          to: email,
          subject: welcomeEmail.subject,
          html: welcomeEmail.html,
        });
        console.log(`✅ Welcome email sent to ${email}`);
      } catch (emailError) {
        console.log("⚠️ Failed to send welcome email:", emailError.message);
      }

      res.status(201).json({
        message: "Account created successfully!",
        user,
        token,
        redirectTo: "/",
      });
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Registration error:", error);
    res.status(500).json({
      error: "Registration failed",
      details: "An internal server error occurred. Please try again.",
    });
  }
});

// Login endpoint
app.post("/api/auth/login", async (req, res) => {
  const { username, password } = req.body;

  try {
    if (!username || !password) {
      return res.status(400).json({
        error: "Missing credentials",
        details: "Please provide both username and password",
      });
    }

    const connection = await pool.getConnection();

    try {
      // Check students table
      let [rows] = await connection.execute(
        "SELECT * FROM students WHERE username = ?",
        [username]
      );

      let user = null;
      let role = "student";

      if (rows.length === 0) {
        // Check admins table
        [rows] = await connection.execute(
          "SELECT * FROM admins WHERE username = ?",
          [username]
        );
        role = "admin";
      }

      if (rows.length === 0) {
        return res.status(401).json({
          error: "Invalid credentials",
          details: "Username not found",
        });
      }

      user = rows[0];

      // Verify password
      const validPassword = await bcrypt.compare(password, user.password);
      if (!validPassword) {
        return res.status(401).json({
          error: "Invalid credentials",
          details: "Incorrect password",
        });
      }

      // Create user object (exclude password)
      const userResponse = {
        id: user.id.toString(),
        username: user.username,
        email: user.email,
        role: role,
        name: user.username, // Using username as name since it's not stored separately in your schema
        createdAt: user.joining_date,
      };

      // Generate token
      const token = createToken(userResponse);

      res.json({
        message: "Login successful!",
        user: userResponse,
        token,
        redirectTo: "/",
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Login error:", error);
    res.status(500).json({
      error: "Login failed",
      details: "An internal server error occurred. Please try again.",
    });
  }
});

// Forgot password endpoint
app.post("/api/auth/forgot-password", async (req, res) => {
  const { email } = req.body;

  try {
    if (!email) {
      return res.status(400).json({
        error: "Email required",
        details: "Please provide your email address",
      });
    }

    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    if (!emailRegex.test(email)) {
      return res.status(400).json({
        error: "Invalid email format",
        details: "Please provide a valid email address",
      });
    }

    const connection = await pool.getConnection();

    try {
      // Check if email exists in students table
      let [rows] = await connection.execute(
        "SELECT * FROM students WHERE email = ?",
        [email]
      );

      let user = null;
      let role = "student";
      let tableName = "students";

      if (rows.length === 0) {
        // Check admins table
        [rows] = await connection.execute(
          "SELECT * FROM admins WHERE email = ?",
          [email]
        );
        role = "admin";
        tableName = "admins";
      }

      if (rows.length === 0) {
        // Don't reveal if email exists or not for security
        return res.json({
          message:
            "If this email is registered, a reset password will be sent shortly.",
          details: "Please check your email inbox and spam folder",
        });
      }

      user = rows[0];

      // Generate new random password
      const newPassword = generateRandomPassword();
      const hashedPassword = await bcrypt.hash(newPassword, SALT_ROUNDS);

      // Update password in database
      await connection.execute(
        `UPDATE ${tableName} SET password = ? WHERE email = ?`,
        [hashedPassword, email]
      );

      // Send password reset email
      const resetEmail = createPasswordResetEmail(user.username, newPassword);
      await emailTransporter.sendMail({
        from: '"Assignment Help Hub Security" <assignmenthelphub2024@gmail.com>',
        to: email,
        subject: resetEmail.subject,
        html: resetEmail.html,
      });

      console.log(`🔐 Password reset email sent to ${email}`);

      res.json({
        message: "Password reset successful!",
        details:
          "A new temporary password has been sent to your email. Please check your inbox and change it immediately after logging in.",
        timestamp: new Date().toISOString(),
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Forgot password error:", error);

    if (error.code === "EAUTH" || error.code === "ECONNECTION") {
      res.status(503).json({
        error: "Email service temporarily unavailable",
        details: "Please try again later or contact support",
      });
    } else {
      res.status(500).json({
        error: "Password reset failed",
        details: "An internal server error occurred. Please try again.",
      });
    }
  }
});

// Verify token endpoint
app.get("/api/auth/verify", authenticateToken, async (req, res) => {
  try {
    const connection = await pool.getConnection();

    try {
      // Get fresh user data
      let [rows] = await connection.execute(
        "SELECT * FROM students WHERE id = ?",
        [req.user.id]
      );

      let role = "student";

      if (rows.length === 0) {
        [rows] = await connection.execute("SELECT * FROM admins WHERE id = ?", [
          req.user.id,
        ]);
        role = "admin";
      }

      if (rows.length === 0) {
        return res.status(404).json({ error: "User not found" });
      }

      const user = rows[0];
      const userResponse = {
        id: user.id.toString(),
        username: user.username,
        email: user.email,
        role: role,
        name: user.username,
        createdAt: user.joining_date,
      };

      res.json({ user: userResponse });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Token verification error:", error);
    res.status(500).json({ error: "Token verification failed" });
  }
});

// Change password endpoint (protected)
app.post("/api/auth/change-password", authenticateToken, async (req, res) => {
  const { currentPassword, newPassword } = req.body;

  try {
    if (!currentPassword || !newPassword) {
      return res.status(400).json({
        error: "Both current and new passwords are required",
      });
    }

    if (newPassword.length < 6) {
      return res.status(400).json({
        error: "New password must be at least 6 characters long",
      });
    }

    const connection = await pool.getConnection();

    try {
      // Get user data
      let [rows] = await connection.execute(
        "SELECT * FROM students WHERE id = ?",
        [req.user.id]
      );

      let tableName = "students";

      if (rows.length === 0) {
        [rows] = await connection.execute("SELECT * FROM admins WHERE id = ?", [
          req.user.id,
        ]);
        tableName = "admins";
      }

      if (rows.length === 0) {
        return res.status(404).json({ error: "User not found" });
      }

      const user = rows[0];

      // Verify current password
      const validPassword = await bcrypt.compare(
        currentPassword,
        user.password
      );
      if (!validPassword) {
        return res.status(401).json({
          error: "Current password is incorrect",
        });
      }

      // Hash new password
      const hashedNewPassword = await bcrypt.hash(newPassword, SALT_ROUNDS);

      // Update password
      await connection.execute(
        `UPDATE ${tableName} SET password = ? WHERE id = ?`,
        [hashedNewPassword, req.user.id]
      );

      res.json({
        message: "Password changed successfully!",
        timestamp: new Date().toISOString(),
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Change password error:", error);
    res.status(500).json({
      error: "Password change failed",
      details: "An internal server error occurred",
    });
  }
});

// File upload endpoint
app.post(
  "/api/upload",
  authenticateToken,
  upload.array("files", 10),
  async (req, res) => {
    try {
      if (!req.files || req.files.length === 0) {
        return res.status(400).json({
          error: "No files uploaded",
          details: "Please select at least one file to upload",
        });
      }

      const uploadedFiles = req.files.map((file) => {
        const fileUrl = generateFileUrl(req, file.filename, req.user.id);
        return {
          id: uuidv4(),
          name: file.originalname,
          filename: file.filename,
          type: file.mimetype,
          size: file.size,
          url: fileUrl,
          path: file.path,
          uploadedAt: new Date(),
        };
      });

      console.log(
        `✅ ${uploadedFiles.length} files uploaded for user ${req.user.id}`
      );

      res.json({
        message: "Files uploaded successfully",
        files: uploadedFiles,
      });
    } catch (error) {
      console.error("File upload error:", error);

      // Clean up any uploaded files if there was an error
      if (req.files) {
        req.files.forEach((file) => {
          deleteFile(file.path);
        });
      }

      if (error.code === "LIMIT_FILE_SIZE") {
        return res.status(400).json({
          error: "File too large",
          details: "Each file must be smaller than 10MB",
        });
      }

      if (error.code === "LIMIT_FILE_COUNT") {
        return res.status(400).json({
          error: "Too many files",
          details: "Maximum 10 files allowed per upload",
        });
      }

      res.status(500).json({
        error: "File upload failed",
        details: error.message || "An error occurred during file upload",
      });
    }
  }
);

// Delete file endpoint
app.delete(
  "/api/files/:userId/:filename",
  authenticateToken,
  async (req, res) => {
    try {
      const { userId, filename } = req.params;

      // Security check - users can only delete their own files, or admins can delete any files
      if (req.user.role !== "admin" && req.user.id !== userId) {
        return res.status(403).json({
          error: "Access denied",
          details: "You can only delete your own files",
        });
      }

      const filePath = path.join(uploadsDir, userId, filename);

      if (!fs.existsSync(filePath)) {
        return res.status(404).json({
          error: "File not found",
          details: "The requested file does not exist",
        });
      }

      await deleteFile(filePath);

      res.json({
        message: "File deleted successfully",
        filename: filename,
      });
    } catch (error) {
      console.error("File deletion error:", error);
      res.status(500).json({
        error: "File deletion failed",
        details: "An error occurred while deleting the file",
      });
    }
  }
);

// Add these routes to your existing index.js file after the auth routes

// Assignment Routes

// 1. UPDATE: Get all assignments - Fixed payment methods fetching
app.get("/api/assignments", authenticateToken, async (req, res) => {
  try {
    const connection = await pool.getConnection();

    try {
      const [assignments] = await connection.execute(`
        SELECT 
          a.*,
          s.username as student_name,
          ad.username as admin_name
        FROM assignments a
        LEFT JOIN students s ON a.student_id = s.id
        LEFT JOIN admins ad ON a.admin_id = ad.id
        ORDER BY a.posted_on DESC
      `);

      // Get additional data for each assignment
      for (let assignment of assignments) {
        // Get admin payment methods if admin is assigned
        if (assignment.admin_id) {
          const [paymentMethods] = await connection.execute(
            `SELECT id, name, type, details, isAvailable 
             FROM payment_methods 
             WHERE admin_id = ? AND isAvailable = 1`,
            [assignment.admin_id]
          );
          assignment.admin_payment_methods = paymentMethods;
        } else {
          assignment.admin_payment_methods = [];
        }

        // Get negotiations
        const [negotiations] = await connection.execute(
          `SELECT n.*, 
           CASE 
             WHEN n.student_id IS NOT NULL THEN s.username 
             ELSE a.username 
           END as from_user_name,
           CASE 
             WHEN n.student_id IS NOT NULL THEN 'student' 
             ELSE 'admin' 
           END as from_user_role
           FROM negotiations n
           LEFT JOIN students s ON n.student_id = s.id
           LEFT JOIN admins a ON n.admin_id = a.id
           WHERE n.assignment_id = ?
           ORDER BY n.id DESC`,
          [assignment.id]
        );

        // Get submissions
        const [submissions] = await connection.execute(
          `SELECT s.*, a.username as admin_name
           FROM submissions s
           LEFT JOIN admins a ON s.admin_id = a.id
           WHERE s.assignment_id = ?`,
          [assignment.id]
        );

        // Get payment proofs
        const [paymentProofs] = await connection.execute(
          `SELECT * FROM payment_proofs WHERE assignment_id = ?`,
          [assignment.id]
        );

        assignment.negotiations = negotiations;
        assignment.submissions = submissions;
        assignment.paymentProofs = paymentProofs;
      }

      res.json({ assignments });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Get assignments error:", error);
    res.status(500).json({ error: "Failed to fetch assignments" });
  }
});

// 2. UPDATE: Get assignments by student - Fixed payment methods fetching
app.get(
  "/api/assignments/student/:studentId",
  authenticateToken,
  async (req, res) => {
    try {
      const { studentId } = req.params;
      const connection = await pool.getConnection();

      try {
        const [assignments] = await connection.execute(
          `SELECT 
          a.*,
          s.username as student_name,
          ad.username as admin_name
        FROM assignments a
        LEFT JOIN students s ON a.student_id = s.id
        LEFT JOIN admins ad ON a.admin_id = ad.id
        WHERE a.student_id = ?
        ORDER BY a.posted_on DESC`,
          [studentId]
        );

        // Get related data for each assignment
        for (let assignment of assignments) {
          // Get admin payment methods if admin is assigned
          if (assignment.admin_id) {
            const [paymentMethods] = await connection.execute(
              `SELECT id, name, type, details, isAvailable 
             FROM payment_methods 
             WHERE admin_id = ? AND isAvailable = 1`,
              [assignment.admin_id]
            );
            assignment.admin_payment_methods = paymentMethods;
          } else {
            assignment.admin_payment_methods = [];
          }

          // Get negotiations
          const [negotiations] = await connection.execute(
            `SELECT n.*, 
           CASE 
             WHEN n.student_id IS NOT NULL THEN s.username 
             ELSE a.username 
           END as from_user_name,
           CASE 
             WHEN n.student_id IS NOT NULL THEN 'student' 
             ELSE 'admin' 
           END as from_user_role
           FROM negotiations n
           LEFT JOIN students s ON n.student_id = s.id
           LEFT JOIN admins a ON n.admin_id = a.id
           WHERE n.assignment_id = ?
           ORDER BY n.id DESC`,
            [assignment.id]
          );

          // Get submissions
          const [submissions] = await connection.execute(
            `SELECT s.*, a.username as admin_name
           FROM submissions s
           LEFT JOIN admins a ON s.admin_id = a.id
           WHERE s.assignment_id = ?`,
            [assignment.id]
          );

          // Get payment proofs
          const [paymentProofs] = await connection.execute(
            `SELECT * FROM payment_proofs WHERE assignment_id = ?`,
            [assignment.id]
          );

          assignment.negotiations = negotiations;
          assignment.submissions = submissions;
          assignment.paymentProofs = paymentProofs;
        }

        res.json({ assignments });
      } finally {
        connection.release();
      }
    } catch (error) {
      console.error("Get student assignments error:", error);
      res.status(500).json({ error: "Failed to fetch student assignments" });
    }
  }
);

// 3. UPDATE: Get assignments by admin - Fixed payment methods fetching
app.get(
  "/api/assignments/admin/:adminId",
  authenticateToken,
  async (req, res) => {
    try {
      const { adminId } = req.params;
      const connection = await pool.getConnection();

      try {
        const [assignments] = await connection.execute(
          `SELECT 
          a.*,
          s.username as student_name,
          ad.username as admin_name
        FROM assignments a
        LEFT JOIN students s ON a.student_id = s.id
        LEFT JOIN admins ad ON a.admin_id = ad.id
        WHERE a.admin_id = ?
        ORDER BY a.posted_on DESC`,
          [adminId]
        );

        // Get related data for each assignment
        for (let assignment of assignments) {
          // Get admin payment methods
          const [paymentMethods] = await connection.execute(
            `SELECT id, name, type, details, isAvailable 
           FROM payment_methods 
           WHERE admin_id = ? AND isAvailable = 1`,
            [adminId]
          );
          assignment.admin_payment_methods = paymentMethods;

          // Get negotiations
          const [negotiations] = await connection.execute(
            `SELECT n.*, 
           CASE 
             WHEN n.student_id IS NOT NULL THEN s.username 
             ELSE a.username 
           END as from_user_name,
           CASE 
             WHEN n.student_id IS NOT NULL THEN 'student' 
             ELSE 'admin' 
           END as from_user_role
           FROM negotiations n
           LEFT JOIN students s ON n.student_id = s.id
           LEFT JOIN admins a ON n.admin_id = a.id
           WHERE n.assignment_id = ?
           ORDER BY n.id DESC`,
            [assignment.id]
          );

          const [submissions] = await connection.execute(
            `SELECT s.*, a.username as admin_name
           FROM submissions s
           LEFT JOIN admins a ON s.admin_id = a.id
           WHERE s.assignment_id = ?`,
            [assignment.id]
          );

          const [paymentProofs] = await connection.execute(
            `SELECT * FROM payment_proofs WHERE assignment_id = ?`,
            [assignment.id]
          );

          assignment.negotiations = negotiations;
          assignment.submissions = submissions;
          assignment.paymentProofs = paymentProofs;
        }

        res.json({ assignments });
      } finally {
        connection.release();
      }
    } catch (error) {
      console.error("Get admin assignments error:", error);
      res.status(500).json({ error: "Failed to fetch admin assignments" });
    }
  }
);

// 4. UPDATE: Get single assignment - Fixed payment methods fetching
app.get("/api/assignments/:id", authenticateToken, async (req, res) => {
  try {
    const { id } = req.params;
    const connection = await pool.getConnection();

    try {
      const [assignments] = await connection.execute(
        `SELECT 
          a.*,
          s.username as student_name,
          ad.username as admin_name
        FROM assignments a
        LEFT JOIN students s ON a.student_id = s.id
        LEFT JOIN admins ad ON a.admin_id = ad.id
        WHERE a.id = ?`,
        [id]
      );

      if (assignments.length === 0) {
        return res.status(404).json({ error: "Assignment not found" });
      }

      const assignment = assignments[0];

      // Get admin payment methods if admin is assigned
      if (assignment.admin_id) {
        const [paymentMethods] = await connection.execute(
          `SELECT id, name, type, details, isAvailable 
           FROM payment_methods 
           WHERE admin_id = ? AND isAvailable = 1`,
          [assignment.admin_id]
        );
        assignment.admin_payment_methods = paymentMethods;
      } else {
        assignment.admin_payment_methods = [];
      }

      // Get related data
      const [negotiations] = await connection.execute(
        `SELECT n.*, 
         CASE 
           WHEN n.student_id IS NOT NULL THEN s.username 
           ELSE a.username 
         END as from_user_name,
         CASE 
           WHEN n.student_id IS NOT NULL THEN 'student' 
           ELSE 'admin' 
         END as from_user_role
         FROM negotiations n
         LEFT JOIN students s ON n.student_id = s.id
         LEFT JOIN admins a ON n.admin_id = a.id
         WHERE n.assignment_id = ?
         ORDER BY n.id DESC`,
        [assignment.id]
      );

      const [submissions] = await connection.execute(
        `SELECT s.*, a.username as admin_name
         FROM submissions s
         LEFT JOIN admins a ON s.admin_id = a.id
         WHERE s.assignment_id = ?`,
        [assignment.id]
      );

      const [paymentProofs] = await connection.execute(
        `SELECT * FROM payment_proofs WHERE assignment_id = ?`,
        [assignment.id]
      );

      assignment.negotiations = negotiations;
      assignment.submissions = submissions;
      assignment.paymentProofs = paymentProofs;

      res.json({ assignment });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Get assignment error:", error);
    res.status(500).json({ error: "Failed to fetch assignment" });
  }
});

// 5. NEW: Get admin payment methods endpoint
app.get(
  "/api/admins/:adminId/payment-methods",
  authenticateToken,
  async (req, res) => {
    try {
      const { adminId } = req.params;
      console.log("User role:", req.user.role);

      console.log(
        "req.user.id:",
        req.user.id,
        "adminId:",
        adminId,
        "parsed adminId:",
        parseInt(adminId)
      );

      // Security check - admins can only view their own payment methods unless it's for assignment purposes
      if (req.user.role !== "admin" && req.user.role !== "student") {
        return res.status(403).json({ error: "Access denied" });
      }

      // Admins can only view their own payment methods, students can view any admin's methods
      if (req.user.role == "admin" && req.user.id != parseInt(adminId)) {
        return res
          .status(403)
          .json({ error: "You can only view your own payment methods" });
      }

      const connection = await pool.getConnection();

      try {
        const [paymentMethods] = await connection.execute(
          `SELECT id, name, type, details, isAvailable, createdAt 
         FROM payment_methods 
         WHERE admin_id = ? AND isAvailable = 1
         ORDER BY createdAt DESC`,
          [adminId]
        );

        res.json({ paymentMethods });
      } finally {
        connection.release();
      }
    } catch (error) {
      console.error("Get payment methods error:", error);
      res.status(500).json({ error: "Failed to fetch payment methods" });
    }
  }
);

// 6. NEW: Add/Update admin payment methods endpoint
app.post("/api/admins/payment-methods", authenticateToken, async (req, res) => {
  try {
    if (req.user.role !== "admin") {
      return res
        .status(403)
        .json({ error: "Only admins can manage payment methods" });
    }

    const { paymentMethods } = req.body;

    if (
      !paymentMethods ||
      !Array.isArray(paymentMethods) ||
      paymentMethods.length === 0
    ) {
      return res.status(400).json({
        error: "Payment methods required",
        details: "Please provide at least one payment method",
      });
    }

    // Validate payment methods
    for (const method of paymentMethods) {
      if (!method.name || !method.type || !method.details) {
        return res.status(400).json({
          error: "Invalid payment method",
          details: "Each payment method must have a name, type, and details",
        });
      }
    }

    const connection = await pool.getConnection();

    try {
      await connection.beginTransaction();

      // Delete existing payment methods for this admin
      await connection.execute(
        "DELETE FROM payment_methods WHERE admin_id = ?",
        [req.user.id]
      );

      // Insert new payment methods
      for (const method of paymentMethods) {
        await connection.execute(
          `INSERT INTO payment_methods (admin_id, name, type, details, isAvailable) 
           VALUES (?, ?, ?, ?, 1)`,
          [req.user.id, method.name, method.type, method.details]
        );
      }

      await connection.commit();

      // Fetch the updated payment methods
      const [updatedMethods] = await connection.execute(
        `SELECT id, name, type, details, isAvailable, createdAt 
         FROM payment_methods 
         WHERE admin_id = ? 
         ORDER BY createdAt DESC`,
        [req.user.id]
      );

      res.json({
        message: "Payment methods updated successfully",
        paymentMethods: updatedMethods,
      });
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Update payment methods error:", error);
    res.status(500).json({ error: "Failed to update payment methods" });
  }
});

// 7. NEW: Toggle payment method availability
app.patch(
  "/api/payment-methods/:id/toggle",
  authenticateToken,
  async (req, res) => {
    try {
      const { id } = req.params;

      if (req.user.role !== "admin") {
        return res
          .status(403)
          .json({ error: "Only admins can manage payment methods" });
      }

      const connection = await pool.getConnection();

      try {
        // Check if the payment method belongs to the current admin
        const [methods] = await connection.execute(
          "SELECT * FROM payment_methods WHERE id = ? AND admin_id = ?",
          [id, req.user.id]
        );

        if (methods.length === 0) {
          return res.status(404).json({ error: "Payment method not found" });
        }

        const currentMethod = methods[0];
        const newStatus = currentMethod.isAvailable ? 0 : 1;

        // Update availability
        await connection.execute(
          "UPDATE payment_methods SET isAvailable = ? WHERE id = ?",
          [newStatus, id]
        );

        res.json({
          message: `Payment method ${
            newStatus ? "enabled" : "disabled"
          } successfully`,
          id: id,
          isAvailable: Boolean(newStatus),
        });
      } finally {
        connection.release();
      }
    } catch (error) {
      console.error("Toggle payment method error:", error);
      res.status(500).json({ error: "Failed to toggle payment method" });
    }
  }
);

// 8. NEW: Delete specific payment method
app.delete("/api/payment-methods/:id", authenticateToken, async (req, res) => {
  try {
    const { id } = req.params;

    if (req.user.role != "admin") {
      return res
        .status(403)
        .json({ error: "Only admins can manage payment methods" });
    }

    const connection = await pool.getConnection();

    try {
      // Check if the payment method belongs to the current admin
      const [methods] = await connection.execute(
        "SELECT * FROM payment_methods WHERE id = ? AND admin_id = ?",
        [id, req.user.id]
      );

      if (methods.length === 0) {
        return res.status(404).json({ error: "Payment method not found" });
      }

      // Delete the payment method
      await connection.execute("DELETE FROM payment_methods WHERE id = ?", [
        id,
      ]);

      res.json({
        message: "Payment method deleted successfully",
        deletedId: id,
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Delete payment method error:", error);
    res.status(500).json({ error: "Failed to delete payment method" });
  }
});

// Create new assignment
app.post("/api/assignments", authenticateToken, async (req, res) => {
  try {
    const {
      title,
      subject,
      budget,
      deadline,
      description,
      specific_requirements,
      supporting_materials_urls,
    } = req.body;

    // Validation
    if (!title || !subject || !budget || !deadline || !description) {
      return res.status(400).json({
        error: "Missing required fields",
        details:
          "Title, subject, budget, deadline, and description are required",
      });
    }

    const connection = await pool.getConnection();

    try {
      const [result] = await connection.execute(
        `
        INSERT INTO assignments (
          title, subject, budget, deadline, description, 
          specific_requirements, supporting_materials_urls, 
          student_id, admin_id, posted_on
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1, NOW())
      `,
        [
          title,
          subject,
          budget,
          deadline,
          description,
          specific_requirements || null,
          supporting_materials_urls || null,
          req.user.id,
        ]
      );

      // Get the created assignment
      const [assignments] = await connection.execute(
        `
        SELECT 
          a.*,
          s.username as student_name
        FROM assignments a
        LEFT JOIN students s ON a.student_id = s.id
        WHERE a.id = ?
      `,
        [result.insertId]
      );

      const assignment = assignments[0];
      assignment.negotiations = [];
      assignment.submissions = [];
      assignment.paymentProofs = [];

      res.status(201).json({
        message: "Assignment created successfully",
        assignment,
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Create assignment error:", error);
    res.status(500).json({ error: "Failed to create assignment" });
  }
});

// 9. UPDATE: Accept assignment - Include payment methods in response
app.post("/api/assignments/:id/accept", authenticateToken, async (req, res) => {
  try {
    const { id } = req.params;

    if (req.user.role !== "admin") {
      return res
        .status(403)
        .json({ error: "Only admins can accept assignments" });
    }

    const connection = await pool.getConnection();

    try {
      await connection.execute(
        `UPDATE assignments 
         SET status = 'accepted', admin_id = ?, admin_name = ?, accepted_at = NOW()
         WHERE id = ?`,
        [req.user.id, req.user.username, id]
      );

      // Get updated assignment with payment methods
      const [assignments] = await connection.execute(
        `SELECT 
          a.*,
          s.username as student_name,
          ad.username as admin_name
        FROM assignments a
        LEFT JOIN students s ON a.student_id = s.id
        LEFT JOIN admins ad ON a.admin_id = ad.id
        WHERE a.id = ?`,
        [id]
      );

      if (assignments.length === 0) {
        return res.status(404).json({ error: "Assignment not found" });
      }

      const assignment = assignments[0];

      // Get admin payment methods
      const [paymentMethods] = await connection.execute(
        `SELECT id, name, type, details, isAvailable 
         FROM payment_methods 
         WHERE admin_id = ? AND isAvailable = 1`,
        [req.user.id]
      );
      assignment.admin_payment_methods = paymentMethods;

      res.json({
        message: "Assignment accepted successfully",
        assignment,
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Accept assignment error:", error);
    res.status(500).json({ error: "Failed to accept assignment" });
  }
});
// Reject assignment (admin)
app.post("/api/assignments/:id/reject", authenticateToken, async (req, res) => {
  try {
    const { id } = req.params;
    const { rejectionReason } = req.body;

    if (req.user.role !== "admin") {
      return res
        .status(403)
        .json({ error: "Only admins can reject assignments" });
    }

    if (!rejectionReason) {
      return res.status(400).json({ error: "Rejection reason is required" });
    }

    const connection = await pool.getConnection();

    try {
      await connection.execute(
        `
        UPDATE assignments 
        SET status = 'rejected', rejection_reason = ?, rejected_at = NOW()
        WHERE id = ?
      `,
        [rejectionReason, id]
      );

      res.json({ message: "Assignment rejected successfully" });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Reject assignment error:", error);
    res.status(500).json({ error: "Failed to reject assignment" });
  }
});

// Submit assignment (admin)
app.post("/api/assignments/:id/submit", authenticateToken, async (req, res) => {
  try {
    const { id } = req.params;
    const { description, urls } = req.body;

    if (req.user.role !== "admin") {
      return res
        .status(403)
        .json({ error: "Only admins can submit assignments" });
    }

    if (!description) {
      return res
        .status(400)
        .json({ error: "Submission description is required" });
    }

    const connection = await pool.getConnection();

    try {
      // Add submission
      await connection.execute(
        `
        INSERT INTO submissions (assignment_id, admin_id, description, urls, submitted_at)
        VALUES (?, ?, ?, ?, NOW())
      `,
        [id, req.user.id, description, urls || null]
      );

      // Update assignment status
      await connection.execute(
        `
        UPDATE assignments 
        SET status = 'completed', completed_at = NOW()
        WHERE id = ?
      `,
        [id]
      );

      res.json({ message: "Assignment submitted successfully" });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Submit assignment error:", error);
    res.status(500).json({ error: "Failed to submit assignment" });
  }
});

// Price negotiation endpoint
app.post(
  "/api/assignments/:id/negotiate",
  authenticateToken,
  async (req, res) => {
    try {
      const { id } = req.params;
      const { price, description } = req.body;

      if (!price) {
        return res.status(400).json({ error: "Price is required" });
      }

      const connection = await pool.getConnection();

      try {
        // Fetch the assignment details to get the student_id
        const [assignmentRows] = await connection.execute(
          "SELECT student_id FROM assignments WHERE id = ?",
          [id]
        );

        if (assignmentRows.length === 0) {
          return res.status(404).json({ error: "Assignment not found" });
        }

        const studentId = assignmentRows[0].student_id;

        // Add negotiation
        await connection.execute(
          `
        INSERT INTO negotiations (assignment_id, admin_id, student_id, price, description)
        VALUES (?, ?, ?, ?, ?)
        `,
          [
            id,
            req.user.role === "admin" ? req.user.id : null,
            req.user.role === "student" ? studentId : null,
            price,
            description || null,
          ]
        );

        // Update assignment status
        const newStatus =
          req.user.role === "admin"
            ? "waiting_student_response"
            : "negotiating";

        await connection.execute(
          `
        UPDATE assignments
        SET status = ?
        WHERE id = ?
        `,
          [newStatus, id]
        );

        res.json({ message: "Price negotiation sent successfully" });
      } finally {
        connection.release();
      }
    } catch (error) {
      console.error("Negotiate price error:", error);
      res.status(500).json({ error: "Failed to negotiate price" });
    }
  }
);

// Confirm negotiation endpoint
app.post(
  "/api/assignments/:id/confirm",
  authenticateToken,
  async (req, res) => {
    try {
      const { id } = req.params;

      const connection = await pool.getConnection();
      try {
        // Get the latest negotiation
        const [negotiations] = await connection.execute(
          `
        SELECT * FROM negotiations
        WHERE assignment_id = ?
        ORDER BY id DESC
        LIMIT 1
      `,
          [id]
        );

        if (negotiations.length === 0) {
          return res.status(404).json({ error: "No negotiations found" });
        }

        const latestNegotiation = negotiations[0];

        // Update the assignment with the negotiated price
        await connection.execute(
          `
        UPDATE assignments
        SET final_pay = ?, status = 'accepted'
        WHERE id = ?
      `,
          [latestNegotiation.price, id]
        );

        res.json({
          message: "Price confirmed and assignment updated successfully",
        });
      } finally {
        connection.release();
      }
    } catch (error) {
      console.error("Confirm negotiation error:", error);
      res.status(500).json({ error: "Failed to confirm negotiation" });
    }
  }
);

// Update payment status
app.post(
  "/api/assignments/:id/payment",
  authenticateToken,
  async (req, res) => {
    try {
      const { id } = req.params;
      const { isPaid } = req.body;

      const connection = await pool.getConnection();

      try {
        await connection.execute(
          `
        UPDATE assignments 
        SET ispaid = ?
        WHERE id = ?
      `,
          [isPaid, id]
        );

        res.json({ message: "Payment status updated successfully" });
      } finally {
        connection.release();
      }
    } catch (error) {
      console.error("Update payment error:", error);
      res.status(500).json({ error: "Failed to update payment status" });
    }
  }
);

// Upload payment proof
app.post(
  "/api/assignments/:id/payment-proof",
  authenticateToken,
  async (req, res) => {
    try {
      const { id } = req.params;
      const { urls } = req.body;

      if (!urls) {
        return res
          .status(400)
          .json({ error: "Payment proof URLs are required" });
      }

      const connection = await pool.getConnection();

      try {
        await connection.execute(
          `
        INSERT INTO payment_proofs (assignment_id, urls, uploaded_at)
        VALUES (?, ?, NOW())
      `,
          [id, urls]
        );

        res.json({ message: "Payment proof uploaded successfully" });
      } finally {
        connection.release();
      }
    } catch (error) {
      console.error("Upload payment proof error:", error);
      res.status(500).json({ error: "Failed to upload payment proof" });
    }
  }
);

// Chat routes
app.get("/api/assignments/:id/chat", authenticateToken, async (req, res) => {
  try {
    const { id } = req.params;
    const connection = await pool.getConnection();

    try {
      const [messages] = await connection.execute(
        `
        SELECT c.*, 
               from_user.username as from_username,
               to_user.username as to_username
        FROM chat c
        LEFT JOIN (
          SELECT id, username FROM students
          UNION
          SELECT id, username FROM admins
        ) as from_user ON c.from_user = from_user.id
        LEFT JOIN (
          SELECT id, username FROM students
          UNION
          SELECT id, username FROM admins
        ) as to_user ON c.to_user = to_user.id
        WHERE c.assignment_id = ?
        ORDER BY c.timestamp ASC
      `,
        [id]
      );

      res.json({ messages });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Get chat error:", error);
    res.status(500).json({ error: "Failed to fetch chat messages" });
  }
});

// Update the chat POST endpoint
app.post("/api/assignments/:id/chat", authenticateToken, async (req, res) => {
  try {
    const { id } = req.params;
    const { toUserId, message } = req.body;

    if (!toUserId || !message) {
      return res
        .status(400)
        .json({ error: "Recipient and message are required" });
    }

    const connection = await pool.getConnection();
    try {
      // Get assignment details
      const [assignmentRows] = await connection.execute(
        `
        SELECT a.title, a.student_id, a.admin_id,
               s.username as student_name, s.email as student_email,
               ad.username as admin_name, ad.email as admin_email
        FROM assignments a
        LEFT JOIN students s ON a.student_id = s.id
        LEFT JOIN admins ad ON a.admin_id = ad.id
        WHERE a.id = ?
        `,
        [id]
      );

      if (assignmentRows.length === 0) {
        return res.status(404).json({ error: "Assignment not found" });
      }

      const assignment = assignmentRows[0];

      // Insert the message
      await connection.execute(
        `
        INSERT INTO chat (assignment_id, to_user, from_user, message, timestamp)
        VALUES (?, ?, ?, ?, NOW())
        `,
        [id, toUserId, req.user.id, message]
      );

      // Send email notification to the recipient
      try {
        const recipientId = toUserId;
        const recipientRole = req.user.role === "student" ? "admin" : "student";
        const recipientName =
          recipientRole === "admin"
            ? assignment.admin_name
            : assignment.student_name;
        const recipientEmail =
          recipientRole === "admin"
            ? assignment.admin_email
            : assignment.student_email;
        const senderName = req.user.username;

        const emailContent = createNewMessageEmail(
          recipientName,
          senderName,
          assignment.title,
          message
        );

        await emailTransporter.sendMail({
          from: '"Assignment Help Hub" <assignmenthelphub2024@gmail.com>',
          to: recipientEmail,
          subject: emailContent.subject,
          html: emailContent.html,
        });

        console.log(`📧 Notification email sent to ${recipientEmail}`);
      } catch (emailError) {
        console.error(
          "⚠️ Failed to send notification email:",
          emailError.message
        );
        // Don't fail the request if email fails
      }

      res.json({ message: "Message sent successfully" });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Send message error:", error);
    res.status(500).json({ error: "Failed to send message" });
  }
});

// Add these routes to your existing index.js file after the existing routes

// Get all students with their statistics (Admin only)
app.get("/api/students", authenticateToken, async (req, res) => {
  try {
    if (req.user.role !== "admin") {
      return res.status(403).json({ error: "Only admins can view students" });
    }

    const connection = await pool.getConnection();

    try {
      // Get all students with their assignment statistics
      const [students] = await connection.execute(`
        SELECT 
          s.id,
          s.username as name,
          s.email,
          s.joining_date,
          COUNT(a.id) as total_assignments,
          COUNT(CASE WHEN a.status = 'completed' THEN 1 END) as completed_assignments,
          COALESCE(SUM(CASE WHEN a.ispaid = 1 AND a.final_pay IS NOT NULL THEN a.final_pay 
                           WHEN a.ispaid = 1 THEN a.budget 
                           ELSE 0 END), 0) as total_spent
        FROM students s
        LEFT JOIN assignments a ON s.id = a.student_id
        GROUP BY s.id, s.username, s.email, s.joining_date
        ORDER BY s.joining_date DESC
      `);

      const studentsData = students.map((student) => ({
        id: student.id.toString(),
        name: student.name,
        email: student.email,
        joinDate: student.joining_date,
        totalAssignments: student.total_assignments,
        completedAssignments: student.completed_assignments,
        totalSpent: parseFloat(student.total_spent) || 0,
      }));

      res.json({ students: studentsData });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Get students error:", error);
    res.status(500).json({ error: "Failed to fetch students" });
  }
});

// Delete student endpoint (Admin only)
app.delete("/api/students/:id", authenticateToken, async (req, res) => {
  try {
    const { id } = req.params;

    if (req.user.role !== "admin") {
      return res.status(403).json({ error: "Only admins can delete students" });
    }

    const connection = await pool.getConnection();

    try {
      // Get student info before deletion for email
      const [studentRows] = await connection.execute(
        "SELECT username, email FROM students WHERE id = ?",
        [id]
      );

      if (studentRows.length === 0) {
        return res.status(404).json({ error: "Student not found" });
      }

      const student = studentRows[0];

      // Start transaction
      await connection.beginTransaction();

      try {
        // Delete related records first (due to foreign key constraints)

        // Delete payment proofs for student's assignments
        await connection.execute(
          `
          DELETE pp FROM payment_proofs pp
          INNER JOIN assignments a ON pp.assignment_id = a.id
          WHERE a.student_id = ?
        `,
          [id]
        );

        // Delete submissions for student's assignments
        await connection.execute(
          `
          DELETE s FROM submissions s
          INNER JOIN assignments a ON s.assignment_id = a.id
          WHERE a.student_id = ?
        `,
          [id]
        );

        // Delete negotiations for student's assignments
        await connection.execute(
          `
          DELETE n FROM negotiations n
          WHERE n.assignment_id IN (SELECT a.id FROM assignments a WHERE a.student_id = ?)
        `,
          [id]
        );

        // Delete chat messages for student's assignments
        await connection.execute(
          `
          DELETE c FROM chat c
          INNER JOIN assignments a ON c.assignment_id = a.id
          WHERE a.student_id = ?
        `,
          [id]
        );

        // Delete assignments
        await connection.execute(
          "DELETE FROM assignments WHERE student_id = ?",
          [id]
        );

        // Delete the student
        await connection.execute("DELETE FROM students WHERE id = ?", [id]);

        // Commit transaction
        await connection.commit();

        // Send deletion notification email
        try {
          const deletionEmail = createStudentDeletionEmail(student.username);
          await emailTransporter.sendMail({
            from: '"Assignment Help Hub Admin" <assignmenthelphub.service@gmail.com>',
            to: student.email,
            subject: deletionEmail.subject,
            html: deletionEmail.html,
          });
          console.log(
            `✅ Student deletion notification sent to ${student.email}`
          );
        } catch (emailError) {
          console.log("⚠️ Failed to send deletion email:", emailError.message);
        }

        res.json({
          message: "Student deleted successfully",
          deletedStudent: {
            id: id,
            name: student.username,
            email: student.email,
          },
        });
      } catch (error) {
        // Rollback transaction on error
        await connection.rollback();
        throw error;
      }
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error("Delete student error:", error);
    res.status(500).json({
      error: "Failed to delete student",
      details: "An error occurred while deleting the student account",
    });
  }
});

// Add this email template function near the other email template functions
const createStudentDeletionEmail = (studentName) => ({
  subject: "🚨 Assignment Help Hub - Account Deletion Notice",
  html: `
    <!DOCTYPE html>
    <html>
    <head>
      <style>
        body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; line-height: 1.6; color: #333; max-width: 600px; margin: 0 auto; }
        .header { background: linear-gradient(135deg, #dc3545 0%, #c82333 100%); color: white; padding: 30px; text-align: center; border-radius: 10px 10px 0 0; }
        .content { padding: 30px; background: #f9f9f9; }
        .warning-box { background: #fff3cd; border: 3px solid #ffc107; padding: 20px; margin: 20px 0; border-radius: 10px; text-align: center; }
        .info-box { background: #d1ecf1; border-left: 5px solid #17a2b8; padding: 15px; margin: 20px 0; border-radius: 5px; }
        .footer { background: #333; color: white; padding: 20px; text-align: center; border-radius: 0 0 10px 10px; }
        .emoji { font-size: 1.5em; margin-right: 10px; }
        .highlight { color: #dc3545; font-weight: bold; }
      </style>
    </head>
    <body>
      <div class="header">
        <h1><span class="emoji">🚨</span>Account Deletion Notice</h1>
        <p>Assignment Help Hub Administration</p>
      </div>
      <div class="content">
        <h2>Dear ${studentName},</h2>
        
        <div class="warning-box">
          <h3><span class="emoji">⚠️</span>Your Account Has Been Deleted</h3>
          <p>This is to inform you that your Assignment Help Hub student account has been <span class="highlight">permanently deleted</span> by our administration team.</p>
        </div>

        <p>Your account and all associated data have been removed from our system, including:</p>
        <ul>
          <li><strong>Profile Information</strong> - Username, contact details, and preferences</li>
          <li><strong>Assignment History</strong> - All past and current assignments</li>
          <li><strong>Payment Records</strong> - Transaction history and payment proofs</li>
          <li><strong>Communication History</strong> - All chat messages and correspondence</li>
          <li><strong>File Uploads</strong> - All documents and materials uploaded</li>
        </ul>

        <div class="info-box">
          <h4><span class="emoji">📋</span>What This Means</h4>
          <p><strong>Immediate Effect:</strong> You will no longer be able to access your account or any services on Assignment Help Hub.</p>
          <p><strong>Data Recovery:</strong> This deletion is permanent and cannot be undone. No data can be recovered.</p>
          <p><strong>Future Access:</strong> You may create a new account if you wish to use our services again.</p>
        </div>

        <div style="background: #f8d7da; border-left: 5px solid #dc3545; padding: 15px; margin: 20px 0; border-radius: 5px;">
          <h4><span class="emoji">🔒</span>Account Security</h4>
          <p>If you believe this deletion was made in error or if you have any concerns about your account security, please contact our support team immediately.</p>
        </div>

        <div style="background: #d4edda; border-left: 5px solid #28a745; padding: 15px; margin: 20px 0; border-radius: 5px;">
          <h4><span class="emoji">💬</span>Need Assistance?</h4>
          <p>If you have questions about this deletion or need to discuss your account status, please reach out to our support team. We're here to help address any concerns you may have.</p>
        </div>

        <p>Thank you for your previous participation in Assignment Help Hub. We apologize for any inconvenience this may cause.</p>

        <div style="text-align: center; margin: 30px 0;">
          <p><strong>Assignment Help Hub Administration Team</strong></p>
        </div>
      </div>
      <div class="footer">
        <p><span class="emoji">📧</span>Support: support@assignmenthelphub.com</p>
        <p><span class="emoji">🕒</span>Deletion processed on: ${new Date().toLocaleString()}</p>
        <p><small>© 2025 Assignment Help Hub. All rights reserved.</small></p>
      </div>
    </body>
    </html>
  `,
});

// Add to your backend routes
app.post(
  "/api/chat/:assignmentId/read",
  authenticateToken,
  async (req, res) => {
    try {
      const { assignmentId } = req.params;
      const userId = req.user.id;

      const connection = await pool.getConnection();
      try {
        // Upsert the read status
        await connection.execute(
          `
        INSERT INTO chat_read_status (assignment_id, user_id, last_read_at)
        VALUES (?, ?, NOW())
        ON DUPLICATE KEY UPDATE last_read_at = NOW()
        `,
          [assignmentId, userId]
        );

        res.json({ message: "Read status updated" });
      } finally {
        connection.release();
      }
    } catch (error) {
      console.error("Update read status error:", error);
      res.status(500).json({ error: "Failed to update read status" });
    }
  }
);

// Error handling middleware
app.use((err, req, res, next) => {
  console.error("Unhandled error:", err);
  res.status(500).json({
    error: "Internal server error",
    details: "Something went wrong on our end",
  });
});

// 404 handler

app.use((req, res) => {
  res.status(404).json({
    error: "Endpoint not found",
    details: `The endpoint ${req.method} ${req.originalUrl} does not exist`,
  });
});

// Start server
app.listen(PORT, () => {
  console.log(`
🚀 Assignment Help Hub API Server Started!
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
📍 Server running on: http://localhost:${PORT}
🌐 API Endpoints: http://localhost:${PORT}/api
🔒 JWT Secret: ${JWT_SECRET.substring(0, 10)}...
📧 Email Service: ${emailTransporter.options.service}
🗄️  Database: ${dbConfig.database}
⏰ Started at: ${new Date().toISOString()}
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Available Endpoints:
• POST /api/auth/register     - Create new account
• POST /api/auth/login        - User login
• POST /api/auth/forgot-password - Reset password
• GET  /api/auth/verify       - Verify JWT token
• POST /api/auth/change-password - Change password
• GET  /api/health           - Service health check

🎯 Ready to serve requests!
  `);
});

// Graceful shutdown
process.on("SIGTERM", () => {
  console.log("🔄 SIGTERM received. Shutting down gracefully...");
  process.exit(0);
});

process.on("SIGINT", () => {
  console.log("🔄 SIGINT received. Shutting down gracefully...");
  process.exit(0);
});

module.exports = app;
