'use strict';

/** Transaction modules and their short prefix for document numbers (company code + prefix + FY + sequence) */
const TRANSACTION_MODULES = [
  { module_name: 'PRODUCTION_ORDER', format_prefix: 'PRO', description: 'Production Order number' },
  { module_name: 'MATERIAL_REQUEST', format_prefix: 'MR', description: 'Material Request to Stores' },
  { module_name: 'MATERIAL_ISSUE', format_prefix: 'MI', description: 'Material Issue / Goods Issue' },
  { module_name: 'PURCHASE_ORDER', format_prefix: 'PO', description: 'Purchase Order' },
  { module_name: 'PURCHASE_REQUISITION', format_prefix: 'PR', description: 'Purchase Requisition' },
  { module_name: 'GOODS_RECEIPT_NOTE', format_prefix: 'GRN', description: 'Goods Receipt Note' },
  { module_name: 'VENDOR_INVOICE', format_prefix: 'PINV', description: 'Vendor/Purchase Invoice' },
  { module_name: 'SALES_INVOICE', format_prefix: 'SINV', description: 'Sales Invoice' },
  { module_name: 'REQUEST_FOR_QUOTATION', format_prefix: 'RFQ', description: 'Request for Quotation' },
  { module_name: 'CUSTOMER_PO', format_prefix: 'CPO', description: 'Customer PO number (if auto-generated)' },
  { module_name: 'CUSTOMER_QUOTATION', format_prefix: 'CQ', description: 'Customer Quotation number' },
  { module_name: 'MATERIAL_RETURN_NOTE', format_prefix: 'MRN', description: 'Material Return Note' },
  { module_name: 'GATE_ENTRY', format_prefix: 'GE', description: 'Gate Entry' }
];

module.exports = {
  async up(queryInterface, Sequelize) {
    const [companies] = await queryInterface.sequelize.query(
      'SELECT id, code, name FROM company WHERE status = ?',
      { replacements: ['ACTIVE'] }
    );
    if (!companies || companies.length === 0) return;

    const now = new Date();
    const rows = [];

    for (const company of companies) {
      for (const mod of TRANSACTION_MODULES) {
        rows.push({
          company_id: company.id,
          module_name: mod.module_name,
          material_type_id: null,
          format_pattern: '{COMPANY_CODE}-{PREFIX}-{FY}-{SEQUENCE}',
          format_prefix: mod.format_prefix,
          prefix: mod.format_prefix,
          sequence_length: 6,
          separator: '-',
          current_sequence: 0,
          reset_period: 'YEARLY',
          include_fiscal_year: true,
          fiscal_year_format: 'YYYY-YY',
          description: mod.description,
          is_active: true,
          status: 'ACTIVE',
          created_at: now,
          updated_at: now
        });
      }
    }

    if (rows.length === 0) return;

    const table = 'number_format_master';
    for (const row of rows) {
      const [existing] = await queryInterface.sequelize.query(
        `SELECT id FROM ${table} WHERE company_id = ? AND module_name = ? AND (material_type_id IS NULL OR material_type_id = 0) LIMIT 1`,
        { replacements: [row.company_id, row.module_name] }
      );
      if (existing && existing.length > 0) {
        await queryInterface.sequelize.query(
          `UPDATE ${table} SET format_prefix = ?, include_fiscal_year = ?, fiscal_year_format = ?, format_pattern = ?, prefix = ?, sequence_length = ?, separator = ?, reset_period = 'YEARLY', description = ?, updated_at = ? WHERE id = ?`,
          {
            replacements: [
              row.format_prefix,
              row.include_fiscal_year,
              row.fiscal_year_format,
              row.format_pattern,
              row.prefix,
              row.sequence_length,
              row.separator,
              row.description,
              now,
              existing[0].id
            ]
          }
        );
      } else {
        await queryInterface.bulkInsert(table, [row], {});
      }
    }
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.bulkDelete('number_format_master', {
      module_name: { [Sequelize.Op.in]: TRANSACTION_MODULES.map(m => m.module_name) }
    });
  }
};
