<?php
namespace My\Component\GrafikKeuangan\Site\Model;

defined('_JEXEC') or die;

use Joomla\CMS\MVC\Model\BaseDatabaseModel;

class GraphModel extends BaseDatabaseModel
{
	public function getData($year, $month)
	{
		$db = $this->getDatabase();
		$query = $db->getQuery(true);

		$query->select('*')
			->from($db->quoteName('#__grafik_keuangan'))
			->where($db->quoteName('state') . ' = 1')
			->where($db->quoteName('tahun') . ' = ' . (int) $year)
			->where($db->quoteName('bulan') . ' = ' . (int) $month);

		$result = $db->setQuery($query)->loadObject();
		return $result;
	}

	public function getLatestPeriod()
	{
		$db = $this->getDatabase();
		$query = $db->getQuery(true);

		$query->select($db->quoteName(['tahun', 'bulan']))
			->from($db->quoteName('#__grafik_keuangan'))
			->where($db->quoteName('state') . ' = 1')
			->order($db->quoteName('tahun') . ' DESC, ' . $db->quoteName('bulan') . ' DESC')
			->setLimit(1);

		return $db->setQuery($query)->loadObject();
	}

	public function getChartData($year)
	{
		$db = $this->getDatabase();

		// 1. Fetch Pagu (Month 0)
		$queryPagu = $db->getQuery(true)
			->select('*')
			->from($db->quoteName('#__grafik_keuangan'))
			->where($db->quoteName('tahun') . ' = ' . (int) $year)
			->where($db->quoteName('bulan') . ' = 0');
		$db->setQuery($queryPagu);
		$paguData = $db->loadObject();

		// 2. Fetch Realisasi (Months 1-12)
		$queryReal = $db->getQuery(true)
			->select('*')
			->from($db->quoteName('#__grafik_keuangan'))
			->where($db->quoteName('tahun') . ' = ' . (int) $year)
			->where($db->quoteName('bulan') . ' > 0')
			->order('bulan ASC');
		$db->setQuery($queryReal);
		$realisasiData = $db->loadObjectList('bulan');

		// 3. Merge Data: Create entry ONLY for months that have Realisasi Data
		$results = [];
		for ($m = 1; $m <= 12; $m++) {
			// Check if this month exists in realisasiData
			if (!isset($realisasiData[$m])) {
				continue; // Skip months with no data
			}

			$obj = new \stdClass();
			$obj->bulan = $m;
			$obj->tahun = $year;

			// Set Pagu (fixed for year)
			$obj->pagu_51 = $paguData ? $paguData->pagu_51 : 0;
			$obj->pagu_52 = $paguData ? $paguData->pagu_52 : 0;
			$obj->pagu_53 = $paguData ? $paguData->pagu_53 : 0;
			$obj->pagu_posbakum = $paguData ? $paguData->pagu_posbakum : 0;
			$obj->pagu_prodeo = $paguData ? $paguData->pagu_prodeo : 0;

			// Set Realisasi (monthly)
			$rItem = $realisasiData[$m];
			$obj->realisasi_51 = $rItem->realisasi_51;
			$obj->realisasi_52 = $rItem->realisasi_52;
			$obj->realisasi_53 = $rItem->realisasi_53;
			$obj->realisasi_posbakum = $rItem->realisasi_posbakum;
			$obj->realisasi_prodeo = $rItem->realisasi_prodeo;

			$results[] = $obj;
		}

		return $results;
	}

	public function getYears()
	{
		$db = $this->getDatabase();
		$query = $db->getQuery(true);

		$query->select('DISTINCT ' . $db->quoteName('tahun'))
			->from($db->quoteName('#__grafik_keuangan'))
			->where($db->quoteName('state') . ' = 1')
			->order($db->quoteName('tahun') . ' DESC');

		return $db->setQuery($query)->loadColumn();
	}
}
