<?php

class letters extends datatable
{
    protected static $table = array(
        "name" => "mis_letters",
        "primary" => "id",
        "creators" => "admin",
        "editors" => ""
    );

    protected static $data = array(
        "timestamp" => [
            "locked" => true,
            "required" => true,
            "default" => "#timestamp"
        ],
        "recorded_at" => [
            "locked" => true,
            "required" => true,
            "default" => "#timestamp"
        ],
        "recorded_by" => [
            "locked" => true,
            "required" => true,
            "default" => "#user"
        ],
        "user" => [
            "locked" => true,
            "required" => true,
            "default" => "#user"
        ],
        "received" => [
            "required" => true,
            "validators" => ["date"],
            "default" => "#date"
        ],
        "source" => [
            "required" => true,
            "default" => 1
        ],
        "ref" => [],
        "date" => [
            "required" => true,
            "validators" => ["date"],
            "default" => "#date"
        ],
        "sender" => [
            "required" => true,
        ],
        "sender2" => [],
        "number" => [
            "default" => null
        ],
        "title" => [
            "validators" => ["nonempty"]
        ],
        "forwarded" => [
            "default" => "#userinstitution"
        ],
        "forwarded_by" => [
            "default" => null
        ],
        "forwarded_at" => [
            "default" => null
        ],
        "assigned" => [
            "default" => null
        ],
        "assigned_by" => [
            "default" => null
        ],
        "assigned_at" => [
            "default" => null
        ],
        "state" => [
            "default" => 1
        ]
    );

    public static function bydateinfo()
    {
        $query = datatable::query("select year(received) as y, month(received) as m,day(received) as d,count(id) as count, MONTHNAME(received) as m_name from " . static::$table['name'] . " group by y,m,d order by y desc,m desc,d desc");

        if ($query['success'] && $query['count'] > 0) {
            return $query['data'];
        } else {
            return false;
        }
    }

    public static function received_by_date_info($division)
    {
        $query = datatable::query("select year(received) as y, month(received) as m,day(received) as d,count(*) as count, MONTHNAME(received) as m_name from " . static::$table['name'] . " where forwarded = :division group by y,m,d order by y desc,m desc,d desc", ["division" => $division]);

        if ($query['success'] && $query['count'] > 0) {
            return $query['data'];
        } else {
            return false;
        }
    }

    public static function by_assigned_permonth($userid)
    {
        $query = datatable::query("select year(received) as y, month(received) as m, count(id) as count, MONTHNAME(received) as m_name from " . static::$table['name'] . " where assigned = :assigned group by y,m order by y desc,m desc", ["assigned" => $userid]);

        if ($query['success'] && $query['count'] > 0) {
            return $query['data'];
        } else {
            return false;
        }
    }

    public static function byforwardedinfo()
    {
        $query = datatable::query("select f.id, l.forwarded, f.name, f.shortname, f.codename, count(l.id) as count from mis_letters as l, institutions as f where l.forwarded = f.id group by l.forwarded");

        if ($query['success'] && $query['count'] > 0) {
            return $query['data'];
        } else {
            return false;
        }
    }

    public static function get($id)
    {
        $query = "SELECT 
            l.*, 
            u.shortname as user_name,
            IF(l.sender IS NULL, l.sender2, s.name) as sender_name,
            IF(s.priority = 1, true, null) as sender_priority,
            sources.name as source_name,
            f.name as forwarded_name,
            f.shortname as forwarded_shortname,
            f.codename as forwarded_codename,
            f_user.shortname as forwarded_by_user,
            DATE_FORMAT(l.forwarded_at, '%y %b %d %H:%i') as forwarded_at_text,
            a.name as assigned_name,
            a.shortname as assigned_shortname,
            a_user.shortname as assigned_by_user,
            a.code as assigned_code,
            DATE_FORMAT(l.assigned_at, '%y %b %d %H:%i') as assigned_at_text,
            ls.name as current_state
        FROM 
            mis_letters as l
        INNER JOIN users as u on l.user = u.id
        INNER JOIN mis_letter_sources as sources on l.source = sources.id
        LEFT JOIN institutions as s on l.sender = s.id
        LEFT JOIN institutions as f on l.forwarded = f.id
        LEFT JOIN users as a on l.assigned = a.id
        LEFT JOIN users as a_user on l.assigned_by = a_user.id
        LEFT JOIN users as f_user on l.forwarded_by = f_user.id
        LEFT JOIN mis_letter_states as ls on l.state = ls.id
        WHERE l.id = :id";
        $data = self::query($query, ["id" => $id]);

        if ($data['success'] && $data['count'] == 1) {
            return $data['data'][0];
        } else {
            return false;
        }
    }

    public static function sources()
    {
        $query = datatable::query("select * from mis_letter_sources order by id");

        if ($query['success'] && $query['count'] > 0) {
            return $query['data'];
        } else {
            return false;
        }
    }


    public static function states()
    {
        $query = datatable::query("select * from mis_letter_states order by id");

        if ($query['success'] && $query['count'] > 0) {
            return $query['data'];
        } else {
            return false;
        }
    }

    public static function forward($letter_id, $forwardto)
    {
        $letter = self::get($letter_id);
        if (!$letter) {
            $response["success"] = false;
            $response["message"] = "Invalid Letter ID";
            return $response;
        }

        if ($letter["forwarded"] == $forwardto) {
            return false; // already forwarded to the same, ignore
        } else {

            // update letters table for current value
            $update_letters = self::update($letter_id, [
                "forwarded" => $forwardto,
                "forwarded_by" => auth::user_id(),
                "forwarded_at" => timestamp()
            ]);

            // update forward table 
            $forward = letterforward::create([
                "letter" => $letter_id,
                "institution" => $forwardto
            ]);

            // remove assigned user from old institutions
            $remove_assign = self::assign($letter_id, null);

            if ($forward["success"]) {
                return true;
            } else {
                return false;
            }
        }
    }

    public static function assign($letter_id, $assign_to = null)
    {

        $letter = self::get($letter_id);
        if ($letter["assigned"] == $assign_to) {
            return false; // already asigned to same user
        } else {
            // update letter table
            $assign = self::update($letter_id, [
                "assigned_at" => timestamp(),
                "assigned_by" => auth::user_id(),
                "assigned" => $assign_to
            ]);
            // update assign history
            $assign = letterassign::create([
                "assigned_letter" => $letter_id,
                "assigned_to" => $assign_to
            ]);

            if ($assign["success"]) {
                return true;
            } else {
                return false;
            }
        }
    }

    public static function forwardhistory($letter)
    {

        // Old method
        $response = database::query("select * from mis_letter_forward where letter = :letter", ["letter" => $letter]);

        if ($response["success"]) {
            return $response["data"];
        } else {
            return false;
        }
    }

    public static function assignhistory($letter)
    {

        // Old method
        $response = database::query("select * from mis_letter_assign where assigned_letter = :letter", ["letter" => $letter]);

        if ($response["success"]) {
            return $response["data"];
        } else {
            return false;
        }
    }

    public static function receivedcount($institution, $year = null)
    {
        // Old method
        if ($year) {
            $response = database::query("select count(*) as count from mis_letters where forwarded = :forwarded and year(received) = :year", ["forwarded" => $institution, "year" => $year]);
        } else {
            $response = database::query("select count(*) as count from mis_letters where forwarded = :forwarded", ["forwarded" => $institution]);
        }

        if ($response["success"]) {
            return $response["data"][0]["count"];
        } else {
            return false;
        }
    }
}
