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

class Coi_disclosure_model extends App_Model
{
    private $t;
    private $ti;
    private $tf;

    public function __construct()
    {
        parent::__construct();
        $this->t  = db_prefix().'coi_disclosures';
        $this->ti = db_prefix().'coi_disclosure_items';
        $this->tf = db_prefix().'coi_disclosure_files';
    }

    public function get($id)
    {
        return $this->db->get_where($this->t, ['id' => (int)$id])->row_array();
    }

    public function get_full($id)
    {
        $decl = $this->get($id);
        if (!$decl) return null;

        $items = $this->db->order_by('question_no', 'ASC')->get_where($this->ti, ['disclosure_id' => (int)$id])->result_array();
        $files = $this->db->order_by('id', 'DESC')->get_where($this->tf, ['disclosure_id' => (int)$id])->result_array();

        return ['decl' => $decl, 'items' => $items, 'files' => $files];
    }

    public function create($header, $items)
    {
        $this->db->insert($this->t, $header);
        $id = $this->db->insert_id();
        if (!$id) return false;

        foreach ($items as $it) {
            $it['disclosure_id'] = (int)$id;
            $this->db->insert($this->ti, $it);
        }
        return $id;
    }

    public function update_declaration($id, $header, $items)
{
    $id = (int)$id;

    // 1) Update header
    $this->db->where('id', $id)->update($this->t, $header);

    // 2) Read existing questions BEFORE deleting items (so we can preserve them)
    $existing = $this->db->select('question_no, question')
        ->from($this->ti)
        ->where('disclosure_id', $id)
        ->get()
        ->result_array();

    $oldQ = [];
    foreach ($existing as $e) {
        $qno = (int)($e['question_no'] ?? 0);
        if ($qno > 0) {
            $oldQ[$qno] = $e['question'];
        }
    }

    // 3) Replace items (delete + insert)
    $this->db->where('disclosure_id', $id)->delete($this->ti);

    if (is_array($items)) {
        foreach ($items as $it) {

            // Normalize / validate
            $qno = (int)($it['question_no'] ?? 0);
            if ($qno <= 0) {
                continue; // invalid row
            }

            // Preserve question text if not submitted during edit
            if (!isset($it['question']) || $it['question'] === null || trim((string)$it['question']) === '') {
                $it['question'] = $oldQ[$qno] ?? null;
            }

            // Defaults (avoid NULL surprises)
            if (!isset($it['answer']) || trim((string)$it['answer']) === '') {
                $it['answer'] = 'No';
            }
            if (!isset($it['details'])) {
                $it['details'] = '';
            }

            // Force disclosure_id + ensure correct question_no
            $row = [
                'disclosure_id' => $id,
                'question_no'   => $qno,
                'question'      => $it['question'],
                'answer'        => $it['answer'],
                'details'       => $it['details'],
            ];

            $this->db->insert($this->ti, $row);
        }
    }

    return true;
}

    public function delete($id)
    {
        $this->db->where('disclosure_id', (int)$id)->delete($this->ti);
        $this->db->where('disclosure_id', (int)$id)->delete($this->tf);
        $this->db->where('id', (int)$id)->delete($this->t);
        return $this->db->affected_rows() > 0;
    }

public function add_file($disclosure_id, $staff_id, $data)
{
    $data['disclosure_id'] = (int)$disclosure_id;
    $data['staff_id']      = (int)$staff_id;
    $data['created_at']    = date('Y-m-d H:i:s');

    $this->db->insert(db_prefix().'coi_disclosure_files', $data);
    return $this->db->insert_id();
}

 public function get_files($disclosure_id)
{
    $this->db->order_by('id', 'DESC');
    return $this->db->get_where(db_prefix().'coi_disclosure_files', [
        'disclosure_id' => (int)$disclosure_id
    ])->result_array();
}

public function get_file($file_id)
{
    return $this->db->get_where(db_prefix().'coi_disclosure_files', [
        'id' => (int)$file_id
    ])->row_array();
}
    public function log_activity($message, $id)
    {
        if (function_exists('log_activity')) {
            log_activity('[COI Disclosure] '.$message.' (ID: '.(int)$id.')', get_staff_user_id());
            return;
        }

        $this->db->insert(db_prefix().'activity_log', [
            'description' => '[COI Disclosure] '.$message.' (ID: '.(int)$id.')',
            'date'        => date('Y-m-d H:i:s'),
            'staffid'     => (int)get_staff_user_id(),
        ]);
    }

