const db = require('../models');
const { sendResponse, sendError } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const { getPagination, getPagingData } = require('../utils/pagination');
const { Op } = require('sequelize');
const { Sequelize } = require('sequelize');

const toNum = (value) => {
  const n = parseFloat(value);
  return Number.isFinite(n) ? n : 0;
};

exports.getAll = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, status, documentType, companyId, fiscalYearId, fiscalPeriodId, fromDate, toDate } = req.query;
  const { limit: limitNum, offset } = getPagination(page, limit);

  const where = {};
  
  if (companyId) {
    where.company_id = companyId;
  } else if (req.user && !req.user.isSuperAdmin) {
    where.company_id = req.user.companyId;
  }

  if (fiscalYearId) {
    where.fiscal_year_id = fiscalYearId;
  }

  if (fiscalPeriodId) {
    where.fiscal_period_id = fiscalPeriodId;
  }

  if (status) {
    where.status = status;
  }

  if (documentType) {
    where.document_type = documentType;
  }

  if (fromDate && toDate) {
    where.posting_date = {
      [Op.between]: [fromDate, toDate]
    };
  } else if (fromDate) {
    where.posting_date = {
      [Op.gte]: fromDate
    };
  } else if (toDate) {
    where.posting_date = {
      [Op.lte]: toDate
    };
  }

  if (search) {
    where[Op.or] = [
      { document_number: { [Op.like]: `%${search}%` } },
      { reference_number: { [Op.like]: `%${search}%` } },
      { remarks: { [Op.like]: `%${search}%` } }
    ];
  }

  const records = await db.AccountingDocument.findAndCountAll({
    where,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'], required: false },
      { model: db.FiscalYear, as: 'fiscalYear', attributes: ['id', 'fiscal_year'] },
      { model: db.FiscalPeriod, as: 'fiscalPeriod', attributes: ['id', 'period_code', 'period_name'], required: false },
      { model: db.Currency, as: 'currency', attributes: ['id', 'currency_code', 'currency_name'], required: false }
    ],
    limit: limitNum,
    offset,
    order: [['posting_date', 'DESC'], ['document_number', 'DESC']]
  });

  const response = getPagingData(records, page, limitNum);
  sendResponse(res, 200, true, 'Accounting documents retrieved successfully', response);
});

exports.getById = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const document = await db.AccountingDocument.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      { model: db.FiscalPeriod, as: 'fiscalPeriod' },
      { model: db.Currency, as: 'currency' },
      {
        model: db.AccountingDocumentItem,
        as: 'items',
        include: [
          { model: db.GLAccount, as: 'glAccount' },
          { model: db.CostCenter, as: 'costCenter', required: false }
        ],
        order: [['line_number', 'ASC']]
      }
    ]
  });

  if (!document) {
    return sendError(res, 'Accounting document not found', 404);
  }

  sendResponse(res, 200, true, 'Accounting document retrieved successfully', document);
});

exports.getTrialBalance = asyncHandler(async (req, res) => {
  const { companyId, fiscalYearId, fiscalPeriodId, fromDate, toDate, accountType } = req.query;

  const where = {
    status: 'POSTED'
  };

  if (companyId) {
    where.company_id = companyId;
  } else if (req.user && !req.user.isSuperAdmin) {
    where.company_id = req.user.companyId;
  }

  if (fiscalYearId) {
    where.fiscal_year_id = fiscalYearId;
  }

  if (fiscalPeriodId) {
    where.fiscal_period_id = fiscalPeriodId;
  }

  if (fromDate && toDate) {
    where.posting_date = {
      [Op.between]: [fromDate, toDate]
    };
  } else if (fromDate) {
    where.posting_date = {
      [Op.gte]: fromDate
    };
  } else if (toDate) {
    where.posting_date = {
      [Op.lte]: toDate
    };
  }

  const accountWhere = {
    status: 'ACTIVE',
    allows_posting: true
  };

  if (accountType) {
    accountWhere.account_type = accountType;
  }

  const trialBalance = await db.AccountingDocumentItem.findAll({
    attributes: [
      'gl_account_id',
      [Sequelize.fn('SUM', Sequelize.col('debit_amount')), 'total_debit'],
      [Sequelize.fn('SUM', Sequelize.col('credit_amount')), 'total_credit']
    ],
    include: [
      {
        model: db.AccountingDocument,
        as: 'document',
        where,
        attributes: []
      },
      {
        model: db.GLAccount,
        as: 'glAccount',
        where: accountWhere,
        attributes: ['id', 'account_code', 'account_name', 'account_type', 'balance_type']
      }
    ],
    group: ['gl_account_id', 'glAccount.id'],
    raw: false,
    nest: true
  });

  const formattedResults = trialBalance.map(item => {
    const debit = parseFloat(item.get('total_debit') || 0);
    const credit = parseFloat(item.get('total_credit') || 0);
    let balance = 0;
    
    if (item.glAccount.balance_type === 'DEBIT') {
      balance = debit - credit;
    } else if (item.glAccount.balance_type === 'CREDIT') {
      balance = credit - debit;
    } else {
      balance = debit - credit;
    }

    return {
      gl_account_id: item.gl_account_id,
      account_code: item.glAccount.account_code,
      account_name: item.glAccount.account_name,
      account_type: item.glAccount.account_type,
      balance_type: item.glAccount.balance_type,
      total_debit: debit,
      total_credit: credit,
      balance: balance
    };
  });

  sendResponse(res, 200, true, 'Trial balance retrieved successfully', formattedResults);
});

