const express = require("express");
const router = express.Router();
const multer = require("multer");
const db = require("../db");

// Memory storage for database uploads only
const memoryStorage = multer.memoryStorage();

// File filter to only allow Word documents
const fileFilter = (req, file, cb) => {
  const allowedTypes = [
    "application/msword", // .doc
    "application/vnd.openxmlformats-officedocument.wordprocessingml.document", // .docx
    "application/vnd.ms-word.document.12", // .docx (alternative MIME type)
  ];

  if (allowedTypes.includes(file.mimetype)) {
    cb(null, true);
  } else {
    cb(new Error("Only Word documents (.doc, .docx) are allowed!"), false);
  }
};

// Helper function to generate unique log ID
const generateLogId = async () => {
  try {
    // Get the next available ID from the database
    const [result] = await db.execute(
      "SELECT COALESCE(MAX(log_id), 0) + 1 as next_id FROM project_log"
    );

    if (result && result.length > 0) {
      return result[0].next_id;
    }

    // Fallback: use timestamp-based ID but ensure it fits in int range
    const timestamp = Date.now();
    const random = Math.floor(Math.random() * 1000);
    // Use modulo to ensure it fits in int range (2,147,483,647)
    return (timestamp % 2000000000) + random;
  } catch (error) {
    console.error("Error generating log ID:", error);
    // Ultimate fallback: use current timestamp modulo to fit in int range
    return Date.now() % 2000000000;
  }
};

// Helper function to get current user ID from request
const getCurrentUserId = (req) => {
  try {
    // Try to get user from request headers or body
    if (req.headers.authorization) {
      // Extract user ID from authorization header if available
      const token = req.headers.authorization.replace("Bearer ", "");
      // You can implement JWT decoding here if using JWT
      // For now, return default user ID
      return 2001; // Use existing user ID from database
    }
    // Return default user ID if no authorization found
    return 2001; // Use existing user ID from database
  } catch (error) {
    console.error("Error getting current user ID:", error);
    return 2001; // Default fallback user ID - koushik
  }
};

const createProjectLog = async (logData) => {
  try {
    // First, verify that the user exists
    const [userCheck] = await db.execute(
      "SELECT user_id FROM users WHERE user_id = ?",
      [logData.user_id]
    );

    // If user doesn't exist, try to use a known valid user
    if (userCheck.length === 0) {
      // Try to find any valid user in the system
      const [validUsers] = await db.execute(
        "SELECT user_id FROM users LIMIT 1"
      );

      if (validUsers.length > 0) {
        logData.user_id = validUsers[0].user_id;
        console.log(`Using fallback user ID: ${logData.user_id} for logging`);
      } else {
        // If no users exist at all, skip logging but don't fail
        console.warn("No users found in system, skipping project log creation");
        return null;
      }
    }

    const [result] = await db.execute(
      "INSERT INTO project_log (log_id, user_id, project_name, type_name, status_name, invoice_status_name, priority_level_name, deadline, percent_completed, estimated_hours, actual_hours, remarks, action, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
      [
        logData.log_id,
        logData.user_id,
        logData.project_name,
        logData.type_name || null,
        logData.status_name || null,
        logData.invoice_status_name || null,
        logData.priority_level_name || null,
        logData.deadline || null,
        logData.percent_completed || null,
        logData.estimated_hours || null,
        logData.actual_hours || null,
        logData.remarks || null,
        logData.action,
        logData.created_at ||
          new Date().toISOString().slice(0, 19).replace("T", " "),
      ]
    );

    console.log(`Project log created successfully with ID: ${result.insertId}`);
    return result;
  } catch (error) {
    console.error("Error creating project log:", error);
    // Don't throw error, just log it and return null
    // This prevents logging failures from breaking the main upload functionality
    return null;
  }
};

// Configure multer with memory storage for database uploads only
const upload = multer({
  storage: memoryStorage,
  fileFilter: fileFilter,
  limits: {
    fileSize: 10 * 1024 * 1024, // 10MB limit
  },
});

// POST endpoint to upload Word document to database
router.post("/upload", upload.single("document"), async (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({
        success: false,
        message: "No file uploaded",
      });
    }

    // Validate that file buffer exists
    if (!req.file.buffer) {
      return res.status(400).json({
        success: false,
        message: "File buffer is missing",
      });
    }

    const { project_type } = req.body;
    const original_name = req.file.originalname;
    const file_size = req.file.size;

    // Validate project_type
    if (!project_type) {
      return res.status(400).json({
        success: false,
        message: "Project type is required",
      });
    }

    // Insert into database with file as BLOB
    const [result] = await db.execute(
      "INSERT INTO kickoff_forms (project_type, form) VALUES (?, ?)",
      [project_type, req.file.buffer]
    );

    res.json({
      success: true,
      message: "Document uploaded successfully",
      data: {
        id: result.insertId,
        project_type: project_type,
        original_name: original_name,
        file_size: file_size,
        upload_date: new Date(),
      },
    });
  } catch (err) {
    console.error("File upload error:", err);

    res.status(500).json({
      success: false,
      message: "Server error during file upload",
      error: err.message,
    });
  }
});

