<?php defined('SYSPATH') or die('No direct script access.');

class Model_Account extends Model
{
    public static function setHashKey($key) {
        $sql = "SELECT `SetSettings`('HashKey', '', :key) AS `key`;";
        DB::query(Database::SELECT, $sql)
            ->param(':key', $key)
            ->execute()->get('key');
    }

    public static function getHashKey() {
        $sql = "CALL `GetSettings`('HashKey');";
        return DB::query(Database::SELECT, $sql)
            ->execute()->get('ValS');
    }

    public static function getMaintenanceInfo() {
        $sql = "CALL `GetSettings`('maintenance_active');";
        $key = DB::query(Database::SELECT, $sql)->execute();
        $result['active'] = ($key->get('Val') == 1);
        $sql = "CALL `GetSettings`('maintenance_return');";
        $key = DB::query(Database::SELECT, $sql)->execute();
        $result['return'] = $key->get('ValS');
        return $result;
    }

    public static function checkAuth($login, $password) {
        $sql = "SELECT `SignIn`(:login, :pass) AS `ID`;";
        return DB::query(Database::SELECT, $sql)
            ->param(':login', $login)
            ->param(':pass', $password)
            ->execute()->get('ID');
    }

    public static function changeTeacherInfo($id, $lastName, $firstName, $secondName, $degreeID, $departmentID) {
        $sql = "SELECT `ChangeTeacherInfo`(:id, :last, :first, :second, :degree, :department) AS `UserID`;";
        return DB::query(Database::SELECT, $sql)
            ->parameters([
                ':id' => $id,
                ':last' => $lastName,
                ':first' => $firstName,
                ':second' => $secondName,
                ':degree' => $degreeID,
                'department' => $departmentID,
            ])->execute()->get('UserID');
    }

    public static function createTeacher($lastName, $firstName, $secondName, $degreeID, $departmentID, $activationCode) {
        $sql = "SELECT `CreateTeacher`() AS `UserID`;";
        return DB::query(Database::SELECT, $sql)
            ->parameters([
                ':last' => $lastName,
                ':first' => $firstName,
                ':second' => $secondName,
                ':degree' => $degreeID,
                ':department' => $departmentID,
                ':code' => $activationCode,
            ])->execute()->get('UserID');
    }

    public static function createTeacherByDepName($lastName, $firstName, $secondName, $departmentID, $facultyID, $activationCode) {
        if ($departmentID == '') {
            return -1;
        }

        $sql = "SELECT `CreateTeacherByDepName`(:last, :first, :second, :department, :faculty, :code) AS `UserID`;";
        return DB::query(Database::SELECT, $sql)
            ->parameters([
                ':last' => $lastName,
                ':first' => $firstName,
                ':second' => $secondName,
                ':department' => $departmentID,
                ':faculty' => $facultyID,
                ':code' => $activationCode,
            ])->execute()->get('UserID');
    }

    public static function createStudent($lastName, $firstName, $secondName, $grade, $groupNum, $facultyID, $activationCode) {
        $sql = "SELECT `CreateStudent`(:last, :first, :second, :grade, :group, :faculty, :code) AS `UserID`;";
        return DB::query(Database::SELECT, $sql)
            ->parameters([
                ':last' => $lastName,
                ':first' => $firstName,
                ':second' => $secondName,
                ':grade' => $grade,
                ':group' => $groupNum,
                ':faculty' => $facultyID,
                ':code' => $activationCode,
            ])->execute()->get('UserID');
    }

    public static function createStudentEx($lastName, $firstName, $secondName, $gradeNum, $groupNum, $degree, $specialization, $facultyID, $activationCode) {
        $sql = "SELECT `CreateStudentEx`(:last, :first, :second, :grade, :group, :degree, :spec, :faculty, :code) AS `UserID`;";
        return DB::query(Database::SELECT, $sql)
            ->parameters([
                ':last' => $lastName,
                ':first' => $firstName,
                ':second' => $secondName,
                ':grade' => $gradeNum,
                ':group' => $groupNum,
                ':degree' => $degree,
                ':spec' => $specialization,
                ':faculty' => $facultyID,
                ':code' => $activationCode,
            ])->execute()->get('UserID');
    }


