<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        // MariaDB/MySQL: gunakan CREATE OR REPLACE VIEW
        DB::statement("
            CREATE OR REPLACE VIEW vw_sales AS
            SELECT
                o.id,
                o.invoice_number,
                o.customer_name,
                o.customer_phone,
                o.total_price,
                o.paid_amount,
                o.change_amount,
                o.payment_status,
                o.production_status,
                COALESCE(SUM(oi.qty), 0) AS qty,
                DATE(o.created_at) AS order_date,
                o.created_at,
                o.designer_id,
                o.cashier_id,
                o.operator_id
            FROM orders o
            LEFT JOIN order_items oi ON oi.order_id = o.id
            GROUP BY
                o.id,
                o.invoice_number,
                o.customer_name,
                o.customer_phone,
                o.total_price,
                o.paid_amount,
                o.change_amount,
                o.payment_status,
                o.production_status,
                DATE(o.created_at),
                o.created_at,
                o.designer_id,
                o.cashier_id,
                o.operator_id
        ");
    }

    public function down(): void
    {
        DB::statement('DROP VIEW IF EXISTS vw_sales');
    }
};