const express = require('express');
const router = express.Router();
const fs = require('fs');
const path = require('path');
const multer = require('multer');

const pool = require('../db');
const verifyUserToken = require('../middleware/authClient');
const requireOrgPermission = require('../middleware/requireOrgPermission');

const { deleteProjectFolders, createProjectFolders } = require('../utils/projectFilesystem');

const {
  notifyProjectFileUpload
} = require('../sockets/projectEvents');



/* ============================================================
   MULTER SETUP
============================================================ */
const upload = multer({
  dest: '/tmp',
  limits: { fileSize: 50 * 1024 * 1024 } // 50MB
});

/* ============================================================
   HELPERS
============================================================ */
function getSafeFilename(dir, originalName) {
  const ext = path.extname(originalName);
  const base = path.basename(originalName, ext).replace(/[^\w\-]/g, '_');
  let name = `${base}${ext}`;
  let i = 1;

  while (fs.existsSync(path.join(dir, name))) {
    name = `${base}_${i}${ext}`;
    i++;
  }
  return name;
}

async function requireProjectAccess(req, res, next) {
  const { role, userId, organizationId } = req.user;
  const projectId = req.params.id || req.params.projectId;

  if (role === 'owner' || role === 'admin') return next();

  const [[assigned]] = await pool.query(`
    SELECT 1
    FROM project_users pu
    JOIN projects p ON p.id = pu.projectId
    WHERE pu.projectId = ?
      AND pu.userId = ?
      AND p.organizationId = ?
  `, [projectId, userId, organizationId]);

  if (!assigned) {
    return res.status(403).json({ success: false, error: 'Project access denied' });
  }

  next();
}

/* ============================================================
   PROJECT CRUD
============================================================ */

// Create project

router.post(
  '/',
  verifyUserToken,
  requireOrgPermission(),
  async (req, res) => {
    const {
      name,
      slug,
      description,
      projectHeadUserId,
      relatedProjectId
    } = req.body;

    const { userId, organizationId } = req.user;

    if (!name) {
      return res.status(400).json({
        success: false,
        error: 'Project name required'
      });
    }

    const [result] = await pool.query(
      `
      INSERT INTO projects
      (name, slug, description, projectHeadUserId, relatedProjectId, creatorUserId, organizationId)
      VALUES (?, ?, ?, ?, ?, ?, ?)
      `,
      [
        name,
        slug || null,
        description || null,
        projectHeadUserId || null,
        relatedProjectId || null,
        userId,
        organizationId
      ]
    );

    createProjectFolders({
      organizationId,
      projectId: result.insertId
    });

    res.json({ success: true });
  }
);

// List projects
router.get('/', verifyUserToken, async (req, res) => {
  try {
    const userId = req.user.userId;

    const [projects] = await pool.query(`
      SELECT p.id, p.name
      FROM projects p
      JOIN project_users pu ON pu.projectId = p.id
      WHERE pu.userId = ?
      ORDER BY p.name
    `, [userId]);

    res.json(projects);
  } catch (err) {
    console.error('PROJECT LIST ERROR:', err);
    res.status(500).json({ error: 'Failed to load projects' });
  }
});


// Get single project
router.get(
  '/:id',
  verifyUserToken,
  requireProjectAccess,
  async (req, res) => {
    const projectId = Number(req.params.id);
    const { organizationId } = req.user;

    const [[project]] = await pool.query(
      `
      SELECT *
      FROM projects
      WHERE id = ? AND organizationId = ?
      `,
      [projectId, organizationId]
    );

    if (!project) {
      return res.status(404).json({ success: false });
    }

    const [users] = await pool.query(
      `
  SELECT
    u.id,
    u.firstName,
    u.lastName,
    u.email,
    u.role
  FROM users u
  JOIN project_users pu
    ON pu.userId = u.id
  WHERE pu.projectId = ?
    AND pu.organizationId = ?
  `,
      [projectId, organizationId]
    );


    return res.json({
      success: true,
      project: {
        ...project,
        users
      }
    });
  }
);


