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

class Pembelian_stok_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
        $this->ensure_schema();
    }

    public function ensure_schema()
    {
        $this->load->dbforge();

        if ($this->db->table_exists('info') && !$this->db->field_exists('sinkron_pembelian_stok', 'info')) {
            $this->dbforge->add_column('info', array(
                'sinkron_pembelian_stok' => array('type' => 'TINYINT', 'constraint' => 1, 'null' => false, 'default' => 0)
            ));
        }

        if ($this->db->table_exists('item_pembelian') && !$this->db->field_exists('id_bahan_stok', 'item_pembelian')) {
            $this->dbforge->add_column('item_pembelian', array(
                'id_bahan_stok' => array('type' => 'INT', 'constraint' => 11, 'null' => false, 'default' => 0)
            ));
            $this->db->query('ALTER TABLE `item_pembelian` ADD INDEX `idx_item_pembelian_bahan_stok` (`id_bahan_stok`)');
        }
		if ($this->db->table_exists('item_pembelian') && $this->db->field_exists('id_bahan_stok', 'item_pembelian')) {
			$hasIndex = $this->db->query("SHOW INDEX FROM `item_pembelian` WHERE Key_name='idx_item_pembelian_bahan_stok'")->num_rows() > 0;
			if (!$hasIndex) $this->db->query('ALTER TABLE `item_pembelian` ADD INDEX `idx_item_pembelian_bahan_stok` (`id_bahan_stok`)');
		}

        if (!$this->db->table_exists('pembelian_stok_pending')) {
            $this->db->query("CREATE TABLE `pembelian_stok_pending` (
                `id` INT(11) NOT NULL AUTO_INCREMENT,
                `id_pembelian` INT(11) NOT NULL,
                `id_pembelian_detail` INT(11) NOT NULL,
                `id_bahan` INT(11) NOT NULL,
                `jumlah` DECIMAL(15,4) NOT NULL DEFAULT 0,
                `harga_beli` DECIMAL(15,2) NOT NULL DEFAULT 0,
                `status` ENUM('pending','approved') NOT NULL DEFAULT 'pending',
                `requested_by` INT(11) NOT NULL DEFAULT 0,
                `approved_by` INT(11) NOT NULL DEFAULT 0,
                `approved_at` DATETIME NULL,
                `stok_masuk_id` INT(11) NOT NULL DEFAULT 0,
                `created_at` DATETIME NOT NULL,
                PRIMARY KEY (`id`),
                UNIQUE KEY `uq_pending_detail` (`id_pembelian_detail`),
                KEY `idx_pending_pembelian_status` (`id_pembelian`,`status`),
                KEY `idx_pending_bahan` (`id_bahan`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        }
    }

    public function is_enabled()
    {
        if (!$this->db->field_exists('sinkron_pembelian_stok', 'info')) return false;
        $row = $this->db->select('sinkron_pembelian_stok')->limit(1)->get('info')->row();
        return $row && (int) $row->sinkron_pembelian_stok === 1;
    }

    public function set_enabled($enabled)
    {
        $this->db->set('sinkron_pembelian_stok', $enabled ? 1 : 0)->update('info');
        if ($enabled) $this->sync_stock_items();
        return $this->db->affected_rows() >= 0;
    }

    public function sync_stock_items()
    {
        if (!$this->is_enabled()) return;

        $stocks = $this->db->select('id,title')->from('bahan')
            ->where('pub', 1)->where('status_stok', 'Y');
		if ($this->db->field_exists('varian_aktif', 'bahan')) $this->db->where('varian_aktif !=', 1);
		$stocks = $this->db->order_by('title', 'ASC')->get()->result_array();

        foreach ($stocks as $stock) {
            $linked = $this->db->where('id_bahan_stok', (int) $stock['id'])->get('item_pembelian')->row_array();
            if ($linked) {
                $this->db->where('id', $linked['id'])->update('item_pembelian', array('title'=>$stock['title'], 'pub'=>'Y', 'kunci'=>0));
                continue;
            }

            $sameTitleValue = $this->db->escape(strtolower(trim($stock['title'])));
            $sameTitle = $this->db->where('LOWER(title) = '.$sameTitleValue, null, false)
                ->limit(1)->get('item_pembelian')->row_array();
            if ($sameTitle) {
                $this->db->where('id', $sameTitle['id'])->update('item_pembelian', array(
                    'id_bahan_stok'=>(int)$stock['id'], 'pub'=>'Y', 'kunci'=>0
                ));
                continue;
            }

            $this->db->insert('item_pembelian', array(
                'title'=>$stock['title'], 'harga_beli'=>0, 'kunci'=>0,
                'kelompok_akun'=>8, 'id_akun'=>0, 'pub'=>'Y', 'id_bahan_stok'=>(int)$stock['id']
            ));
        }
    }

    public function queue_purchase($purchaseId, $userId)
    {
        if (!$this->is_enabled()) return 0;
        $this->sync_stock_items();

        $rows = $this->db->select('pd.no,pd.id_pembelian,pd.jumlah,pd.harga,ip.id_bahan_stok')
            ->from('pembelian_detail pd')->join('item_pembelian ip', 'ip.id = pd.id_bahan')
            ->where('pd.id_pembelian', (int)$purchaseId)->where('ip.id_bahan_stok >', 0)
            ->where('pd.jumlah >', 0)->get()->result_array();

        $count = 0;
        foreach ($rows as $row) {
            $exists = $this->db->where('id_pembelian_detail', (int)$row['no'])->count_all_results('pembelian_stok_pending');
            if ($exists) continue;
            $this->db->insert('pembelian_stok_pending', array(
                'id_pembelian'=>(int)$purchaseId,
                'id_pembelian_detail'=>(int)$row['no'],
                'id_bahan'=>(int)$row['id_bahan_stok'],
                'jumlah'=>(float)$row['jumlah'],
                'harga_beli'=>(float)$row['harga'],
                'status'=>'pending', 'requested_by'=>(int)$userId,
                'created_at'=>date('Y-m-d H:i:s')
            ));
            $count++;
        }
        return $count;
    }

    public function summaries(array $purchaseIds)
    {
        if (empty($purchaseIds)) return array();
        $rows = $this->db->select("id_pembelian, COUNT(*) AS total, SUM(status='pending') AS pending, SUM(status='approved') AS approved", false)
            ->from('pembelian_stok_pending')->where_in('id_pembelian', $purchaseIds)
            ->group_by('id_pembelian')->get()->result_array();
        $out = array();
        foreach ($rows as $row) $out[(int)$row['id_pembelian']] = $row;
        return $out;
    }

    public function pending_purchases()
    {
        $rows = $this->db->select('q.id_pembelian,p.no_transaksi,p.tgl_pembelian,q.id_bahan,b.title,s.satuan,SUM(q.jumlah) AS qty', false)
            ->from('pembelian_stok_pending q')->join('pembelian p', 'p.id_pembelian=q.id_pembelian')
            ->join('bahan b', 'b.id=q.id_bahan')->join('satuan s', 's.id=b.id_satuan', 'left')
            ->where('q.status', 'pending')
            ->group_by(array('q.id_pembelian','p.no_transaksi','p.tgl_pembelian','q.id_bahan','b.title','s.satuan'))
            ->order_by('q.id_pembelian', 'DESC')->order_by('b.title', 'ASC')->get()->result_array();

        $purchases = array();
        foreach ($rows as $row) {
            $purchaseId = (int) $row['id_pembelian'];
            if (!isset($purchases[$purchaseId])) {
                $purchases[$purchaseId] = array(
                    'id_pembelian'=>$purchaseId,
                    'no_transaksi'=>$row['no_transaksi'],
                    'tgl_pembelian'=>$row['tgl_pembelian'],
                    'jumlah_item'=>0,
                    'total_qty'=>0,
                    'items'=>array()
                );
            }
            $qty = (float) $row['qty'];
            $purchases[$purchaseId]['jumlah_item']++;
            $purchases[$purchaseId]['total_qty'] += $qty;
            $purchases[$purchaseId]['items'][] = array(
                'title'=>$row['title'],
                'qty'=>$qty,
                'satuan'=>trim((string) $row['satuan']) !== '' ? $row['satuan'] : 'unit'
            );
        }
        return array_values($purchases);
    }

    public function approve_purchase($purchaseId, $userId)
    {
        $this->db->trans_begin();
        $rows = $this->db->query(
            "SELECT q.*, b.harga_jual, p.no_transaksi FROM pembelian_stok_pending q JOIN bahan b ON b.id=q.id_bahan JOIN pembelian p ON p.id_pembelian=q.id_pembelian WHERE q.id_pembelian=? AND q.status='pending' FOR UPDATE",
            array((int)$purchaseId)
        )->result_array();
        if (!$rows) {
            $this->db->trans_rollback();
            return array('status'=>false, 'msg'=>'Tidak ada stok pembelian yang menunggu persetujuan.');
        }

        foreach ($rows as $row) {
            $stock = array(
                'id_bahan'=>(int)$row['id_bahan'], 'jumlah'=>(float)$row['jumlah'],
                'harga_beli'=>(float)$row['harga_beli'], 'harga_jual'=>(float)$row['harga_jual'],
                'create_date'=>date('Y-m-d H:i:s'),
                'ket'=>'Pembelian '.$row['no_transaksi'].' (disetujui)', 'stat'=>1
            );
            if ($this->db->field_exists('id_user', 'stok_masuk')) $stock['id_user'] = (int)$userId;
            $this->db->insert('stok_masuk', $stock);
            $stockId = $this->db->insert_id();
            $this->db->where('id', (int)$row['id'])->where('status', 'pending')->update('pembelian_stok_pending', array(
                'status'=>'approved', 'approved_by'=>(int)$userId,
                'approved_at'=>date('Y-m-d H:i:s'), 'stok_masuk_id'=>(int)$stockId
            ));
        }

        if ($this->db->trans_status() === false) {
            $this->db->trans_rollback();
            return array('status'=>false, 'msg'=>'Persetujuan stok gagal diproses.');
        }
        $this->db->trans_commit();
        return array('status'=>true, 'msg'=>'Pembelian disetujui. Stok masuk sudah ditambahkan.');
    }

    public function has_approved($purchaseId)
    {
        return $this->db->where('id_pembelian', (int)$purchaseId)->where('status', 'approved')
            ->count_all_results('pembelian_stok_pending') > 0;
    }
}
