'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    const [companies, users] = await Promise.all([
      queryInterface.sequelize.query("SELECT id FROM company WHERE code = 'COMP001' LIMIT 1"),
      queryInterface.sequelize.query("SELECT id FROM user WHERE username = 'admin' LIMIT 1")
    ]);

    if (companies[0].length === 0 || users[0].length === 0) return;

    const companyId = companies[0][0].id;
    const userId = users[0][0].id;

    // Insert root accounts first
    const accounts = [
      // Assets
      { company_id: companyId, account_code: '1000', account_name: 'Assets', account_type: 'ASSET', account_group: 'ASSETS', balance_type: 'DEBIT', is_active: true, allows_posting: false, is_control_account: true, level: 1, sort_order: 1, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      { company_id: companyId, account_code: '1100', account_name: 'Current Assets', account_type: 'ASSET', account_group: 'CURRENT_ASSETS', balance_type: 'DEBIT', is_active: true, allows_posting: false, is_control_account: true, level: 2, sort_order: 2, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      { company_id: companyId, account_code: '1110', account_name: 'Trade Receivables', account_type: 'ASSET', account_group: 'RECEIVABLES', balance_type: 'DEBIT', is_active: true, allows_posting: false, is_control_account: true, level: 3, sort_order: 3, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      { company_id: companyId, account_code: '1111', account_name: 'Sundry Debtors', account_type: 'ASSET', account_group: 'RECEIVABLES', balance_type: 'DEBIT', is_active: true, allows_posting: true, is_control_account: false, level: 4, sort_order: 4, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      { company_id: companyId, account_code: '1200', account_name: 'Inventory', account_type: 'ASSET', account_group: 'INVENTORY', balance_type: 'DEBIT', is_active: true, allows_posting: true, is_control_account: false, level: 2, sort_order: 5, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      
      // Liabilities
      { company_id: companyId, account_code: '2000', account_name: 'Liabilities', account_type: 'LIABILITY', account_group: 'LIABILITIES', balance_type: 'CREDIT', is_active: true, allows_posting: false, is_control_account: true, level: 1, sort_order: 10, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      { company_id: companyId, account_code: '2100', account_name: 'Current Liabilities', account_type: 'LIABILITY', account_group: 'CURRENT_LIABILITIES', balance_type: 'CREDIT', is_active: true, allows_posting: false, is_control_account: true, level: 2, sort_order: 11, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      { company_id: companyId, account_code: '2110', account_name: 'Trade Payables', account_type: 'LIABILITY', account_group: 'PAYABLES', balance_type: 'CREDIT', is_active: true, allows_posting: false, is_control_account: true, level: 3, sort_order: 12, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      { company_id: companyId, account_code: '2111', account_name: 'Sundry Creditors', account_type: 'LIABILITY', account_group: 'PAYABLES', balance_type: 'CREDIT', is_active: true, allows_posting: true, is_control_account: false, level: 4, sort_order: 13, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      { company_id: companyId, account_code: '2200', account_name: 'GST Payable', account_type: 'LIABILITY', account_group: 'TAX_LIABILITIES', balance_type: 'CREDIT', is_active: true, allows_posting: true, is_control_account: false, level: 2, sort_order: 14, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      { company_id: companyId, account_code: '2210', account_name: 'GST Input Credit', account_type: 'ASSET', account_group: 'TAX_ASSETS', balance_type: 'DEBIT', is_active: true, allows_posting: true, is_control_account: false, level: 2, sort_order: 15, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      
      // Revenue
      { company_id: companyId, account_code: '4000', account_name: 'Revenue', account_type: 'REVENUE', account_group: 'REVENUE', balance_type: 'CREDIT', is_active: true, allows_posting: false, is_control_account: true, level: 1, sort_order: 20, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      { company_id: companyId, account_code: '4100', account_name: 'Sales Revenue', account_type: 'REVENUE', account_group: 'SALES', balance_type: 'CREDIT', is_active: true, allows_posting: true, is_control_account: false, level: 2, sort_order: 21, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      
      // Expenses
      { company_id: companyId, account_code: '5000', account_name: 'Expenses', account_type: 'EXPENSE', account_group: 'EXPENSES', balance_type: 'DEBIT', is_active: true, allows_posting: false, is_control_account: true, level: 1, sort_order: 30, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      { company_id: companyId, account_code: '5100', account_name: 'Cost of Goods Sold', account_type: 'EXPENSE', account_group: 'COGS', balance_type: 'DEBIT', is_active: true, allows_posting: true, is_control_account: false, level: 2, sort_order: 31, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() },
      { company_id: companyId, account_code: '5200', account_name: 'Operating Expenses', account_type: 'EXPENSE', account_group: 'OPEX', balance_type: 'DEBIT', is_active: true, allows_posting: true, is_control_account: false, level: 2, sort_order: 32, status: 'ACTIVE', created_by: userId, created_at: new Date(), updated_at: new Date() }
    ];

    await queryInterface.bulkInsert('gl_account', accounts, { ignoreDuplicates: true });

    // Update parent_account_id for child accounts
    const [insertedAccounts] = await queryInterface.sequelize.query(`
      SELECT id, account_code FROM gl_account WHERE company_id = ${companyId}
    `);

    const accountMap = {};
    insertedAccounts.forEach(acc => {
      accountMap[acc.account_code] = acc.id;
    });

    // Update parent relationships
    await queryInterface.sequelize.query(`
      UPDATE gl_account SET parent_account_id = ${accountMap['1000']} WHERE account_code IN ('1100', '1200') AND company_id = ${companyId}
    `);
    await queryInterface.sequelize.query(`
      UPDATE gl_account SET parent_account_id = ${accountMap['1100']} WHERE account_code = '1110' AND company_id = ${companyId}
    `);
    await queryInterface.sequelize.query(`
      UPDATE gl_account SET parent_account_id = ${accountMap['1110']} WHERE account_code = '1111' AND company_id = ${companyId}
    `);
    await queryInterface.sequelize.query(`
      UPDATE gl_account SET parent_account_id = ${accountMap['2000']} WHERE account_code IN ('2100', '2200', '2210') AND company_id = ${companyId}
    `);
    await queryInterface.sequelize.query(`
      UPDATE gl_account SET parent_account_id = ${accountMap['2100']} WHERE account_code = '2110' AND company_id = ${companyId}
    `);
    await queryInterface.sequelize.query(`
      UPDATE gl_account SET parent_account_id = ${accountMap['2110']} WHERE account_code = '2111' AND company_id = ${companyId}
    `);
    await queryInterface.sequelize.query(`
      UPDATE gl_account SET parent_account_id = ${accountMap['4000']} WHERE account_code = '4100' AND company_id = ${companyId}
    `);
    await queryInterface.sequelize.query(`
      UPDATE gl_account SET parent_account_id = ${accountMap['5000']} WHERE account_code IN ('5100', '5200') AND company_id = ${companyId}
    `);
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.bulkDelete('gl_account', null, {});
  }
};

