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

class Report_model extends CI_Model
{
	//data akun
	public function getAccountData()
	{
		return $this->db->get('akun')->result_array();
	}

	public function set_account_group_id($id){
		$this->db->where_in('kelompok_akun.kel_akun', $id);
	}

	//ditampilkan di buku besar
	public function getData($idAkun = null, $tglAwal = null, $tglAkhir = null)
{
    $this->db->select('
        jurnal.id_jurnal, jurnal.no_trans, jurnal.tgl, jurnal.keterangan, 
        jurnal_detail.id_perkiraan, jurnal_detail.nilai, jurnal_detail.id_akun,
        akun.nama, akun.noakun, perkiraan.nama AS perkiraan,
        kelompok_akun.kel_akun
    ');
    $this->db->from('jurnal_detail');
    $this->db->join('jurnal', 'jurnal_detail.id_jurnal = jurnal.id_jurnal');
    $this->db->join('akun', 'jurnal_detail.id_akun = akun.id_akun');
    $this->db->join('kelompok_akun', 'akun.id_kelompok_akun = kelompok_akun.id_kelompok_akun');
    $this->db->join('perkiraan', 'jurnal_detail.id_perkiraan = perkiraan.id_perkiraan');

    // optional filter (supaya aman di PHP8 + bisa dipakai tanpa "where side-effect")
    if ($idAkun !== null && $idAkun !== '') {
        $this->db->where('jurnal_detail.id_akun', (int)$idAkun);
    }
    if (!empty($tglAwal)) {
        $this->db->where('jurnal.tgl >=', $tglAwal);
    }
    if (!empty($tglAkhir)) {
        $this->db->where('jurnal.tgl <=', $tglAkhir);
    }

    $this->db->order_by('jurnal.tgl', 'ASC');
    return $this->db->get()->result_array();
}


	public function getAkunId(){
		$this->db->select('
			jurnal_detail.id_akun, 
			akun.nama,
			kelompok_akun.kel_akun');
		$this->db->from('jurnal_detail');
		$this->db->join('jurnal', 'jurnal_detail.id_jurnal = jurnal.id_jurnal');
		$this->db->join('akun', 'jurnal_detail.id_akun = akun.id_akun');
		$this->db->join('kelompok_akun', 'akun.id_kelompok_akun = kelompok_akun.id_kelompok_akun');
		$this->db->group_by('jurnal_detail.id_akun');
		$this->db->order_by('jurnal_detail.id_akun', 'ASC');
		$query = $this->db->get()->result_array();
		return $query;
	}

	public function getTrialBalance($id_akun, $tglAwal,$tglAkhir){
		$this->db->select('
			jurnal_detail.*, 
			jurnal.*, 
			akun.nama, akun.noakun, 
			kelompok_akun.kel_akun, 
			perkiraan.nama AS perkiraan');
		$this->db->from('jurnal_detail');
		$this->db->join('jurnal', 'jurnal_detail.id_jurnal = jurnal.id_jurnal');
		$this->db->join('akun', 'jurnal_detail.id_akun = akun.id_akun');
		$this->db->join('kelompok_akun', 'akun.id_kelompok_akun = kelompok_akun.id_kelompok_akun');
		$this->db->join('perkiraan', 'jurnal_detail.id_perkiraan = perkiraan.id_perkiraan');
		$array = array('jurnal_detail.id_akun' => $id_akun, 'jurnal.tgl >=' => $tglAwal, 'jurnal.tgl <=' => $tglAkhir);
		$this->db->where($array);
		$this->db->order_by('jurnal_detail.id_akun', 'ASC');
		$query = $this->db->get()->result_array();

		return $query;
	}

	public function getSaldoTrialBalance($id_akun, $tglAwal,$tglAkhir){
		$this->db->select('
			jurnal_detail.id_perkiraan, jurnal_detail.nilai');
		$this->db->from('jurnal_detail');
		$this->db->join('jurnal', 'jurnal_detail.id_jurnal = jurnal.id_jurnal');
		$array = array('jurnal_detail.id_akun' => $id_akun, 'jurnal.tgl >=' => $tglAwal, 'jurnal.tgl <=' => $tglAkhir);
		$this->db->where($array);
		$this->db->order_by('jurnal_detail.id_akun', 'ASC');
		$query = $this->db->get()->result_array();

		return $query;
	}

	public function get_all_data()
	{
		$this->db->select('
			akun.id_akun, akun.noakun, akun.id_kelompok_akun, akun.nama, akun.saldoawal, 
			kelompok_akun.nama, kelompok_akun.kel_akun');
		$this->db->from('akun');
		$this->db->join('kelompok_akun', 'akun.id_kelompok_akun = kelompok_akun.id_kelompok_akun');
		$query = $this->db->get()->result_array();
		return $query;
	}

	public function insert_kategori()
	{
		$data = [
			"id_kategori" 						=> "",
			"nama_kategori" 			=> $this->input->post('nama-kategori', true),
			"id_kelompok_aktivitas" 	=> $this->input->post('tipe-aktivitas', true),
			"keterangan" 				=> $this->input->post('keterangan', true),
			"date" 						=> date('Y-m-d H:i:s') ,
        	"id_user" 					=> $this->input->post('idUser', true)
		];

		$this->db->insert('kategori', $data);
		$kategoriId 	= $this->db->insert_id();
		
		$akunSumber 	= $this->input->post('akun-sumber[]', true);

		$result = array();
			   foreach($akunSumber AS $key => $val){
				     $result[] = array(
					     	'id_akun_sumber' 	=> '',
					     	'id_kategori' 		=> $kategoriId,
					        'id_akun'			=>$_POST['akun-sumber'][$key],
				      );
			    }

		$this->db->insert_batch('akun_sumber', $result);   
	}

	public function updateKategori()
	{
		$id = $this->input->post('id', true);

		$data = [
			"nama_kategori" 			=> $this->input->post('nama-kategori', true),
			"id_kelompok_aktivitas" 	=> $this->input->post('tipe-aktivitas', true),
			"keterangan" 				=> $this->input->post('keterangan', true),
			"date" 						=> date('Y-m-d H:i:s'),
        	"id_user" 					=> $this->input->post('idUser', true) 
		];
		$this->db->where('id_kategori', $id);
		$this->db->update('kategori', $data);

		$this->db->delete('akun_sumber', array('id_kategori' => $id));

		$akunSumber 	= $this->input->post('akun-sumber[]', true);

		$result = array();
		   foreach($akunSumber AS $key => $val){
			     $result[] = array(
				     	'id_akun_sumber' 	=> '',
				     	'id_kategori' 		=> $id,
				        'id_akun'			=> $_POST['akun-sumber'][$key],
			      );
		    }
		$this->db->insert_batch('akun_sumber', $result);   
	}

	public function delete_kategori($id)
	{
		$this->db->delete('akun_sumber', array('id_kategori' => $id));
		$this->db->delete('kategori', array('id_kategori' => $id));
	}

	public function aktivitas_arus_kas()
	{
		$this->db->select('
			kategori.*,
			akun_sumber.*');
		$this->db->from('akun_sumber');
		$this->db->join('kategori', 'akun_sumber.id_kategori = kategori.id_kategori');
		$query = $this->db->get()->result_array();

		return $query;
	}

	public function kel_aktivitas()
	{
		return $this->db->get('kelompok_aktivitas')->result_array();
	}

	public function set_kategori_by_tipe_aktivitas($id){
		$this->db->where_in('id_kelompok_aktivitas', $id);
	}

	public function kategori_arus_kas()
	{
		return $this->db->get('kategori')->result_array();
	}

	public function getDataKas()
	{
		$this->db->select('
			jurnal.no_trans, jurnal.tgl,
			jurnal_detail.id_perkiraan, jurnal_detail.nilai, jurnal_detail.tipe_kas,
			akun.nama, akun.noakun, 
			perkiraan.nama AS perkiraan,
			kelompok_akun.kel_akun, 
			akun_sumber.id_akun, 
			kategori.id_kategori, kategori.nama_kategori, kategori.id_kelompok_aktivitas');
		$this->db->from('jurnal_detail');
		$this->db->join('jurnal', 'jurnal_detail.id_jurnal = jurnal.id_jurnal');
		$this->db->join('akun', 'jurnal_detail.id_akun = akun.id_akun');
		$this->db->join('akun_sumber', 'jurnal_detail.id_akun = akun_sumber.id_akun');
		$this->db->join('kategori', 'akun_sumber.id_kategori = kategori.id_kategori');
		$this->db->join('kelompok_akun', 'akun.id_kelompok_akun = kelompok_akun.id_kelompok_akun');
		$this->db->join('perkiraan', 'jurnal_detail.id_perkiraan = perkiraan.id_perkiraan');
		$this->db->order_by('jurnal.tgl', 'ASC');
		$query = $this->db->get()->result_array();

		return $query;
	}

	/**
	 * Saldo kas/bank pada suatu tanggal berdasarkan akun yang terdaftar pada
	 * set_account_kas. Nilai saldo awal akun ikut diperhitungkan.
	 */
	public function getCashBalanceAt($date)
	{
		$cashAccounts = $this->db
			->select('akun.id_akun, akun.id_perkiraan, akun.saldo_awal')
			->from('set_account_kas')
			->join('akun', 'akun.id_akun = set_account_kas.id_akun')
			->get()
			->result_array();

		if (empty($cashAccounts)) {
			return 0;
		}

		$cashIds = array_map('intval', array_column($cashAccounts, 'id_akun'));
		$balance = 0;
		foreach ($cashAccounts as $account) {
			$opening = (int) $account['saldo_awal'];
			$balance += ((int) $account['id_perkiraan'] === 1) ? $opening : -$opening;
		}

		$this->db->select('COALESCE(SUM(CASE WHEN jurnal_detail.id_perkiraan = 1 THEN jurnal_detail.nilai ELSE -jurnal_detail.nilai END), 0) AS movement', false);
		$this->db->from('jurnal_detail');
		$this->db->join('jurnal', 'jurnal.id_jurnal = jurnal_detail.id_jurnal');
		$this->db->where_in('jurnal_detail.id_akun', $cashIds);
		if (!empty($date)) {
			$this->db->where('jurnal.tgl <=', $date);
		}
		$row = $this->db->get()->row_array();

		return $balance + (int) ($row['movement'] ?? 0);
	}

	/**
	 * Ringkasan arus kas metode langsung.
	 *
	 * Arus kas dihitung dari mutasi akun kas/bank, bukan dari sisi akun lawan.
	 * Dengan demikian total arus selalu dapat direkonsiliasi ke saldo kas awal
	 * dan akhir. Transfer antar akun kas otomatis bernilai neto nol.
	 */
	public function getCashFlowSummary($startDate, $endDate)
	{
		$cashIds = array_map('intval', array_column(
			$this->db->select('id_akun')->from('set_account_kas')->get()->result_array(),
			'id_akun'
		));

		$result = array(1 => array(), 2 => array(), 3 => array());
		if (empty($cashIds)) {
			return $result;
		}

		$this->db->select('jurnal.id_jurnal, jurnal.no_trans, jurnal.tgl, jurnal.keterangan, jurnal_detail.id_akun, jurnal_detail.id_perkiraan, jurnal_detail.nilai, akun.nama, kelompok_akun.kel_akun');
		$this->db->from('jurnal_detail');
		$this->db->join('jurnal', 'jurnal.id_jurnal = jurnal_detail.id_jurnal');
		$this->db->join('akun', 'akun.id_akun = jurnal_detail.id_akun');
		$this->db->join('kelompok_akun', 'kelompok_akun.id_kelompok_akun = akun.id_kelompok_akun');
		$this->db->where('jurnal.tgl >=', $startDate);
		$this->db->where('jurnal.tgl <=', $endDate);
		$this->db->order_by('jurnal.tgl', 'ASC');
		$this->db->order_by('jurnal.id_jurnal', 'ASC');
		$details = $this->db->get()->result_array();

		$journals = array();
		foreach ($details as $detail) {
			$journalId = (int) $detail['id_jurnal'];
			if (!isset($journals[$journalId])) {
				$journals[$journalId] = array(
					'cash' => 0,
					'counterparts' => array(),
				);
			}

			$amount = (int) $detail['nilai'];
			$signed = ((int) $detail['id_perkiraan'] === 1) ? $amount : -$amount;
			if (in_array((int) $detail['id_akun'], $cashIds, true)) {
				$journals[$journalId]['cash'] += $signed;
			} else {
				$detail['signed'] = $signed;
				$journals[$journalId]['counterparts'][] = $detail;
			}
		}

		$mappingRows = $this->db
			->select('akun_sumber.id_akun, kategori.id_kategori, kategori.nama_kategori, kategori.id_kelompok_aktivitas, kategori.keterangan')
			->from('akun_sumber')
			->join('kategori', 'kategori.id_kategori = akun_sumber.id_kategori')
			->order_by('kategori.id_kategori', 'ASC')
			->get()
			->result_array();
		$mappings = array();
		foreach ($mappingRows as $mapping) {
			$mappings[(int) $mapping['id_akun']][] = $mapping;
		}

		$summary = array();
		foreach ($journals as $journalId => $journal) {
			$cashMovement = (int) $journal['cash'];
			if ($cashMovement === 0) {
				continue;
			}

			$direction = $cashMovement > 0 ? 1 : 0;
			$activityId = 1;
			$chosen = null;
			$largest = null;
			foreach ($journal['counterparts'] as $counterpart) {
				if ($largest === null || abs((int) $counterpart['signed']) > abs((int) $largest['signed'])) {
					$largest = $counterpart;
				}
			}

			if ($largest !== null) {
				$group = (int) $largest['kel_akun'];
				if ($group === 120) {
					$activityId = 2;
				} elseif (in_array($group, array(220, 310), true)) {
					$activityId = 3;
				}

				foreach (($mappings[(int) $largest['id_akun']] ?? array()) as $mapping) {
					if ((int) $mapping['keterangan'] === $direction
						&& (int) $mapping['id_kelompok_aktivitas'] === $activityId) {
						$chosen = $mapping;
						break;
					}
				}
			}

			if ($chosen === null) {
				$activityName = array(1 => 'operasi', 2 => 'investasi', 3 => 'pendanaan');
				$chosen = array(
					'id_kategori' => 'fallback-' . $activityId . '-' . $direction,
					'nama_kategori' => ($direction === 1 ? 'Penerimaan' : 'Pengeluaran') . ' kas dari aktivitas ' . $activityName[$activityId],
					'id_kelompok_aktivitas' => $activityId,
				);
			}

			$key = $activityId . ':' . $chosen['id_kategori'];
			if (!isset($summary[$key])) {
				$summary[$key] = array(
					'id' => $chosen['id_kategori'],
					'nama_kategori' => $chosen['nama_kategori'],
					'id_kelompok_aktivitas' => $activityId,
					'nilai' => 0,
				);
			}
			$summary[$key]['nilai'] += $cashMovement;
		}

		foreach ($summary as $row) {
			$activityId = (int) $row['id_kelompok_aktivitas'];
			$result[$activityId][] = array(
				'id' => $row['id'],
				'nama_kategori' => $row['nama_kategori'],
				'id_kelompok_aktivitas' => $activityId,
				'akun_sumber' => array(array('nilai' => $row['nilai'])),
			);
		}

		return $result;
	}

	public function getJournalIntegritySummary()
	{
		$unbalanced = $this->db->query(
			'SELECT COUNT(*) AS total FROM (' .
			'SELECT jurnal.id_jurnal FROM jurnal JOIN jurnal_detail ON jurnal_detail.id_jurnal = jurnal.id_jurnal ' .
			'GROUP BY jurnal.id_jurnal HAVING ' .
			'SUM(CASE WHEN jurnal_detail.id_perkiraan = 1 THEN jurnal_detail.nilai ELSE 0 END) <> ' .
			'SUM(CASE WHEN jurnal_detail.id_perkiraan = 2 THEN jurnal_detail.nilai ELSE 0 END)' .
			') AS unbalanced'
		)->row_array();

		$orphan = $this->db
			->from('jurnal_detail')
			->join('akun', 'akun.id_akun = jurnal_detail.id_akun', 'left')
			->where('akun.id_akun IS NULL', null, false)
			->count_all_results();

		return array(
			'unbalanced' => (int) ($unbalanced['total'] ?? 0),
			'orphan_accounts' => (int) $orphan,
		);
	}

}
