DELIMITER //

DROP FUNCTION IF EXISTS GetGradeID//
DROP FUNCTION IF EXISTS SetCurSemesterID//
DROP FUNCTION IF EXISTS SetHashKey//

drop function if exists InternalIsTeacherBinded//
DROP FUNCTION IF EXISTS GetSemesterID//

DROP FUNCTION IF EXISTS GetRateForDiscExtra//
DROP FUNCTION IF EXISTS GetRateForDiscBonus//
DROP FUNCTION IF EXISTS GetRateForDiscSemester//
DROP FUNCTION IF EXISTS GetRateForDiscExamNum//
DROP FUNCTION IF EXISTS GetRateForDiscExam//
DROP FUNCTION IF EXISTS InternalNotify//
DROP FUNCTION IF EXISTS GetDisciplineMaxRate//
DROP FUNCTION IF EXISTS OrderModuleTypesForSession//

DROP FUNCTION IF EXISTS CreateStudyGroup//

DROP FUNCTION IF EXISTS GetAccCountByCode//
DROP FUNCTION IF EXISTS GetAccCountByMail//
DROP FUNCTION IF EXISTS GetAccCountByLogin//

DROP FUNCTION IF EXISTS ChangePassword//
DROP FUNCTION IF EXISTS ChangeLogin//
DROP FUNCTION IF EXISTS ChangeMail//

DROP FUNCTION IF EXISTS GetRateForDisc//

drop function if exists InternalIsTeacherBound//


# -------------------------------------------------------------------------------------------
# Label: abbreviations
# -------------------------------------------------------------------------------------------

# abbreviation: abbr
# specialization: spec
# department: dep

# -------------------------------------------------------------------------------------------
# Label: internals
# -------------------------------------------------------------------------------------------

# actually check for first scoring, in this case you cannot yet edit discipline
# "SetRate" stored procedure can change isLocked flag
DROP FUNCTION IF EXISTS InternalIsMapLocked//
CREATE FUNCTION `InternalIsMapLocked` (
        `pDisciplineID` INT
    ) RETURNS BOOLEAN
    NO SQL
BEGIN
    DECLARE vChecker BOOLEAN DEFAULT FALSE;

    SELECT disciplines.isLocked INTO vChecker
        FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    RETURN vChecker;
END //


# check, that student really take this course
DROP FUNCTION IF EXISTS InternalIsStudentAttached//
CREATE FUNCTION `InternalIsStudentAttached` (
        `pStudentID` INT,
        `pDisciplineID` INT,
        `pSemesterID` INT
    ) RETURNS BOOLEAN
    NO SQL
BEGIN
    DECLARE vAttachType enum('attach','detach') DEFAULT NULL;

    SELECT view_disciplines_students.AttachType INTO vAttachType
        FROM `view_disciplines_students`
        WHERE view_disciplines_students.SemesterID = pSemesterID AND
              view_disciplines_students.StudentID = pStudentID AND
              view_disciplines_students.DisciplineID = pDisciplineID
        LIMIT 1;

    RETURN ( NOT vAttachType  <=> 'detach' );
END //



# check, that teacher teach this course
DROP FUNCTION IF EXISTS InternalIsTeacherBound//
CREATE FUNCTION InternalIsTeacherBound (
        `pTeacherID` INT,
        `pDisciplineID` INT
    ) RETURNS BOOLEAN
    NO SQL
BEGIN
    RETURN EXISTS (
        SELECT * FROM `disciplines_teachers`
            WHERE   disciplines_teachers.TeacherID = pTeacherID AND
                    disciplines_teachers.DisciplineID = pDisciplineID
            LIMIT 1
    );
END //


DROP FUNCTION IF EXISTS InternalIsTeacherAuthor//
CREATE FUNCTION `InternalIsTeacherAuthor` (
        `pTeacherID` INT,
        `pDisciplineID` INT
    ) RETURNS BOOLEAN
    NO SQL
BEGIN
    DECLARE vAuthorID INT DEFAULT -1;

    SELECT disciplines.AuthorID INTO vAuthorID
        FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    RETURN ( vAuthorID = pTeacherID );
END //



