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

class StatusBoard_model extends CI_Model
{
    const AUTO_HIDE_SETTING_KEY = 'status_board_auto_hide_minutes';
    const DEFAULT_AUTO_HIDE_MINUTES = 30;

    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Ambil daftar pesanan untuk papan status.
     * - Jika $tanggal null  -> semua transaksi tersimpan (bukan batal/bukan baru, tidak hidden)
     * - Jika $tanggal isi   -> hanya transaksi tanggal tsb (bukan batal/bukan baru, tidak hidden)
     * Status agregat:
     * - Ikut status item terendah (MIN per item).
     * - Effective status item:
     *   - Dikirim jika jumlah_kirim > 0 atau status=5
     *   - Diambil jika ambil='Y' atau status=4
     *   - Selain itu pakai status asli item
     */
	public function getOrders($tanggal = null)
    {
        // Sinkronisasi visibilitas:
        // jika transaksi pernah tersembunyi (hide_status_board=1) lalu status turun
        // dari Diambil/Dikirim, tampilkan kembali otomatis di status board.
        $this->restoreUnhiddenWhenStatusDrops();
        $this->hideExpiredCompleted($tanggal);

        $params = [];
        $dateRange = $this->dateRangeForDay($tanggal);

        $sql = "
            SELECT 
                i.id_invoice,
                i.id_transaksi,
                i.tgl_trx,
                COALESCE(NULLIF(TRIM(MAX(k.nama)), ''), 'Umum') AS nama_pelanggan,
                MAX(d.update_date) AS last_detail_update,
                {$this->aggregateStatusSql('d')} AS st
            FROM invoice i
            LEFT JOIN invoice_detail d ON d.id_invoice = i.id_invoice
            LEFT JOIN konsumen k ON k.id = i.id_konsumen
            WHERE i.status NOT IN ('batal', 'baru')
              AND i.hide_status_board = 0
        ";

        if ($dateRange !== null) {
            $sql      .= " AND i.tgl_trx >= ? AND i.tgl_trx < ? ";
            $params[]  = $dateRange[0];
            $params[]  = $dateRange[1];
        }

        $sql .= "
            GROUP BY i.id_invoice, i.id_transaksi, i.tgl_trx
			ORDER BY i.tgl_trx ASC, i.id_transaksi ASC
        ";

        $rows = $this->db->query($sql, $params)->result_array();

        $orders = [];
        foreach ($rows as $r) {
            $r['status_angka'] = (int) $r['st'];
            unset($r['st']);
            $r['status_label'] = $this->labelStatus($r['status_angka']);
            unset($r['last_detail_update']);
            $orders[] = $r;
        }

        return $orders;
    }

    /**
     * Tampilkan kembali transaksi tersembunyi jika status agregat saat ini
     * sudah bukan Diambil (4) / Dikirim (5).
     */
    private function restoreUnhiddenWhenStatusDrops()
    {
        $sql = "
            UPDATE invoice i
            JOIN (
                SELECT
                    ih.id_invoice,
                    {$this->aggregateStatusSql('d')} AS st
                FROM invoice ih
                LEFT JOIN invoice_detail d ON d.id_invoice = ih.id_invoice
                WHERE ih.status <> 'batal'
                  AND ih.hide_status_board = 1
                GROUP BY ih.id_invoice
            ) x ON x.id_invoice = i.id_invoice
            SET i.hide_status_board = 0
            WHERE i.hide_status_board = 1
              AND x.st NOT IN (4,5)
        ";

        $this->db->query($sql);
    }

    /**
     * Sembunyikan order Diambil/Dikirim yang sudah lewat jeda tampil.
     * Dijalankan sebelum SELECT utama supaya board tidak ikut menarik order lama.
     */
    private function hideExpiredCompleted($tanggal = null)
    {
        $params = [];
        $dateRange = $this->dateRangeForDay($tanggal);
        $dateWhere = '';
        $autoHideSeconds = $this->getAutoHideMinutes() * 60;

        if ($dateRange !== null) {
            $dateWhere = ' AND i2.tgl_trx >= ? AND i2.tgl_trx < ? ';
            $params[] = $dateRange[0];
            $params[] = $dateRange[1];
        }

        $sql = "
            UPDATE invoice i
            JOIN (
                SELECT
                    i2.id_invoice,
                    i2.tgl_trx,
                    MAX(d.update_date) AS last_detail_update,
                    {$this->aggregateStatusSql('d')} AS st
                FROM invoice i2
                LEFT JOIN invoice_detail d ON d.id_invoice = i2.id_invoice
                WHERE i2.status NOT IN ('batal', 'baru')
                  AND i2.hide_status_board = 0
                  {$dateWhere}
                GROUP BY i2.id_invoice, i2.tgl_trx
                HAVING st IN (4,5)
                   AND TIMESTAMPDIFF(
                        SECOND,
                        COALESCE(last_detail_update, i2.tgl_trx),
                        NOW()
                   ) >= ?
            ) x ON x.id_invoice = i.id_invoice
            SET i.hide_status_board = 1
            WHERE i.hide_status_board = 0
        ";

        $params[] = $autoHideSeconds;
        $this->db->query($sql, $params);
    }