// POST endpoint to upload kickoff form for a specific project
router.post(
  "/upload-project-form",
  upload.single("document"),
  async (req, res) => {
    try {
      if (!req.file) {
        return res.status(400).json({ error: "No file uploaded" });
      }

      // Validate that file buffer exists
      if (!req.file.buffer) {
        return res.status(400).json({ error: "File buffer is missing" });
      }

      const { project_name } = req.body;

      // Validate required fields
      if (!project_name) {
        return res.status(400).json({
          error: "project_name is required",
        });
      }

      // Update the project's kickoff_form field with the uploaded document
      const [result] = await db.execute(
        `UPDATE projects SET kickoff_form = ? WHERE project_name = ?`,
        [req.file.buffer, project_name]
      );

      if (result.affectedRows === 0) {
        return res.status(404).json({ error: "Project not found" });
      }
      // Get project details for logging
      try {
        const [projectDetails] = await db.execute(
          "SELECT * FROM project_view WHERE project_name = ?",
          [project_name]
        );

        if (projectDetails.length > 0) {
          const project = projectDetails[0];

          // Prepare log data with complete project state
          const logId = await generateLogId();
          console.log(
            `Generated log ID: ${logId} for project: ${project.project_name}`
          );

          const logData = {
            log_id: logId,
            user_id: getCurrentUserId(req),
            project_name: project.project_name,
            type_name: project.type_name || null,
            status_name: project.status_name || null,
            invoice_status_name: project.invoice_status_name || null,
            priority_level_name: project.priority_level_name || null,
            deadline: project.deadline
              ? project.deadline.toISOString().slice(0, 19).replace("T", " ")
              : null,
            percent_completed: project.percent_completed || null,
            estimated_hours: project.estimated_hours || null,
            actual_hours: project.actual_hours || null,
            remarks: project.remarks || null,
            action: "Project Form Uploaded",
            created_at: new Date().toISOString().slice(0, 19).replace("T", " "),
          };

          // Create project log entry
          const logResult = await createProjectLog(logData);
          if (logResult) {
            console.log(
              `Project log created successfully for project: ${project.project_name}`
            );
          } else {
            console.warn(
              `Project log creation failed for project: ${project.project_name}, but upload continued`
            );
          }
        }
      } catch (logError) {
        // Log the error but don't fail the upload
        console.error("Error in logging process:", logError);
      }

      // Send success response
      res.status(200).json({
        message: "Document uploaded successfully to project kickoff_form",
        project_name: project_name,
        file_size: req.file.size,
        mime_type: req.file.mimetype,
        original_filename: req.file.originalname,
      });
    } catch (err) {
      console.error("Error uploading document:", err);
      res.status(500).json({ error: err.message });
    }
  }
);

// GET endpoint to retrieve kickoff forms
router.get("/", async (req, res) => {
  try {
    const [rows] = await db.execute(
      "SELECT id, project_type, form FROM kickoff_forms ORDER BY id DESC"
    );

    res.json({
      success: true,
      data: rows,
    });
  } catch (err) {
    console.error("Error fetching kickoff forms:", err);
    res.status(500).json({
      success: false,
      message: "Server error while fetching forms",
      error: err.message,
    });
  }
});

// Debug endpoint to check projects table structure
router.get("/debug/projects-structure", async (req, res) => {
  try {
    const [rows] = await db.execute("DESCRIBE projects");
    res.json({
      success: true,
      message: "Projects table structure",
      data: rows,
    });
  } catch (err) {
    console.error("Error describing projects table:", err);
    res.status(500).json({
      success: false,
      message: "Server error while describing table",
      error: err.message,
    });
  }
});

// GET endpoint to retrieve kickoff form for a specific project
router.get("/project/:project_name", async (req, res) => {
  try {
    const { project_name } = req.params;

    const [rows] = await db.execute(
      "SELECT kickoff_form FROM projects WHERE project_name = ?",
      [project_name]
    );

    if (rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: "Project not found",
      });
    }

    if (!rows[0].kickoff_form) {
      return res.status(404).json({
        success: false,
        message: "No kickoff form found for this project",
      });
    }

    res.json({
      success: true,
      data: {
        project_name: project_name,
        kickoff_form: rows[0].kickoff_form,
      },
    });
  } catch (err) {
    console.error("Error fetching project kickoff form:", err);
    res.status(500).json({
      success: false,
      message: "Server error while fetching project form",
      error: err.message,
    });
  }
});