DROP FUNCTION IF EXISTS SetExamPeriodOption//
CREATE FUNCTION `SetExamPeriodOption` (
        `pStudentID` INT,
        `pSubmoduleID` INT,
        `pType` enum('absence','pass')
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    INSERT INTO `exam_period_options`
        (StudentID, SubmoduleID, Type) VALUES(pStudentID, pSubmoduleID, pType)
        ON DUPLICATE KEY UPDATE
            exam_period_options.Type = pType;

    RETURN  0;
END //


# check, regular + exam rate == 100
DROP FUNCTION IF EXISTS InternalIsMapCreated//
CREATE FUNCTION `InternalIsMapCreated` (
        `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vMaxRate INT DEFAULT -1;

    SELECT view_disciplines_results.DisciplineRateMax INTO vMaxRate
        FROM `view_disciplines_results`
        WHERE view_disciplines_results.DisciplineID = pDisciplineID
        LIMIT 1;

    RETURN ( vMaxRate = 100 );
END //

# ordering helper
DROP FUNCTION IF EXISTS InternalOrderModuleTypesForSession//
CREATE FUNCTION `InternalOrderModuleTypesForSession` (
        `pModuleType` INT
    ) RETURNS INT(3)
    NO SQL
BEGIN
    DECLARE vRes INT DEFAULT 0;

    CASE pModuleType
        WHEN 4 THEN SET vRes = 1; # extra
        WHEN 2 THEN SET vRes = 2; # exam
        WHEN 3 THEN SET vRes = 3; # bonus
        ELSE SET vRes = 4;
    END CASE;

    RETURN vRes;
END //




# -------------------------------------------------------------------------------------------
# Label: preferences
# Label: magic
# -------------------------------------------------------------------------------------------

# set values of record with key \pKey,
# if doesn't exist, then create.
DROP FUNCTION IF EXISTS SetSettings//
CREATE FUNCTION `SetSettings` (
        `pKey` VARCHAR(50) CHARSET utf8,
        `pVal` INT,
        `pValS` VARCHAR(300) CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    INSERT INTO `general_settings`
        (Val, ValS, Name) VALUES (pVal, pValS, pKey)
        ON DUPLICATE KEY UPDATE
            general_settings.Val = pVal,
            general_settings.ValS = pValS;
    RETURN 0;
END//



DROP FUNCTION IF EXISTS SetBitmaskByPagename//
CREATE FUNCTION `SetBitmaskByPagename` (
        `pPagename` TEXT CHARSET utf8,
        `pMask` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    INSERT INTO `page_access`
        (Pagename, Bitmask) VALUES (pPagename, pMask)
        ON DUPLICATE KEY UPDATE
            page_access.Bitmask = pMask;
    RETURN 0;
END //



DROP FUNCTION IF EXISTS GetBitmaskByPagename//
CREATE FUNCTION `GetBitmaskByPagename` (
        `pPagename` TEXT CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vBitmask INT DEFAULT 0;

    SELECT page_access.Bitmask INTO vBitmask
        FROM `page_access`
        WHERE page_access.Pagename = pPagename
        LIMIT 1;

    RETURN vBitmask;
END //




# -------------------------------------------------------------------------------------------
# Label: semesters
# -------------------------------------------------------------------------------------------

# TODO: deprecated
# set current(for user) semester, life time - db session
DROP FUNCTION IF EXISTS SetSemesterID//
CREATE FUNCTION `SetSemesterID` (`pSemesterID` INT) RETURNS int(11)
    NO SQL
BEGIN
    SET @CurrentSemesterID := pSemesterID;
    RETURN 0;
END //



# -------------------------------------------------------------------------------------------
# Label: faculties
# -------------------------------------------------------------------------------------------

# TODO: return faculty id
DROP FUNCTION IF EXISTS CreateFaculty //
CREATE FUNCTION CreateFaculty (
        `pFacultyName` TEXT CHARSET utf8,
        `pFacultyAbbr` TEXT CHARSET utf8
    ) RETURNS INT(11) # 0 - success
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    INSERT INTO faculties
        (Name, Abbr) VALUES(pFacultyName, pFacultyAbbr);
    RETURN 0;
END //

# -------------------------------------------------------------------------------------------
# Label: departments
# -------------------------------------------------------------------------------------------

# create department or return existing
DROP FUNCTION IF EXISTS CreateDepartment //
CREATE FUNCTION CreateDepartment (
        `pName` VARCHAR(200) CHARSET utf8,
        `pFacultyID` INT(11)
    ) RETURNS INT(11) # department id or -1 if failed
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    INSERT INTO departments
        (Name, FacultyID) VALUES (pName, pFacultyID)
        ON DUPLICATE KEY UPDATE
            departments.ID = LAST_INSERT_ID(departments.ID);

    RETURN LAST_INSERT_ID();

END //

# -------------------------------------------------------------------------------------------
# Label: specializations
# -------------------------------------------------------------------------------------------



# -------------------------------------------------------------------------------------------
# Label: grades
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS CreateGrade//
CREATE FUNCTION `CreateGrade` (
    `pGradeNum` INT,
    `pDegree` enum('bachelor', 'master', 'specialist')
) RETURNS int(11) # groupID or -1 if failed
    NO SQL
BEGIN
    DECLARE vGradeID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    INSERT INTO `grades`
    (Num, Degree) VALUES (pGradeNum, pDegree)
    ON DUPLICATE KEY UPDATE
        grades.ID = LAST_INSERT_ID(grades.ID);

    RETURN LAST_INSERT_ID();
END //

# -------------------------------------------------------------------------------------------
# Label: groups
# -------------------------------------------------------------------------------------------


# negative int, if already exists
DROP FUNCTION IF EXISTS CreateGroup//
CREATE FUNCTION `CreateGroup` (
    `pGradeID` INT,
    `pGroupNum` INT,
    `pSpecializationID` INT,
    `pGroupName` VARCHAR(50) CHARSET utf8
) RETURNS int(11) # group id
    NO SQL
BEGIN
    # check GradeID, SpecID constraints and (GradeID, GroupNum, SpecID) - unique
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # create discipline
    INSERT INTO `study_groups`
        (GradeID, GroupNum, SpecializationID, Name)
        VALUES (pGradeID, pGroupNum, pSpecializationID, pGroupName)
        ON DUPLICATE KEY UPDATE
            study_groups.ID = LAST_INSERT_ID(study_groups.ID);
    RETURN LAST_INSERT_ID();
END //




DROP FUNCTION IF EXISTS GetGroup//
CREATE FUNCTION `GetGroup` (
        `pGradeID` INT,
        `pGroupNum` INT,
        `pFacultyID` INT
    ) RETURNS int(11) # groupID or -1 if failed
    NO SQL
BEGIN
    DECLARE vGroupID INT DEFAULT -1;

    SELECT study_groups.ID INTO vGroupID
        FROM `study_groups`
        INNER JOIN `specializations` ON specializations.ID = study_groups.SpecializationID
        WHERE   study_groups.GradeID = pGradeID AND
                study_groups.GroupNum = pGroupNum AND
                specializations.FacultyID = pFacultyID
        LIMIT 1;

    RETURN vGroupID;
END //




# -------------------------------------------------------------------------------------------
# Label: subjects
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS CreateSubject//
CREATE FUNCTION `CreateSubject` (
        `pFacultyID` INT,
        `pSubjectName` TEXT CHARSET utf8,
        `pSubjectAbbr` VARCHAR(20) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vSubjectID INT DEFAULT -1;

    # create/get subject (subject name is unique key)
    INSERT INTO `subjects`
        (Name, Abbr) VALUES (pSubjectName, pSubjectAbbr)
        ON DUPLICATE KEY UPDATE
            subjects.ID = LAST_INSERT_ID(subjects.ID);
    SET vSubjectID = LAST_INSERT_ID();

    BEGIN # handler block
        DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

        # try to attach subject to faculty
        INSERT INTO `subjects_faculties`
            (SubjectID, FacultyID) VALUES (vSubjectID, pFacultyID)
            ON DUPLICATE KEY UPDATE # just stub
                subjects_faculties.ID = LAST_INSERT_ID(subjects_faculties.ID);
    END;

    RETURN 0;
END //


DROP FUNCTION IF EXISTS DeleteSubject //
CREATE FUNCTION DeleteSubject (
        `pSubjectID` INT
    ) RETURNS INT(11) # 0 - success
    NO SQL
BEGIN
    DECLARE vChecker BOOLEAN DEFAULT FALSE;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vChecker = EXISTS(
        SELECT * FROM `disciplines`
        WHERE disciplines.SubjectID = pSubjectID
        LIMIT 1
    );
    IF vChecker THEN
        RETURN -1; # Удаляемый предмет используется в disciplines.
    END IF;

    DELETE FROM `subjects_faculties`
        WHERE subjects_faculties.SubjectID = pSubjectID;
    DELETE FROM `subjects`
        WHERE subjects.ID = pSubjectID
        LIMIT 1;

    RETURN 0; # Успешно удалено;
END //


# -------------------------------------------------------------------------------------------
# Label: accounts
# -------------------------------------------------------------------------------------------




DROP FUNCTION IF EXISTS CheckAccountExistence//
CREATE FUNCTION `CheckAccountExistence` (
        `pData` TEXT CHARSET utf8,
        `pType` enum('login','email', 'code')
    ) RETURNS BOOLEAN # TRUE - exist, FALSE - doesn't
    NO SQL
BEGIN
    DECLARE vRes BOOLEAN DEFAULT FALSE;

    SET vRes = EXISTS(
        SELECT * FROM `accounts`
            WHERE CASE pType
                WHEN 'login' THEN pData = accounts.Login
                WHEN 'email' THEN pData = accounts.EMail
                WHEN 'code' THEN pData = accounts.ActivationCode
                ELSE FALSE
            END
        LIMIT 1
    );

    RETURN vRes;
END //


DROP FUNCTION IF EXISTS ActivateAccount//
CREATE FUNCTION `ActivateAccount` (
        `pCode` VARCHAR(40) CHARSET utf8,
        `pLogin` VARCHAR(50) CHARSET utf8,
        `pEMail` VARCHAR(50) CHARSET utf8,
        `pPassword` VARCHAR(255) CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    # check for matching with existing accounts (note: Login & E-Mail are unique)
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # activate account
    UPDATE `accounts`
        SET accounts.Login = pLogin,
            accounts.Password = pPassword,
            accounts.EMail = pEMail,
            accounts.ActivationCode = NULL
        WHERE accounts.ActivationCode = pCode AND
              ( @vAccountID := accounts.ID ) # save accountID
        LIMIT 1;

    IF ( ROW_COUNT() = 0 ) THEN
        RETURN -2; # account with this Code not found
    ELSE
        RETURN @vAccountID;
    END IF;
END //


DROP FUNCTION IF EXISTS ChangeAccountData//
CREATE FUNCTION `ChangeAccountData` (
        `pUserID` INT,
        `pData` TEXT CHARSET utf8,
        `pType` enum('login', 'email', 'password')
    ) RETURNS int(11) # 0 - success, <0 - failed
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    CASE pType
        WHEN 'login' THEN
            UPDATE `accounts`
                SET accounts.Login = pData
                WHERE accounts.ID = pUserID
                LIMIT 1;
        WHEN 'email' THEN
            UPDATE `accounts`
                SET accounts.EMail = pData
                WHERE accounts.ID = pUserID
                LIMIT 1;
        WHEN 'password' THEN
            UPDATE `accounts`
                SET accounts.Password = pData
                WHERE accounts.ID = pUserID
                LIMIT 1;
    END CASE;

    RETURN ROW_COUNT()-1;
END //



DROP FUNCTION IF EXISTS SignIn//
CREATE FUNCTION `SignIn` (
        `pLoginOrMail` VARCHAR(255) CHARSET utf8,
        `pPassword`  VARCHAR(64) CHARSET utf8
    ) RETURNS int(11) # account id
    NO SQL
BEGIN
    DECLARE vAccountID INT DEFAULT -1;

    #check account existence
    SELECT accounts.ID INTO vAccountID
        FROM `accounts`
        WHERE   accounts.Password = pPassword AND
                (accounts.Login = pLoginOrMail OR accounts.EMail = pLoginOrMail)
        LIMIT 1;
    IF vAccountID <= 0 THEN
        RETURN -1;
    END IF;

    # TODO: extract method - log sign in
    INSERT INTO `logs_signin` # logging
        (AccountID) VALUES (vAccountID);
    RETURN vAccountID;
END //




# -------------------------------------------------------------------------------------------
# Label: teachers
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS ChangeTeacherInfo//
CREATE FUNCTION `ChangeTeacherInfo` (
        `pTeacherID` INT,
        `pLastName` VARCHAR(30) CHARSET utf8,
        `pFirstName` VARCHAR(30) CHARSET utf8,
        `pSecondName` VARCHAR(30) CHARSET utf8,
        `pJobPositionID` INT,
        `pDepartmentID` INT
    ) RETURNS int(11) # -1 if teacher doesn't exists, otherwise 0
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # set new info
    UPDATE `teachers`
        SET teachers.LastName = pLastName,
            teachers.FirstName = pFirstName,
            teachers.SecondName = pSecondName,
            teachers.JobPositionID = pJobPositionID,
            teachers.DepartmentID = pDepartmentID
        WHERE teachers.ID = pTeacherID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //



DROP FUNCTION IF EXISTS CreateTeacher//
CREATE FUNCTION `CreateTeacher` (
        `pLastName` VARCHAR(30) CHARSET utf8,
        `pFirstName` VARCHAR(30) CHARSET utf8,
        `pSecondName` VARCHAR(30) CHARSET utf8,
        `pJobPositionID` INT,
        `pDepartmentID`  INT,
        `pActivationCode` VARCHAR(40) CHARSET utf8
    ) RETURNS int(11) # 0 - success, <0 failed
    NO SQL
BEGIN
    DECLARE vAccountID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # TODO: kill magic constants
    # user role 2 - common teacher
    # create account
    INSERT INTO `accounts`
        (Login , Password , EMail, UserRoleID, ActivationCode )
        VALUES  ( NULL, NULL, NULL, 2, pActivationCode);
    SET vAccountID = LAST_INSERT_ID();

    # add new teacher
    INSERT INTO `teachers`
        (AccountID, LastName, FirstName, SecondName, JobPositionID, DepartmentID)
        VALUES  (vAccountID, pLastName, pFirstName, pSecondName, pJobPositionID, pDepartmentID);
    RETURN ROW_COUNT()-1;
END //


DROP FUNCTION IF EXISTS CreateTeacherByDepName//
CREATE FUNCTION `CreateTeacherByDepName` (
        `pLastName` VARCHAR(30) CHARSET utf8,
        `pFirstName` VARCHAR(30) CHARSET utf8,
        `pSecondName` VARCHAR(30) CHARSET utf8,
        `pDepartmentName` VARCHAR(200) CHARSET utf8,
        `pFacultyID` INT,
        `pActivationCode` VARCHAR(40) CHARSET utf8
    )   RETURNS int(11) # 0 - success, < 0 - failed
    NO SQL
BEGIN
    DECLARE vAccountID, vRoleID, vDepID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vDepID = CreateDepartment(pDepartmentName, pFacultyID);
    IF vDepID < 0 THEN
       RETURN -1;
    END IF;

    RETURN CreateTeacher(pLastName, pFirstName, pSecondName, 12, vDepID, pActivationCode);
END //


-- -1 - не сотрудник деканата и не преподаватель дисциплины
-- 0 - только чтение
-- 1 - редактирование
DROP FUNCTION IF EXISTS GetEditRightsForTeacher//
CREATE FUNCTION `GetEditRightsForTeacher` (
        `pTeacherID` INT,
        `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vUserRole INT DEFAULT -1;
    DECLARE vIsBound BOOLEAN;

    SET vIsBound = InternalIsTeacherBound(pTeacherID, pDisciplineID);
    IF vIsBound > 0 THEN
        RETURN 1;
    END IF;

    SELECT accounts.UserRoleID INTO vUserRole
        FROM `teachers`
        INNER JOIN `accounts` ON teachers.AccountID=accounts.ID
        WHERE teachers.ID = pTeacherID
        LIMIT 1;
    IF vUserRole = 4 THEN # 4 - сотрудник деканата
        RETURN 0;
    END IF;

    RETURN -1;
END //



# -------------------------------------------------------------------------------------------
# Label: students
# -------------------------------------------------------------------------------------------

# TODO: magic constants (UserRoleID)
# TODO: transaction
# TODO: group id instead num and grade
DROP FUNCTION IF EXISTS CreateStudent//
CREATE FUNCTION `CreateStudent` (
        `pLastName` VARCHAR(30) CHARSET utf8,
        `pFirstName` VARCHAR(30) CHARSET utf8,
        `pSecondName` VARCHAR(30) CHARSET utf8,
        `pGradeID` INT,
        `pGroupNum` INT,
        `pFacultyID` INT,
        `pActivationCode` VARCHAR(40) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vAccountID, vGroupID, vStudentID, vSemesterID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vGroupID = GetGroup(pGradeID, pGroupNum, pFacultyID);
    IF vGroupID <= 0 THEN
        RETURN -1;
    END IF;

    # create new account
    # UserRoleID 1 = student
    INSERT INTO `accounts`
        (Login , Password , EMail, UserRoleID, ActivationCode )
        VALUES ( NULL, NULL, NULL, 1, pActivationCode);
    SET vAccountID = LAST_INSERT_ID();

    # create student
    INSERT INTO `students`
        (AccountID, LastName, FirstName, SecondName)
        VALUES  (vAccountID, pLastName, pFirstName, pSecondName);
    SET vStudentID = LAST_INSERT_ID();

    # TODO: add param semester id
    SELECT general_settings.Val INTO vSemesterID
        FROM `general_settings`
        WHERE general_settings.Name = 'SemesterID'
        LIMIT 1;

    RETURN ControlStudentGroup(vStudentID, vGroupID, FALSE, vSemesterID);
END //


# unlike fn CreateStudent, this can create all missing records (group, grade, specialization)
DROP FUNCTION IF EXISTS CreateStudentEx//
CREATE FUNCTION `CreateStudentEx` (
        `pLastName` VARCHAR(30) CHARSET utf8,
        `pFirstName` VARCHAR(30) CHARSET utf8,
        `pSecondName` VARCHAR(30) CHARSET utf8,
        `pGradeNum` INT,
        `pGroupNum` INT,
        `pDegree` enum('bachelor', 'master', 'specialist'),
        `pSpecName` VARCHAR(50) CHARSET utf8,
        `pFacultyID` INT,
        `pActivationCode` VARCHAR(40) CHARSET utf8
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vAccountID, vGradeID, vSpecID, vGroupID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # get specialization id
    INSERT INTO `specializations`
        (Name, Abbr, FacultyID) VALUES  (pSpecName, NULL, pFacultyID)
        ON DUPLICATE KEY UPDATE
            specializations.ID = LAST_INSERT_ID(specializations.ID);
    SET vSpecID = LAST_INSERT_ID();

    SET vGradeID = CreateGrade(pGradeNum, pDegree);
    SET vGroupID = CreateGroup(vGradeID, pGroupNum, vSpecID, NULL);
    RETURN CreateStudent(pLastName, pFirstName, pSecondName, vGradeID, pGroupNum, pFacultyID, pActivationCode);
END //



# Give a student an academic leave or attach him to group.
#   params:
#      StudentID (int)
#      GroupID (int)   : is used, if OnLeave == false
#      OnLeave (bool)  : true - into study leave, false - attach to group
DROP FUNCTION IF EXISTS ControlStudentGroup//
CREATE FUNCTION `ControlStudentGroup` (
        `pStudentID` INT,
        `pGroupID` INT,
        `pState` BOOLEAN,
        `pSemesterID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # out to study leave
    IF pState THEN
        UPDATE `students_groups`
            SET students_groups.IsStudyLeave = TRUE,
                students_groups.Date = CURDATE()
            WHERE students_groups.StudentID = pStudentID AND
                  students_groups.SemesterID = pSemesterID
            LIMIT 1;
    # attach to new group
    ELSE
        INSERT INTO `students_groups`
            (StudentID, GroupID, SemesterID)
            VALUES(pStudentID, pGroupID, pSemesterID)
            ON DUPLICATE KEY UPDATE
                students_groups.GroupID = pGroupID;
    END IF;
    RETURN ROW_COUNT()-1;
END //


# -------------------------------------------------------------------------------------------
# Label: disciplines
# -------------------------------------------------------------------------------------------



DROP FUNCTION IF EXISTS GetDisciplineSemesterID//
CREATE FUNCTION `GetDisciplineSemesterID` (
    `pDisciplineID` INT
) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vSemesterID INT DEFAULT -1;

    SELECT disciplines.SemesterID INTO vSemesterID
        FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    RETURN vSemesterID;
END //

DROP FUNCTION IF EXISTS AddDiscipline//
DROP FUNCTION IF EXISTS Discipline_Create//
CREATE FUNCTION `Discipline_Create` (
    `pTeacherID` INT,
    `pGradeID` INT,
    `pSubjectID` INT,
    `pExamType` VARCHAR(30) CHARSET utf8,
    `pLectureCount` INT,
    `pPracticeCount` INT,
    `pLabCount` INT,
    `pFacultyID` INT,
    `pSemesterID` INT,
    `pSubtype` VARCHAR(30) CHARSET utf8 # scientific_coursework discipline_coursework
) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, vDisciplineID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # create discipline
    INSERT INTO `disciplines`
        (AuthorID, GradeID, SubjectID, ExamType,
        LectureCount, PracticeCount,LabCount,
        SemesterID, FacultyID, Subtype)
        VALUES (
            pTeacherID, pGradeID, pSubjectID, pExamType,
            pLectureCount, pPracticeCount, pLabCount,
            pSemesterID, pFacultyID, pSubtype
        );
    SET vDisciplineID = LAST_INSERT_ID();


    SET vChecker = BindTeacher(pTeacherID, pTeacherID, vDisciplineID);

    # add exam and extra modules
    IF pExamType = 'exam' THEN
        SET vChecker = AddModuleExam(pTeacherID, vDisciplineID);
    END IF;
    SET vChecker = AddModuleExtra(pTeacherID, vDisciplineID);

    RETURN vDisciplineID;
END //



DROP FUNCTION IF EXISTS ChangeDisciplineSubject//
CREATE FUNCTION `ChangeDisciplineSubject` (
        `pTeacherID` INT,
        `pDisciplineID` INT,
        `pSubjectID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    UPDATE `disciplines`
        SET disciplines.SubjectID = pSubjectID
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //



DROP FUNCTION IF EXISTS GetMilestone//
CREATE FUNCTION `GetMilestone` (
        `pFacultyID` INT,
        `pSemesterID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vMilestone, vCounter INT DEFAULT 0;

    # get most frequent milestone
    SELECT COUNT(*) AS 'cnt', disciplines.Milestone
        INTO vCounter, vMilestone
        FROM `disciplines`
        WHERE disciplines.SemesterID = pSemesterID AND
              disciplines.FacultyID = pFacultyID
        GROUP BY disciplines.Milestone
        ORDER BY cnt DESC
        LIMIT 1;

    RETURN vMilestone;
END //



DROP FUNCTION IF EXISTS ChangeDisciplineGrade//
CREATE FUNCTION `ChangeDisciplineGrade` (
        `pTeacherID` INT,
        `pDisciplineID` INT,
        `pGradeID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vCurGradeID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    IF  InternalIsMapLocked(pDisciplineID) OR
        NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # get current grade
    SELECT disciplines.GradeID INTO vCurGradeID
        FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    IF vCurGradeID = pGradeID THEN
        RETURN 0;
    END IF;

    # delete all students
    DELETE FROM `disciplines_groups`
        WHERE disciplines_groups.DisciplineID = pDisciplineID;
    DELETE FROM `disciplines_students`
        WHERE disciplines_students.DisciplineID = pDisciplineID;

    # set grade
    UPDATE `disciplines`
        SET disciplines.GradeID = pGradeID
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //


DROP FUNCTION IF EXISTS ChangeDisciplineControl//
CREATE FUNCTION `ChangeDisciplineControl` (
        `pTeacherID` INT,
        `pDisciplineID` INT,
        `pExamType` enum('exam', 'credit', 'grading_credit')
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, vExtraMax, vExtraID INT DEFAULT -1;
    DECLARE vOldExamType enum('exam', 'credit', 'grading_credit') DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    IF  InternalIsMapLocked(pDisciplineID) OR
        NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # get exam type and extra module ID
    SELECT disciplines.ExamType, modules.ID INTO vOldExamType, vExtraID
        FROM `disciplines`
        INNER JOIN `modules` ON  modules.DisciplineID = pDisciplineID AND
                                 modules.Type = 'extra'
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    IF vExtraID <= 0 THEN
        RETURN -1;
    END IF;

    # set new exam type
    UPDATE `disciplines`
        SET disciplines.ExamType = pExamType
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    # check type changing: exam <-> credit/grading_credit
    IF vOldExamType = 'exam' XOR pExamType != 'exam' THEN
        RETURN 0;
    END IF;


    # TODO: extract method addExtraModule
    IF pExamType = 'exam' THEN # change to exam
        SET vExtraMax = 7;
        # count discipline's current max rate
        SELECT view_disciplines_results.DisciplineRateMax INTO vChecker
            FROM `view_disciplines_results`
            WHERE view_disciplines_results.DisciplineID = pDisciplineID
            LIMIT 1;

        IF vChecker >= 61 THEN # can't add exam module ( > 100 points)
            RETURN 1;
        END IF;
        SET vChecker = AddModuleExam(pTeacherID, pDisciplineID);

        # delete extra submodules(only 1 extra for exam)
        DELETE FROM `submodules`
            WHERE submodules.OrderNum > 1 AND submodules.ModuleID = vExtraID;
    ELSE # change to credit
        SET vExtraMax = 29;
        SET vChecker = DeleteModuleExam(pTeacherID, pDisciplineID);
        # 2 extra submodules (1 already created for exam)
        SET vChecker = AddSubmodule(pTeacherID, vExtraID, vExtraMax, '', NULL, 'LandmarkControl');
    END IF;

    # set max rate for extra
    UPDATE `submodules`
        SET submodules.MaxRate = vExtraMax
        WHERE submodules.ModuleID = vExtraID;
    RETURN 0;
END //

DROP FUNCTION IF EXISTS ChangeDisciplineHours//
CREATE FUNCTION `ChangeDisciplineHours` (
        `pTeacherID` INT,
        `pDisciplineID` INT,
        `pHours` INT,
        `pType` INT
        # Type: 0 - Practice Hours, 1 - Lecture Hours, 2 - Lab Hours
    )   RETURNS int(11)
    NO SQL
BEGIN
    IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    CASE pType
    WHEN 0 THEN
        UPDATE `disciplines`
            SET disciplines.PracticeCount = pHours
            WHERE disciplines.ID = pDisciplineID
            LIMIT 1;
    WHEN 1 THEN
        UPDATE `disciplines`
            SET disciplines.LectureCount = pHours
            WHERE disciplines.ID = pDisciplineID
            LIMIT 1;
    WHEN 2 THEN
        UPDATE `disciplines`
            SET disciplines.LabCount = pHours
            WHERE disciplines.ID = pDisciplineID
            LIMIT 1;
    END CASE;
    RETURN ROW_COUNT()-1;
END //



DROP FUNCTION IF EXISTS BindGroup//
CREATE FUNCTION `BindGroup` (
        `pTeacherID` INT,
        `pDisciplineID` INT,
        `pGroupID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, vSemesterID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -3;

    # 1. check if AccessedTeacher is author
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # 2. check if group is bound to discipline
    SELECT disciplines_groups.ID INTO vChecker
        FROM `disciplines_groups`
        WHERE   disciplines_groups.GroupID = pGroupID AND
                disciplines_groups.DisciplineID = pDisciplineID
        LIMIT 1;
    IF vChecker > 0 THEN
        RETURN 1;
    END IF;

    # TODO: add param SemesterID
    SELECT disciplines.SemesterID INTO vSemesterID
        FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    # 3. delete students of this group which were bound to discipline before
    DELETE FROM `disciplines_students`
        WHERE   disciplines_students.DisciplineID = pDisciplineID AND
                disciplines_students.StudentID IN (
                    SELECT students_groups.StudentID
                        FROM `students_groups`
                        WHERE   students_groups.GroupID = pGroupID AND
                                students_groups.SemesterID = vSemesterID
                );

    # 4. bind whole group
    INSERT INTO `disciplines_groups`
        (DisciplineID, GroupID)
        VALUES (pDisciplineID, pGroupID );
    RETURN 0;
END //



DROP FUNCTION IF EXISTS BindStudent//
CREATE FUNCTION `BindStudent` (
        `pTeacherID` INT,
        `pDisciplineID` INT,
        `pStudentID` INT
    )   RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, vGroupID, vTemp, vSemesterID INT DEFAULT -1;
    DECLARE vInGroup BOOLEAN DEFAULT FALSE;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vSemesterID = GetDisciplineSemesterID(pDisciplineID);

    # 1. check if AccessedTeacher is author
    IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    # TODO: extract method - Get Student Group
    # 2. check if student's group is bound yet
    SET vInGroup = EXISTS(
        SELECT disciplines_groups.ID
            FROM `students_groups`
            INNER JOIN `disciplines_groups` ON disciplines_groups.DisciplineID = pDisciplineID AND
                                               disciplines_groups.GroupID = students_groups.GroupID
            WHERE students_groups.StudentID = pStudentID AND
                  students_groups.SemesterID = vSemesterID
            LIMIT 1
    );

    # 3. bind student
    IF vInGroup THEN # student in group -> try to remove detached attribute
        DELETE FROM `disciplines_students`
            WHERE   disciplines_students.DisciplineID = pDisciplineID AND
                    disciplines_students.StudentID = pStudentID
            LIMIT 1;
    ELSE # bind stand alone student ;(
        INSERT INTO `disciplines_students`
            (DisciplineID, StudentID, Type)
            VALUES (pDisciplineID, pStudentID, 'attach')
            ON DUPLICATE KEY UPDATE
                disciplines_students.Type = 'attach';
    END IF;

    RETURN 0;
END //



DROP FUNCTION IF EXISTS UnbindGroup//
CREATE FUNCTION `UnbindGroup` (
        `pTeacherID` INT,
        `pDisciplineID` INT,
        `pGroupID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vSemesterID INT DEFAULT -1;

    IF NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR InternalIsMapLocked(pDisciplineID)THEN
        RETURN -1;
    END IF;

    # detach group from the discipline
    DELETE FROM `disciplines_groups`
        WHERE   disciplines_groups.DisciplineID = pDisciplineID AND
                disciplines_groups.GroupID = pGroupID
        LIMIT 1;

    # TODO: add param SemesterID
    SELECT disciplines.SemesterID INTO vSemesterID
        FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    # delete attached, and detached (doesn't take disc in any case)
    DELETE FROM `disciplines_students`
    WHERE disciplines_students.DisciplineID = pDisciplineID AND
          disciplines_students.StudentID IN (
              SELECT students_groups.StudentID
                 FROM `students_groups`
                 WHERE  students_groups.GroupID = pGroupID AND
                        students_groups.SemesterID = vSemesterID
          );
    RETURN 0;
END //



DROP FUNCTION IF EXISTS UnbindStudent//
CREATE FUNCTION `UnbindStudent` (
        `pTeacherID` INT,
        `pDisciplineID` INT,
        `pStudentID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vInGroup, vSemesterID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    SET vSemesterID = GetDisciplineSemesterID(pDisciplineID);

    # TODO: extract method - GetDisciplineSemesterID
    # TODO: join students_groups by discipline SemesterID

    # try to get general group, if student in it.
    SELECT disciplines_groups.ID INTO vInGroup
        FROM `students`
        INNER JOIN `students_groups` ON students_groups.StudentID = students.ID AND
                                        students_groups.SemesterID = vSemesterID
        INNER JOIN `disciplines_groups` ON disciplines_groups.DisciplineID = pDisciplineID AND
                                           disciplines_groups.GroupID = students_groups.GroupID
        WHERE students.ID = pStudentID
        LIMIT 1;

    IF vInGroup > 0 THEN # student in general group
        INSERT INTO `disciplines_students`
            (DisciplineID, StudentID, Type)
            VALUES (pDisciplineID, pStudentID, 'detach');
    ELSE
        DELETE FROM `disciplines_students`
        WHERE disciplines_students.DisciplineID = pDisciplineID AND
            disciplines_students.StudentID = pStudentID
        LIMIT 1;
    END IF;
    RETURN 0;
END //



DROP FUNCTION IF EXISTS BindTeacher//
CREATE FUNCTION `BindTeacher` (
        `pAuthorTeacherID` INT,
        `pBindingTeacherID` INT,
        `pDisciplineID` INT
    ) RETURNS int(11) # 0 - if was attached (or already bound)
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
    IF  NOT InternalIsTeacherAuthor(pAuthorTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    # try to insert BindingTeacher in access list
    INSERT INTO `disciplines_teachers`
        (DisciplineID, TeacherID)
        VALUES (pDisciplineID, pBindingTeacherID)
        ON DUPLICATE KEY UPDATE # just stub
            disciplines_teachers.ID = LAST_INSERT_ID(disciplines_teachers.ID);
    RETURN 0;
END //



DROP FUNCTION IF EXISTS UnbindTeacher//
CREATE FUNCTION `UnbindTeacher` (
        `pAuthorTeacherID` INT,
        `pBindingTeacher` INT,
        `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    IF pAuthorTeacherID = pBindingTeacher OR
       NOT InternalIsTeacherAuthor(pAuthorTeacherID, pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    DELETE FROM `disciplines_teachers`
        WHERE   disciplines_teachers.DisciplineID = pDisciplineID AND
                disciplines_teachers.TeacherID = pBindingTeacher;
    RETURN ROW_COUNT()-1;
END //


# assign new author to discipline
DROP FUNCTION IF EXISTS DelegateDiscipline//
CREATE FUNCTION `DelegateDiscipline` (
        `pAuthorTeacherID` INT,
        `pNewAuthorID` INT,
        `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    IF  pAuthorTeacherID = pNewAuthorID OR
        NOT InternalIsTeacherAuthor(pAuthorTeacherID, pDisciplineID) OR
        NOT InternalIsTeacherBound(pNewAuthorID, pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    UPDATE `disciplines`
        SET disciplines.AuthorID = pNewAuthorID
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //


# erase all discipline's rates(and logs), unlock discipline for editing
DROP FUNCTION IF EXISTS ClearDiscipline//
CREATE FUNCTION `ClearDiscipline` (
        `pAuthorTeacherID` INT,
        `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    IF NOT InternalIsTeacherAuthor(pAuthorTeacherID, pDisciplineID) THEN
        RETURN -1;
    END IF;

    # clear logs
    DELETE FROM `logs_rating`
        WHERE logs_rating.SubModuleID IN
            (SELECT view_roadmap.SubmoduleID
                FROM `view_roadmap`
                WHERE view_roadmap.DisciplineID = pDisciplineID);

    # clear rating
    DELETE FROM `rating_table`
        WHERE rating_table.SubModuleID IN
            (SELECT view_roadmap.SubmoduleID
             FROM `view_roadmap`
             WHERE view_roadmap.DisciplineID = pDisciplineID);

    # unlock discipline
    UPDATE `disciplines`
        SET disciplines.isLocked = 0
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    RETURN ROW_COUNT()-1;
END //



DROP FUNCTION IF EXISTS DeleteDiscipline//
DROP FUNCTION IF EXISTS Discipline_Delete//
CREATE FUNCTION `Discipline_Delete` (
        `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    # delete roadmap
    DELETE FROM `submodules`
        WHERE submodules.ModuleID IN (
            SELECT modules.ID
                FROM `modules`
                WHERE modules.DisciplineID = pDisciplineID
        );
    DELETE FROM `modules`
        WHERE modules.DisciplineID = pDisciplineID;

    # detach all entities from discipline
    DELETE FROM `disciplines_teachers`
        WHERE disciplines_teachers.DisciplineID = pDisciplineID;
    DELETE FROM `disciplines_students`
        WHERE disciplines_students.DisciplineID = pDisciplineID;
    DELETE FROM `disciplines_groups`
        WHERE disciplines_groups.DisciplineID = pDisciplineID;

    # delete discipline
    DELETE FROM `disciplines`
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;

    RETURN 0;
END //


# get count of related with discipline records in rating_table
DROP FUNCTION IF EXISTS CountRatings//
DROP FUNCTION IF EXISTS Discipline_CountRatings//
CREATE FUNCTION `Discipline_CountRatings` (
        `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vRes INT DEFAULT 0;

    SELECT COUNT(rating_table.StudentID)
        INTO vRes
        FROM `rating_table`
        INNER JOIN `submodules` ON rating_table.SubmoduleID = submodules.ID
        INNER JOIN `modules` ON submodules.ModuleID = modules.ID
        WHERE modules.DisciplineID = pDisciplineID
        LIMIT 1;

    RETURN vRes;
END //



DROP FUNCTION IF EXISTS RestrictAfterMilestone//
CREATE FUNCTION `RestrictAfterMilestone` (
        `pTeacherID` INT,
        `pDisciplineID` INT,
        `pMilestone` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    UPDATE `disciplines`
        SET disciplines.MilestoneDate = CURDATE(),
            disciplines.Milestone = pMilestone
        WHERE disciplines.ID = pDisciplineID
        LIMIT 1;
    RETURN 0;
END //


DROP FUNCTION IF EXISTS RestrictAfterMilestoneForCredits//
CREATE FUNCTION `RestrictAfterMilestoneForCredits` (
        `pTeacherID` INT,
        `pFacultyID` INT,
        `pMilestone` INT,
        `pSemesterID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    UPDATE `disciplines`
        SET disciplines.MilestoneDate = CURDATE(),
            disciplines.Milestone = pMilestone
        WHERE    disciplines.FacultyID= pFacultyID AND
                disciplines.SemesterID = pSemesterID AND
                ( disciplines.ExamType = 'credit' OR disciplines.ExamType = 'grading_credit');
    RETURN 0;
END //

# -------------------------------------------------------------------------------------------
# Label: modules
# Label: roadmap
# -------------------------------------------------------------------------------------------



DROP FUNCTION IF EXISTS ChangeModuleName//
CREATE FUNCTION `ChangeModuleName` (
        `pTeacherID` INT,
        `pModuleID` INT,
        `pName` VARCHAR(200) CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    UPDATE `modules`
        SET modules.Name = pName
        WHERE modules.ID = pModuleID AND
              modules.Type = 'regular' AND
              NOT InternalIsMapLocked(modules.DisciplineID)
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //



DROP FUNCTION IF EXISTS AddModule//
CREATE FUNCTION `AddModule` (
        `pTeacherID` INT,
        `pDisciplineID` INT,
        `pName` VARCHAR(200) CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vOrderNum INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -2;
    END IF;

    # get free orderNum
    SELECT MAX(modules.OrderNum)+1 INTO vOrderNum
        FROM `modules`
        WHERE   modules.DisciplineID = pDisciplineID AND modules.Type = 'regular'
        LIMIT 1;
    IF vOrderNum IS NULL THEN
        SET vOrderNum = 1;
    END IF;

    INSERT INTO `modules`
        (Name, OrderNum, DisciplineID )
        VALUES (pName, vOrderNum, pDisciplineID);
    RETURN LAST_INSERT_ID();
END //



DROP FUNCTION IF EXISTS AddModuleExam//
CREATE FUNCTION `AddModuleExam` (
        `pTeacherID` INT,
        `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, vModule INT DEFAULT -1;
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # TODO: exists
    # check exam module existence
    SELECT modules.ID
        INTO vChecker
        FROM `modules`
        WHERE   modules.DisciplineID = pDisciplineID AND
                modules.Type = 'exam';
    IF vChecker > 0 THEN
        RETURN -2;
    END IF;

    INSERT INTO `modules`
        (Name, OrderNum, DisciplineID, Type)
        VALUES  ('Экзамен' , 3141592 , pDisciplineID, 'exam');

    SET vModule = LAST_INSERT_ID();
    # 3 attempt for pass exam
    SET vChecker = AddSubmodule(pTeacherID, vModule, 40, '', NULL, 'LandmarkControl');
    SET vChecker = AddSubmodule(pTeacherID, vModule, 40, '', NULL, 'LandmarkControl');
    SET vChecker = AddSubmodule(pTeacherID, vModule, 40, '', NULL, 'LandmarkControl');
    RETURN vModule;
END //


DROP FUNCTION IF EXISTS AddModuleExtra//
CREATE FUNCTION `AddModuleExtra` (
        `pTeacherID` INT,
        `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, vModule, vType, vGap INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # try to find existing extra module
    SELECT modules.ID INTO vChecker
        FROM `modules`
        WHERE modules.DisciplineID = pDisciplineID AND modules.Type = 'extra'
        LIMIT 1;
    IF vChecker > 0 THEN
        RETURN -2;
    END IF;

    # add extra module
    INSERT INTO `modules`
        (Name, OrderNum, DisciplineID, Type)
        VALUES ('Добор баллов' , 2900666 , pDisciplineID, 'extra');


    # get discipline exam type
    SELECT  modules.ID, disciplines.ExamType
        INTO vModule, vType
        FROM `modules`
        INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID
        WHERE   modules.DisciplineID = pDisciplineID AND
                modules.Type = 'extra'
        LIMIT 1;
    IF vModule <= 0 THEN
        RETURN -1;
    END IF;

    # 1 extra attempt for exam and 2 for credit
    SET vGap = -1;
    IF vType = 1 THEN # exam
        SET vGap = 7;
    END IF;
    IF vType = 2 OR vType = 3 THEN # credit, grading_credit
        SET vGap = 29;
        SET vChecker = AddSubmodule(pTeacherID, vModule, vGap, '', NULL, 'LandmarkControl');
    END IF;

    SET vChecker = AddSubmodule(pTeacherID, vModule, vGap, '', NULL, 'LandmarkControl');
    RETURN vModule;
END //


DROP FUNCTION IF EXISTS DeleteModule//
CREATE FUNCTION `DeleteModule` (
        `pTeacherID` INT,
        `pModuleID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vDisciplineID INT DEFAULT -1;

    # get discipline ID
    SELECT disciplines.ID INTO vDisciplineID
        FROM `modules`
        INNER JOIN `disciplines`    ON  modules.DisciplineID = disciplines.ID AND
                                        disciplines.AuthorID = pTeacherID
        WHERE modules.ID = pModuleID
        LIMIT 1;

    # check rights
    IF  NOT InternalIsTeacherAuthor(pTeacherID, vDisciplineID) OR
        InternalIsMapLocked(vDisciplineID)
    THEN
        RETURN -1;
    END IF;

    DELETE FROM `submodules`
        WHERE submodules.ModuleID = pModuleID;
    DELETE FROM `modules`
        WHERE modules.ID = pModuleID;

    # restore continuous ordering
    SET @counter = 0;
    UPDATE `modules`
        SET modules.OrderNum = (@counter := @counter + 1)
        WHERE   modules.DisciplineID = vDisciplineID AND
                modules.Type = 'regular'
        ORDER BY modules.OrderNum ASC;

    RETURN 0;
END //



DROP FUNCTION IF EXISTS DeleteModuleExam//
CREATE FUNCTION `DeleteModuleExam` (
        `pTeacherID` INT,
        `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vExamModuleID INT DEFAULT -1;
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # get exam module ID
    SELECT modules.ID INTO vExamModuleID
        FROM `modules`
        WHERE   modules.Type = 'exam' AND
                modules.DisciplineID = pDisciplineID
        LIMIT 1;
    IF vExamModuleID <= 0 THEN
        RETURN -1;
    END IF;

    DELETE FROM `submodules`
        WHERE vExamModuleID = submodules.ModuleID;
    DELETE FROM `modules`
        WHERE vExamModuleID = modules.ID
        LIMIT 1;

    RETURN 0;
END //



DROP FUNCTION IF EXISTS SwapModuleOrder//
CREATE FUNCTION `SwapModuleOrder` (
        `pTeacherID` INT,
        `pModuleID1` INT,
        `pModuleID2` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, vOrder1, vOrder2,
            vDisciplineID1, vDisciplineID2 INT DEFAULT -1;

    # get disciplineID and orderNum for 1st module(pModuleID1)
    SELECT  modules.OrderNum,
            modules.DisciplineID
        INTO vOrder1, vDisciplineID1
        FROM `modules`
        INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                modules.ID = pModuleID1 AND
                modules.Type = 'regular'
        LIMIT 1;

    # get disciplineID and orderNum for 2st module(pModuleID2)
    SELECT  modules.OrderNum,
            modules.DisciplineID
        INTO vOrder2, vDisciplineID2
        FROM `modules`
        INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                modules.ID = pModuleID2 AND
                modules.Type = 'regular'
        LIMIT 1;

    # check that modules belong to one discipline, check rights
    IF  vDisciplineID1 != vDisciplineID2 OR vDisciplineID1 <= 0 OR
        InternalIsMapLocked(vDisciplineID1) THEN
        RETURN -1;
    END IF;

    # swap
    UPDATE `modules`
        SET modules.OrderNum = 271828
        WHERE modules.ID = pModuleID1;
    UPDATE `modules`
        SET modules.OrderNum = vOrder1
        WHERE modules.ID = pModuleID2
        LIMIT 1;
    UPDATE `modules`
        SET modules.OrderNum = vOrder2
        WHERE modules.ID = pModuleID1
        LIMIT 1;

    RETURN 0;
END //



DROP FUNCTION IF EXISTS AddModuleBonus//
CREATE FUNCTION `AddModuleBonus` (
        `pTeacherID` INT,
        `pDisciplineID` INT
    )   RETURNS INT(11)
    NO SQL
BEGIN
    DECLARE vChecker, vModuleID INT DEFAULT -1;
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # check existing of bonus module
    SELECT modules.ID INTO vChecker
        FROM `modules`
        WHERE   modules.DisciplineID = pDisciplineID AND
                modules.Type = 'bonus';
    IF vChecker > 0 THEN
        RETURN -2;
    END IF;

    INSERT INTO `modules`
        (Name, OrderNum, DisciplineID, Type)
        VALUES  ('Бонусные баллы' , 2141692 , pDisciplineID, 'bonus');

    SET vModuleID = LAST_INSERT_ID();
    SET vChecker = AddSubmodule(pTeacherID, vModuleID, 10, '', NULL, 'LandmarkControl');
    RETURN 0;
END //


DROP FUNCTION IF EXISTS DeleteModuleBonus//
CREATE FUNCTION `DeleteModuleBonus` (
        `pTeacherID` INT,
        `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vBonusModuleID INT DEFAULT -1;
    IF  NOT InternalIsTeacherAuthor(pTeacherID, pDisciplineID) OR
        InternalIsMapLocked(pDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # get bonus module ID
    SELECT modules.ID INTO vBonusModuleID
        FROM `modules`
        WHERE   modules.Type = 'bonus' AND
                modules.DisciplineID = pDisciplineID
        LIMIT 1;
    IF vBonusModuleID <= 0 THEN
        RETURN -1;
    END IF;

    DELETE FROM `submodules`
        WHERE vBonusModuleID = submodules.ModuleID;

    DELETE FROM `modules`
        WHERE vBonusModuleID = modules.ID
        LIMIT 1;

    RETURN 0;
END //



# -------------------------------------------------------------------------------------------
# Label: submodules
# Label: roadmap
# -------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS ChangeSubmoduleMaxAndControl//
CREATE FUNCTION `ChangeSubmoduleMaxAndControl` (
        `pTeacherID`     INT,
        `pSubmoduleID`   INT,
        `pMaxRate`       INT,
        `pControlType`   VARCHAR(30) CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker, vDisciplineID, vIsLocked, vNewDiscMaxRate, vCurRate INT DEFAULT -1;

    # check that discipline and submodule exists and doesn't locked
    SELECT  disciplines.isLocked,
            view_disciplines_results.DisciplineRateMax - submodules.MaxRate + pMaxRate
            INTO vIsLocked, vNewDiscMaxRate
        FROM `submodules`
        INNER JOIN `modules`        ON  submodules.ModuleID = modules.ID
        INNER JOIN `disciplines`    ON  disciplines.ID = modules.DisciplineID
        INNER JOIN `view_disciplines_results` ON view_disciplines_results.DisciplineID = disciplines.ID
        WHERE submodules.ID = pSubmoduleID AND
              disciplines.AuthorID = pTeacherID
        LIMIT 1;
    IF  vIsLocked != 0 OR
        vNewDiscMaxRate > 100
    THEN
        RETURN -1;
    END IF;

    UPDATE `submodules`
        SET     submodules.MaxRate = pMaxRate,
                submodules.Type = pControlType
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END //



DROP FUNCTION IF EXISTS ChangeSubmoduleName//
CREATE FUNCTION `ChangeSubmoduleName` (
        `pTeacherID` INT,
        `pSubmoduleID` INT,
        `pName` VARCHAR(200) CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vIsLocked INT DEFAULT -1;

    SELECT disciplines.isLocked INTO vIsLocked
        FROM `submodules`
        INNER JOIN `modules`        ON  submodules.ModuleID = modules.ID
        INNER JOIN `disciplines`    ON  disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID
        LIMIT 1;
    IF  vIsLocked != 0 THEN
        RETURN -1;
    END IF;

    UPDATE `submodules`
        SET submodules.Name = pName
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;
    RETURN 0;
END //



DROP FUNCTION IF EXISTS ChangeSubmoduleDescription//
CREATE FUNCTION `ChangeSubmoduleDescription` (
        `pTeacherID` INT,
        `pSubmoduleID` INT,
        `pDescription` VARCHAR(200) CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vIsLocked INT DEFAULT -1;

    SELECT disciplines.isLocked INTO vIsLocked
        FROM `submodules`
        INNER JOIN `modules`        ON  submodules.ModuleID = modules.ID
        INNER JOIN `disciplines`    ON  disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID
        LIMIT 1;
    IF  vIsLocked != 0 THEN
        RETURN -1;
    END IF;

    UPDATE `submodules`
        SET submodules.Description  = pDescription
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;
    RETURN 0;
END //




DROP FUNCTION IF EXISTS DeleteSubmodule//
CREATE FUNCTION `DeleteSubmodule` (
        `pTeacherID` INT,
        `pSubmoduleID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vIsLocked, vModuleID INT DEFAULT -1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SELECT modules.ID, disciplines.isLocked
        INTO vModuleID, vIsLocked
        FROM `submodules`
        INNER JOIN `modules` ON modules.ID = submodules.ModuleID
        INNER JOIN `disciplines` ON modules.DisciplineID = disciplines.ID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID
        LIMIT 1;
    IF  vIsLocked != 0 THEN
        RETURN -1;
    END IF;

    # handler will catch constraint violation
    DELETE FROM `submodules`
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;

    # restore continuous ordering
    SET @counter = 0;
    UPDATE `submodules`
        SET submodules.OrderNum = (@counter := @counter + 1)
        WHERE   submodules.ModuleID = vModuleID
        ORDER BY submodules.OrderNum ASC;

    RETURN 0;
END //



DROP FUNCTION IF EXISTS AddSubmodule//
CREATE FUNCTION `AddSubmodule` (
        `pTeacherID` INT,
        `pModuleID` INT,
        `pMaxRate` INT,
        `pName` VARCHAR(200) CHARSET utf8,
        `pDescription` VARCHAR(200) CHARSET utf8,
        `pControlType` VARCHAR(30) CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vOrderNum, vIsLocked INT DEFAULT -1;
    DECLARE vDescription VARCHAR(200) CHARSET utf8 DEFAULT pDescription;
    # DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    # check author and discipline lock
    SELECT disciplines.isLocked INTO vIsLocked
        FROM `modules`
        INNER JOIN `disciplines`    ON  disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                modules.ID = pModuleID
        LIMIT 1;
    IF  vIsLocked != 0 THEN
        RETURN -2;
    END IF;

    # get free order
    SET vOrderNum = 0;
    SELECT MAX(submodules.OrderNum) INTO vOrderNum
        FROM `submodules`
        WHERE submodules.ModuleID = pModuleID
        LIMIT 1;
    IF vOrderNum IS NULL THEN
        SET vOrderNum = 0;
    END IF;
    SET vOrderNum = vOrderNum + 1;

    # insert submodule

    IF vDescription = '' THEN
        SET vDescription = NULL;
    END IF;
    INSERT INTO `submodules`
        (ModuleID, MaxRate, OrderNum, Name, Description, Type)
        VALUES  (pModuleID, pMaxRate, vOrderNum, pName, vDescription, pControlType);

    RETURN  LAST_INSERT_ID();
END //



DROP FUNCTION IF EXISTS SwapSubmoduleOrder//
CREATE FUNCTION `SwapSubmoduleOrder` (
        `pTeacherID` INT,
        `pSubmoduleID1` INT,
        `pSubmoduleID2` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vDisciplineID, vOrder1, vOrder2,
            vModule1, vModule2 INT DEFAULT -1;

    SELECT  submodules.OrderNum,
            submodules.ModuleID,
            disciplines.ID
        INTO vOrder1, vModule1, vDisciplineID
        FROM `submodules`
        INNER JOIN `modules`        ON  submodules.ModuleID = modules.ID
        INNER JOIN `disciplines`    ON  disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID1
        LIMIT 1;

    SELECT  submodules.OrderNum,
            submodules.ModuleID
        INTO vOrder2, vModule2
        FROM `submodules`
        INNER JOIN `modules`        ON  submodules.ModuleID = modules.ID
        INNER JOIN `disciplines`    ON  disciplines.ID = modules.DisciplineID
        WHERE   disciplines.AuthorID = pTeacherID AND
                submodules.ID = pSubmoduleID2
        LIMIT 1;

    # check, that modules exists and belong to one discipline
    IF  vModule1 <= 0 OR vModule1 != vModule2 OR
        InternalIsMapLocked(vDisciplineID)
    THEN
        RETURN -1;
    END IF;

    # swap
    UPDATE `submodules`
        SET submodules.OrderNum     = 271828
        WHERE submodules.ID = pSubmoduleID1
        LIMIT 1;
    UPDATE `submodules`
        SET submodules.OrderNum     = vOrder1
        WHERE submodules.ID = pSubmoduleID2
        LIMIT 1;
    UPDATE `submodules`
        SET submodules.OrderNum     = vOrder2
        WHERE submodules.ID = pSubmoduleID1
        LIMIT 1;
    RETURN 0;
END //


# -------------------------------------------------------------------------------------------
# Label: rating
# -------------------------------------------------------------------------------------------

# TODO: kill
DROP FUNCTION IF EXISTS GetMaxRateForDisc//
CREATE FUNCTION `GetMaxRateForDisc` (
        `pDisciplineID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vResult INT DEFAULT 0;

    SELECT SUM(submodules.MaxRate)
        INTO vResult
        FROM `modules`
        LEFT JOIN `submodules` ON   submodules.ModuleID = modules.ID
        WHERE   modules.DisciplineID = pDisciplineID AND
                submodules.IsUsed != 0 AND
                (modules.Type = 1 OR ( modules.Type = 2 AND submodules.OrderNum = 1))
        LIMIT 1;
    RETURN  (vResult);
END //


# Вычисление максимального балла для submodule
DROP FUNCTION IF EXISTS CalculateMaxRateForExtra//
CREATE FUNCTION `CalculateMaxRateForExtra` (
        `pSubmoduleID` INT,
        `pStudentID` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vExamType INT DEFAULT -1; # enum('exam', 'credit');# utf8;
    DECLARE vLim INT;
    DECLARE vResult INT DEFAULT 0;

    SELECT  disciplines.ExamType INTO vExamType
        FROM `submodules`
        INNER JOIN `modules` ON modules.ID = submodules.ModuleID
        INNER JOIN `disciplines` ON disciplines.ID = modules.DisciplineID
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;

    IF vExamType = -1 THEN
        RETURN -1;
    ELSEIF vExamType = 1 THEN # exam
        SET vLim = 38;
    ELSE # credit or grading_credit
        SET vLim = 60;
    END IF;

    SELECT  view_rating_result.RateRegular INTO vResult
        FROM `submodules`
        INNER JOIN `modules` ON modules.ID = submodules.ModuleID
        INNER JOIN view_rating_result ON view_rating_result.DisciplineID = modules.DisciplineID AND
                                         view_rating_result.StudentID = pStudentID
        WHERE submodules.ID = pSubmoduleID
        LIMIT 1;
    RETURN vLim - vResult;
END //


DROP FUNCTION IF EXISTS SetStudentRate//
CREATE FUNCTION `SetStudentRate` (
        `pTeacherID` INT,
        `pStudentID` INT,
        `pSubmoduleID` INT,
        `pRate` INT
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vDisciplineID, vMaxRate, vModuleType, vSemesterID INT DEFAULT -1;
    DECLARE vIsOver, vIsLocked, vIsUsed BOOLEAN DEFAULT FALSE;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    SET vIsOver = TRUE;
    SELECT  disciplines.ID,
            disciplines.SemesterID,
            disciplines.isLocked,
            disciplines.Milestone,
            submodules.IsUsed,
            submodules.maxRate,
            modules.Type
        INTO vDisciplineID, vSemesterID, vIsLocked, vIsOver, vIsUsed, vMaxRate, vModuleType
        FROM `submodules`
        INNER JOIN `modules`                ON  submodules.ModuleID = modules.ID
        INNER JOIN `disciplines`            ON  modules.DisciplineID = disciplines.ID
        WHERE   submodules.ID = pSubModuleID
        LIMIT 1;

    # correct max rate for extra module
    IF vModuleType = 4 THEN # 4 - extra
        SET vMaxRate = CalculateMaxRateForExtra(pSubmoduleID, pStudentID);
    END IF;

    # 1) check rights
    # 2) check, you can't rate regular and bonus after milestone
    # 3) check, max rate exceeding
    IF  NOT InternalIsStudentAttached(pStudentID, vDisciplineID, vSemesterID) OR
        NOT InternalIsTeacherBound(pTeacherID, vDisciplineID) OR
        pRate > vMaxRate OR
        (vIsOver AND (vModuleType = 1 OR vModuleType = 3)) # 1 - regular, 3 - bonus
    THEN
        RETURN -2;
    END IF;

    # add rate, or update old
    SET @tmp = 0;
    INSERT INTO `rating_table`
        (StudentID, TeacherID, SubmoduleID, Rate, Date)
        VALUES  ( pStudentID, pTeacherID, pSubmoduleID, pRate, CURDATE())
        ON DUPLICATE KEY UPDATE
            rating_table.TeacherID  = (@tmp := pTeacherID),
            rating_table.Rate       = pRate,
            rating_table.Date       = CURDATE();

    # log rate
    INSERT INTO `logs_rating`
        (StudentID, SubmoduleID, TeacherID, Rate, Action )
        VALUES  (pStudentID, pSubModuleID, pTeacherID, pRate,
                        CASE WHEN @tmp > 0 THEN 'add' ELSE 'change' END);

    # lock discipline for structure editing
    IF NOT vIsLocked THEN
        UPDATE `disciplines`
        SET disciplines.isLocked = TRUE
        WHERE disciplines.ID = vDisciplineID
        LIMIT 1;
    END IF;

    # add submodule to max rate counting
    IF NOT vIsUsed THEN
        UPDATE `submodules`
            SET submodules.IsUsed = TRUE
            WHERE submodules.ID = pSubModuleID
            LIMIT 1;
    END IF;
    RETURN 0;
END //





# -------------------------------------------------------------------------------------------
# Label: requests
# -------------------------------------------------------------------------------------------



DROP FUNCTION IF EXISTS SetRequestStatus//
CREATE FUNCTION `SetRequestStatus` (
        `pRequestID` INT,
        `pStatus` VARCHAR(20) CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    UPDATE `requests`
        SET requests.Status = pStatus
        WHERE requests.ID = pRequestID
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END//


DROP FUNCTION IF EXISTS CreateRequest//
CREATE FUNCTION `CreateRequest`
    (   `pAccountID` INT,
        `pTitle` VARCHAR(50) CHARSET utf8,
        `pDescription` TEXT CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -1;

    INSERT INTO `requests`
        (AccountID, Title, Description, Status)
        VALUES  (pAccountID, pTitle, pDescription, 'opened');
    RETURN LAST_INSERT_ID();
END//




# -------------------------------------------------------------------------------------------
# Label: recovery
# -------------------------------------------------------------------------------------------



DROP FUNCTION IF EXISTS CreateRecoveryToken//
CREATE FUNCTION `CreateRecoveryToken` (
        `pAccountOrEMail` VARCHAR(255) CHARSET utf8,
        `pToken` VARCHAR(100) CHARSET utf8
    ) RETURNS VARCHAR(255) charset utf8
    NO SQL
BEGIN
    DECLARE vAccountID INT DEFAULT -1;
    DECLARE vUserFullName TEXT charset utf8;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN -2;

    # get account ID
    SELECT accounts.ID INTO vAccountID
        FROM `accounts`
        WHERE accounts.EMail = pAccountOrEMail OR
              accounts.Login = pAccountOrEMail
        LIMIT 1;
    IF vAccountID <= 0 THEN
        RETURN '';
    END IF;

    SET vUserFullName = GetUserFullNameByAccountID(vAccountID);
    IF vUserFullName IS NULL OR vUserFullName = '' THEN
        RETURN '';
    END IF;

    # transform all unused recovery tokens into used
    UPDATE `recovery_tokens`
    SET recovery_tokens.isUsed = 1
    WHERE   recovery_tokens.isUsed = 0 AND
            recovery_tokens.AccountID = vAccountID;

    # handle catch constraints violations
    INSERT INTO `recovery_tokens`
        ( AccountID, Token )
        VALUES  (vAccountID, pToken);
    RETURN vUserFullName;
END//

DROP FUNCTION IF EXISTS GetUserFullNameByAccountID//
CREATE FUNCTION `GetUserFullNameByAccountID` (
        `pAccountID` INT(11)
    ) RETURNS VARCHAR(255) charset utf8
NO SQL
BEGIN
    DECLARE vUserFullName VARCHAR(255) charset utf8;
    DECLARE vChecker INT DEFAULT -1;

    # try to find student with that account id
    SELECT students.ID, CONCAT(students.LastName,' ',students.FirstName,' ',students.SecondName)
        INTO vChecker, vUserFullName
        FROM `students`
        WHERE students.AccountID = pAccountID
        LIMIT 1;

    IF vChecker <= 0 THEN # try to find teacher with that account id
        SELECT teachers.ID, CONCAT(teachers.LastName,' ',teachers.FirstName,' ',teachers.SecondName)
            INTO vChecker, vUserFullName
            FROM `teachers`
            WHERE teachers.AccountID = pAccountID
            LIMIT 1;

        IF vChecker <= 0 THEN
          RETURN '';
        END IF;
    END IF;

    RETURN vUserFullName;
END//

DROP FUNCTION IF EXISTS UseRecoveryToken//
CREATE FUNCTION `UseRecoveryToken` (
        `pToken` VARCHAR(100) CHARSET utf8
    ) RETURNS int(11)
    NO SQL
BEGIN
    DECLARE vChecker INT DEFAULT -1;

    # set token used
    UPDATE `recovery_tokens`
        SET recovery_tokens.IsUsed = 1
        WHERE recovery_tokens.Token = pToken
        LIMIT 1;
    RETURN ROW_COUNT()-1;
END//

DROP FUNCTION IF EXISTS SaveSession //
CREATE FUNCTION SaveSession
    (   pSessionID VARCHAR(40),
        pUserLogin VARCHAR(50) CHARSET utf8,
        pUserPassword VARCHAR(64)
    ) RETURNS INT(11)
    NO SQL
BEGIN
    INSERT INTO sessions (SessionID, Login, Password)
        VALUES(pSessionID, pUserLogin, pUserPassword);
    RETURN LAST_INSERT_ID();
END //

DELIMITER ;