<?php

namespace App\Models;

use CodeIgniter\Model;

/**
 * Model untuk tb_pembelian_bahan (HEADER transaksi).
 * Detail item ada di DetailPembelianBahanModel.
 */
class PembelianBahanModel extends Model
{
    protected $table      = 'tb_pembelian_bahan';
    protected $primaryKey = 'id_pembelian_bahan';
    protected $returnType = 'array';

    protected $useSoftDeletes = true;
    protected $deletedField   = 'deleted_at';

    protected $useTimestamps = true;
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';

    protected $allowedFields = [
        'tanggal_pembelian_bahan',
        'supplier_pembelian',
        'tempat_pembelian_bahan',
        'foto_nota_pembelian_bahan',
        'catatan_pembelian',
        'total_pembelian_bahan',
    ];

    // ── Ambil semua header + detail ──────────────────────────────────────────
    public function getAll(): array
    {
        // Ambil semua header (tanpa alias tabel di from agar CI4 tidak bingung)
        $headers = $this->db
            ->query("
                SELECT *
                FROM tb_pembelian_bahan
                WHERE deleted_at IS NULL
                ORDER BY tanggal_pembelian_bahan DESC, id_pembelian_bahan DESC
            ")
            ->getResultArray();

        if (empty($headers)) return [];

        // Ambil semua detail + nama bahan sekaligus
        $ids        = implode(',', array_map('intval', array_column($headers, 'id_pembelian_bahan')));
        $details    = $this->db
            ->query("
                SELECT d.*, b.nama_bahan, b.satuan_bahan
                FROM tb_detail_pembelian_bahan d
                LEFT JOIN tb_bahan b ON b.id_bahan = d.id_bahan
                WHERE d.id_pembelian_bahan IN ({$ids})
            ")
            ->getResultArray();

        // Kelompokkan detail per id_pembelian_bahan
        $detailMap = [];
        foreach ($details as $d) {
            $detailMap[(int)$d['id_pembelian_bahan']][] = $d;
        }

        // Embed detail ke header
        foreach ($headers as &$h) {
            $h['detail'] = $detailMap[(int)$h['id_pembelian_bahan']] ?? [];
        }

        return $headers;
    }

    // ── Summary stats ─────────────────────────────────────────────────────────
    public function getSummary(): array
    {
        $db = $this->db;

        $totals = $db->table('tb_pembelian_bahan pb')
            ->select('
                COUNT(DISTINCT pb.id_pembelian_bahan) AS total_transaksi,
                COALESCE(SUM(pb.total_pembelian_bahan), 0) AS total_nilai,
                COALESCE(SUM(d.jumlah_bahan), 0) AS total_item
            ')
            ->join('tb_detail_pembelian_bahan d', 'd.id_pembelian_bahan = pb.id_pembelian_bahan', 'left')
            ->where('pb.deleted_at IS NULL', null, false)
            ->get()
            ->getRowArray();

        $bulan = $db->table('tb_pembelian_bahan')
            ->selectSum('total_pembelian_bahan', 'nilai_bulan_ini')
            ->where('deleted_at IS NULL', null, false)
            ->where('YEAR(tanggal_pembelian_bahan) = YEAR(NOW())', null, false)
            ->where('MONTH(tanggal_pembelian_bahan) = MONTH(NOW())', null, false)
            ->get()
            ->getRowArray();

        return [
            'total_transaksi' => (int)   ($totals['total_transaksi'] ?? 0),
            'total_nilai'     => (float) ($totals['total_nilai']     ?? 0),
            'total_item'      => (float) ($totals['total_item']      ?? 0),
            'nilai_bulan_ini' => (float) ($bulan['nilai_bulan_ini']  ?? 0),
        ];
    }

    // ── Total nilai seluruh pembelian (dipakai dari halaman bahan) ────────────
    public function getTotalNilai(): float
    {
        $result = $this->db->table('tb_pembelian_bahan')
            ->selectSum('total_pembelian_bahan')
            ->where('deleted_at IS NULL', null, false)
            ->get()
            ->getRowArray();
        return (float) ($result['total_pembelian_bahan'] ?? 0);
    }
}