CREATE EXTENSION IF NOT EXISTS pgcrypto;

INSERT INTO "user" (username, password, last_login, created_at, updated_at)

VALUES ('Melissa', crypt('123456', gen_salt('bf')), NULL, NOW(), NOW());

CREATE OR REPLACE PROCEDURE user_login(user_name VARCHAR, user_password VARCHAR) AS $$

DECLARE

was_found BOOLEAN;

found_user_id INT;

are_valid_credentials BOOLEAN;

SELECT COUNT(*) INTO was_found

FROM "user" WHERE username = user_name;

RAISE NOTICE 'was_found: %', was_found;

IF (was_found = false) THEN

RAISE EXCEPTION 'User not found: %', user_name;

END IF;

SELECT id INTO found_user_id

FROM "user" WHERE username = user_name;

SELECT COUNT(*) INTO are_valid_credentials

FROM "user"

WHERE id = found_user_id

AND password = crypt(user_password, password);

IF (are_valid_credentials = false) THEN

INSERT INTO session_failed (user_id)

VALUES (found_user_id);

COMMIT;

RAISE EXCEPTION 'Invalid credentials';

END IF;

UPDATE "user"

SET last_login = NOW()

WHERE username = user_name;

COMMIT;

RAISE NOTICE 'User with valid credentials: %', are_valid_credentials;

END;

$$ LANGUAGE plpgsql;

CALL user_login('Stiven', '12345');

SELECT *

FROM "user";

SELECT *

FROM session_failed;

ALTER TABLE session_failed

ADD CONSTRAINT fk_user_id

FOREIGN KEY (user_id)

REFERENCES "user" (id);

SELECT *

FROM session;

ALTER TABLE session

ADD CONSTRAINT fk_user_id

FOREIGN KEY (user_id)

REFERENCES "user" (id);

CREATE OR REPLACE FUNCTION create_session_log()

RETURNS TRIGGER AS $$

INSERT INTO "session" (user_id)

VALUES (NEW.id);

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER create_session_trigger

AFTER UPDATE ON "user"

FOR EACH ROW

WHEN (OLD.last_login IS DISTINCT FROM NEW.last_login)

EXECUTE FUNCTION create_session_log();

CALL user_login('Stiven', '123456');

SELECT *

FROM "user";

SELECT *

FROM "session";

SELECT *

FROM session_failed;