exports.getLedgerAccountOptions = asyncHandler(async (req, res) => {
  const { companyId } = req.query;
  const resolvedCompanyId = companyId || (req.user && !req.user.isSuperAdmin ? req.user.companyId : null);
  if (!resolvedCompanyId) return sendError(res, 'companyId is required', 400);

  const rows = await db.GLAccount.findAll({
    where: {
      company_id: resolvedCompanyId,
      status: 'ACTIVE',
      allows_posting: true
    },
    attributes: ['id', 'account_code', 'account_name', 'account_type', 'balance_type', 'account_group'],
    order: [['account_code', 'ASC']]
  });

  sendResponse(res, 200, true, 'Ledger account options retrieved successfully', rows);
});

exports.getAccountLedger = asyncHandler(async (req, res) => {
  const {
    page = 1,
    limit = 50,
    companyId,
    fiscalYearId,
    fiscalPeriodId,
    accountId,
    documentType,
    referenceType,
    fromDate,
    toDate
  } = req.query;

  const resolvedCompanyId = companyId || (req.user && !req.user.isSuperAdmin ? req.user.companyId : null);
  if (!resolvedCompanyId) return sendError(res, 'companyId is required', 400);
  if (!accountId) return sendError(res, 'accountId is required', 400);

  const glAccount = await db.GLAccount.findOne({
    where: {
      id: accountId,
      company_id: resolvedCompanyId
    },
    attributes: ['id', 'account_code', 'account_name', 'account_type', 'balance_type']
  });
  if (!glAccount) return sendError(res, 'GL account not found', 404);

  const baseDocWhere = {
    company_id: resolvedCompanyId,
    status: 'POSTED'
  };
  if (fiscalYearId) baseDocWhere.fiscal_year_id = fiscalYearId;
  if (fiscalPeriodId) baseDocWhere.fiscal_period_id = fiscalPeriodId;
  if (documentType) baseDocWhere.document_type = documentType;
  if (referenceType) baseDocWhere.reference_type = referenceType;
  if (toDate) baseDocWhere.posting_date = { [Op.lte]: toDate };

  const openingDocWhere = { ...baseDocWhere };
  if (fromDate) openingDocWhere.posting_date = { [Op.lt]: fromDate };
  else delete openingDocWhere.posting_date;

  const ledgerDocWhere = { ...baseDocWhere };
  if (fromDate && toDate) ledgerDocWhere.posting_date = { [Op.between]: [fromDate, toDate] };
  else if (fromDate) ledgerDocWhere.posting_date = { [Op.gte]: fromDate };
  else if (toDate) ledgerDocWhere.posting_date = { [Op.lte]: toDate };

  const openingRows = await db.AccountingDocumentItem.findAll({
    where: { gl_account_id: accountId },
    attributes: [
      [Sequelize.fn('COALESCE', Sequelize.fn('SUM', Sequelize.col('debit_amount')), 0), 'opening_debit'],
      [Sequelize.fn('COALESCE', Sequelize.fn('SUM', Sequelize.col('credit_amount')), 0), 'opening_credit']
    ],
    include: [{
      model: db.AccountingDocument,
      as: 'document',
      where: openingDocWhere,
      attributes: []
    }],
    raw: true
  });

  const openingDebit = parseFloat(openingRows?.[0]?.opening_debit || 0);
  const openingCredit = parseFloat(openingRows?.[0]?.opening_credit || 0);
  const openingBalance = glAccount.balance_type === 'CREDIT'
    ? (openingCredit - openingDebit)
    : (openingDebit - openingCredit);

  const { limit: limitNum, offset } = getPagination(page, limit);
  const records = await db.AccountingDocumentItem.findAndCountAll({
    where: { gl_account_id: accountId },
    include: [
      {
        model: db.AccountingDocument,
        as: 'document',
        where: ledgerDocWhere,
        attributes: ['id', 'posting_date', 'document_date', 'document_number', 'document_type', 'reference_type', 'reference_number', 'remarks']
      },
      {
        model: db.GLAccount,
        as: 'glAccount',
        attributes: ['id', 'account_code', 'account_name', 'balance_type']
      }
    ],
    limit: limitNum,
    offset,
    order: [
      [{ model: db.AccountingDocument, as: 'document' }, 'posting_date', 'ASC'],
      [{ model: db.AccountingDocument, as: 'document' }, 'document_number', 'ASC'],
      ['line_number', 'ASC'],
      ['id', 'ASC']
    ]
  });

  let runningBalance = openingBalance;
  const ledgerItems = (records.rows || []).map((row) => {
    const debit = parseFloat(row.debit_amount || 0);
    const credit = parseFloat(row.credit_amount || 0);
    const impact = row.glAccount?.balance_type === 'CREDIT'
      ? (credit - debit)
      : (debit - credit);
    runningBalance += impact;
    return {
      id: row.id,
      posting_date: row.document?.posting_date,
      document_date: row.document?.document_date,
      document_number: row.document?.document_number,
      document_type: row.document?.document_type,
      reference_type: row.document?.reference_type,
      reference_number: row.document?.reference_number,
      line_number: row.line_number,
      description: row.description || row.document?.remarks || '',
      debit_amount: debit,
      credit_amount: credit,
      running_balance: runningBalance,
      running_balance_side: runningBalance >= 0
        ? (row.glAccount?.balance_type === 'CREDIT' ? 'CR' : 'DR')
        : (row.glAccount?.balance_type === 'CREDIT' ? 'DR' : 'CR')
    };
  });

  sendResponse(res, 200, true, 'Account ledger retrieved successfully', {
    account: glAccount,
    opening: {
      debit: openingDebit,
      credit: openingCredit,
      balance: openingBalance
    },
    items: ledgerItems,
    totalItems: records.count,
    totalPages: Math.ceil(records.count / limitNum),
    currentPage: parseInt(page, 10),
    closing_balance: ledgerItems.length ? ledgerItems[ledgerItems.length - 1].running_balance : openingBalance
  });
});

