const { pool } = require("../../config/db");

/* ===========================
   ✅ CREATE ASSET
=========================== */

const createAsset = async (req, res) => {
  try {
    console.log("=== CREATE ASSET DEBUG ===");
    console.log("req.body:", req.body);
    console.log("req.files:", req.files);
    console.log("req.user:", req.user); // Check what user data is available
    const org_id = req.session.user.org_id;

    let { 
      asset_name,
      employee_id,
      status,
      // org_id,
      created_by
    } = req.body;

    // Validation
    if (!asset_name || !org_id) {
      return res.status(400).json({
        success: false,
        message: "Asset name and Organization ID are required",
      });
    }

    // Handle asset image upload
    const asset_image = req.files?.asset_image?.[0]?.originalname || null;

    // Generate serial number if not provided
    let s_no = req.body.s_no;
    if (!s_no) {
      const countResult = await pool.query(
        `SELECT COUNT(*) as count FROM assets WHERE org_id = $1`,
        [org_id]
      );
      const count = parseInt(countResult.rows[0].count) + 1;
      s_no = `AST-${org_id.substring(0, 8)}-${String(count).padStart(4, '0')}`;
    }

    // Check if serial number already exists
    const existingAsset = await pool.query(
      `SELECT id FROM assets WHERE s_no = $1 AND is_deleted = false`,
      [s_no]
    );
    
    if (existingAsset.rows.length > 0) {
      return res.status(400).json({
        success: false,
        message: "Asset with this serial number already exists",
      });
    }

    // If employee_id is provided, verify employee exists
    let employee_name = null;
    if (employee_id) {
      const employeeResult = await pool.query(
        `SELECT CONCAT(first_name, ' ', last_name) as full_name 
         FROM employees 
         WHERE employee_id = $1 AND is_deleted = false`,
        [employee_id]
      );
      
      if (employeeResult.rows.length === 0) {
        return res.status(404).json({
          success: false,
          message: "Employee not found",
        });
      }
      
      employee_name = employeeResult.rows[0].full_name;
      
      // Auto-set status to 'Assigned' if employee is assigned
      if (!status) {
        status = 'Assigned';
      }
    }

    // Set default status if not provided
    if (!status) {
      status = 'Available';
    }

    // Handle created_by - ensure it's a string or NULL
    let createdBy = null;
    if (created_by) {
      createdBy = String(created_by);
    } else if (req.user?.id) {
      createdBy = String(req.user.id);
    } else if (req.user?.email) {
      createdBy = req.user.email;
    } else {
      createdBy = 'system';
    }

    const result = await pool.query(
      `
  INSERT INTO assets (
    asset_name,
    asset_image,
    s_no,
    status,
    employee_id,
    employee_name,
    assigned_date,
    org_id,
    created_by,
    created_at,
    updated_at
  )
  VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW(), NOW())
  RETURNING *
  `,
  [
    asset_name,
    asset_image,
    s_no,
    status,
    employee_id || null,
    employee_name,
    assigned_date || null,  // Add this
    org_id,
    created_by || req.user?.email || 'system'
  ]
);

    res.status(201).json({
      success: true,
      message: "Asset created successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("CREATE ASSET ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};



/* ===========================
   ✅ GET ALL ASSETS
=========================== */

const getAllAssets = async (req, res) => {
  try {
    const { page = 1, limit = 10, status, search } = req.query;
    const offset = (page - 1) * limit;
    const org_id = req.session.user.orgId;
    
    let query = `
      SELECT 
        a.*,
        e.employee_code,
        e.department,
        e.designation,
        e.email as employee_email
      FROM assets a
      LEFT JOIN employees e ON a.employee_id = e.employee_id
      WHERE a.is_deleted = false
    `;
    
    const queryParams = [];
    let paramCount = 1;
    
    // Apply filters
    if (status) {
      query += ` AND a.status = $${paramCount}`;
      queryParams.push(status);
      paramCount++;
    }
    
    if (org_id) {
      query += ` AND a.org_id = $${paramCount}`;
      queryParams.push(org_id);
      paramCount++;
    }
    
    if (search) {
      query += ` AND (a.asset_name ILIKE $${paramCount} OR a.s_no ILIKE $${paramCount} OR a.employee_name ILIKE $${paramCount})`;
      queryParams.push(`%${search}%`);
      paramCount++;
    }
    
    // Get total count
    const countQuery = query.replace(
      /SELECT.*FROM/,
      'SELECT COUNT(*) as total FROM'
    );
    const countResult = await pool.query(countQuery, queryParams);
    const totalRecords = parseInt(countResult.rows[0].total);
    
    // Add pagination
    query += ` ORDER BY a.created_at DESC LIMIT $${paramCount} OFFSET $${paramCount + 1}`;
    queryParams.push(limit, offset);
    
    const result = await pool.query(query, queryParams);
    
    res.json({
      success: true,
      data: result.rows,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(totalRecords / limit),
        totalRecords,
        recordsPerPage: parseInt(limit)
      }
    });
  } catch (err) {
    console.error("FETCH ASSETS ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET ASSET BY ID
=========================== */

const getAssetById = async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await pool.query(
      `
      SELECT 
        a.*,
        e.employee_code,
        e.first_name,
        e.last_name,
        e.department,
        e.designation,
        e.email as employee_email,
        e.phone as employee_phone
      FROM assets a
      LEFT JOIN employees e ON a.employee_id = e.employee_id
      WHERE a.id = $1 AND a.is_deleted = false
      `,
      [id]
    );
    
    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Asset not found",
      });
    }
    
    res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (err) {
    console.error("GET ASSET BY ID ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Fetch failed",
    });
  }
};