// Update project
router.patch(
  '/:id',
  verifyUserToken,
  requireOrgPermission(),
  async (req, res) => {
    const {
      name,
      slug,
      description,
      projectHeadUserId,
      relatedProjectId
    } = req.body;

    const { organizationId } = req.user;

    await pool.query(
      `
      UPDATE projects
      SET
        name = ?,
        slug = ?,
        description = ?,
        projectHeadUserId = ?,
        relatedProjectId = ?
      WHERE id = ?
        AND organizationId = ?
      `,
      [
        name,
        slug || null,
        description || null,
        projectHeadUserId || null,
        relatedProjectId || null,
        req.params.id,
        organizationId
      ]
    );

    res.json({ success: true });
  }
);


// Delete project (DB + FS cleanup)
router.delete(
  '/:id',
  verifyUserToken,
  requireOrgPermission(),
  async (req, res) => {
    const projectId = Number(req.params.id);
    const organizationId = req.user.organizationId;

    await pool.query(
      'DELETE FROM projects WHERE id = ? AND organizationId = ?',
      [projectId, organizationId]
    );

    deleteProjectFolders({ organizationId, projectId });

    res.json({ success: true });
  }
);

/* ============================================================
   PROJECT USER ASSIGNMENT
============================================================ */

router.post(
  '/:id/users',
  verifyUserToken,
  requireOrgPermission(),
  async (req, res) => {
    const projectId = Number(req.params.id);
    const userId = Number(req.body.userId);
    const { organizationId } = req.user;

    if (!projectId || !userId) {
      return res.status(400).json({
        success: false,
        error: 'Invalid projectId or userId'
      });
    }

    await pool.query(
      `
      INSERT INTO project_users (organizationId, projectId, userId)
      VALUES (?, ?, ?)
      `,
      [organizationId, projectId, userId]
    );

    return res.json({ success: true });
  }
);


router.delete(
  '/:projectId/users/:userId',
  verifyUserToken,
  requireOrgPermission(),
  async (req, res) => {
    await pool.query(`
      DELETE FROM project_users
      WHERE projectId = ? AND userId = ?
    `, [req.params.projectId, req.params.userId]);

    res.json({ success: true });
  }
);

/* ============================================================
   FILE UPLOAD
============================================================ */

router.post(
  '/:projectId/files/upload',
  verifyUserToken,
  requireProjectAccess,
  upload.single('file'),
  async (req, res) => {

    try {

      const { projectId } = req.params;
      const { folderType } = req.body; // still saved in DB for UI grouping
      const { organizationId, userId } = req.user;

      if (!req.file) {
        return res.status(400).json({ success: false, error: 'No file uploaded' });
      }

      if (!['images', 'docs'].includes(folderType)) {
        return res.status(400).json({ success: false, error: 'Invalid folder' });
      }

      // ============================
      // CORRECT FILESYSTEM PATH
      // ============================

      const baseDir = path.join(
        '/var/www/saas.gocrm.net/project_files',
        `org_${organizationId}`,
        `project_${projectId}`
      );

      // Ensure folder exists
      await fs.promises.mkdir(baseDir, { recursive: true });

      // Sanitize filename
      const safeName = getSafeFilename(baseDir, req.file.originalname);
      const finalPath = path.join(baseDir, safeName);

      // ============================
      // SAFE MOVE (cross-device safe)
      // ============================

      await fs.promises.copyFile(req.file.path, finalPath);
      await fs.promises.unlink(req.file.path);

      // Verify write
      if (!fs.existsSync(finalPath)) {
        return res.status(500).json({
          success: false,
          error: 'File write failed'
        });
      }

      // ============================
      // DB ENTRY
      // ============================

      await pool.query(`
        INSERT INTO project_files
          (organizationId, projectId, userId, filename, folderType)
        VALUES (?, ?, ?, ?, ?)
      `, [
        organizationId,
        projectId,
        userId,
        safeName,
        folderType
      ]);

      // 🔔 SOCKET EVENT
      await notifyProjectFileUpload({
        organizationId,
        projectId,
        uploaderId: userId,
        filename: safeName,
        folderType
      });

      res.json({ success: true, filename: safeName });

    } catch (err) {

      console.error('UPLOAD ERROR:', err);

      res.status(500).json({
        success: false,
        error: 'Upload failed'
      });

    }
  }
);