exports.getCreditNotes = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, companyId, plantId, fiscalYearId, fromDate, toDate } = req.query;
  const { limit: limitNum, offset } = getPagination(page, limit);
  const resolvedCompanyId = companyId || (req.user && !req.user.isSuperAdmin ? req.user.companyId : null);
  if (!resolvedCompanyId) return sendError(res, 'companyId is required', 400);

  const buildWhere = ({ usePlant = true, useFiscal = true } = {}) => {
    const where = {
      company_id: resolvedCompanyId,
      status: 'APPROVED'
    };
    if (usePlant && plantId) where.plant_id = plantId;
    if (useFiscal && fiscalYearId) where.fiscal_year_id = fiscalYearId;
    if (fromDate && toDate) where.return_date = { [Op.between]: [fromDate, toDate] };
    else if (fromDate) where.return_date = { [Op.gte]: fromDate };
    else if (toDate) where.return_date = { [Op.lte]: toDate };
    if (search) {
      where[Op.or] = [
        { return_number: { [Op.like]: `%${search}%` } },
        { '$salesInvoice.invoice_number$': { [Op.like]: `%${search}%` } }
      ];
    }
    return where;
  };

  const fetchRows = (where) => db.SalesReturn.findAndCountAll({
    where,
    include: [
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name', 'legal_name'], required: false },
      { model: db.SalesInvoice, as: 'salesInvoice', attributes: ['id', 'invoice_number'], required: false },
      {
        model: db.SalesReturnItem,
        as: 'items',
        attributes: ['id', 'return_quantity'],
        include: [{ model: db.SalesInvoiceItem, as: 'invoiceItem', attributes: ['id', 'unit_price', 'tax_rate'], required: false }],
        required: false
      }
    ],
    order: [['return_date', 'DESC'], ['id', 'DESC']],
    distinct: true,
    limit: limitNum,
    offset
  });

  let rows = await fetchRows(buildWhere({ usePlant: true, useFiscal: true }));
  if (!rows.count && fiscalYearId) rows = await fetchRows(buildWhere({ usePlant: true, useFiscal: false }));
  if (!rows.count && plantId) rows = await fetchRows(buildWhere({ usePlant: false, useFiscal: false }));

  const mapped = (rows.rows || []).map((row) => {
    const baseAmount = (row.items || []).reduce((sum, it) => sum + (toNum(it.return_quantity) * toNum(it.invoiceItem?.unit_price)), 0);
    const taxAmount = (row.items || []).reduce((sum, it) => {
      const base = toNum(it.return_quantity) * toNum(it.invoiceItem?.unit_price);
      return sum + (base * toNum(it.invoiceItem?.tax_rate)) / 100;
    }, 0);
    return {
      id: row.id,
      credit_note_no: `CN-${row.return_number}`,
      credit_note_date: row.return_date,
      sales_return_no: row.return_number,
      invoice_no: row.salesInvoice?.invoice_number || '',
      customer_name: row.customer?.name || row.customer?.legal_name || '',
      customer_code: row.customer?.bp_number || '',
      base_amount: baseAmount,
      tax_amount: taxAmount,
      total_amount: baseAmount + taxAmount
    };
  });

  sendResponse(res, 200, true, 'Credit notes retrieved successfully', {
    items: mapped,
    totalItems: rows.count,
    totalPages: Math.ceil(rows.count / limitNum),
    currentPage: parseInt(page, 10)
  });
});

