// In the Node.js server, in the routes/tenants.js file
const express = require('express');
const bodyParser = require('body-parser');
const jwt = require('jsonwebtoken');
const bcrypt = require('bcryptjs');
const router = express.Router();
router.use(bodyParser.json());
const secretKey = 'secretkey123'; // A secret key used to sign the JWT
const { generateOTP } = require('../utils');
const db = require('../db');
const logger = require('../utils/logger');
const nodemailer = require('nodemailer');
const { CustomAuthorization } = require('../utils/authorization');
router.post('/transaction', async (req, res) => {
  try {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];
    console.log(token);
    if (!token) {
      return res.status(401).json({ error: 'Unauthorized' });
    }
    const decoded = jwt.verify(token, 'secretkey123');

    const result = db.query(
      'SELECT * FROM tenants WHERE LOWER(email) = ? and id = ? ',
      [decoded.email.toLowerCase(), decoded.id],
      function (error, results, fields) {
        if (error) throw error;
        if (!results.length)
          return res.json({
            message: 'we failed to authorize you',
            code: 401,
          });
        const user = results[0]; // This will store the first row that matches the query in the variable user
        console.log(req.body);
        if (results) {
          // extract the transaction data from the request body
          const { deposit, recipient } = req.body;

          // define the SQL query and parameters
          const query = `
    INSERT INTO transactions (amount, currency, exchanged, \`from\`, recipient, number, account, accountName, channel, userid, transaction_date)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
  `;
          const params = [
            parseFloat(deposit.amount),
            deposit.currency,
            parseFloat(deposit.exchanged),
            deposit.from,
            recipient.recipient,
            recipient.number,
            recipient.account,
            recipient.accountName,
            recipient.channel,
            decoded.id,
          ];

          // execute the query using the db.query method
          db.query(query, params, (err, result) => {
            if (err) {
              console.error(err);
              res.status(500).json({ message: 'Error inserting transaction' });
              return;
            }

            console.log('Transaction inserted successfully');
            const insertedId = result.insertId;
            const transaction = { id: insertedId, ...req.body };
            res.status(201).json({
              message: 'Transaction inserted successfully',
              transaction,
              code: 201,
            });
          });
          logger.info('User successfully transacted ');
        } else {
          // Passwords don't match
          res.json({
            token,
            message: "User couldn't be verified",
            code: 403,
          });
          logger.info('User password dont match');
        }
      },
    );
    // Verify the token with the same secret key
    // return res.status(200).json({ data: jwt.verify(token, 'secret-key') });
  } catch (error) {
    logger.error('Error fetching forex', error);
    res.status(500).send('Server Error');
  }
});
router.get('/transaction', async (req, res) => {
  try {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];
    console.log(token);
    if (!token) {
      return res.status(401).json({ error: 'Unauthorized' });
    }
    const decoded = jwt.verify(token, 'secretkey123');

    const result = db.query(
      'SELECT * FROM tenants WHERE LOWER(email) = ? and tenant_id = ? ',
      [decoded.email.toLowerCase(), decoded.tenant_id],
      function (error, results, fields) {
        if (error) throw error;
        if (!results.length)
          return res.json({
            message: 'we failed to authorize you',
            code: 401,
          });
        const user = results[0]; // This will store the first row that matches the query in the variable user
        // console.log(req.body);
        if (results) {
          const transactions = db.query(
            `SELECT blocks.block_name, units.unit_name, CONCAT(tenants.firstname, ' ', tenants.middle, ' ', tenants.lastname) AS tenant_name, SUM(units_history.rent) AS rent_total, SUM(units_history.arears) AS utility_total, SUM(units_history.amount_paid) AS amount_paid_total, SUM(units_history.balance_carried_forward) AS arrears_total, DATE_FORMAT(units_history.date, '%M %Y') AS date_formatted 
            FROM 
            units_history 
            INNER JOIN 
            units ON units_history.unit_id = units.unit_id 
            INNER JOIN 
            tenants ON units_history.tenant_id = tenants.tenant_id 
            INNER JOIN 
            blocks ON units.block_id = blocks.block_id 
            where units.tenant_id=? 
            GROUP BY units.tenant_id, DATE_FORMAT(units_history.date, '%Y-%m')
            ORDER BY units_history.date DESC `,
            [decoded.tenant_id],
            function (error, results, fields) {
              if (error) throw error;
              if (!results.length)
                return res.json({
                  message: 'we failed to authorize you',
                  code: 401,
                });
              res.status(200).json(results);
            },
          );
        }
      },
    );
  } catch (error) {
    logger.error('Error fetching transaction', error);
    res.status(500).send('Server Error');
  }
});
router.get('/transaction/current', async (req, res) => {
  try {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];
    console.log(token);
    if (!token) {
      return res.status(401).json({ error: 'Unauthorized' });
    }
    const decoded = jwt.verify(token, 'secretkey123');

    /**
     * $sql = "select *,DATE_FORMAT(units_history.date, '%M %Y') AS date_formatted from 
      (units inner join units_history on units.unit_id=units_history.unit_id) 
      inner join tenants 
      on units_history.tenant_id=tenants.tenant_id 
      where DATE_FORMAT(date, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') 
      and tenants.tenant_id 
      order by unit_history_id desc";
                                    $result = $connect->query($sql);

                                    $rows = 71;
                                    while ($row = $result->fetch_array()) :
                                        $unit_name = $row["unit_name"];
                                        $tenant = $row["firstname"] . " " . $row["middle"] . " " . $row["lastname"];
                                        $rent = $row["rent"];
                                        $amount_paid = $row["amount_paid"];
                                        $arears = $row["arears"];
                                        $date_paid = $row["date_paid"];
                                        $date = $row["date_formatted"];
                                        $balance_carried_forward = $row["balance_carried_forward"];


     */

    const result = db.query(
      'SELECT * FROM tenants WHERE LOWER(email) = ? and tenant_id = ? ',
      [decoded.email.toLowerCase(), decoded.tenant_id],
      function (error, results, fields) {
        if (error) throw error;
        if (!results.length)
          return res.json({
            message: 'we failed to authorize you',
            code: 401,
          });
        const user = results[0]; // This will store the first row that matches the query in the variable user
        // console.log(req.body);
        if (results) {
          const transactions = db.query(
            `select *,DATE_FORMAT(units_history.date, '%M %Y') AS date_formatted from 
            (units inner join units_history on units.unit_id=units_history.unit_id) 
            inner join tenants 
            on units_history.tenant_id=tenants.tenant_id 
            where DATE_FORMAT(date, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') 
            and tenants.tenant_id =?
            order by unit_history_id desc `,
            [decoded.tenant_id],
            function (error, results, fields) {
              if (error) throw error;
              // if (!results.length)
              //   return res.json({
              //     message: 'we failed to authorize you',
              //     code: 401,
              //   });
              console.log({ results, tenant_id: decoded.tenant_id });
              res.status(200).json(results);
            },
          );
        }
      },
    );
  } catch (error) {
    logger.error('Error fetching transaction', error);
    res.status(500).send('Server Error');
  }
});
router.get('/transaction/paid', async (req, res) => {
  try {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];
    console.log(token);
    if (!token) {
      return res.status(401).json({ error: 'Unauthorized' });
    }
    const decoded = jwt.verify(token, 'secretkey123');

    const result = db.query(
      'SELECT * FROM tenants WHERE LOWER(email) = ? and tenant_id = ? ',
      [decoded.email.toLowerCase(), decoded.tenant_id],
      function (error, results, fields) {
        if (error) throw error;
        if (!results.length)
          return res.json({
            message: 'we failed to authorize you',
            code: 401,
          });
        const user = results[0]; // This will store the first row that matches the query in the variable user
        // console.log(req.body);
        if (results) {
          const transactions = db.query(
            `SELECT blocks.block_name, units.unit_name, CONCAT(tenants.firstname, ' ', tenants.middle, ' ', tenants.lastname) AS tenant_name, 
             SUM(units_history.rent) AS rent_total,
             SUM(units_history.arears) AS utility_total,
             SUM(units_history.amount_paid) AS amount_paid_total, 
             SUM(units_history.balance_carried_forward) 
             AS arrears_total, DATE_FORMAT(units_history.date, '%M %Y') AS date_formatted 
              FROM 
              units_history 
              INNER JOIN 
              units ON units_history.unit_id = units.unit_id 
              INNER JOIN 
              tenants ON units_history.tenant_id = tenants.tenant_id 
              INNER JOIN 
              blocks ON units.block_id = blocks.block_id 
              where units.tenant_id=?  
              GROUP BY units.tenant_id, DATE_FORMAT(units_history.date, '%Y-%m')
              ORDER BY units_history.date DESC `,
            [decoded.tenant_id],
            function (error, results, fields) {
              if (error) throw error;
              // if (!results.length)
              //   return res.json({
              //     message: 'we failed to authorize you',
              //     code: 401,
              //   });
              console.log({ results, tenant_id: decoded.tenant_id });
              res.status(200).json(results);
            },
          );
        }
      },
    );
  } catch (error) {
    logger.error('Error fetching transaction', error);
    res.status(500).send('Server Error');
  }
});

