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

class Komisi_sales_model extends CI_Model
{
    private $menuParentId = 155;
    private $menuName = 'Komisi Desain';

    public function __construct()
    {
        parent::__construct();
        $this->load->dbforge();
    }

    public function ensureInfoSetting()
    {
        if (!$this->db->table_exists('info')) {
            return;
        }

        if (!$this->db->field_exists('komisi_sales_aktif', 'info')) {
            $this->dbforge->add_column('info', [
                'komisi_sales_aktif' => [
                    'type' => 'INT',
                    'constraint' => 1,
                    'null' => false,
                    'default' => 1,
                ],
            ]);
        }
    }

    public function isFeatureEnabled()
    {
        if (!$this->db->table_exists('info')) {
            return true;
        }

        $this->ensureInfoSetting();

        $row = $this->db
            ->select('komisi_sales_aktif')
            ->from('info')
            ->where('id', 1)
            ->limit(1)
            ->get()
            ->row_array();

        if (!$row || !array_key_exists('komisi_sales_aktif', $row)) {
            return true;
        }

        return (int) $row['komisi_sales_aktif'] === 1;
    }

    public function setFeatureEnabled($enabled)
    {
        $enabled = (int) $enabled === 1 ? 1 : 0;
        $this->ensureInfoSetting();

        if ($this->db->table_exists('info') && $this->db->field_exists('komisi_sales_aktif', 'info')) {
            $this->db
                ->where('id', 1)
                ->update('info', ['komisi_sales_aktif' => $enabled]);
        }

        $this->syncMenuVisibility($enabled === 1);
    }

    public function syncMenuVisibility($enabled)
    {
        return $this->ensureMenuEntry($enabled === true || (int) $enabled === 1);
    }

    public function ensureMenuEntry($active = null)
    {
        if (!$this->db->table_exists('menuadmin')) {
            return 0;
        }

        if ($active === null) {
            $active = $this->isFeatureEnabled();
        }
        $activeFlag = $active ? 'Y' : 'N';

        $menu = $this->db
            ->select('idmenu, aktif, nama_menu')
            ->from('menuadmin')
            ->where('link', 'komisi-sales')
            ->limit(1)
            ->get()
            ->row_array();

        if (!empty($menu['idmenu'])) {
            $updates = [];
            if (!isset($menu['aktif']) || $menu['aktif'] !== $activeFlag) {
                $updates['aktif'] = $activeFlag;
            }
            if (!isset($menu['nama_menu']) || $menu['nama_menu'] !== $this->menuName) {
                $updates['nama_menu'] = $this->menuName;
            }

            if (!empty($updates)) {
                $this->db
                    ->where('idmenu', (int) $menu['idmenu'])
                    ->update('menuadmin', $updates);
                $this->syncMenuUsersByLevels('1,2,3,4,5,6');
                $this->clearSidebarMenuCache();
            }

            return (int) $menu['idmenu'];
        }

        $parent = $this->db
            ->select('idmenu')
            ->from('menuadmin')
            ->where('idmenu', $this->menuParentId)
            ->limit(1)
            ->get()
            ->row_array();

        if (empty($parent['idmenu'])) {
            return 0;
        }

        $nextOrderRow = $this->db
            ->select_max('urutan')
            ->get('menuadmin')
            ->row_array();

        $nextOrder = isset($nextOrderRow['urutan']) ? ((int) $nextOrderRow['urutan'] + 1) : 1;

        $this->db->insert('menuadmin', [
            'idparent' => (int) $parent['idmenu'],
            'id_level' => '1,2,3,4,5,6',
            'nama_menu' => $this->menuName,
            'link' => 'komisi-sales',
            'target' => '_self',
            'link_on' => 'N',
            'treeview' => '',
            'classes' => '',
            'classicon' => 'N',
            'icon' => '',
            'aktif' => $activeFlag,
            'level' => 'admin',
            'urutan' => $nextOrder,
        ]);

        $menuId = (int) $this->db->insert_id();
        if ($menuId > 0) {
            $this->syncMenuUsersByLevels('1,2,3,4,5,6');
            $this->clearSidebarMenuCache();
        }

        return $menuId;
    }

    public function ensureSchema()
    {
        $this->ensureUserCommissionSchema();
        $this->ensureBahanCommissionSchema();

        if (!$this->db->table_exists('invoice')) {
            return;
        }

        $fields = [];

        if (!$this->db->field_exists('id_sales_komisi', 'invoice')) {
            $fields['id_sales_komisi'] = [
                'type' => 'INT',
                'constraint' => 11,
                'null' => false,
                'default' => 0,
            ];
        }

        if (!$this->db->field_exists('komisi_persen', 'invoice')) {
            $fields['komisi_persen'] = [
                'type' => 'FLOAT',
                'null' => false,
                'default' => 0,
            ];
        }

        if (!$this->db->field_exists('komisi_nominal', 'invoice')) {
            $fields['komisi_nominal'] = [
                'type' => 'INT',
                'constraint' => 11,
                'null' => false,
                'default' => 0,
            ];
        }

        if (!empty($fields)) {
            $this->dbforge->add_column('invoice', $fields);
        }
    }