// GET /projects/:projectId/users 
// Load project users in Add Tasks dropdown
router.get('/:projectId/users', verifyUserToken, async (req, res) => {
  try {
    const projectId = Number(req.params.projectId);
    const { organizationId } = req.user;

    if (!projectId) {
      return res.status(400).json({ message: 'Invalid projectId' });
    }

    // 🔒 Ensure project belongs to org
    const [[project]] = await pool.query(
      `
      SELECT id
      FROM projects
      WHERE id = ?
        AND organizationId = ?
      LIMIT 1
      `,
      [projectId, organizationId]
    );

    if (!project) {
      return res.status(404).json({ message: 'Project not found' });
    }

    // 👥 Fetch project members
    const [users] = await pool.query(
      `
  SELECT
    u.id,
    CONCAT(u.firstName, ' ', u.lastName) AS name,
    u.email
  FROM project_users pu
  JOIN users u ON u.id = pu.userId
  WHERE pu.projectId = ?
    AND pu.organizationId = ?
  ORDER BY u.firstName ASC, u.lastName ASC
  `,
      [projectId, organizationId]
    );

    return res.json({
      success: true,
      users
    });

  } catch (err) {
    console.error('Error loading project users:', err);
    return res.status(500).json({
      message: 'Failed to load project users'
    });
  }
});



/* ============================================================
   FILE TREE VIEW
============================================================ */

router.get(
  '/:projectId/files',
  verifyUserToken,
  requireProjectAccess,
  async (req, res) => {
    const { projectId } = req.params;
    const { organizationId } = req.user;

    const [files] = await pool.query(`
      SELECT id, filename, folderType, userId, createdAt
      FROM project_files
      WHERE projectId = ? AND organizationId = ?
      ORDER BY folderType, createdAt DESC
    `, [projectId, organizationId]);

    const tree = { images: [], docs: [] };
    files.forEach(f => tree[f.folderType].push(f));

    res.json({ success: true, tree });
  }
);

/* ============================================================
   FILE DOWNLOAD / PREVIEW
============================================================ */

router.get(
  '/:projectId/files/:fileId/download',
  verifyUserToken,
  requireProjectAccess,
  async (req, res) => {
    const { projectId, fileId } = req.params;
    const { organizationId } = req.user;

    const [[file]] = await pool.query(`
      SELECT filename, folderType
      FROM project_files
      WHERE id = ? AND projectId = ? AND organizationId = ?
    `, [fileId, projectId, organizationId]);

    if (!file) return res.status(404).send('File not found');

    const filePath = path.join(
      '/var/www/saas.gocrm.net/project_files',
      `org_${organizationId}`,
      `project_${projectId}`,
      file.filename
    );


    res.download(filePath, file.filename);
  }
);

router.get(
  '/:projectId/files/:fileId/preview',
  verifyUserToken,
  requireProjectAccess,
  async (req, res) => {
    const { projectId, fileId } = req.params;
    const { organizationId } = req.user;

    const [[file]] = await pool.query(`
      SELECT filename, folderType
      FROM project_files
      WHERE id = ? AND projectId = ? AND organizationId = ?
    `, [fileId, projectId, organizationId]);

    if (!file) return res.status(404).send('Not found');

    const ext = path.extname(file.filename).toLowerCase();
    if (!['.jpg', '.jpeg', '.png', '.gif', '.pdf'].includes(ext)) {
      return res.status(403).send('Not previewable');
    }

    const filePath = path.join(
      '/var/www/saas.gocrm.net/orgs',
      `org_${organizationId}`,
      'project_files',
      `project_${projectId}`,
      file.folderType,
      file.filename
    );

    res.setHeader('Content-Disposition', 'inline');
    res.sendFile(filePath);
  }
);

