-- sql procedura neni urcite spustitelna, psal sem to ve vlaku v textaku .. je to zapis myslenky

CREATE PROCEDURE user_login_handler(IP VARCHAR(15), USERNAME VARCHAR(40), PASS VARCHAR(40))
BEGIN
DECLARE LOGIN_KEY VARCHAR(40);
DECLARE SESSION_KEY VARCHAR(40);
DECLARE userid INT(10);
DECLARE userpass VARCHAR(40);
SET userid = SELECT UID FROM users  WHERE nick = USERNAME;
SET userpass = SELECT passhash FROM users WHERE nick = USERNAME;
IF (userid) THEN
    IF (userpass = sha1(PASS)) THEN

        SET LOGIN_KEY = SELECT KEYVALUE FROM security_keys WHERE SKID = 2;
        SET SESSION_KEY = SELECT KEYVALUE FROM security_keys WHERE SKID = 1;

        IF (LOGIN_KEY AND SESSION_KEY) THEN

        DECLARE LOGIN_KEY_HASH VARCHAR(40);
        DECLARE SESSION_KEY_HASH VARCHAR(40);
        SET LOGIN_KEY_HASH = sha1(CONCAT(PASS,TIME,LOGIN_KEY));
        SET SESSION_KEY_HASH = sha1(CONCAT(PASS,IP,TIME,SESSION_KEY));

            IF exist (SELECT UID FROM users_keys WHERE UID = userid) THEN

                UPDATE users_keys SET LOGIN_KEY = LOGIN_KEY_HASH WHERE UID = userid;
                UPDATE users_keys SET SESSION_KEY = SESSION_KEY_HASH WHERE UID = userid;

            ELSE

            INSERT INTO users_keys (UID ,LOGIN_KEY ,COOKIES_KEY ,PROTECTED)
            VALUES (userid,LOGIN_KEY_HASH,SESSION_KEY_HASH,0);

            END IF;
                RETURN CONCAT(1,LOGIN_KEY_HASH,SESSION_KEY_HASH);
        ELSE

        RETURN CONCAT(2,sha1(2),sha1(2));
        END IF;

    ELSE

    RETURN CONCAT(3,sha1(2),sha1(2));
    END IF;
ELSE
RETURN CONCAT(4,sha1(2),sha1(2));
END IF;

END

-- veskere dalsi spojeni s db bude pres session_key, ktery bude identifikator dane relace. cookies_key - neni to jeste domyslene , ale urcene
-- to je k automatickemu prihlasovani

-- bezpecnostni klice se muzou regenerovat dle potreby,  lze menit cronem klidne kazdych 10 minut - je potreba jen k vytvoreni relace pro
-- uzivatele, pak uz se nevyuziva do dalsiho prihlasovani (popripade lze udelat bezpecnostni refresh kazdych napr 60 minut - pregenerovat
-- relaci)

-- vsechny ostatni procedury se pak museji volat s vygenerovanym klicem, podle ktereho db pozna o jakeho uzivatele jde
-- zmena hesla napr: procedura change_user_pass(KEY,STARE,NOVE). Podle klice urci uzivatele, zkusi jestli sedi stare heslo, a pokud ano, zapise nove. Navratove hodnoty jen flagy (1:success,2:bad_old,3:key_expired,4:unkown_key ....)

-- je to pracnejsi na napsani, ale vysledek je, ze web nebude potrebovat zapis do db nikde, cteni pro jednoduchost u dat, ktera nejsou kriticka
-- -vypisovat polozky eshopu pres proceduru je uz zbytecne ...


--
-- Struktura tabulky `security_keys`
--

CREATE TABLE IF NOT EXISTS `security_keys` (
  `SKID` INT(2) NOT NULL AUTO_INCREMENT COMMENT 'security key id',
  `KEYVALUE` VARCHAR(40) COLLATE utf8_bin NOT NULL COMMENT 'security key value',
  `DESCR` VARCHAR(40) COLLATE utf8_bin NOT NULL COMMENT 'decription',
  PRIMARY KEY (`SKID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=5 ;

--
-- Vypisuji data pro tabulku `security_keys`
--

INSERT INTO `security_keys` (`SKID`, `KEYVALUE`, `DESCR`) VALUES
(1, '9ecef4d8c37e772896c20384c6e4d4bbbbf4d968', 'users login key'),
(2, 'd65107910e31a728fe31f958316ba48809d8e7c1', 'users session key'),
(3, 'a5bca2ccdfa0cc1ea3423cde347b13f4955b4f30', 'administration access key'),
(4, 'ae272fc89ab540bee64d920092ef0a34edc24377', 'master key for root');

-- --------------------------------------------------------

--
-- Struktura tabulky `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `UID` INT(10) NOT NULL AUTO_INCREMENT,
  `passhash` VARCHAR(40) COLLATE utf8_bin NOT NULL,
  `nick` VARCHAR(40) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`UID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;

--
-- Vypisuji data pro tabulku `users`
--

INSERT INTO `users` (`UID`, `passhash`, `nick`) VALUES
(1, '7c4a8d09ca3762af61e59520943dc26494f8941b', 'tany');

-- --------------------------------------------------------

--
-- Struktura tabulky `users_keys`
--

CREATE TABLE IF NOT EXISTS `users_keys` (
  `UID` INT(12) NOT NULL AUTO_INCREMENT COMMENT 'user id',
  `LOGIN_KEY` VARCHAR(40) COLLATE utf8_bin NOT NULL COMMENT 'key for login',
  `COOKIES_KEY` VARCHAR(40) COLLATE utf8_bin NOT NULL COMMENT 'key for remerber login ',
  `PROTECTED` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'bool readonly user (GM ...)',
  PRIMARY KEY (`UID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;

--
-- Vypisuji data pro tabulku `users_keys`
--