// GET endpoint to download kickoff form for a specific project
// GET - Download document from project kickoff_form
router.get("/download-project-form/:projectId", async (req, res) => {
  try {
    const { projectId } = req.params;

    // Try to find project by project_id first
    let [rows] = await db.execute(
      "SELECT project_id, project_name, kickoff_form FROM projects WHERE project_id = ? AND kickoff_form IS NOT NULL",
      [projectId]
    );

    // If not found by ID, try to find by project_name
    if (rows.length === 0) {
      [rows] = await db.execute(
        "SELECT project_id, project_name, kickoff_form FROM projects WHERE project_name = ? AND kickoff_form IS NOT NULL",
        [projectId]
      );
    }

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

    const project = rows[0];

    // Set appropriate headers for file download
    // Since we don't store file extension separately, we'll use a generic approach
    res.setHeader("Content-Type", "application/octet-stream");
    res.setHeader(
      "Content-Disposition",
      `attachment; filename="kickoff_form_${
        project.project_name || project.project_id
      }.docx"`
    );

    // Send the file buffer
    res.send(project.kickoff_form);
  } catch (err) {
    console.error("Error downloading document:", err);
    res.status(500).json({ error: err.message });
  }
});

// GET - Download document by project name (alternative route)
router.get("/download-project-form-by-name/:projectName", async (req, res) => {
  try {
    const { projectName } = req.params;

    const [rows] = await db.execute(
      "SELECT project_id, project_name, kickoff_form FROM projects WHERE project_name = ? AND kickoff_form IS NOT NULL",
      [projectName]
    );

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

    const project = rows[0];

    // Set appropriate headers for file download
    res.setHeader("Content-Type", "application/octet-stream");
    res.setHeader(
      "Content-Disposition",
      `attachment; filename="kickoff_form_${project.project_name}.docx"`
    );

    // Send the file buffer
    res.send(project.kickoff_form);
  } catch (err) {
    console.error("Error downloading document:", err);
    res.status(500).json({ error: err.message });
  }
});

// GET - Check if project has kickoff form document
router.get("/project/:projectId", async (req, res) => {
  try {
    const [rows] = await db.execute(
      `SELECT 
        project_id,
        CASE 
          WHEN kickoff_form IS NOT NULL THEN 'Document uploaded'
          ELSE 'No document uploaded'
        END as document_status
       FROM projects 
       WHERE project_id = ?`,
      [req.params.projectId]
    );

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

    res.json(rows[0]);
  } catch (err) {
    console.error("Error checking project document status:", err);
    res.status(500).json({ error: err.message });
  }
});

// GET - List all projects with kickoff form status
router.get("/", async (req, res) => {
  try {
    const [rows] = await db.execute(
      `SELECT 
        project_id,
        project_name,
        CASE 
          WHEN kickoff_form IS NOT NULL THEN 'Document uploaded'
          ELSE 'No document uploaded'
        END as kickoff_form_status
       FROM projects 
       ORDER BY project_id DESC`
    );

    res.json(rows);
  } catch (err) {
    console.error("Error fetching projects document status:", err);
    res.status(500).json({ error: err.message });
  }
});

// DELETE - Remove kickoff form document from project
router.delete("/project/:projectId", async (req, res) => {
  try {
    const [result] = await db.execute(
      "UPDATE projects SET kickoff_form = NULL WHERE project_id = ?",
      [req.params.projectId]
    );

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: "Project not found" });
    }

    res.json({
      message: "Kickoff form document removed from project successfully",
    });
  } catch (err) {
    console.error("Error removing document:", err);
    res.status(500).json({ error: err.message });
  }
});

// GET endpoint to download a specific document from database
router.get("/download/:id", async (req, res) => {
  try {
    const { id } = req.params;

    const [rows] = await db.execute(
      "SELECT form, project_type FROM kickoff_forms WHERE id = ?",
      [id]
    );

    if (rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: "Document not found",
      });
    }

    const formBuffer = rows[0].form;
    const projectType = rows[0].project_type;

    // Check if form buffer exists
    if (!formBuffer) {
      return res.status(404).json({
        success: false,
        message: "Document data not found in database",
      });
    }

    // Set headers for file download
    res.setHeader("Content-Type", "application/octet-stream");
    res.setHeader(
      "Content-Disposition",
      `attachment; filename="kickoff_form_${projectType}_${id}.docx"`
    );

    // Send the file buffer directly
    res.send(formBuffer);
  } catch (err) {
    console.error("Error downloading document:", err);
    res.status(500).json({
      success: false,
      message: "Server error while downloading document",
      error: err.message,
    });
  }
});

// DELETE endpoint to remove a kickoff form from database
router.delete("/form/:id", async (req, res) => {
  try {
    const { id } = req.params;

    // Check if document exists before deleting
    const [rows] = await db.execute(
      "SELECT id FROM kickoff_forms WHERE id = ?",
      [id]
    );

    if (rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: "Document not found",
      });
    }

    // Delete from database (file is stored as BLOB, so no file system cleanup needed)
    await db.execute("DELETE FROM kickoff_forms WHERE id = ?", [id]);

    res.json({
      success: true,
      message: "Document deleted successfully",
    });
  } catch (err) {
    console.error("Error deleting document:", err);
    res.status(500).json({
      success: false,
      message: "Server error while deleting document",
      error: err.message,
    });
  }
});
module.exports = router;
