<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Model_transaksi extends CI_Model
{
    /* ============================================================
     * Util: normalisasi tanggal ke Y-m-d
     * ============================================================ */
    private function toYmd($s)
    {
        $s = trim((string)$s);
        if ($s === '') return '';
        $dmy = DateTime::createFromFormat('d/m/Y', $s);
        if ($dmy && $dmy->format('d/m/Y') === $s) return $dmy->format('Y-m-d');
        $ymd = DateTime::createFromFormat('Y-m-d', $s);
        if ($ymd && $ymd->format('Y-m-d') === $s) return $s;
        $ts = strtotime($s);
        return $ts ? date('Y-m-d', $ts) : '';
    }

    /* ============================================================
     * Util: siapkan start & end (end boleh kosong)
     * ============================================================ */
    private function normalizeRange($startDate, $endDate)
    {
        $start = $this->toYmd($startDate);
        $end   = $this->toYmd($endDate);

        if ($start !== '' && $end === '') $end = $start;
        if ($start === '' && $end !== '') $start = $end;
        if ($start !== '' && $end !== '' && strtotime($start) > strtotime($end)) {
            $tmp = $start; $start = $end; $end = $tmp;
        }
        return [$start, $end];
    }

    /* ============================================================
     * PENJUALAN (Harian – by tgl_bayar)  ✅ FIX filter per-baris
     * ============================================================ */
    public function getLaporanHarian($startDate, $endDate, $iduser = 0)
{
    list($start, $end) = $this->normalizeRange($startDate, $endDate);

    $this->db->select('
        bid.id                       AS id,          
        i.id_invoice                 AS noid,     
        i.id_transaksi               AS id_transaksi, 
        i.id_konsumen,
        i.tgl_trx,
        bid.tgl_bayar,
        bid.jml_bayar,
        bid.id_sub_bayar,
        bid.lampiran,
        u.nama_lengkap              AS fo
    ');
    $this->db->from('invoice AS i');
    $this->db->join('bayar_invoice_detail AS bid', 'i.id_invoice = bid.id_invoice', 'inner');
    $this->db->join('tb_users AS u', 'bid.id_user = u.id_user', 'left');

    // filter ke user pembuat baris pembayaran
    if (!empty($iduser)) {
        $this->db->where('bid.id_user', (int)$iduser);
    }

    if ($start !== '' && $end !== '') {
        $this->db->where('bid.tgl_bayar >=', $start);
        $this->db->where('bid.tgl_bayar <=', $end);
    }

    $this->db->order_by('bid.tgl_bayar', 'DESC');
    $q = $this->db->get();
    return $q ? $q->result_array() : [];
}


    /* ============================================================
     * PENJUALAN (Bulanan)  ✅ FIX filter per-baris
     * ============================================================ */
    public function getLaporanBulanan($bulan, $tahun, $iduser = 0)
{
    $this->db->select('
        bid.id                       AS id,          
        i.id_invoice                 AS noid,       
        i.id_transaksi               AS id_transaksi, 
        i.id_konsumen,
        bid.tgl_bayar,
        bid.jml_bayar,
        bid.id_sub_bayar,
        bid.lampiran,
        u.nama_lengkap              AS fo
    ');
    $this->db->from('invoice AS i');
    $this->db->join('bayar_invoice_detail AS bid', 'i.id_invoice = bid.id_invoice', 'inner');
    $this->db->join('tb_users AS u', 'bid.id_user = u.id_user', 'left');

    if (!empty($iduser)) {
        $this->db->where('bid.id_user', (int)$iduser);
    }

    $this->db->where('MONTH(bid.tgl_bayar)', (int)$bulan);
    $this->db->where('YEAR(bid.tgl_bayar)',  (int)$tahun);
    $this->db->where('i.status', 'simpan');

    $q = $this->db->get();
    return ($q && $q->num_rows() > 0) ? $q->result_array() : [];
}



    /* ============================================================
     * PENGELUARAN (Harian – kas_keluar.tanggal)
     * ============================================================ */
    public function getPengeluaranHarian($startDate, $endDate, $iduser = 0)
    {
        list($start, $end) = $this->normalizeRange($startDate, $endDate);

        $this->db->select('
        kas_keluar.tanggal        AS tgl_bayar,
        kas_keluar.jumlah         AS jml_bayar,
        kas_keluar.id_akun        AS id_bayar,
        kas_keluar.id,
        kas_keluar.no_transaksi   AS no_transaksi,
        kas_keluar.keterangan     AS keterangan,
        tb_users.nama_lengkap     AS fo
        ');
        $this->db->from('kas_keluar');
        $this->db->join('tb_users', 'kas_keluar.id_user = tb_users.id_user', 'left');

        if (!empty($iduser)) {
            $this->db->where('kas_keluar.id_user', (int)$iduser);
        }
        if ($start !== '' && $end !== '') {
            $this->db->where('kas_keluar.tanggal >=', $start);
            $this->db->where('kas_keluar.tanggal <=', $end);
        }

        $this->db->order_by('kas_keluar.tanggal', 'DESC');
        $this->db->order_by('kas_keluar.id', 'DESC');

        $q = $this->db->get();
        return $q ? $q->result_array() : [];
    }

    /* ============================================================
     * PENGELUARAN (Bulanan)
     * ============================================================ */
    public function getPengeluaranBulanan($bulan, $tahun, $iduser = 0)
    {
        $this->db->select('
            kas_keluar.id,
            kas_keluar.tanggal       AS tgl_bayar,
            kas_keluar.jumlah        AS jml_bayar,
            kas_keluar.id_akun       AS id_bayar,
            kas_keluar.keterangan,
            tb_users.nama_lengkap    AS fo
        ');
        $this->db->from('kas_keluar');
        $this->db->join('tb_users', 'kas_keluar.id_user = tb_users.id_user', 'left');

        if (!empty($iduser)) {
            $this->db->where('kas_keluar.id_user', (int)$iduser);
        }
        $this->db->where('MONTH(kas_keluar.tanggal)', (int)$bulan);
        $this->db->where('YEAR(kas_keluar.tanggal)',  (int)$tahun);

        $q = $this->db->get();
        return ($q && $q->num_rows() > 0) ? $q->result_array() : [];
    }

    /* ============================================================
     * PEMBELIAN (Harian – by bayar_pembelian.tgl_bayar)
     * ============================================================ */
    public function getPembelianHarian($startDate, $endDate, $iduser = 0)
    {
        list($start, $end) = $this->normalizeRange($startDate, $endDate);

        $this->db->select('
        bayar_pembelian.tgl_bayar,
        bayar_pembelian.jml_bayar,
        bayar_pembelian.id_bayar,
        pembelian.id_pembelian     AS id,
        pembelian.no_transaksi     AS no_transaksi,
        pembelian.keterangan,
        tb_users.nama_lengkap      AS fo
        ');
        $this->db->from('pembelian');
        $this->db->join('bayar_pembelian', 'pembelian.id_pembelian = bayar_pembelian.id_pembelian');
        $this->db->join('tb_users', 'bayar_pembelian.id_user = tb_users.id_user', 'left');

        if (!empty($iduser)) {
            $this->db->where('bayar_pembelian.id_user', (int)$iduser);
        }
        if ($start !== '' && $end !== '') {
            $this->db->where('bayar_pembelian.tgl_bayar >=', $start);
            $this->db->where('bayar_pembelian.tgl_bayar <=', $end);
        }

        $this->db->order_by('bayar_pembelian.tgl_bayar', 'DESC');
        $q = $this->db->get();
        return ($q && $q->num_rows() > 0) ? $q->result_array() : [];
    }

    /* ============================================================
     * PEMBELIAN (Bulanan)
     * ============================================================ */
    public function getPembelianBulanan($bulan, $tahun, $iduser = 0)
    {
        $this->db->select('
            pembelian.id_pembelian   AS id,
            bayar_pembelian.tgl_bayar,
            bayar_pembelian.jml_bayar,
            bayar_pembelian.id_bayar,
            pembelian.keterangan,
            tb_users.nama_lengkap    AS fo
        ');
        $this->db->from('pembelian');
        $this->db->join('bayar_pembelian', 'pembelian.id_pembelian = bayar_pembelian.id_pembelian');
        $this->db->join('tb_users', 'bayar_pembelian.id_user = tb_users.id_user', 'left');

        if (!empty($iduser)) {
            $this->db->where('bayar_pembelian.id_user', (int)$iduser);
        }
        $this->db->where('MONTH(bayar_pembelian.tgl_bayar)', (int)$bulan);
        $this->db->where('YEAR(bayar_pembelian.tgl_bayar)',  (int)$tahun);

        $q = $this->db->get();
        return ($q && $q->num_rows() > 0) ? $q->result_array() : [];
    }

    /* ============================================================
     * LAPORAN BULANAN (Rekap per tanggal)
     * ============================================================ */
    public function get_laporan($bulan, $tahun, $iduser = 0)
    {
        // Rekap PENJUALAN per tanggal (sum dari bayar_invoice_detail)
        $this->db->select("DATE(bd.tgl_bayar) AS tanggal", false);
        $this->db->select("SUM(bd.jml_bayar) AS TOTAL", false);
        $this->db->select("SUM(CASE WHEN bd.id_bayar = 1 THEN bd.jml_bayar ELSE 0 END) AS TUNAI", false);
        $this->db->select("SUM(CASE WHEN bd.id_bayar = 2 THEN bd.jml_bayar ELSE 0 END) AS TRANSFER", false);

        // Subquery PENGELUARAN per tanggal (ikut filter user jika ada)
        $pengeluaranSql = "
            (SELECT SUM(kk.jumlah)
             FROM kas_keluar kk
             WHERE DATE(kk.tanggal) = DATE(bd.tgl_bayar)
               " . (!empty($iduser) ? "AND kk.id_user = " . (int)$iduser : "") . "
             GROUP BY DATE(kk.tanggal)
            ) AS PENGELUARAN
        ";
        $this->db->select($pengeluaranSql, false);

        // Subquery PEMBELIAN per tanggal (ikut filter user jika ada)
        $pembelianSql = "
            (SELECT SUM(bp.jml_bayar)
             FROM bayar_pembelian bp
             WHERE DATE(bp.tgl_bayar) = DATE(bd.tgl_bayar)
               " . (!empty($iduser) ? "AND bp.id_user = " . (int)$iduser : "") . "
             GROUP BY DATE(bp.tgl_bayar)
            ) AS PEMBELIAN
        ";
        $this->db->select($pembelianSql, false);

        $this->db->from('bayar_invoice_detail bd');
        $this->db->where('MONTH(bd.tgl_bayar)', (int)$bulan);
        $this->db->where('YEAR(bd.tgl_bayar)',  (int)$tahun);

        if (!empty($iduser)) {
            // ✅ penting: rekap penjualan difilter pembuat baris pembayaran
            $this->db->where('bd.id_user', (int)$iduser);
        }

        $this->db->group_by('DATE(bd.tgl_bayar)');
        $this->db->order_by('tanggal', 'ASC');

        $q = $this->db->get();
        return $q ? $q->result() : [];
    }

}
