'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('machine_history_card', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false
      },
      company_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'company',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT',
        comment: 'Company this history card belongs to'
      },
      plant_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'plant',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT',
        comment: 'Plant where the machine is located'
      },
      fiscal_year_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'fiscal_year',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT',
        comment: 'Fiscal year for this history card'
      },
      machine_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'machine',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'RESTRICT',
        comment: 'Machine this history card is for'
      },
      machine_number: {
        type: Sequelize.STRING(50),
        allowNull: true,
        comment: 'Machine number/code'
      },
      machine_description: {
        type: Sequelize.STRING(200),
        allowNull: true,
        comment: 'Machine description/name'
      },
      date: {
        type: Sequelize.DATEONLY,
        allowNull: false,
        comment: 'Date of the problem/maintenance'
      },
      problem_details: {
        type: Sequelize.TEXT,
        allowNull: true,
        comment: 'Details of the problem'
      },
      idle_time_from: {
        type: Sequelize.TIME,
        allowNull: true,
        comment: 'Idle time start'
      },
      idle_time_to: {
        type: Sequelize.TIME,
        allowNull: true,
        comment: 'Idle time end'
      },
      total_idle_hours: {
        type: Sequelize.DECIMAL(10, 2),
        allowNull: true,
        comment: 'Total idle hours calculated'
      },
      action_taken: {
        type: Sequelize.TEXT,
        allowNull: true,
        comment: 'Action taken to resolve the problem'
      },
      spares_replaced: {
        type: Sequelize.TEXT,
        allowNull: true,
        comment: 'Details of spares replaced if any'
      },
      cost_if_known: {
        type: Sequelize.DECIMAL(18, 2),
        allowNull: true,
        comment: 'Cost of repair/maintenance if known'
      },
      signed_by: {
        type: Sequelize.STRING(100),
        allowNull: true,
        comment: 'Person who signed/approved the card'
      },
      remarks: {
        type: Sequelize.TEXT,
        allowNull: true,
        comment: 'Additional remarks'
      },
      status: {
        type: Sequelize.ENUM('OPEN', 'IN_PROGRESS', 'RESOLVED', 'CLOSED'),
        allowNull: false,
        defaultValue: 'OPEN',
        comment: 'Status of the history card'
      },
      created_by: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'user',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      updated_by: {
        type: Sequelize.INTEGER,
        allowNull: true,
        references: {
          model: 'user',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')
      }
    }, {
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci',
      comment: 'Machine History Card - Track machine problems and maintenance history'
    });

    // Add indexes
    await queryInterface.addIndex('machine_history_card', ['company_id'], {
      name: 'idx_machine_history_card_company_id'
    });

    await queryInterface.addIndex('machine_history_card', ['plant_id'], {
      name: 'idx_machine_history_card_plant_id'
    });

    await queryInterface.addIndex('machine_history_card', ['machine_id'], {
      name: 'idx_machine_history_card_machine_id'
    });

    await queryInterface.addIndex('machine_history_card', ['date'], {
      name: 'idx_machine_history_card_date'
    });

    await queryInterface.addIndex('machine_history_card', ['status'], {
      name: 'idx_machine_history_card_status'
    });
  },

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