    public function getAutoHideMinutes()
    {
        if (!$this->db->table_exists('shared_folder')) {
            return self::DEFAULT_AUTO_HIDE_MINUTES;
        }

        $row = $this->db
            ->select('isi')
            ->from('shared_folder')
            ->where('nama', self::AUTO_HIDE_SETTING_KEY)
            ->limit(1)
            ->get()
            ->row_array();

        if (!$row || !isset($row['isi'])) {
            $this->saveAutoHideMinutes(self::DEFAULT_AUTO_HIDE_MINUTES);
            return self::DEFAULT_AUTO_HIDE_MINUTES;
        }

        return $this->normalizeAutoHideMinutes($row['isi']);
    }

    public function saveAutoHideMinutes($minutes)
    {
        if (!$this->db->table_exists('shared_folder')) {
            return false;
        }

        $minutes = $this->normalizeAutoHideMinutes($minutes);
        $exists = $this->db
            ->select('id')
            ->from('shared_folder')
            ->where('nama', self::AUTO_HIDE_SETTING_KEY)
            ->limit(1)
            ->get()
            ->row_array();

        if ($exists) {
            return $this->db
                ->where('nama', self::AUTO_HIDE_SETTING_KEY)
                ->update('shared_folder', ['isi' => (string) $minutes]);
        }

        $nextIdRow = $this->db
            ->select('COALESCE(MAX(id), 0) + 1 AS next_id', false)
            ->from('shared_folder')
            ->get()
            ->row_array();

        return $this->db->insert('shared_folder', [
            'id'   => (int) ($nextIdRow['next_id'] ?? 1),
            'nama' => self::AUTO_HIDE_SETTING_KEY,
            'isi'  => (string) $minutes,
        ]);
    }

    private function normalizeAutoHideMinutes($minutes)
    {
        $minutes = (int) $minutes;
        if ($minutes < 0) {
            return self::DEFAULT_AUTO_HIDE_MINUTES;
        }
        if ($minutes > 1440) {
            return 1440;
        }
        return $minutes;
    }

    private function dateRangeForDay($tanggal)
    {
        if (empty($tanggal)) {
            return null;
        }

        $ts = strtotime((string) $tanggal);
        if ($ts === false) {
            return null;
        }

        $start = date('Y-m-d 00:00:00', $ts);
        $end = date('Y-m-d 00:00:00', strtotime('+1 day', $ts));

        return [$start, $end];
    }

    /**
     * Ambil daftar pesanan dengan status Diambil/Dikirim (+ info hide)
     * untuk halaman manage.
     */
    public function getCompletedOrders($limit = null, $offset = 0, $sort = 'DESC')
    {
        $sort = strtoupper((string) $sort);
        if (!in_array($sort, ['ASC', 'DESC'], true)) {
            $sort = 'DESC';
        }

        $sql = "
            SELECT
                x.id_invoice,
                x.id_transaksi,
                x.tgl_trx,
                x.st AS status_angka,
                i.hide_status_board
            " . $this->completedOrdersBaseSql() . "
            ORDER BY x.tgl_trx {$sort}, x.id_transaksi {$sort}
        ";

        if ($limit !== null) {
            $limit = max(1, min(100, (int) $limit));
            $offset = max(0, (int) $offset);
            $sql .= " LIMIT {$offset}, {$limit}";
        }

        $rows = $this->db->query($sql)->result_array();

        foreach ($rows as &$r) {
            $r['status_label'] = $this->labelStatus((int)$r['status_angka']);
        }

        return $rows;
    }

    public function countCompletedOrders()
    {
        $row = $this->db
            ->query('SELECT COUNT(*) AS total ' . $this->completedOrdersBaseSql())
            ->row_array();

        return isset($row['total']) ? (int) $row['total'] : 0;
    }