    public static function getUserInfo($id)
    {
        $sql = "CALL `GetFullInfo`(:id);";
        return DB::query(Database::SELECT, $sql)
            ->param(':id', (int) $id)
            ->execute()[0];
    }


    # TODO: deprecated (see getUserInfo)
    public static function getPersonalInfo($id, $semesterID = null) {
        $semesterID = $semesterID ? $semesterID : User::instance()->SemesterID;
        $sql = "CALL `GetPersonalInfo`(:id, :semester);";
        return DB::query(Database::SELECT, $sql)
            ->param(':semester', (int) $semesterID)
            ->param(':id', (int) $id)
            ->execute()[0];
    }

    # TODO: deprecated (see getUserInfo)
    public static function getAccountInfo($id) {
        return DB::query(Database::SELECT, "CALL GetAccountInfo(:id);")
            ->param(':id', (int) $id)->execute()[0];
    }

    /**
     * @param int $id
     * @param string $value
     * @param string $type 'email', 'login' or 'password'
     * @return int
     */
    public static function changeAccountData($id, $value, $type) {
        $sql = "SELECT `ChangeAccountData`(:account, :value, :type) AS Num;";
        return DB::query(Database::SELECT, $sql)
            ->parameters([
                ':account' => $id,
                ':value' => $value,
                ':type' => $type,
            ])->execute()->get('Num');
    }

    /**
     * @param string $data
     * @param string $type 'login','email' or 'code'
     * @return int
     */
    public static function checkAccountExistence($data, $type) {
        $sql = "SELECT `CheckAccountExistence`(:data, :type) AS Num;";
        return DB::query(Database::SELECT, $sql)
            ->param(':data', $data)
            ->param(':type', $type)
            ->execute()->get('Num');
    }

    public static function isActivationCodeValid($code) {
        $sql = "SELECT `CheckAccountExistence`(:acode, 'code') AS Num;";
        $res = DB::query(Database::SELECT, $sql)
            ->param(':acode', $code)
            ->execute()->get('Num');
        return $res == 1;
    }

    public static function createRecoveryToken($email, $token) {
        $sql = "SELECT `CreateRecoveryToken`(:email, :token) AS UserName;";
        return DB::query(Database::SELECT, $sql)
            ->param(':email', $email)
            ->param(':token', $token)
            ->execute()->get('UserName');
    }

    public static function getRecoveryInfoByEMail($email) {
        $sql = "CALL GetRecoveryInfoByEMail(:email);";
        return DB::query(Database::SELECT, $sql)
            ->param(':email', $email)
            ->execute();
    }

    public static function getRecoveryInfoByToken($token) {
        $sql = "CALL GetRecoveryInfoByToken(:token);";
        return DB::query(Database::SELECT, $sql)
            ->param(':token', $token)->execute();
    }

    public static function useRecoveryToken($token) {
        $sql = "SELECT `UseRecoveryToken`(:token) AS Num;";
        return DB::query(Database::SELECT, $sql)
            ->param(':token', $token)
            ->execute()->get('Num');
    }

    public static function activateAccount($login, $password, $email, $code) {
        $sql = "SELECT `ActivateAccount`(:code, :login, :email, :pass) AS `Num`;";
        return DB::query(Database::SELECT, $sql)
            ->parameters([
                ':code' => $code,
                ':login' => $login,
                ':email' => $email,
                ':pass' => $password,
            ])->execute()->get('Num');
    }

    public static function getCurSemesterID() {
        $sql = "CALL `GetSettings`('SemesterID');";
        return DB::query(Database::SELECT, $sql)
            ->execute()->get('Val');
    }
}