/* ===========================
   ✅ UPDATE ASSET
=========================== */

const updateAsset = async (req, res) => {
  try {
    const { id } = req.params;
    const { 
      asset_name, 
      employee_id, 
      status,
      updated_by 
    } = req.body;
    
    // Check if asset exists
    const existingAsset = await pool.query(
      `SELECT * FROM assets WHERE id = $1 AND is_deleted = false`,
      [id]
    );
    
    if (!existingAsset.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Asset not found",
      });
    }
    
    const oldAsset = existingAsset.rows[0];
    
    // Handle asset image update
    const asset_image = req.files?.asset_image?.[0]?.originalname || oldAsset.asset_image;
    
    // Verify employee if provided
    let employee_name = oldAsset.employee_name;
    let finalStatus = status || oldAsset.status;
    
    if (employee_id && employee_id !== oldAsset.employee_id) {
      const employeeResult = await pool.query(
        `SELECT CONCAT(first_name, ' ', last_name) as full_name 
         FROM employees 
         WHERE employee_id = $1 AND is_deleted = false`,
        [employee_id]
      );
      
      if (employeeResult.rows.length === 0) {
        return res.status(404).json({
          success: false,
          message: "Employee not found",
        });
      }
      
      employee_name = employeeResult.rows[0].full_name;
      
      // Auto-update status to 'Assigned' when employee is assigned
      if (employee_id && !status) {
        finalStatus = 'Assigned';
      }
    } else if (!employee_id && !status && oldAsset.status === 'Assigned') {
      // If employee is removed and status not specified, change to 'Available'
      finalStatus = 'Available';
      employee_name = null;
    }
    
    // Handle updated_by - ensure it's a string
    let updatedBy = null;
    if (updated_by) {
      updatedBy = String(updated_by);
    } else if (req.user?.id) {
      updatedBy = String(req.user.id);
    } else if (req.user?.email) {
      updatedBy = req.user.email;
    } else {
      updatedBy = 'system';
    }
    
const result = await pool.query(
  `
  UPDATE assets
  SET 
    asset_name = COALESCE($1, asset_name),
    asset_image = $2,
    s_no = COALESCE($3, s_no),
    status = $4,
    employee_id = $5,
    employee_name = $6,
    assigned_date = $7,
    updated_by = $8,
    updated_at = NOW()
  WHERE id = $9
  RETURNING *
  `,
  [
    asset_name || oldAsset.asset_name,
    asset_image,
    req.body.s_no || oldAsset.s_no,
    finalStatus,
    employee_id || null,
    employee_name,
    req.body.assigned_date || null,  // Add this
    updated_by || req.user?.email || 'system',
    id
  ]
);
    
    res.status(200).json({
      success: true,
      message: "Asset updated successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("UPDATE ASSET ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};


/* ===========================
   ✅ DELETE ASSET (SOFT DELETE)
=========================== */

const deleteAsset = async (req, res) => {
  try {
    const { id } = req.params;
    
    // Check if asset exists
    const existingAsset = await pool.query(
      `SELECT * FROM assets WHERE id = $1 AND is_deleted = false`,
      [id]
    );
    
    if (!existingAsset.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Asset not found",
      });
    }
    
    await pool.query(
      `
      UPDATE assets
      SET is_deleted = true,
          updated_at = NOW()
      WHERE id = $1
      `,
      [id]
    );
    
    res.json({
      success: true,
      message: "Asset deleted successfully ✅",
    });
  } catch (err) {
    console.error("DELETE ASSET ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Delete failed",
    });
  }
};