    public function getSalesOptions()
    {
        $this->ensureSchema();

        return $this->db
            ->select('id_user, nama_lengkap, komisi_desain_persen')
            ->from('tb_users')
            ->where('aktif', 'Y')
            ->where('komisi_desain_aktif', 1)
            ->order_by('nama_lengkap', 'ASC')
            ->get()
            ->result_array();
    }

    public function saveInvoiceCommissionMeta($invoiceId, $salesId, $percent)
    {
        $this->ensureSchema();

        $invoiceId = (int) $invoiceId;
        if ($invoiceId <= 0) {
            return false;
        }

        $salesId = (int) $salesId;
        $percent = $salesId > 0 ? $this->getUserCommissionPercent($salesId) : 0;

        $this->db
            ->where('id_invoice', $invoiceId)
            ->update('invoice', [
                'id_sales_komisi' => $salesId,
                'komisi_persen' => $percent,
            ]);

        $this->syncInvoiceCommissionAmount($invoiceId);

        return true;
    }

    public function syncInvoiceCommissionAmount($invoiceId)
    {
        $this->ensureSchema();

        $invoiceId = (int) $invoiceId;
        if ($invoiceId <= 0) {
            return 0;
        }

        $row = $this->db
            ->select('id_sales_komisi')
            ->from('invoice')
            ->where('id_invoice', $invoiceId)
            ->get()
            ->row_array();

        if (!$row) {
            return 0;
        }

        $salesId = isset($row['id_sales_komisi']) ? (int) $row['id_sales_komisi'] : 0;
        $percent = $salesId > 0 ? $this->getUserCommissionPercent($salesId) : 0;
        $total = $this->getEligibleInvoiceSubtotal($invoiceId);

        $nominal = 0;
        if ($salesId > 0 && $percent > 0 && $total > 0) {
            $nominal = (int) round(($total * $percent) / 100);
        }

        $this->db
            ->where('id_invoice', $invoiceId)
            ->update('invoice', [
                'komisi_persen' => $percent,
                'komisi_nominal' => $nominal,
            ]);

        return $nominal;
    }

