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

class Integrity_pact_model extends App_Model
{
    protected $t  = 'integrity_pacts';
    protected $tf = 'integrity_pact_files';


    public function create_request($header)
{
    $tbl = db_prefix() . $this->t;

    $staffId = (int) ($header['staff_id'] ?? 0);
    $year    = (int) ($header['year'] ?? 0);

    // enforce unique staff/year
    $exists = $this->db->where('staff_id', $staffId)
        ->where('year', $year)
        ->get($tbl)
        ->row_array();

    if ($exists) {
        $id = (int) $exists['id'];

        // Don't overwrite uniqueness fields / created_at
        unset($header['id'], $header['staff_id'], $header['year'], $header['created_at']);

        // Update timestamp
        $header['updated_at'] = date('Y-m-d H:i:s');

        $this->db->where('id', $id)->update($tbl, $header);

        log_activity(
    '[Integrity Declaration #' . (int)$id . '] Updated by ' . get_staff_full_name($staffId),
    $staffId
);

        return $id;
    }

    // Insert
    $header['created_at'] = $header['created_at'] ?? date('Y-m-d H:i:s');
    $header['updated_at'] = $header['updated_at'] ?? null;

    $this->db->insert($tbl, $header);

    if ($this->db->affected_rows() > 0) {
        return (int) $this->db->insert_id();
    }

    return false;
}


    public function create_request__OL($header)
    {
        $tbl = db_prefix().$this->t;

        // enforce unique staff/year
        $exists = $this->db->where('staff_id', (int)$header['staff_id'])
            ->where('year', (int)$header['year'])
            ->get($tbl)->row_array();

        if ($exists) {
          $this->db->update($tbl, $header);
            return (int)$exists['id'];
        }

        $this->db->insert($tbl, $header);
        if ($this->db->affected_rows() > 0) {
            return (int)$this->db->insert_id();
        }

        return false;
    }

    public function update_request($id, $header)
    {
        $this->db->where('id', (int)$id)->update(db_prefix().$this->t, $header);
        return true;
    }

    public function get($id)
    {
        return $this->db->where('id', (int)$id)->get(db_prefix().$this->t)->row_array();
    }

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

        $files = $this->db->where('pact_id', (int)$id)
            ->order_by('id', 'DESC')
            ->get(db_prefix().$this->tf)->result_array();

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

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

    public function add_file($pact_id, $row)
    {
        $row['pact_id'] = (int)$pact_id;
        $this->db->insert(db_prefix().$this->tf, $row);
        return (int)$this->db->insert_id();
    }

    public function get_file($pact_id, $file_id)
    {
        return $this->db->where('pact_id', (int)$pact_id)
            ->where('id', (int)$file_id)
            ->get(db_prefix().$this->tf)->row_array();
    }

    public function get_staff_hod($staff_id)
    {
        $row = $this->db->select('hod')
            ->from(db_prefix().'staff')
            ->where('staffid', (int)$staff_id)
            ->get()->row_array();

        return (int)($row['hod'] ?? 0);
    }

    public function get_staff_row($staff_id)
    {
        return $this->db->select('staffid, firstname, lastname, email, hod, position, inceif_staff_id, active')
            ->from(db_prefix().'staff')
            ->where('staffid', (int)$staff_id)
            ->get()->row_array();
    }

    public function get_notify_recipients()
    {
        $role_ids  = array_filter(array_map('intval', explode(',', (string)get_option('ip_notify_role_ids'))));
        $staff_ids = array_filter(array_map('intval', explode(',', (string)get_option('ip_notify_staff_ids'))));

        $recipients = [];

        if (!empty($role_ids)) {
            $roleUsers = $this->db->select('s.staffid, s.email, s.firstname, s.lastname')
                ->from(db_prefix().'staff s')
                ->join(db_prefix().'staff_roles sr', 'sr.staffid=s.staffid', 'inner')
                ->where('s.active', 1)
                ->where_in('sr.roleid', $role_ids)
                ->get()->result_array();

            foreach ($roleUsers as $u) $recipients[(int)$u['staffid']] = $u;
        }

        if (!empty($staff_ids)) {
            $staffUsers = $this->db->select('staffid, email, firstname, lastname')
                ->from(db_prefix().'staff')
                ->where('active', 1)
                ->where_in('staffid', $staff_ids)
                ->get()->result_array();

            foreach ($staffUsers as $u) $recipients[(int)$u['staffid']] = $u;
        }

        return array_values($recipients);
    }

    public function get_activity_logs($pact_id, $limit = 80)
    {
        $prefix = '[Integrity Declaration #' . (int)$pact_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();
    }
}