router.get('/projects/:projectId/rooms', verifyUserToken, async (req, res) => {
  const userId = req.user.id;
  const { projectId } = req.params;

  const [rooms] = await pool.query(`
    SELECT pr.room_id, pr.room_name
    FROM project_rooms pr
    JOIN project_users pu 
      ON pu.project_id = pr.project_id
    WHERE pu.user_id = ? AND pr.project_id = ?
    ORDER BY pr.room_name
  `, [userId, projectId]);

  res.json(rooms);
});

router.get(
  '/files/tree',
  verifyUserToken,
  async (req, res) => {

    const { userId, organizationId, role } = req.user;

    let rows;

    if (role === 'admin' || role === 'owner') {

      [rows] = await pool.query(`
        SELECT 
          p.id        AS projectId,
          p.name      AS projectName,
          pf.id       AS fileId,
          pf.filename,
          pf.folderType,
          pf.userId   AS uploaderId
        FROM projects p
        LEFT JOIN project_files pf 
          ON pf.projectId = p.id
         AND pf.organizationId = ?
        WHERE p.organizationId = ?
        ORDER BY p.name, pf.folderType
      `, [organizationId, organizationId]);

    } else {

      [rows] = await pool.query(`
        SELECT 
          p.id        AS projectId,
          p.name      AS projectName,
          pf.id       AS fileId,
          pf.filename,
          pf.folderType,
          pf.userId   AS uploaderId
        FROM project_users pu
        JOIN projects p 
          ON p.id = pu.projectId
        LEFT JOIN project_files pf 
          ON pf.projectId = p.id
         AND pf.organizationId = ?
        WHERE pu.userId = ?
          AND pu.organizationId = ?
        ORDER BY p.name, pf.folderType
      `, [organizationId, userId, organizationId]);

    }

    const tree = {};

    rows.forEach(row => {

      if (!tree[row.projectName]) {
        tree[row.projectName] = {
          slug: row.projectId,
          docs: [],
          images: []
        };
      }

      if (row.fileId && row.filename) {

        tree[row.projectName][row.folderType].push({
          id: row.fileId,
          filename: row.filename,
          uploaderId: row.uploaderId
        });

      }

    });

    res.json({
      success: true,
      tree
    });

  }
);

// to delete project files from tree
router.delete(
  '/:projectId/files/:fileId',
  verifyUserToken,
  requireProjectAccess,
  async (req, res) => {

    try {

      const { projectId, fileId } = req.params;
      const { userId, organizationId, role } = req.user;

      // ---------------------------
      // FETCH FILE RECORD
      // ---------------------------

      const [[file]] = await pool.query(
        `
        SELECT *
        FROM project_files
        WHERE id = ?
          AND projectId = ?
          AND organizationId = ?
        LIMIT 1
        `,
        [fileId, projectId, organizationId]
      );

      if (!file) {
        return res.status(404).json({
          success: false,
          error: 'File not found'
        });
      }

      // ---------------------------
      // PERMISSION CHECK
      // ---------------------------

      const isAdmin = role === 'admin' || role === 'owner';
      const isUploader = Number(file.userId) === Number(userId);

      if (!isAdmin && !isUploader) {
        return res.status(403).json({
          success: false,
          error: 'Not authorized to delete this file'
        });
      }

      // ---------------------------
      // FILESYSTEM DELETE
      // ---------------------------

      const filePath = path.join(
        '/var/www/saas.gocrm.net/project_files',
        `org_${organizationId}`,
        `project_${projectId}`,
        file.filename
      );

      if (fs.existsSync(filePath)) {
        await fs.promises.unlink(filePath);
      }

      // ---------------------------
      // DB DELETE
      // ---------------------------

      await pool.query(
        `DELETE FROM project_files WHERE id = ?`,
        [fileId]
      );

      res.json({ success: true });

    } catch (err) {

      console.error('FILE DELETE ERROR:', err);

      res.status(500).json({
        success: false,
        error: 'Delete failed'
      });

    }
  }
);



module.exports = router;
