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

class Coi_special_approval_model extends App_Model
{
  protected $t  = '';
  protected $tf = '';

  public function __construct()
  {
    parent::__construct();
    $this->t  = db_prefix().'coi_special_approvals';
    $this->tf = db_prefix().'coi_special_approval_files';
  }

  public function create($header)
  {
    $this->db->insert($this->t, $header);
    return $this->db->insert_id();
  }

  public function update_row($id, $data)
  {
    $this->db->where('id', (int)$id)->update($this->t, $data);
    return $this->db->affected_rows() >= 0;
  }

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

   public function count_rows($staff_id, $can_view_all)
    {
        if (!$can_view_all) {
            $this->db->where('staff_id', (int)$staff_id);
        }
        return (int)$this->db->count_all_results($this->t);
    }


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

        // Safety: don't allow changing primary keys / ownership via update payload
        unset($header['id'], $header['staff_id'], $header['staff_name'], $header['created_at']);

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

        return $this->db->affected_rows() >= 0;
    }

  public function get_files($id)
  {
    return $this->db->where('special_id', (int)$id)->get($this->tf)->result_array();
  }

  public function add_file($id, $staff_id, $data)
  {
    $row = [
      'special_id'    => (int)$id,
      'staff_id'      => (int)$staff_id,
      'file_name'     => $data['file_name'],
      'original_name' => $data['original_name'] ?? null,
      'filetype'      => $data['filetype'] ?? null,
      'filesize'      => $data['filesize'] ?? null,
      'created_at'    => date('Y-m-d H:i:s'),
    ];
    $this->db->insert($this->tf, $row);
    return $this->db->insert_id();
  }

  public function delete_file($file_id)
  {
    $file = $this->db->where('id', (int)$file_id)->get($this->tf)->row_array();
    if (!$file) return false;

    $this->db->where('id', (int)$file_id)->delete($this->tf);
    return $file;
  }

  public function approve($id, $approver_id, $status, $comment)
  {
    $status = in_array($status, ['approved','rejected'], true) ? $status : 'approved';

    return $this->update_row($id, [
      'status'           => $status,
      'approved_by'      => (int)$approver_id,
      'approved_at'      => date('Y-m-d H:i:s'),
      'approval_comment' => $comment ?: null,
      'updated_at'       => date('Y-m-d H:i:s'),
    ]);
  }
  
  public function get_activity_logs($pact_id, $limit = 80)
    {
        $prefix = '[Conflict of Interest Disclosure #' . (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();
    }

  public function get_export_rows($ids, $staff_id, $can_view_all)
  {
    if (!is_array($ids) || empty($ids)) {
      // export all allowed
      if (!$can_view_all) {
        $this->db->where('staff_id', (int)$staff_id);
      }
      return $this->db->order_by('id', 'DESC')->get($this->t)->result_array();
    }

    $this->db->where_in('id', array_map('intval', $ids));
    if (!$can_view_all) {
      $this->db->where('staff_id', (int)$staff_id);
    }
    return $this->db->get($this->t)->result_array();
  }

  public function get_notify_recipients()
  {
    $roleIds  = array_filter(array_map('intval', explode(',', (string)get_option('csa_notify_role_ids'))));
    $staffIds = array_filter(array_map('intval', explode(',', (string)get_option('csa_notify_staff_ids'))));

    $recipients = [];

    // by role
    if (!empty($roleIds)) {
      $rows = $this->db->select('s.staffid,s.email,s.firstname,s.lastname')
        ->from(db_prefix().'staff s')
        //->join(db_prefix().'roles sr', 'sr.staff_id = s.staffid', 'left')
        ->join(db_prefix().'roles', db_prefix().'roles.roleid = s.role', 'left')
        ->where('s.active', 1)
        ->where_in('roles.roleid', $roleIds)
        ->get()->result_array();
      foreach ($rows as $r) $recipients[(int)$r['staffid']] = $r;
    }

    // explicit staff
    if (!empty($staffIds)) {
      $rows = $this->db->select('staffid,email,firstname,lastname')
        ->from(db_prefix().'staff')
        ->where('active', 1)
        ->where_in('staffid', $staffIds)
        ->get()->result_array();
      foreach ($rows as $r) $recipients[(int)$r['staffid']] = $r;
    }

    return array_values($recipients);
  }
   /**
     * Delete ALL file records for a request (disk delete handled in controller)
     */
    public function delete_all_files($request_id)
    {
        $this->db->where('request_id', (int)$request_id)->delete($this->tf);
        return $this->db->affected_rows() >= 0;
    }
    public function delete_request($id)
{
    $id = (int)$id;

    // 1) delete files rows (and any other child tables if you have them)
    if ($this->db->table_exists($this->tf)) {
        $this->db->where('special_id', $id)->delete($this->tf);
    }

    // If you later add items table, uncomment and set $this->ti:
    // if ($this->db->table_exists($this->ti)) {
    //     $this->db->where('request_id', $id)->delete($this->ti);
    // }

    // 2) delete header row
    $this->db->where('id', $id)->delete($this->t);

    return $this->db->affected_rows() > 0;
}

}