    private function completedOrdersBaseSql()
    {
        return "
            FROM (
                SELECT
                    i.id_invoice,
                    i.id_transaksi,
                    i.tgl_trx,
                    {$this->aggregateStatusSql('d')} AS st
                FROM invoice i
                LEFT JOIN invoice_detail d ON d.id_invoice = i.id_invoice
                WHERE i.status <> 'batal'
                GROUP BY i.id_invoice, i.id_transaksi, i.tgl_trx
                HAVING st IN (4,5)
            ) AS x
            JOIN invoice i ON i.id_invoice = x.id_invoice
        ";
    }

    /**
     * Set hide_status_board untuk beberapa id_invoice
     * @param array $ids
     * @param int   $hidden 0/1
     */
    public function setHidden(array $ids, $hidden)
    {
        if (empty($ids)) return;
        $hidden = (int) ($hidden ? 1 : 0);
        $this->db->where_in('id_invoice', $ids);
        $this->db->update('invoice', ['hide_status_board' => $hidden]);
    }

    /**
     * Sembunyikan semua transaksi dengan status Diambil/Dikirim.
     */
    public function hideAllCompleted()
    {
        $sql = "
            UPDATE invoice i
            JOIN (
                SELECT 
                    i.id_invoice,
                    {$this->aggregateStatusSql('d')} AS st
                FROM invoice i
                LEFT JOIN invoice_detail d ON d.id_invoice = i.id_invoice
                WHERE i.status <> 'batal'
                GROUP BY i.id_invoice
            ) x ON x.id_invoice = i.id_invoice
            SET i.hide_status_board = 1
            WHERE x.st IN (4,5)
        ";
        $this->db->query($sql);
    }

    /**
     * Data ringkas untuk modal preview status board.
     * Dibuat khusus agar klik kartu tidak memakai view laporan yang banyak
     * memanggil helper per item.
     */
    public function getCardPreviewByTransaction($trx)
    {
        $trx = trim((string) $trx);
        if ($trx === '') {
            return null;
        }

        $summarySql = "
            SELECT
                i.id_invoice,
                i.id_transaksi,
                i.tgl_trx,
                i.tgl_ambil,
                i.total_bayar,
                i.potongan_harga,
                i.cashback,
                COALESCE(NULLIF(TRIM(k.nama), ''), 'Umum') AS nama_pelanggan,
                COALESCE((
                    SELECT SUM(CASE WHEN bid.hapus = 0 THEN bid.jml_bayar ELSE 0 END)
                    FROM bayar_invoice_detail bid
                    WHERE bid.id_invoice = i.id_invoice
                ), 0) AS total_terbayar,
                COALESCE((
                    SELECT SUM(d2.jumlah * d2.harga)
                    FROM invoice_detail d2
                    WHERE d2.id_invoice = i.id_invoice
                ), 0) AS total_order_detail,
                COALESCE((
                    SELECT SUM(d2.jumlah * d2.harga * d2.diskon / 100)
                    FROM invoice_detail d2
                    WHERE d2.id_invoice = i.id_invoice
                ), 0) AS total_diskon_detail
            FROM invoice i
            LEFT JOIN konsumen k ON k.id = i.id_konsumen
            WHERE i.id_transaksi = ?
            LIMIT 1
        ";

        $order = $this->db->query($summarySql, [$trx])->row_array();
        if (!$order) {
            return null;
        }

        $detailSql = "
            SELECT
                d.id_rincianinvoice,
                d.id_invoice,
                d.id_produk,
                d.id_bahan,
                d.jumlah,
                d.ukuran,
                d.keterangan,
                d.detail,
                d.status,
                d.ambil,
                d.jumlah_kirim,
                d.update_date,
                d.jenis_cetakan,
                COALESCE(NULLIF(TRIM(p.title), ''), 'Produk') AS nama_produk,
                COALESCE(p.id_jenis, 0) AS id_jenis_produk,
                COALESCE(NULLIF(TRIM(b.title), ''), '-') AS nama_bahan,
                COALESCE(b.kunci, 0) AS cek_bahan,
                {$this->effectiveStatusSql('d')} AS effective_status
            FROM invoice_detail d
            LEFT JOIN produk p ON p.id = d.id_produk
            LEFT JOIN bahan b ON b.id = d.id_bahan
            WHERE d.id_invoice = ?
            ORDER BY d.id_rincianinvoice ASC
        ";

        $details = $this->db
            ->query($detailSql, [(int) $order['id_invoice']])
            ->result_array();

        $statusAngka = 0;
        if (!empty($details)) {
            $statusAngka = job_status_slowest(array_map(static function ($row) {
                return (int) ($row['effective_status'] ?? 0);
            }, $details));
        }

        $totalOrder = (int) ($order['total_order_detail'] ?: $order['total_bayar']);
        $totalDiskon = (int) ($order['total_diskon_detail'] ?: 0);
        $totalTagihan = max(0, $totalOrder - $totalDiskon);
        $totalBayar = (int) ($order['total_terbayar'] ?? 0);
        $isLunas = $totalTagihan > 0 ? ($totalBayar >= $totalTagihan) : false;

        $order['status_angka'] = $statusAngka;
        $order['status_label'] = $this->labelStatus($statusAngka);
        $order['payment_label'] = $isLunas ? 'Lunas' : 'Belum Lunas';
        $order['payment_class'] = $isLunas ? 'lunas' : 'belum';

        return [
            'order' => $order,
            'details' => $details,
        ];
    }

