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

class Spk_produksi_model extends CI_Model
{
    public function ensureDraftTable()
    {
        if ($this->db->table_exists('spk_produksi_draft')) return;
        $this->db->query("CREATE TABLE IF NOT EXISTS `spk_produksi_draft` (
            `id_rincianinvoice` INT NOT NULL,
            `data_json` TEXT NULL,
            `updated_by` INT NOT NULL DEFAULT 0,
            `updated_at` DATETIME NULL,
            PRIMARY KEY (`id_rincianinvoice`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    public function getItem($detailId)
    {
        return $this->db->select("d.*, i.id_transaksi, i.tgl_trx, i.tgl_ambil, i.id_konsumen, COALESCE(p.title,'Produk') AS produk, COALESCE(b.title,'-') AS bahan", false)
            ->from('invoice_detail d')->join('invoice i','i.id_invoice=d.id_invoice')
            ->join('produk p','p.id=d.id_produk','left')->join('bahan b','b.id=d.id_bahan','left')
            ->where('d.id_rincianinvoice',(int)$detailId)->limit(1)->get()->row();
    }

    public function getDraft($detailId)
    {
        $this->ensureDraftTable();
        $row = $this->db->where('id_rincianinvoice',(int)$detailId)->get('spk_produksi_draft')->row();
        if (!$row || empty($row->data_json)) return [];
        $data = json_decode($row->data_json, true);
        return is_array($data) ? $data : [];
    }

    public function saveDraft($detailId, array $data, $userId)
    {
        $this->ensureDraftTable();
        $payload = ['data_json'=>json_encode($data),'updated_by'=>(int)$userId,'updated_at'=>date('Y-m-d H:i:s')];
        $exists = $this->db->where('id_rincianinvoice',(int)$detailId)->count_all_results('spk_produksi_draft') > 0;
        if ($exists) return $this->db->where('id_rincianinvoice',(int)$detailId)->update('spk_produksi_draft',$payload);
        $payload['id_rincianinvoice'] = (int)$detailId;
        return $this->db->insert('spk_produksi_draft',$payload);
    }

    public function getStockMaterials()
    {
        return $this->db
            ->select("b.id, b.title, b.varian_ukuran, COALESCE(s.nama_satuan, s.satuan, '') AS satuan, (COALESCE(sm.jumlah,0) - COALESCE(sk.jumlah,0)) AS total_stok", false)
            ->from('bahan b')
            ->join('satuan s', 's.id=b.id_satuan', 'left')
            ->join('(SELECT id_bahan, SUM(jumlah) AS jumlah FROM stok_masuk WHERE stat <> 9 GROUP BY id_bahan) sm', 'sm.id_bahan=b.id', 'left', false)
            ->join('(SELECT id_bahan, SUM(jumlah) AS jumlah FROM stok_keluar WHERE stat <> 9 GROUP BY id_bahan) sk', 'sk.id_bahan=b.id', 'left', false)
            ->where('b.status_stok', 'Y')
            ->where('b.varian_aktif !=', 1)
            ->order_by('b.title', 'ASC')
            ->get()->result();
    }

    public function getStockMaterial($stockId)
    {
        return $this->db->select('id, title, varian_ukuran')->where('id', (int)$stockId)->where('status_stok', 'Y')->limit(1)->get('bahan')->row();
    }

    public function syncStockUsage($detailId, $invoiceId, $stockId, $quantity, $userId)
    {
        $note = 'SPK Produksi #'.(int)$detailId;
        $existing = $this->db->where('ket', $note)->limit(1)->get('stok_keluar')->row();
        $payload = [
            'id_user' => (int)$userId,
            'id_invoice' => (int)$invoiceId,
            'id_bahan' => (int)$stockId,
            'jumlah' => (float)$quantity,
            'create_date' => date('Y-m-d H:i:s'),
            'ket' => $note,
            'stat' => 1,
        ];
        if ($existing) return $this->db->where('id', (int)$existing->id)->update('stok_keluar', $payload);
        return $this->db->insert('stok_keluar', $payload);
    }

    public function syncStockPages($detailId, $invoiceId, array $usages, $userId)
    {
        $baseNote = 'SPK Produksi #'.(int)$detailId;
        $activeNotes = [];
        foreach (array_values($usages) as $index=>$usage) {
            $note = $index === 0 ? $baseNote : $baseNote.' Halaman '.($index + 1);
            $activeNotes[] = $note;
            $payload = [
                'id_user'=>(int)$userId,
                'id_invoice'=>(int)$invoiceId,
                'id_bahan'=>(int)$usage['stock_id'],
                'jumlah'=>(float)$usage['quantity'],
                'create_date'=>date('Y-m-d H:i:s'),
                'ket'=>$note,
                'stat'=>1,
            ];
            $existing = $this->db->where('ket', $note)->limit(1)->get('stok_keluar')->row();
            if ($existing) {
                if (!$this->db->where('id', (int)$existing->id)->update('stok_keluar', $payload)) return false;
            } elseif (!$this->db->insert('stok_keluar', $payload)) return false;
        }
        $oldRows = $this->db->group_start()->where('ket', $baseNote)->or_like('ket', $baseNote.' Halaman ', 'after')->group_end()->get('stok_keluar')->result();
        foreach ($oldRows as $oldRow) {
            if (!in_array($oldRow->ket, $activeNotes, true) && !$this->db->where('id', (int)$oldRow->id)->update('stok_keluar', ['stat'=>9])) return false;
        }
        return true;
    }

    private function buildQuery(array $filters = [], $scope = 'all')
    {
        $this->db->from('invoice i')
            ->join('konsumen k', 'k.id = i.id_konsumen', 'left')
            ->join('tb_users u', 'u.id_user = i.id_user', 'left')
            ->where('i.status', 'simpan')
            ->where('EXISTS (SELECT 1 FROM invoice_detail d WHERE d.id_invoice = i.id_invoice)', null, false);

        $hasSpk = "EXISTS (SELECT 1 FROM invoice_detail dspk WHERE dspk.id_invoice = i.id_invoice AND TRIM(COALESCE(dspk.no_spk, '')) <> '')";
        if ($scope === 'created') {
            $this->db->where($hasSpk, null, false);
        } elseif ($scope === 'available') {
            $this->db->where('NOT '.$hasSpk, null, false);
        }

        if (!empty($filters['keyword'])) {
            $keyword = trim($filters['keyword']);
            $this->db->group_start()
                ->like('i.id_transaksi', $keyword)
                ->or_like('k.nama', $keyword)
                ->or_like('k.no_hp', $keyword)
                ->or_where('EXISTS (SELECT 1 FROM invoice_detail ds WHERE ds.id_invoice = i.id_invoice AND (ds.no_spk LIKE '.$this->db->escape('%'.$keyword.'%').' OR ds.keterangan LIKE '.$this->db->escape('%'.$keyword.'%').'))', null, false)
                ->group_end();
        }
        if (!empty($filters['dari'])) {
            $this->db->where('i.tgl_trx >=', $filters['dari']);
        }
        if (!empty($filters['sampai'])) {
            // tgl_trx menyimpan jam. Gunakan batas eksklusif hari berikutnya
            // agar transaksi pada tanggal "sampai" tetap ikut ditampilkan.
            $nextDay = date('Y-m-d', strtotime($filters['sampai'].' +1 day'));
            $this->db->where('i.tgl_trx <', $nextDay);
        }
    }

    public function countRows(array $filters = [], $scope = 'created')
    {
        $this->buildQuery($filters, $scope);
        return (int) $this->db->count_all_results();
    }

    public function getRows(array $filters, $limit, $offset, $scope = 'created')
    {
        $this->db->select('i.id_invoice, i.id_transaksi, i.tgl_trx, i.tgl_ambil, k.nama, k.no_hp, u.nama_lengkap AS kasir');
        $this->buildQuery($filters, $scope);
        $direction = isset($filters['sort']) && $filters['sort'] === 'ASC' ? 'ASC' : 'DESC';
        return $this->db->order_by('i.id_invoice', $direction)->limit($limit, $offset)->get()->result_array();
    }

    public function getDetails(array $invoiceIds)
    {
        $result = [];
        $ids = array_values(array_filter(array_map('intval', $invoiceIds)));
        if (!$ids) return $result;

        $rows = $this->db->select("d.*, COALESCE(p.title, 'Produk') AS produk, COALESCE(b.title, '-') AS bahan, COALESCE(u.nama_lengkap, '-') AS operator", false)
            ->from('invoice_detail d')->join('produk p', 'p.id = d.id_produk', 'left')
            ->join('bahan b', 'b.id = d.id_bahan', 'left')->join('tb_users u', 'u.id_user = d.id_operator', 'left')
            ->where_in('d.id_invoice', $ids)->order_by('d.id_rincianinvoice', 'ASC')->get()->result_array();
        foreach ($rows as $row) $result[(int)$row['id_invoice']][] = $row;
        return $result;
    }
}
