<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class BackfillLegacyOrderProductionSchedules extends Migration
{
    public function up()
    {
        if (! $this->db->tableExists('order_production_schedules')
            || ! $this->db->tableExists('orders')
            || ! $this->db->tableExists('order_items')) {
            return;
        }

        // Existing PO data predates the batch table. Give each legacy order
        // one batch in its former target month so dashboard and capacity data
        // remain complete; edited orders will be split automatically later.
        $this->db->query("INSERT INTO order_production_schedules
            (id_order, scheduled_month, quantity, created_at, updated_at)
            SELECT o.id_order,
                   DATE_FORMAT(o.target_date, '%Y-%m-01'),
                   SUM(oi.qty),
                   NOW(),
                   NOW()
            FROM orders o
            INNER JOIN order_items oi ON oi.id_order = o.id_order
            WHERE o.order_type = 'po'
              AND o.order_status != 'cancelled'
              AND o.target_date IS NOT NULL
              AND NOT EXISTS (
                  SELECT 1
                  FROM order_production_schedules ops
                  WHERE ops.id_order = o.id_order
              )
            GROUP BY o.id_order, DATE_FORMAT(o.target_date, '%Y-%m-01')");
    }

    public function down()
    {
        // Data batch lama tidak dihapus saat rollback agar histori kapasitas
        // yang sudah terbentuk tidak hilang.
    }
}
