<?php
if (!defined('BASEPATH')) {
    exit('No direct script access allowed');
}

class Wallet_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct(array('no_cache' => 1));

        $this->load->model('crud_model');
        $this->load->model('accounting_model');
        $this->load->database();
    }


    public function payment_total(int $student_id)
    {
        $query = $this->db->get_where('wallet', ['Student_ID' => $student_id, 'Linked' => '0']);
        $data = $query->result_array();
        $total = 0;
        foreach ($data as $row) {
            if ($row['Status'] != "2" && $row['Status'] != "3") {
                $total =  $total + (float) $row['Payment_amount'];
            }
        }
        $linked_wallet = $this->linked_payment($student_id);
        return $total + $linked_wallet;
    }

    public function linked_payment($studentID)
    {
        $this->db->select('*');
        $query = $this->db->get_where('wallet', array('Linked' => '1','Student_ID' => $studentID));
        $data = $query->result_array();
        $linked_wallet = "0";

        foreach ($data as $transaction) {
            $div = $transaction['Linked_repartition'];

            if ($div > 0 && $div < 100) {
                $linked_wallet = $linked_wallet + (($transaction['Payment_amount'] * $div) / 100);
            } elseif ($div == 100) {
                $linked_wallet = $linked_wallet + $transaction['Payment_amount'];
            }
        }
        return $linked_wallet;
    }

    public function payment_last($studentID)
    {
        $this->db->order_by("ID", "DESC");
        $query = $this->db->get_where('wallet', array('Student_ID' => $studentID));
        $data = $query->result_array();

        foreach ($data as $row) {
            return date("d-m-Y", strtotime($row['Date']));
        }
    }
    public function payment_last_amount($studentID)
    {
        $this->db->order_by("ID", "DESC");
        $query = $this->db->get_where('wallet', array('Student_ID' => $studentID));
        $data = $query->result_array();

        foreach ($data as $row) {
            return $row['Payment_amount'];
        }
    }

    public function get_discount($studentID)
    {
        $query = $this->db->get_where('discount', array('student_id' => $studentID));
        $data = $query->result_array();
        $total = "0";
        foreach ($data as $row) {
            $total = $total + $row['Discount_amount'];
        }
        return $total;
    }

    public function is_vat_course($courseID)
    {
        $query = $this->db->get_where('course_new', array('ID' => $courseID));
        $data = $query->result_array();
        foreach ($data as $row) {
            if ($row['CourseVat'] == "yes") {
                return true;
            }
        }
        return false;
    }

    public function is_factured_course($courseID)
    {
        $query = $this->db->get_where('course_new', array('ID' => $courseID));
        $data = $query->result_array();
        foreach ($data as $row) {
            if ($row['CourseFactured'] == "1") {
                return true;
            }
        }
        return false;
    }

    public function get(int $id): array
    {
        $result = $this->db->get_where('invoices', ['ID' => $id])->result();
        return isset($result[0]) ? $result[0] : null;
    }

    public function get_invoices(bool $include_archived = false, bool $include_wallet = false): array
    {
        $this->db->order_by('ID', 'DESC');
        $where = $include_archived ? [] : ['archived' => 0];
        $query = $this->db->get_where('invoices', $where);
        $invoices = $query->result_array();

        // Add wallets if necessary
        if ($include_wallet) {
            $transactions_ids = array_map(fn ($i) => json_decode($i['Transaction_ID'])->transactions, $invoices);
            $transactions_ids = array_unique(array_merge(...$transactions_ids));
            $wallets_raw = $this->db->where_in('ID', $transactions_ids)->get('wallet')->result_array();
            $wallets_tree = [];

            foreach ($wallets_raw as $w) {
                $wallets_tree[(int) $w['ID']] = $w;
            }

            foreach ($invoices as $k => $i) {
                $tids = json_decode($i['Transaction_ID'])->transactions;
                foreach ($tids as $tid) {
                    $invoices[$k]['transactions'][] = $wallets_tree[(int) $tid];
                }
            }
        }

        return $invoices;
    }

    public function get_user_invoices($user)
    {
        $this->db->order_by('ID', 'DESC');
        $query = $this->db->get_where('invoices', array('Customer_ID' => $user));
        //$query = $this->db->get('invoices');
        return $query->result_array();
    }

    public function get_child_invoices($parent)
    {
        $sql = "SELECT i.* FROM `invoices` as i
                JOIN student as s
                ON i.Customer_ID = s.student_id
                WHERE s.parent_id = $parent
                ORDER by i.ID";
        $query = $this->db->query($sql)->result_array();

        foreach ($query as $key => $invoice) {
            $tr = json_decode($invoice['Transaction_ID'], true)['transactions'];
            $paid = 0.0;
            foreach ($tr as $t) {
                $paid = $paid + $this->get_transaction_amount($t);
            }
            $query[$key]['paid'] = $paid;
        }
        return $query;
    }

    public function get_ncs(bool $include_archived = false): array
    {
        $this->db->order_by('ID', 'ASC');
        $where = $include_archived ? [] : ['archived' => 0];
        $query = $this->db->get_where('credit_note', $where);
        return $query->result_array();
    }
    public function get_nc($id)
    {
        $query = $this->db->get_where('credit_note', array('ID' => $id));
        $query = $query->result_array();
        //$last_nc = array("last_invoice" => $this->accounting_model->get_last_nc($id));
        $query[0]['user_name'] = $this->crud_model->get_student_name($query[0]['Customer_ID']);
        $query[0]['last_invoice'] = $this->accounting_model->get_last_nc($query[0]['Customer_ID']);
        //return $query;

        foreach ($query as $row) {

            return $row;
        }
    }
    public function get_invoice($id)
    {
        $query = $this->db->get_where('invoices', array('ID' => $id));
        $query = $query->result_array();
        foreach ($query as $row) {
            return $row;
        }
    }

    public function get_user_invoice($id)
    {
        $this->db->where('Customer_ID', $id);
        $this->db->or_where('Customer2_ID', $id);

        $query = $this->db->get('invoices');
        $query = $query->result_array();

        return $query;
    }
    public function get_credit_note($invoice_id)
    {
        $query = $this->db->get_where('credit_note', array('ID' => $invoice_id));
        $query = $query->result_array();

        if (empty($query)) {
            return false;
        } else {
            //return $query;
            foreach ($query as $note) {
                return array(
                  'Credit_Note_ID' => $note['ID'],
                  'Credit_Note_Date' => $note['Date'],
                  'Credit_Note_Comment' => $note['Comment']
                  );
            }
        }
    }
    public function get_credit_note_bis($invoice_id)
    {
        $query = $this->db->get_where('credit_note', array('ID' => $invoice_id));
        $query = $query->result_array();

        if (empty($query)) {
            return false;
        } else {
            return $query;

        }
    }

    public function get_invoice_wallet($wallet_id)
    {
        $query = $this->db->get_where('invoices', array('Wallet_ID' => $wallet_id));
        //return $this->db->last_query();
        $array = $query->result_array();

        foreach ($array as $row) {
            return $row['ID'];
        }
    }
    public function get_wallet_invoice($invoice_id)
    {
        $query = $this->db->get_where('invoices', array('ID' => $invoice_id));
        //return $this->db->last_query();
        $array = $query->result_array();

        foreach ($array as $row) {
            return $row['Wallet_ID'];
        }
    }

    public function wallet_balance($student_id)
    {
        $courses = $this->course_model->get_courses_by_student_id($student_id, true);
        $discount = $this->get_discount($student_id);

        $vat = 21; // set vat in db
        $grandTotal = 0;

        /*if ((int) $student_id === 7434) {
            echo 'a';
        }*/

        foreach ($courses as $course) {
            $total = 0;

            if ((int) $course->CourseFactured === 1) {
                $total = (float) $course->CoursePrice * (int) $course->CourseContract;
                //vat check
                if ($course->CourseVat === 'yes') {
                    $vatPrice = (($total / 100) * $vat);
                    $total += $vatPrice;
                }
                $grandTotal = round(($grandTotal + $total), 0);
            }
        }
        //discount check
        if ($discount > 0) {
            $grandTotal = $grandTotal - $discount;
        }
        return round(($this->payment_total($student_id) - $grandTotal), 2); // arrondi au centime
    }

    public function get_students_wallet_spend(): array
    {
        $query = $this->db->query(
            "SELECT cs.student_id, cs.course_id
            FROM `course_student_new` cs
            WHERE cs.student_id != 0 AND cs.course_id != 0 
            GROUP BY cs.student_id, cs.course_id"
        );
        $courses_students = $query->result();
        $courses = [];
        foreach ($this->course_model->listing_all_courses(null, null, true) as $course) {
            $courses[(int) $course->ID] = $course;
        }

        $students_balances = [];
        foreach ($courses_students as $cs) {
            if (!isset($students_balances[(int) $cs->student_id])) {
                $students_balances[(int) $cs->student_id] = 0.0;
            }
            $course = $courses[(int) $cs->course_id];
            if (empty($course) || $course->CourseFactured !== '1') {
                continue;
            }

            $course_total = (float) $course->CoursePrice * (float) $course->CourseContract * ($course->CourseVat === 'yes' ? 1.21 : 1);
            $students_balances[(int) $cs->student_id] = round(($students_balances[(int) $cs->student_id] + $course_total), 0);
        }

        return $students_balances;
    }

    public function check_if_factured($id)
    {
        $query = $this->db->get_where('invoices', array('Wallet_ID' => $id));
        $res = $query->result_array();
        foreach ($res as $row) {
            return $row['Status'];
        }
        return false;
    }

    public function get_transaction_user($id)
    {
        $query = $this->db->get_where('wallet', array('ID' => $id));
        $res = $query->result_array();
        foreach ($res as $row) {
            return $row['Student_ID'];
        }
    }

    public function get_transaction_amount($id)
    {
        $query = $this->db->get_where('wallet', array('ID' => $id));
        $res = $query->result_array();
        // TODO: Linked_repartition must be an integer in database, not a text field.
        foreach ($res as $row) {
            if ($row['Linked_repartition'] == "" || $row['Linked_repartition'] == null) {
                return $row['Payment_amount'];
            }
            return $row['Payment_amount'] * ($row['Linked_repartition'] / 100);
        }
    }

    public function get_transactions_by_ids(array $ids): array
    {
        if (empty($ids)) {
            return [];
        }
        $this->db->select('*');
        $this->db->from('wallet');
        $this->db->where_in('ID', $ids);
        $res = $this->db->get()->result_array();
        return $res;
    }

    public function get_transactions_by_ids_complete(array $ids): array
    {
        if (empty($ids)) {
            return [];
        }
        $this->db->select('wallet.*, shop_transaction.Stripe_ID, shop_transaction.Products');
        $this->db->from('wallet');
        $this->db->join('shop_transaction', 'shop_transaction.ID = wallet.Payment_ID', 'left');
        $this->db->where_in('wallet.ID', $ids);
        $res = $this->db->get()->result_array();
        return $res;
    }

    public function get_transaction_by_id(int $id): ?stdClass
    {
        if (empty($id)) {
            return null;
        }
        $this->db->select('*');
        $this->db->from('shop_transaction');
        $this->db->where('ID', $id);
        $result = $this->db->get()->result();

        return isset($result[0]) ? $result[0] : null;
    }

    public function get_transaction_date($id)
    {
        $query = $this->db->get_where('wallet', array('ID' => $id));
        $res = $query->result_array();
        foreach ($res as $row) {
            return $row['Date'];
        }
    }
    public function get_transaction_method($id)
    {
        $query = $this->db->get_where('wallet', array('ID' => $id));
        $res = $query->result_array();
        foreach ($res as $row) {
            if ($row['Method_ID'] == 1) {
                return "bank transfer";
            } elseif ($row['Method_ID'] == 2) {
                return "cash";
            } else {
                return "online payment";
            }
        }
    }

    public function get_transaction_status($id)
    {
        $query = $this->db->get_where('wallet', array('ID' => $id));
        $res = $query->result_array();
        foreach ($res as $row) {
            if ($row['Status'] == null) {
                return "0";
            }
            return $row['Status'];
        }
    }


    public function get_transactions()
    {
        $query = $this->db->get('wallet');
        $res = $query->result_array();
        return $res;
    }

    public function get_invoice_price($id)
    {
        $query = $this->db->get_where('wallet', array('ID' => $id));
        $data = $query->result_array();
        foreach ($data as $row) {
            return $row['Payment_amount'];
        }
        return "0";
    }

    public function get_invoice_id($id, $society)
    {
        $this->db->where('ID <', $id);
        $this->db->where('Society_ID', $society);
        $r = $this->db->get('invoices')->result_array();
        return count($r) + 1;
    }

    public function verify_linked($id)
    {
        $r = $this->db->get('invoices')->result_array();
        foreach ($r as $key => $row) {
            $j =  json_decode($row['Transaction_ID'], true);
            $t = $j['transactions'];
            if (in_array($id, $t)) {
                return true;
            }
        }
        return false;
    }

    public function get_last_wallet()
    {
        $u = $this->session->userData('userID');
        $this->db->order_by('ID', 'DESC');
        //$this->db->get_where('wallet',array('Student_ID'=>$u))
        return $this->db->get('wallet')->result_array()[0];
    }

    public function students_wallets(array $students_ids): array
    {
        if (empty($students_ids)) {
            return [];
        }
        $students_ids = array_map(fn ($id) => (int) $id, $students_ids);
        $ids = implode(',', $students_ids);
        if (empty($ids)) {
            return [];
        }
        $query = $this->db->query(
            "SELECT w.Student_ID, 
             SUM(CASE
                WHEN w.Linked != 0 AND w.Linked_repartition > 0 AND w.Linked_repartition <= 100
                    THEN (w.Payment_amount * w.Linked_repartition) / 100
                WHEN w.Linked = 0 AND w.Status != 2 AND w.Status != 3
                    THEN w.Payment_amount
                END) as total_payment,
             SUM(DISTINCT d.Discount_amount) as total_discount
             FROM `wallet` w
             LEFT JOIN discount d ON w.Student_ID = d.Student_ID
             WHERE w.Student_ID IN (" . $ids . ")
             GROUP BY w.Student_ID"
        );
        $students_wallets = [];

        foreach ($query->result() as $student_wallet) {
            $students_wallets[(int) $student_wallet->Student_ID] = $student_wallet;
        }
        return $students_wallets;
    }
}