/* ===========================
   ✅ ASSIGN ASSET TO EMPLOYEE
=========================== */

const assignAssetToEmployee = async (req, res) => {
  try {
    const { id } = req.params;
    const { employee_id, updated_by } = req.body;
    
    if (!employee_id) {
      return res.status(400).json({
        success: false,
        message: "Employee ID is required",
      });
    }
    
    // Verify employee exists
    const employeeResult = await pool.query(
      `SELECT CONCAT(first_name, ' ', last_name) as full_name 
       FROM employees 
       WHERE employee_id = $1 AND is_deleted = false`,
      [employee_id]
    );
    
    if (!employeeResult.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Employee not found",
      });
    }
    
    // Check if asset exists and is available
    const assetResult = await pool.query(
      `SELECT * FROM assets WHERE id = $1 AND is_deleted = false`,
      [id]
    );
    
    if (!assetResult.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Asset not found",
      });
    }
    
    const asset = assetResult.rows[0];
    
    if (asset.status === 'Assigned' && asset.employee_id !== employee_id) {
      return res.status(400).json({
        success: false,
        message: "Asset is already assigned to another employee",
      });
    }
    
    const result = await pool.query(
      `
      UPDATE assets
      SET 
        employee_id = $1,
        employee_name = $2,
        status = 'Assigned',
        updated_by = $3,
        updated_at = NOW()
      WHERE id = $4
      RETURNING *
      `,
      [employee_id, employeeResult.rows[0].full_name, updated_by || req.user?.email || 'system', id]
    );
    
    res.status(200).json({
      success: true,
      message: "Asset assigned successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("ASSIGN ASSET ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};

/* ===========================
   ✅ GET AVAILABLE ASSETS
=========================== */

const getAvailableAssets = async (req, res) => {
  try {
    // const { org_id } = req.query;
     const org_id =req.session.user.orgId;
    
    let query = `
      SELECT id, asset_name, s_no, asset_image
      FROM assets
      WHERE status = 'Available' AND is_deleted = false
    `;
    
    const queryParams = [];
    
    if (org_id) {
      query += ` AND org_id = $1`;
      queryParams.push(org_id);
    }
    
    query += ` ORDER BY asset_name`;
    
    const result = await pool.query(query, queryParams);
    
    res.json({
      success: true,
      data: result.rows,
    });
  } catch (err) {
    console.error("GET AVAILABLE ASSETS ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET ASSETS BY EMPLOYEE
=========================== */

const getAssetsByEmployee = async (req, res) => {
  try {
    const { employee_id } = req.params;
    
    const result = await pool.query(
      `
      SELECT 
        id,
        asset_name,
        asset_image,
        s_no,
        status,
        created_at
      FROM assets
      WHERE employee_id = $1 
        AND is_deleted = false
        AND status = 'Assigned'
      ORDER BY created_at DESC
      `,
      [employee_id]
    );
    
    res.json({
      success: true,
      data: result.rows,
    });
  } catch (err) {
    console.error("GET ASSETS BY EMPLOYEE ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ UPDATE ASSET STATUS
=========================== */

const updateAssetStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { status, remarks, updated_by } = req.body;
    
    const validStatuses = ['Available', 'Assigned', 'Under Maintenance', 'Retired'];
    
    if (!validStatuses.includes(status)) {
      return res.status(400).json({
        success: false,
        message: "Invalid status value",
      });
    }
    
    const result = await pool.query(
      `
      UPDATE assets
      SET 
        status = $1,
        updated_by = $2,
        updated_at = NOW()
      WHERE id = $3 
        AND is_deleted = false
      RETURNING *
      `,
      [status, updated_by || req.user?.email || 'system', id]
    );
    
    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Asset not found",
      });
    }
    
    res.status(200).json({
      success: true,
      message: "Asset status updated successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("UPDATE ASSET STATUS ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};


module.exports = {
  createAsset,
  getAllAssets,
  getAssetById,
  updateAsset,
  deleteAsset,
  assignAssetToEmployee,
  getAvailableAssets,
  getAssetsByEmployee,
  updateAssetStatus,
};