    public function getPublicTracking($idInvoice)
    {
        $idInvoice = (int) $idInvoice;
        if ($idInvoice <= 0) {
            return null;
        }

        $summarySql = "
            SELECT
                i.id_invoice,
                i.id_transaksi,
                i.tgl_trx,
                i.jam_order,
                i.tgl_ambil,
                i.status AS status_invoice,
                i.total_bayar,
                i.pajak,
                i.potongan_harga,
                i.cashback,
                i.jumlah_bayar,
                i.kembalian,
                COALESCE(NULLIF(TRIM(k.nama), ''), 'Umum') AS nama_pelanggan,
                COALESCE(NULLIF(TRIM(k.no_hp), ''), '-') AS no_hp,
                COALESCE(NULLIF(TRIM(u.nama_lengkap), ''), '-') AS kasir,
                COALESCE((
                    SELECT SUM(CASE WHEN bid.hapus = 0 THEN bid.jml_bayar ELSE 0 END)
                    FROM bayar_invoice_detail bid
                    WHERE bid.id_invoice = i.id_invoice
                ), 0) AS total_terbayar,
                {$this->aggregateStatusSql('d')} AS status_angka,
                MAX(d.update_date) AS last_detail_update
            FROM invoice i
            LEFT JOIN invoice_detail d ON d.id_invoice = i.id_invoice
            LEFT JOIN konsumen k ON k.id = i.id_konsumen
            LEFT JOIN tb_users u ON u.id_user = i.id_user
            WHERE i.id_invoice = ?
            GROUP BY
                i.id_invoice, i.id_transaksi, i.tgl_trx, i.jam_order, i.tgl_ambil,
                i.status, i.total_bayar, i.pajak, i.potongan_harga, i.cashback,
                i.jumlah_bayar, i.kembalian, k.nama, k.no_hp, u.nama_lengkap
            LIMIT 1
        ";

        $order = $this->db->query($summarySql, [$idInvoice])->row_array();
        if (!$order) {
            return null;
        }

        $hasProofTable = $this->db->table_exists('invoice_detail_bukti');
        $proofJoin = "";
        $proofSelect = $hasProofTable
            ? ", COALESCE((
                    SELECT NULLIF(TRIM(ib.filename), '')
                    FROM invoice_detail_bukti ib
                    WHERE ib.id_rincianinvoice = d.id_rincianinvoice
                    ORDER BY COALESCE(ib.created_at, ib.updated_at) DESC, ib.id DESC
                    LIMIT 1
                ), '') AS bukti_file"
            : ", '' AS bukti_file";

        $detailSql = "
            SELECT
                d.id_rincianinvoice,
                d.id_invoice,
                d.jumlah,
                d.ukuran,
                d.keterangan,
                d.detail,
                d.status,
                d.update_date,
                d.jumlah_kirim,
                d.ambil,
                COALESCE(NULLIF(TRIM(p.title), ''), 'Produk') AS nama_produk,
                COALESCE(NULLIF(TRIM(b.title), ''), '-') AS nama_bahan,
                COALESCE(NULLIF(TRIM(op.nama_lengkap), ''), '-') AS operator,
                {$this->effectiveStatusSql('d')} AS effective_status
                {$proofSelect}
            FROM invoice_detail d
            LEFT JOIN produk p ON p.id = d.id_produk
            LEFT JOIN bahan b ON b.id = d.id_bahan
            LEFT JOIN tb_users op ON op.id_user = d.id_operator
            {$proofJoin}
            WHERE d.id_invoice = ?
              AND d.jenis_cetakan != 9
            ORDER BY d.id_rincianinvoice ASC
        ";

		$details = $this->db->query($detailSql, [$idInvoice])->result_array();
		$proofMap = [];
		if ($hasProofTable && $details) {
			$detailIds = array_values(array_filter(array_map(static function($detail){
				return (int)($detail['id_rincianinvoice'] ?? 0);
			}, $details)));
			if ($detailIds) {
				$captionSelect = $this->db->field_exists('keterangan', 'invoice_detail_bukti') ? 'keterangan' : "'' AS keterangan";
				$proofRows = $this->db
					->select("id, id_rincianinvoice, filename, {$captionSelect}, created_at, updated_at", false)
					->where_in('id_rincianinvoice', $detailIds)
					->order_by('COALESCE(created_at, updated_at) ASC, id ASC', '', false)
					->get('invoice_detail_bukti')
					->result_array();
				foreach ($proofRows as $proof) {
					$detailId = (int)$proof['id_rincianinvoice'];
					$proofTime = !empty($proof['created_at']) ? $proof['created_at'] : ($proof['updated_at'] ?? '');
					$proofMap[$detailId][] = [
						'url' => base_url('uploads/lampiran/progress/' . rawurlencode((string)$proof['filename'])),
						'keterangan' => trim((string)($proof['keterangan'] ?? '')) ?: 'Bukti progres',
						'uploaded_at_label' => $proofTime ? date('d/m/Y H:i', strtotime($proofTime)) . ' WIB' : '',
					];
				}
			}
		}
        foreach ($details as &$detail) {
            $detail['effective_status'] = (int) $detail['effective_status'];
            $detail['status_label'] = $this->getStatusLabel($detail['effective_status']);
            $detail['bukti_url'] = '';
            if (!empty($detail['bukti_file'])) {
                $detail['bukti_url'] = base_url('uploads/lampiran/progress/' . rawurlencode($detail['bukti_file']));
            }
			$detail['bukti_progres'] = $proofMap[(int)$detail['id_rincianinvoice']] ?? [];
        }
        unset($detail);

        $order['status_angka'] = (int) $order['status_angka'];
        $order['status_label'] = $this->getStatusLabel($order['status_angka']);

        $totalOrder = (int) ($order['total_bayar'] ?? 0);
        $totalBayar = (int) ($order['total_terbayar'] ?? 0);
        $diskon = (int) ($order['potongan_harga'] ?? 0);

        $order['payment'] = [
            'total' => $totalOrder,
            'bayar' => $totalBayar,
            'diskon' => $diskon,
            'cashback' => (int) ($order['cashback'] ?? 0),
            'piutang' => max(0, $totalOrder - $totalBayar - $diskon),
            'payment_label' => max(0, $totalOrder - $totalBayar - $diskon) > 0 ? 'Belum Lunas' : 'Lunas',
        ];

        return [
            'order' => $order,
            'details' => $details,
        ];
    }

    public function getStatusLabel($n)
    {
        return $this->labelStatus($n);
    }

    private function effectiveStatusSql($alias)
    {
        $alias = preg_replace('/[^a-zA-Z0-9_]/', '', (string) $alias);
        if ($alias === '') {
            $alias = 'd';
        }

        return "
            CASE
                WHEN {$alias}.jumlah_kirim > 0 OR IFNULL({$alias}.status, 0) = 5 THEN 5
                WHEN {$alias}.ambil = 'Y' OR IFNULL({$alias}.status, 0) = 4 THEN 4
                ELSE IFNULL({$alias}.status, 0)
            END
        ";
    }

	private function aggregateStatusSql($alias)
	{
		$effective = $this->effectiveStatusSql($alias);
		$rankCases = [];
		foreach (job_statuses(false) as $status) {
			$rankCases[] = 'WHEN '.(int)$status['code'].' THEN '.(int)$status['order'];
		}
		$rankSql = 'CASE '.$effective.' '.implode(' ', $rankCases).' ELSE 999 END';
		return "COALESCE(CAST(SUBSTRING_INDEX(GROUP_CONCAT({$effective} ORDER BY {$rankSql} ASC SEPARATOR ','), ',', 1) AS UNSIGNED), 0)";
	}

    private function labelStatus($n)
    {
        $meta = job_status_meta((int)$n);
        return $meta['label'];
    }
}
