'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('inventory_stock_ledger', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      company_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'company',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      plant_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'plant',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      material_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'material',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT'
      },
      transaction_date: {
        type: Sequelize.DATEONLY,
        allowNull: false
      },
      transaction_type: {
        type: Sequelize.ENUM('RECEIPT', 'ISSUE', 'RETURN', 'SCRAP', 'ADJUSTMENT', 'TRANSFER_IN', 'TRANSFER_OUT'),
        allowNull: false,
        comment: 'Type of stock movement'
      },
      reference_type: {
        type: Sequelize.ENUM('GRN', 'MIN', 'MRN', 'SCRAP', 'ADJUSTMENT', 'TRANSFER', 'PRODUCTION_RECEIPT', 'OTHER'),
        allowNull: false,
        comment: 'Type of reference document'
      },
      reference_id: {
        type: Sequelize.INTEGER,
        allowNull: true,
        comment: 'ID of the reference document'
      },
      reference_number: {
        type: Sequelize.STRING(100),
        allowNull: true,
        comment: 'Document number of the reference'
      },
      batch_number: {
        type: Sequelize.STRING(50),
        allowNull: true,
        comment: 'Batch number for batch tracking'
      },
      lot_number: {
        type: Sequelize.STRING(50),
        allowNull: true,
        comment: 'Lot number for lot tracking'
      },
      expiry_date: {
        type: Sequelize.DATEONLY,
        allowNull: true,
        comment: 'Expiry date for batch/lot'
      },
      storage_location: {
        type: Sequelize.STRING(10),
        allowNull: true
      },
      quantity_in: {
        type: Sequelize.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0,
        comment: 'Quantity received/added'
      },
      quantity_out: {
        type: Sequelize.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0,
        comment: 'Quantity issued/removed'
      },
      balance_quantity: {
        type: Sequelize.DECIMAL(18, 3),
        allowNull: false,
        defaultValue: 0,
        comment: 'Running balance after this transaction'
      },
      unit_cost: {
        type: Sequelize.DECIMAL(18, 2),
        allowNull: false,
        defaultValue: 0,
        comment: 'Unit cost at the time of transaction'
      },
      total_cost: {
        type: Sequelize.DECIMAL(18, 2),
        allowNull: false,
        defaultValue: 0,
        comment: 'Total cost (quantity * unit_cost)'
      },
      valuation_method: {
        type: Sequelize.ENUM('FIFO', 'LIFO', 'AVERAGE', 'STANDARD'),
        allowNull: true,
        defaultValue: 'FIFO',
        comment: 'Valuation method used'
      },
      remarks: {
        type: Sequelize.TEXT,
        allowNull: true
      },
      created_by: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'user',
          key: 'id'
        }
      },
      created_at: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updated_at: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });

    // Add indexes with error handling for existing indexes
    const addIndexIfNotExists = async (tableName, fields, options = {}) => {
      try {
        await queryInterface.addIndex(tableName, fields, options);
      } catch (error) {
        // Ignore duplicate key errors
        if (error.message && error.message.includes('Duplicate key name')) {
          console.log(`Index already exists, skipping: ${options.name || fields.join('_')}`);
        } else {
          throw error;
        }
      }
    };

    await addIndexIfNotExists('inventory_stock_ledger', ['company_id'], {
      name: 'idx_stock_ledger_company_id'
    });
    await addIndexIfNotExists('inventory_stock_ledger', ['plant_id'], {
      name: 'idx_stock_ledger_plant_id'
    });
    await addIndexIfNotExists('inventory_stock_ledger', ['material_id'], {
      name: 'idx_stock_ledger_material_id'
    });
    await addIndexIfNotExists('inventory_stock_ledger', ['transaction_date'], {
      name: 'idx_stock_ledger_transaction_date'
    });
    await addIndexIfNotExists('inventory_stock_ledger', ['transaction_type'], {
      name: 'idx_stock_ledger_transaction_type'
    });
    await addIndexIfNotExists('inventory_stock_ledger', ['reference_type', 'reference_id'], {
      name: 'idx_stock_ledger_ref_type_id'
    });
    await addIndexIfNotExists('inventory_stock_ledger', ['batch_number'], {
      name: 'idx_stock_ledger_batch_number'
    });
    await addIndexIfNotExists('inventory_stock_ledger', ['lot_number'], {
      name: 'idx_stock_ledger_lot_number'
    });
    await addIndexIfNotExists('inventory_stock_ledger', ['storage_location'], {
      name: 'idx_stock_ledger_storage_location'
    });
    await addIndexIfNotExists('inventory_stock_ledger', ['company_id', 'plant_id', 'material_id', 'transaction_date'], {
      name: 'idx_stock_ledger_comp_plant_mat_date'
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('inventory_stock_ledger');
  }
};

