% WRITTEN BY CAGDAS

classdef DatabaseManager
    %DATABASEMANAGER has the only connection to database
    %   but it never questions for the permission of user
    %it basically acts as a connector, permissions will be checked
    %by account manager
    

    
    methods (Static) 
        %saves heart rate data to db
        function res = saveHeartRateData(HR,userID)
            res = true;
            conn = database('db4free','cagdas','ru_sweCagdas');
            for i=1:HR.numOfRecords
                curs = exec(conn, ['INSERT INTO tblheartrates (`tblUserId`, `tblHRSession`, `tblHRNum`, `tblHRValue`) VALUES (''' num2str(userID) ''', ''' HR.dataSession ''',''' num2str(i) ''','''  num2str(HR.rawData(i)) ''')']);
                close(curs);
            end
            
        end
        %retrieves all HRSessions
        function res = getHRSessions(userID)
            %DATABASEMANAGER_GETUSERS returns all user names
            conn = database('db4free','cagdas','ru_sweCagdas');
            curs = exec(conn, ['SELECT DISTINCT tblHRSession FROM tblheartrates where tblUserId =''' num2str(userID) '''']);
            curs =fetch(curs);
            res = curs.Data;
        end
        %checks whether HR session exist in database
        function res = doesSessionExistinHR(Session,userID)
            %checks whether given session is already in HeartRate database
            conn = database('db4free','cagdas','ru_sweCagdas');
            curs = exec(conn, ['SELECT Count(*) FROM ruswe.tblheartrates where tblHRSession = ''' Session ''' && tblUserId =''' num2str(userID) '''']);
            curs =fetch(curs);
            if(curs.Data{1} >0) res = true; else res= false; end
            close(curs);

        end
        %checks whether sleep pattern session exist in database
        function res = doesSessionExistinPatterns(Session,userID)
            %checks whether given session is already in Patterns database
            conn = database('db4free','cagdas','ru_sweCagdas');
            curs = exec(conn, ['SELECT Count(*) FROM ruswe.tblpatterns where tblPatternSession = ''' Session ''' && tblUserId =''' num2str(userID) '''']);
            curs =fetch(curs);
            if(curs.Data(1) >0) res = true; else res= false; end
            close(curs);

        end
        %checks whether sleep suggestion session exist in database
        function res = doesSessionExistinSuggestions(Session,userID)
                    %checks whether given session is already in Patterns database
            conn = database('db4free','cagdas','ru_sweCagdas');
            curs = exec(conn, ['SELECT Count(*) FROM ruswe.tblsuggestions where tblSuggestionSession = ''' Session ''' && tblUserId =''' num2str(userID) '''']);
            curs =fetch(curs);
            if(curs.Data{1} >0) res = true; else res= false; end
            close(curs);
        end
        %retrieves all SuggestionSessions
        function res = getSuggestionSessions(userID)
            conn = database('db4free','cagdas','ru_sweCagdas');
            curs = exec(conn, ['SELECT tblSuggestionSession, tblScore FROM tblsuggestions where tblUserId =''' num2str(userID) '''']);
            curs =fetch(curs);
            res = curs.Data;
        end
        %retrieves all Sleep Pattern Sessions
        function res = getSleepSessions(userID)
            %DATABASEMANAGER_GETUSERS returns all user names
            conn = database('db4free','cagdas','ru_sweCagdas');
            curs = exec(conn, ['SELECT DISTINCT tblSleepSession, tblDevice FROM tblpatterns where tblUserId =''' num2str(userID) '''']);
            curs =fetch(curs);
            res = curs.Data;
        end
        %retrieves sleep patterns of given session & userID & device
        function data = getSleepPattern(userID,device,Session)
            conn = database('db4free','cagdas','ru_sweCagdas');
            curs = exec(conn, ['SELECT tblHypnogram FROM ruswe.tblpatterns where tblSleepSession = ''' Session ''' && tblUserId =''' num2str(userID) ''' && tblDevice =''' device '''']);
            curs =fetch(curs);
            data = str2num(curs.Data{1}')';
        
        end
        %saves sleep patterns of given userID & device 
        %session info is embeded in Pattern class
        function res = saveSleepPattern(Pattern,userID,device)
            res = true;
            conn = database('db4free','cagdas','ru_sweCagdas');
            sql =  strcat('INSERT INTO tblpatterns (`tblUserId`, `tblSleepSession`,`tblDevice` ,`tblHypnogram`) VALUES (''', num2str(userID), ''', ''', Pattern.session, ''',''', device, ''',''', Pattern.hypnogram, ''')');
            curs = exec(conn, sql);
            close(curs);
            
        end
        %retrieves sleep suggestion of given session & userID 
        function res = getSuggestion(Session,userID)
            conn = database('db4free','cagdas','ru_sweCagdas');
            curs = exec(conn, ['SELECT tblScore FROM tblsuggestions where tblSuggestionSession = ''' Session ''' && tblUserId =''' num2str(userID) '''']);
            curs =fetch(curs);
            res = curs.Data;
        end
        %saves sleep suggestion(score) of given Session & userID  
        function res = saveSuggestion(Session,userId,score)
            res = true;
            conn = database('db4free','cagdas','ru_sweCagdas');
            sql =  strcat('INSERT INTO tblsuggestions (`tblUserId`, `tblSuggestionSession`,`tblScore`) VALUES (''', num2str(userId), ''', ''', Session, ''',''', num2str(score), ''')');
            curs = exec(conn, sql);
            close(curs);
            
        end
        %retrieves HeartRateData of given session & userID 
        function data = getHeartRateData(Session, userID)
            conn = database('db4free','cagdas','ru_sweCagdas');
            curs = exec(conn, ['SELECT tblHRValue FROM ruswe.tblheartrates where tblHRSession = ''' Session ''' && tblUserId =''' num2str(userID) '''']);
            curs =fetch(curs);
            data = curs.Data;
        end
        %adds a new user to db
        function  addUser( newUser )
        %DATABASEMANAGER_ADDUSER Summary of this function goes here
        %   Detailed explanation goes here
            conn = database('db4free','cagdas','ru_sweCagdas');
            curs = exec(conn, ['INSERT INTO tblusers (`tblUsersUserName`, `tblUsersPassword`, `tblUsersType`) VALUES (''' newUser.name ''', ''' newUser.password ''',''' num2str(newUser.type) ''')']);
            close(curs);


        end
        %deletes given user from db
        function result = deleteUser(user_id)
            conn = database('db4free','cagdas','ru_sweCagdas');
            %check if deleteUser Exist and get its id
            curs = exec(conn, ['DELETE FROM tblusers WHERE idtblUsers= ''' num2str(user_id) '''']);
            result =0; 
            close(curs);
        end
        %retrieves all existing users
        function [ users ] = getAllUsers(  )
            %DATABASEMANAGER_GETUSERS returns all user names
            conn = database('db4free','cagdas','ru_sweCagdas');
            curs = exec(conn, ['SELECT ALL tblUsersUserName FROM tblusers ']);
            curs =fetch(curs);
            users = curs.Data;
        end
        %get complete user info of a user with the given name
        function [ user ] = getUserFromName( userName )
        %DATABASEMANAGER_GETUSERFROMNAME Summary of this function goes here
        % user.id = -1 couldn't find user
            conn = database('db4free','cagdas','ru_sweCagdas');
            curs = exec(conn, ['SELECT ALL idtblUsers, tblUsersUserName,tblUsersPassword,tblUsersType FROM tblusers WHERE tblUsersUserName = ''' userName '''']);
            curs =fetch(curs);
            if(size(curs.Data,2) ~=1)
                user.id =curs.Data{1}; 
                user.name =curs.Data{2};
                user.password =curs.Data{3};
                user.type =curs.Data{4};
            else %create an invalid user
                user.id = -1; user.name = ''; user.password = ''; user.type = -1;   
            end
        end


    end
    
end