   public function get_activity_logs($id, $limit = 80)
    {
        $prefix = '[COI Disclosure #' . (int)$id . ']';

         $this->db->select('a.id, a.description, a.date, a.staffid, s.firstname, s.lastname');
        $this->db->from(db_prefix().'activity_log a');
        $this->db->join(db_prefix().'staff s', 's.staffid = a.staffid', 'left');
        $this->db->like('a.description', $prefix, 'after');
        $this->db->order_by('a.date', 'DESC');
        $this->db->limit((int)$limit);

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

    /** Notify role/staff recipients (same pattern as your working modules) */
    public function notify_recipients($disclosure_id)
    {
        $full = $this->get_full($disclosure_id);
        if (!$full) return;

        $d     = $full['decl'];
        $items = $full['items'];

        $yesCount = 0;
        foreach ($items as $it) {
            if (strtolower((string)$it['answer']) === 'yes') $yesCount++;
        }

        $roleIds  = array_filter(array_map('intval', explode(',', (string)get_option('coi_notify_role_ids'))));
        $staffIds = array_filter(array_map('intval', explode(',', (string)get_option('coi_notify_staff_ids'))));

        $this->db->select('staffid,email,firstname,lastname');
        $this->db->from(db_prefix().'staff');
        $this->db->where('active', 1);

        if (!empty($roleIds)) {
            if ($this->db->field_exists('role', db_prefix().'staff')) $this->db->where_in('role', $roleIds);
            else $this->db->where_in('roleid', $roleIds);
        }
        if (!empty($staffIds)) {
            if (!empty($roleIds)) $this->db->or_where_in('staffid', $staffIds);
            else $this->db->where_in('staffid', $staffIds);
        }

        // If not configured, fallback admins (optional)
        if (empty($roleIds) && empty($staffIds)) {
            $this->db->where('admin', 1);
        }

        $recipients = $this->db->get()->result_array();
        if (empty($recipients)) return;

        $url = admin_url('coi_disclosure/view/'.(int)$disclosure_id);

        foreach ($recipients as $u) {
            if (function_exists('add_notification')) {
                add_notification([
                    'touserid'        => (int)$u['staffid'],
                    'description'     => 'New COI Disclosure submitted by '.$d['staff_name'].' (Yes: '.$yesCount.')',
                    'link'            => $url,
                    'additional_data' => serialize([(int)$disclosure_id]),
                ]);
            }
            $this->send_email_to($u['email'], $full, $url);
        }

        if (function_exists('pusher_trigger_notification')) {
            pusher_trigger_notification($recipients);
        }
    }

    private function send_email_to($toEmail, $full, $url)
    {
        if (empty($toEmail)) return;

        // normalize
        $d     = (isset($full['decl']) && is_array($full['decl'])) ? $full['decl'] : $full;
        $items = (isset($full['items']) && is_array($full['items'])) ? $full['items'] : [];

        $yesCount = 0;
        foreach ($items as $it) {
            if (strtolower((string)($it['answer'] ?? '')) === 'yes') $yesCount++;
        }

        $staffId = $d['staff_code'] ?? ($d['staff_id'] ?? '');
        $subject = 'New COI Disclosure Submitted';

        $message =
            '<p>A new Conflict of Interest and Gift Declaration Disclosure has been submitted.</p>'
            . '<p>'
            . '<strong>Name:</strong> ' . htmlspecialchars($d['staff_name'] ?? '') . '<br>'
            . '<strong>Staff ID:</strong> ' . htmlspecialchars($staffId) . '<br>'
            . '<strong>Department:</strong> ' . htmlspecialchars($d['department'] ?? '') . '<br>'
            . '<strong>Date:</strong> ' . htmlspecialchars($d['signed_date'] ?? '') . '<br>'
            . '<strong>Yes responses:</strong> ' . (int)$yesCount . '<br>'
            . '</p>'
            . '<p>View: <a href="' . $url . '">' . $url . '</a></p>';

        if (function_exists('send_mail')) {
            @send_mail($toEmail, $subject, $message);
            return;
        }

        $CI = &get_instance();
        $CI->load->library('email');
        $CI->email->clear(true);
$fromEmail = 'humancapital@inceif.edu.my';
$fromName  = 'Human Capital INCEIF';
$CI->email->from($fromEmail, $fromName);
$CI->email->reply_to($fromEmail, $fromName);
        $CI->email->to($toEmail);
        $CI->email->subject($subject);
        $CI->email->message($message);
        @$CI->email->send();
    }



    public function get_all_for_export($staffId = null)
    {
        if ($staffId) {
            $this->db->where('staff_id', (int)$staffId);
        }
        $this->db->order_by('id', 'DESC');
        return $this->db->get(db_prefix().'coi_disclosures')->result_array();
    }
    public function get_items_map($disclosureId)
    {
        $rows = $this->db->get_where(db_prefix().'coi_disclosure_items', [
            'disclosure_id' => (int)$disclosureId
        ])->result_array();

        // map by question_no
        $map = [];
        foreach ($rows as $r) {
            $no = (int)($r['question_no'] ?? 0);
            if ($no < 1) continue;
            $map[$no] = [
                'answer'  => $r['answer'] ?? 'No',
                'details' => $r['details'] ?? '',
            ];
        }
        return $map;
    }

    public function delete_file($file_id)
{
    $this->db->where('id', (int)$file_id);
    return $this->db->delete(db_prefix().'coi_disclosure_files');
}

public function delete_all_files($disclosure_id)
{
    $this->db->where('disclosure_id', (int)$disclosure_id);
    return $this->db->delete(db_prefix().'coi_disclosure_files');
}


    public function get_export_rows($ids = [], $staff_id = 0, $can_view_all = false)
{
    $tblD = db_prefix().'coi_disclosures';
    $tblI = db_prefix().'coi_disclosure_items';

    // Base disclosures
    $this->db->from($tblD);

    // Filter by ids if provided
    if (is_array($ids) && count($ids) > 0) {
        $ids = array_map('intval', $ids);
        $this->db->where_in($tblD.'.id', $ids);
    }

    // view_own restriction
    if (!$can_view_all) {
        $this->db->where($tblD.'.staff_id', (int)$staff_id);
    }

    $this->db->order_by($tblD.'.id', 'DESC');
    $decls = $this->db->get()->result_array();

    if (!$decls) return [];

    // Collect IDs
    $declIds = array_map(function($r){ return (int)$r['id']; }, $decls);

    // Fetch items for all disclosures in one query
    $this->db->from($tblI);
    $this->db->where_in('disclosure_id', $declIds);
    $items = $this->db->get()->result_array();

    // Map items: [disclosure_id][question_no] => ['answer'=>..., 'details'=>...]
    $map = [];
    foreach ($items as $it) {
        $did = (int)$it['disclosure_id'];
        $qno = (int)$it['question_no'];
        if ($qno < 1 || $qno > 15) continue;

        $map[$did][$qno] = [
            'answer'  => $it['answer'] ?? 'No',
            'details' => $it['details'] ?? '',
        ];
    }

    // Flatten output rows
    $out = [];
    foreach ($decls as $d) {
        $did = (int)$d['id'];

        $yesCount = 0;
        for ($i=1; $i<=15; $i++) {
            $ans = $map[$did][$i]['answer'] ?? 'No';
            if (strtolower(trim($ans)) === 'yes') $yesCount++;
        }

        $row = [
            'disclosure_id' => $did,
            'staff_id'      => $d['staff_id'],
            'staff_name'    => $d['staff_name'] ?? '',
            'staff_code'    => $d['staff_code'] ?? '',
            'position'      => $d['position'] ?? '',
            'department'    => $d['department'] ?? '',
            'signature'     => $d['signature'] ?? '',
            'signed_date'   => $d['signed_date'] ?? '',
            'created_at'    => $d['created_at'] ?? '',
            'updated_at'    => $d['updated_at'] ?? '',
            'yes_count'     => $yesCount,
        ];

        for ($i=1; $i<=15; $i++) {
            $row["q{$i}_answer"]  = $map[$did][$i]['answer'] ?? 'No';
            $row["q{$i}_details"] = $map[$did][$i]['details'] ?? '';
        }

        $out[] = $row;
    }

    return $out;
}

}
