'use strict';

/**
 * Material Master Sample Data Seeder
 * 
 * This seeder creates sample data for Material Master tabs to demonstrate ERP functionality.
 * It includes comprehensive examples for:
 * - Plant Data (material_plant table)
 * - Planning/MRP Data (material_plant table - MRP fields)
 * - Valuation Data (material_valuation table)
 * - Supplier Data (material_supplier table)
 * 
 * IMPORTANT: Run this seeder AFTER creating at least one Material, Plant, Currency, and Vendor.
 * 
 * ERP Column Explanations:
 * ========================
 * 
 * PLANT DATA TAB:
 * ---------------
 * - plant_id: Which plant/factory this material is available in
 * - opening_balance: Initial stock quantity when material is first set up in plant
 * - opening_balance_unit_cost: Cost per unit of opening stock (for inventory valuation)
 * - opening_balance_date: Date when opening balance was recorded
 * - opening_balance_uom_id: Unit of measure for opening balance (e.g., KG, PC, M)
 * - storage_location: Physical location code within plant (e.g., "WH-01", "STORE-A")
 * - storage_bin: Specific bin/shelf location (e.g., "A-01-05" = Aisle 1, Rack 1, Shelf 5)
 * - status: ACTIVE = material available, INACTIVE = temporarily unavailable, BLOCKED = not allowed
 * - effective_from/effective_to: Date range when this plant data is valid
 * 
 * PLANNING (MRP) TAB:
 * -------------------
 * - mrp_type: How material requirements are calculated
 *   * ND = No Planning (manual ordering)
 *   * VB = Manual Reorder Point (reorder when stock falls below reorder_point)
 *   * PD = MRP (Material Requirements Planning - automatic calculation from production orders)
 *   * PK = Consumption Based (reorder based on past consumption patterns)
 *   * VM = Forecast Based (reorder based on sales forecasts)
 * 
 * - mrp_controller: Person/team responsible for MRP planning (e.g., "PLN", "MGR")
 * - reorder_point: Minimum stock level - when reached, trigger purchase/production
 * - safety_stock: Buffer stock to handle unexpected demand or supply delays
 * - max_stock: Maximum stock level to prevent overstocking
 * - procurement_type: How material is obtained
 *   * E = External (purchase from vendors)
 *   * F = In-house (produce internally)
 *   * X = Both (can purchase or produce)
 * 
 * - special_procurement: Special procurement scenarios (e.g., "10" = subcontracting)
 * 
 * VALUATION TAB:
 * --------------
 * - plant_id: Plant where this valuation applies
 * - price_control: How material cost is calculated
 *   * S = Standard Price (fixed cost, used for finished goods)
 *   * V = Moving Average Price (average cost recalculated after each purchase)
 * 
 * - standard_price: Fixed standard cost per unit (used when price_control = S)
 * - moving_average_price: Current average cost (used when price_control = V)
 * - currency_id: Currency for pricing (e.g., INR, USD)
 * - valuation_area: Accounting area for valuation (usually same as plant code)
 * - valuation_type: S=Standard, V=Moving Average, T=Periodic Unit Price
 * - valuation_class: Material valuation class (for cost accounting)
 * - costing_lot_size: Quantity used for cost calculation (e.g., per 1000 units)
 * - original_price: Original purchase/manufacturing cost
 * - last_price_change_date: When price was last updated
 * 
 * SUPPLIERS TAB:
 * --------------
 * - vendor_id: Supplier/vendor who can supply this material
 * - vendor_material_number: Material code as per vendor's catalog
 * - vendor_material_description: Description from vendor
 * - is_preferred: TRUE = primary supplier (used first in purchase orders)
 * - lead_time_days: Days from order to delivery (for MRP planning)
 * - minimum_order_quantity: Minimum qty vendor requires per order
 * - standard_price: Price quoted by this vendor
 * - currency_id: Currency for vendor pricing
 * - validity_from/validity_to: Date range when vendor quote is valid
 * - status: ACTIVE = can order from this vendor, INACTIVE/BLOCKED = cannot order
 * - remarks: Notes about vendor (e.g., "Good quality", "Long lead time")
 */