router.get('/transaction/limit', async (req, res) => {
  try {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];
    console.log(token);
    if (!token) {
      return res.status(401).json({ error: 'Unauthorized' });
    }
    const decoded = jwt.verify(token, 'secretkey123');

    const result = db.query(
      'SELECT * FROM tenants WHERE LOWER(email) = ? and id = ? ',
      [decoded.email.toLowerCase(), decoded.id],
      function (error, results, fields) {
        if (error) throw error;
        if (!results.length)
          return res.json({
            message: 'we failed to authorize you',
            code: 401,
          });
        const user = results[0]; // This will store the first row that matches the query in the variable user
        console.log(req.body);
        if (results) {
          const transactions = db.query(
            'SELECT amount, currency, exchanged, `from`, recipient, number, account, accountName, channel, transaction_status, transaction_date FROM transactions WHERE  userid = ? ORDER BY transaction_date DESC LIMIT 3',
            [decoded.id],
            function (error, results, fields) {
              if (error) throw error;
              if (!results.length)
                return res.json({
                  message: 'we failed to authorize you',
                  code: 401,
                });
              res.status(200).json(results);
            },
          );
        }
      },
    );
  } catch (error) {
    logger.error('Error fetching transaction', error);
    res.status(500).send('Server Error');
  }
});
router.get('/transaction/pending', async (req, res) => {
  try {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];
    console.log(token);
    if (!token) {
      return res.status(401).json({ error: 'Unauthorized' });
    }
    const decoded = jwt.verify(token, 'secretkey123');

    const result = db.query(
      'SELECT * FROM office WHERE LOWER(email) = ? and id = ? ',
      [decoded.email.toLowerCase(), decoded.id],
      function (error, results, fields) {
        if (error) throw error;
        if (!results.length)
          return res.json({
            message: 'we failed to authorize you',
            code: 401,
          });
        const user = results[0]; // This will store the first row that matches the query in the variable user
        console.log(req.body);
        if (results) {
          const transactions = db.query(
            'SELECT `transactions`.*, tenants.fullname FROM `transactions` inner join tenants on tenants.id=transactions.userid  WHERE transaction_status = "pending" ',
            function (error, results, fields) {
              if (error) throw error;
              if (!results.length)
                return res.json({
                  message: 'we failed to authorize you',
                  code: 401,
                });
              res.status(200).json(results);
            },
          );
        }
      },
    );
  } catch (error) {
    logger.error('Error fetching transaction', error);
    res.status(500).send('Server Error');
  }
});
router.put('/transaction', async (req, res) => {
  try {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];
    console.log(token);
    if (!token) {
      return res.status(401).json({ error: 'Unauthorized' });
    }
    const decoded = jwt.verify(token, 'secretkey123');

    const result = db.query(
      'SELECT * FROM office WHERE LOWER(email) = ? and id = ? ',
      [decoded.email.toLowerCase(), decoded.id],
      function (error, results, fields) {
        if (error) throw error;
        if (!results.length)
          return res.json({
            message: 'we failed to authorize you',
            code: 401,
          });
        const user = results[0]; // This will store the first row that matches the query in the variable user
        console.log(req.body);
        if (req.body.status && req.body.transaction) {
          const transactions = db.query(
            'update `transactions`  set transaction_status=? ,approvedby=? where transaction_id=?',
            [req.body.status, decoded.id, req.body.transaction],
            function (error, results, fields) {
              if (error)
                return res.status(400).json({
                  message: 'Bad request',
                  code: 400,
                });
              error;

              return res.status(200).json({
                message: 'Successfully updated',
                code: 200,
              });
            },
          );
        }
      },
    );
  } catch (error) {
    logger.error('Error fetching transaction', error);
    res.status(500).send('Server Error');
  }
});
module.exports = router;