    public function getRows($params = [])
    {
        $this->ensureSchema();
        $applyFilters = function () use ($params) {
            $this->db->from('invoice i');
            $this->db->join('('.$this->eligibleInvoiceSubtotalSubquery().') kd', 'kd.id_invoice = i.id_invoice', 'inner', false);
            $this->db->join('konsumen k', 'k.id = i.id_konsumen', 'left');
            $this->db->join('tb_users s', 's.id_user = i.id_sales_komisi', 'left');
            $this->db->join('tb_users kasir', 'kasir.id_user = i.id_marketing', 'left');
            $this->db->where('i.status !=', 'batal');
            $this->db->where('i.id_sales_komisi >', 0);
            $this->db->where('kd.komisi_total_item >', 0);
            $this->db->where('s.komisi_desain_aktif', 1);
            $this->db->where('s.komisi_desain_persen >', 0);

            if (!empty($params['search']['dari']) && !empty($params['search']['sampai'])) {
                $this->db->where('DATE(i.tgl_trx) >=', date('Y-m-d', strtotime($params['search']['dari'])));
                $this->db->where('DATE(i.tgl_trx) <=', date('Y-m-d', strtotime($params['search']['sampai'])));
            }

            if (!empty($params['search']['sales_id'])) {
                $this->db->where('i.id_sales_komisi', (int) $params['search']['sales_id']);
            }
        };

        if (!empty($params['returnType']) && $params['returnType'] === 'count') {
            $this->db->select('COUNT(*) AS allcount', false);
            $applyFilters();
            $row = $this->db->get()->row_array();

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

        $this->db->select("
            i.id_invoice,
            i.id_transaksi,
            i.tgl_trx,
            kd.komisi_total_item AS total_bayar,
            s.komisi_desain_persen AS komisi_persen,
            ROUND((kd.komisi_total_item * s.komisi_desain_persen) / 100) AS komisi_nominal,
            i.id_sales_komisi,
            IFNULL(NULLIF(TRIM(k.nama), ''), 'Umum') AS nama_pelanggan,
            IFNULL(NULLIF(TRIM(s.nama_lengkap), ''), '-') AS nama_sales,
            IFNULL(NULLIF(TRIM(kasir.nama_lengkap), ''), '-') AS nama_kasir
        ", false);
        $applyFilters();
        $this->db->order_by('i.tgl_trx', 'DESC');
        $this->db->order_by('i.id_invoice', 'DESC');

        if (isset($params['start']) && isset($params['limit'])) {
            $this->db->limit((int) $params['limit'], (int) $params['start']);
        } elseif (isset($params['limit'])) {
            $this->db->limit((int) $params['limit']);
        }

        return $this->db->get()->result_array();
    }

    private function ensureUserCommissionSchema()
    {
        if (!$this->db->table_exists('tb_users')) {
            return;
        }

        $fields = [];
        if (!$this->db->field_exists('komisi_desain_aktif', 'tb_users')) {
            $fields['komisi_desain_aktif'] = [
                'type' => 'INT',
                'constraint' => 1,
                'null' => false,
                'default' => 0,
            ];
        }
        if (!$this->db->field_exists('komisi_desain_persen', 'tb_users')) {
            $fields['komisi_desain_persen'] = [
                'type' => 'FLOAT',
                'null' => false,
                'default' => 0,
            ];
        }

        if (!empty($fields)) {
            $this->dbforge->add_column('tb_users', $fields);
        }
    }

    private function ensureBahanCommissionSchema()
    {
        if (!$this->db->table_exists('bahan')) {
            return;
        }

        if (!$this->db->field_exists('komisi_desain_aktif', 'bahan')) {
            $this->dbforge->add_column('bahan', [
                'komisi_desain_aktif' => [
                    'type' => 'INT',
                    'constraint' => 1,
                    'null' => false,
                    'default' => 0,
                ],
            ]);
        }
    }

    private function getUserCommissionPercent($userId)
    {
        $userId = (int) $userId;
        if ($userId <= 0 || !$this->db->table_exists('tb_users')) {
            return 0;
        }

        $row = $this->db
            ->select('komisi_desain_aktif, komisi_desain_persen')
            ->from('tb_users')
            ->where('id_user', $userId)
            ->where('aktif', 'Y')
            ->limit(1)
            ->get()
            ->row_array();

        if (!$row || (int) $row['komisi_desain_aktif'] !== 1) {
            return 0;
        }

        return $this->normalizePercent($row['komisi_desain_persen']);
    }

    private function getEligibleInvoiceSubtotal($invoiceId)
    {
        $invoiceId = (int) $invoiceId;
        if (
            $invoiceId <= 0 ||
            !$this->db->table_exists('invoice_detail') ||
            !$this->db->table_exists('bahan') ||
            !$this->db->field_exists('komisi_desain_aktif', 'bahan')
        ) {
            return 0;
        }

        $row = $this->db
            ->select('ROUND(SUM((d.jumlah * d.harga) - ((d.jumlah * d.harga) * (d.diskon / 100)))) AS subtotal', false)
            ->from('invoice_detail d')
            ->join('bahan b', 'b.id = d.id_bahan', 'inner')
            ->where('d.id_invoice', $invoiceId)
            ->where('b.komisi_desain_aktif', 1)
            ->get()
            ->row_array();

        return isset($row['subtotal']) ? (float) $row['subtotal'] : 0;
    }

    private function eligibleInvoiceSubtotalSubquery()
    {
        return '
            SELECT
                d.id_invoice,
                ROUND(SUM((d.jumlah * d.harga) - ((d.jumlah * d.harga) * (d.diskon / 100)))) AS komisi_total_item
            FROM invoice_detail d
            INNER JOIN bahan b ON b.id = d.id_bahan AND b.komisi_desain_aktif = 1
            GROUP BY d.id_invoice
        ';
    }

    private function normalizePercent($percent)
    {
        $percent = str_replace(',', '.', (string) $percent);
        $percent = is_numeric($percent) ? (float) $percent : 0;

        if ($percent < 0) {
            $percent = 0;
        }
        if ($percent > 100) {
            $percent = 100;
        }

        return $percent;
    }

    private function syncMenuUsersByLevels($levels)
    {
        if (!$this->db->table_exists('tb_users') || !$this->db->table_exists('hak_akses')) {
            return;
        }
        if (!function_exists('atur_menu')) {
            return;
        }

        $publishedLevels = $this->db
            ->select('id_level')
            ->from('hak_akses')
            ->where('publish', 'Y')
            ->get()
            ->result();

        if (empty($publishedLevels)) {
            return;
        }

        foreach ($publishedLevels as $row) {
            $levelId = isset($row->id_level) ? (int) $row->id_level : 0;
            if ($levelId <= 0) {
                continue;
            }

            $menuIds = atur_menu($levelId);
            $this->db
                ->where('id_level', $levelId)
                ->update('tb_users', ['idmenu' => $menuIds]);
        }
    }

    private function clearSidebarMenuCache()
    {
        $cacheDir = APPPATH . 'cache' . DIRECTORY_SEPARATOR;
        foreach (glob($cacheDir . 'sidebar_menu_*') ?: [] as $file) {
            if (is_file($file)) {
                @unlink($file);
            }
        }
    }
}