module.exports = {
  async up(queryInterface, Sequelize) {
    // Get existing IDs (assuming sample data exists)
    const [materials] = await queryInterface.sequelize.query(`
      SELECT id, material_number, description FROM material 
      WHERE status = 'ACTIVE' 
      ORDER BY id 
      LIMIT 5
    `);

    const [plants] = await queryInterface.sequelize.query(`
      SELECT id, code FROM plant 
      WHERE status = 'ACTIVE' 
      ORDER BY id 
      LIMIT 2
    `);

    const [currencies] = await queryInterface.sequelize.query(`
      SELECT id, currency_code FROM currency 
      ORDER BY id 
      LIMIT 1
    `);

    const [vendors] = await queryInterface.sequelize.query(`
      SELECT id, bp_number, name FROM business_partner 
      WHERE bp_type = 'VENDOR' AND status = 'ACTIVE' 
      ORDER BY id 
      LIMIT 3
    `);

    const [uoms] = await queryInterface.sequelize.query(`
      SELECT id, uom_code FROM uom 
      ORDER BY id 
      LIMIT 3
    `);

    if (materials.length === 0 || plants.length === 0) {
      console.log('⚠️  No materials or plants found. Please create at least one material and plant first.');
      return;
    }

    const material = materials[0];
    const plant1 = plants[0];
    const plant2 = plants[1] || plants[0];
    const currency = currencies[0] || { id: 1 };
    const vendor1 = vendors[0] || null;
    const vendor2 = vendors[1] || vendors[0] || null;
    const uom1 = uoms[0] || { id: 1 };
    const uom2 = uoms[1] || uoms[0] || { id: 1 };

    const now = new Date();
    const userId = 1; // Default user ID

    // ============================================
    // 1. PLANT DATA (material_plant)
    // ============================================
    console.log('🌱 Seeding Plant Data...');
    
    // Check if records already exist
    const [existingPlantData] = await queryInterface.sequelize.query(`
      SELECT material_id, plant_id FROM material_plant 
      WHERE material_id = ${material.id} AND plant_id IN (${plant1.id}${plant2 && plant2.id !== plant1.id ? `, ${plant2.id}` : ''})
    `);
    
    const existingCombinations = new Set(
      existingPlantData.map(row => `${row.material_id}-${row.plant_id}`)
    );
    
    const plantDataToInsert = [
      {
        material_id: material.id,
        plant_id: plant1.id,
        // Opening Balance
        opening_balance: 100.000,
        opening_balance_unit_cost: 150.50,
        opening_balance_date: '2024-01-01',
        opening_balance_uom_id: uom1.id,
        // Storage
        storage_location: 'WH-01',
        storage_bin: 'A-01-05',
        // MRP Settings
        mrp_type: 'PD', // MRP Planning
        mrp_controller: 'PLN',
        reorder_point: 50.000,
        safety_stock: 20.000,
        max_stock: 500.000,
        procurement_type: 'E', // External (Purchase)
        special_procurement: null,
        // Status
        status: 'ACTIVE',
        effective_from: '2024-01-01',
        effective_to: null,
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now,
      },
      // Second plant example (if exists)
      ...(plant2 && plant2.id !== plant1.id ? [{
        material_id: material.id,
        plant_id: plant2.id,
        opening_balance: 50.000,
        opening_balance_unit_cost: 145.00,
        opening_balance_date: '2024-01-01',
        opening_balance_uom_id: uom1.id,
        storage_location: 'WH-02',
        storage_bin: 'B-02-10',
        mrp_type: 'VB', // Manual Reorder Point
        mrp_controller: 'MGR',
        reorder_point: 25.000,
        safety_stock: 10.000,
        max_stock: 200.000,
        procurement_type: 'X', // Both (Purchase or Produce)
        special_procurement: null,
        status: 'ACTIVE',
        effective_from: '2024-01-01',
        effective_to: null,
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now,
      }] : []),
    ].filter(item => !existingCombinations.has(`${item.material_id}-${item.plant_id}`));
    
    if (plantDataToInsert.length > 0) {
      await queryInterface.bulkInsert('material_plant', plantDataToInsert);
      console.log(`   ✓ Inserted ${plantDataToInsert.length} plant data record(s)`);
    } else {
      console.log('   ⚠️  Plant data already exists, skipping...');
    }

    // ============================================
    // 2. VALUATION DATA (material_valuation)
    // ============================================
    console.log('💰 Seeding Valuation Data...');
    
    // Check if records already exist
    const [existingValuation] = await queryInterface.sequelize.query(`
      SELECT material_id, plant_id FROM material_valuation 
      WHERE material_id = ${material.id} AND plant_id IN (${plant1.id}${plant2 && plant2.id !== plant1.id ? `, ${plant2.id}` : ''})
    `);
    
    const existingValuationCombinations = new Set(
      existingValuation.map(row => `${row.material_id}-${row.plant_id}`)
    );
    
    const valuationDataToInsert = [
      {
        material_id: material.id,
        plant_id: plant1.id,
        valuation_area: plant1.code || 'PLANT1',
        valuation_type: 'S', // Standard
        price_control: 'S', // Standard Price
        standard_price: 150.5000,
        moving_average_price: 0.0000,
        currency_id: currency.id,
        valuation_class: 'MAT001',
        costing_lot_size: 1.000,
        original_price: 150.0000,
        last_price_change_date: '2024-01-15',
        status: 'ACTIVE',
        effective_from: '2024-01-01',
        effective_to: null,
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now,
      },
      // Second plant valuation (if exists)
      ...(plant2 && plant2.id !== plant1.id ? [{
        material_id: material.id,
        plant_id: plant2.id,
        valuation_area: plant2.code || 'PLANT2',
        valuation_type: 'V', // Moving Average
        price_control: 'V', // Moving Average Price
        standard_price: 0.0000,
        moving_average_price: 145.2500,
        currency_id: currency.id,
        valuation_class: 'MAT001',
        costing_lot_size: 1.000,
        original_price: 145.0000,
        last_price_change_date: '2024-01-10',
        status: 'ACTIVE',
        effective_from: '2024-01-01',
        effective_to: null,
        created_by: userId,
        updated_by: userId,
        created_at: now,
        updated_at: now,
      }] : []),
    ].filter(item => !existingValuationCombinations.has(`${item.material_id}-${item.plant_id}`));
    
    if (valuationDataToInsert.length > 0) {
      await queryInterface.bulkInsert('material_valuation', valuationDataToInsert);
      console.log(`   ✓ Inserted ${valuationDataToInsert.length} valuation record(s)`);
    } else {
      console.log('   ⚠️  Valuation data already exists, skipping...');
    }

    // ============================================
    // 3. SUPPLIER DATA (material_supplier)
    // ============================================
    console.log('🏭 Seeding Supplier Data...');
    
    if (vendor1) {
      // Check if records already exist
      const vendorIds = [vendor1.id, ...(vendor2 && vendor2.id !== vendor1.id ? [vendor2.id] : [])];
      const [existingSuppliers] = await queryInterface.sequelize.query(`
        SELECT material_id, vendor_id FROM material_supplier 
        WHERE material_id = ${material.id} AND vendor_id IN (${vendorIds.join(',')})
      `);
      
      const existingSupplierCombinations = new Set(
        existingSuppliers.map(row => `${row.material_id}-${row.vendor_id}`)
      );
      
      const supplierDataToInsert = [
        {
          material_id: material.id,
          vendor_id: vendor1.id,
          vendor_material_number: `${material.material_number}-V1`,
          vendor_material_description: `${material.description} - Vendor Spec`,
          is_preferred: true, // Primary supplier
          lead_time_days: 15,
          minimum_order_quantity: 100.000,
          standard_price: 148.00,
          currency_id: currency.id,
          validity_from: '2024-01-01',
          validity_to: '2024-12-31',
          status: 'ACTIVE',
          remarks: 'Preferred supplier - Good quality, reliable delivery',
          created_at: now,
          updated_at: now,
        },
        // Second vendor (if exists)
        ...(vendor2 && vendor2.id !== vendor1.id ? [{
          material_id: material.id,
          vendor_id: vendor2.id,
          vendor_material_number: `${material.material_number}-V2`,
          vendor_material_description: `${material.description} - Alternate Vendor`,
          is_preferred: false,
          lead_time_days: 20,
          minimum_order_quantity: 200.000,
          standard_price: 147.50,
          currency_id: currency.id,
          validity_from: '2024-01-01',
          validity_to: '2024-12-31',
          status: 'ACTIVE',
          remarks: 'Backup supplier - Lower price but longer lead time',
          created_at: now,
          updated_at: now,
        }] : []),
      ].filter(item => !existingSupplierCombinations.has(`${item.material_id}-${item.vendor_id}`));
      
      if (supplierDataToInsert.length > 0) {
        await queryInterface.bulkInsert('material_supplier', supplierDataToInsert);
        console.log(`   ✓ Inserted ${supplierDataToInsert.length} supplier record(s)`);
      } else {
        console.log('   ⚠️  Supplier data already exists, skipping...');
      }
    } else {
      console.log('   ⚠️  No vendors found, skipping supplier data...');
    }

    console.log('✅ Material Master sample data seeded successfully!');
    console.log(`
    📋 Sample Data Summary:
    ======================
    - Material: ${material.material_number} - ${material.description}
    - Plant Data: ${plant1.code || 'Plant 1'} (and ${plant2 && plant2.id !== plant1.id ? plant2.code || 'Plant 2' : 'same plant'})
    - Valuation: Standard Price (Plant 1), Moving Average (Plant 2)
    - Suppliers: ${vendor1 ? '2 vendors configured' : 'No vendors (create vendors first)'}
    
    💡 Next Steps:
    - Open Material Master form
    - Navigate to Plant Data, Planning (MRP), Valuation, and Suppliers tabs
    - Review the sample data to understand each field's purpose
    `);
  },

  async down(queryInterface, Sequelize) {
    // Remove sample data (be careful - this removes ALL material_plant, material_valuation, material_supplier records)
    console.log('⚠️  Removing Material Master sample data...');
    
    await queryInterface.bulkDelete('material_supplier', null, {});
    await queryInterface.bulkDelete('material_valuation', null, {});
    await queryInterface.bulkDelete('material_plant', null, {});
    
    console.log('✅ Material Master sample data removed.');
  }
};