exports.getDebitNotes = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, companyId, plantId, fiscalYearId, fromDate, toDate } = req.query;
  const { limit: limitNum, offset } = getPagination(page, limit);
  const resolvedCompanyId = companyId || (req.user && !req.user.isSuperAdmin ? req.user.companyId : null);
  if (!resolvedCompanyId) return sendError(res, 'companyId is required', 400);

  const buildWhere = ({ usePlant = true, useFiscal = true } = {}) => {
    const where = {
      company_id: resolvedCompanyId,
      status: 'APPROVED'
    };
    if (usePlant && plantId) where.plant_id = plantId;
    if (useFiscal && fiscalYearId) where.fiscal_year_id = fiscalYearId;
    if (fromDate && toDate) where.return_date = { [Op.between]: [fromDate, toDate] };
    else if (fromDate) where.return_date = { [Op.gte]: fromDate };
    else if (toDate) where.return_date = { [Op.lte]: toDate };
    if (search) {
      where[Op.or] = [
        { return_number: { [Op.like]: `%${search}%` } },
        { '$grn.grn_number$': { [Op.like]: `%${search}%` } }
      ];
    }
    return where;
  };

  const fetchRows = (where) => db.PurchaseReturn.findAndCountAll({
    where,
    include: [
      { model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'bp_number', 'name', 'legal_name'], required: false },
      { model: db.GoodsReceiptNote, as: 'grn', attributes: ['id', 'grn_number'], required: false },
      {
        model: db.PurchaseReturnItem,
        as: 'items',
        attributes: ['id', 'return_quantity'],
        include: [{ model: db.GoodsReceiptNoteItem, as: 'grnItem', attributes: ['id', 'unit_price', 'tax_rate'], required: false }],
        required: false
      }
    ],
    order: [['return_date', 'DESC'], ['id', 'DESC']],
    distinct: true,
    limit: limitNum,
    offset
  });

  let rows = await fetchRows(buildWhere({ usePlant: true, useFiscal: true }));
  if (!rows.count && fiscalYearId) rows = await fetchRows(buildWhere({ usePlant: true, useFiscal: false }));
  if (!rows.count && plantId) rows = await fetchRows(buildWhere({ usePlant: false, useFiscal: false }));

  const mapped = (rows.rows || []).map((row) => {
    const baseAmount = (row.items || []).reduce((sum, it) => sum + (toNum(it.return_quantity) * toNum(it.grnItem?.unit_price)), 0);
    const taxAmount = (row.items || []).reduce((sum, it) => {
      const base = toNum(it.return_quantity) * toNum(it.grnItem?.unit_price);
      return sum + (base * toNum(it.grnItem?.tax_rate)) / 100;
    }, 0);
    return {
      id: row.id,
      debit_note_no: `DN-${row.return_number}`,
      debit_note_date: row.return_date,
      purchase_return_no: row.return_number,
      grn_no: row.grn?.grn_number || '',
      vendor_name: row.vendor?.name || row.vendor?.legal_name || '',
      vendor_code: row.vendor?.bp_number || '',
      base_amount: baseAmount,
      tax_amount: taxAmount,
      total_amount: baseAmount + taxAmount
    };
  });

  sendResponse(res, 200, true, 'Debit notes retrieved successfully', {
    items: mapped,
    totalItems: rows.count,
    totalPages: Math.ceil(rows.count / limitNum),
    currentPage: parseInt(page, 10)
  });
});

