/* 
Tonight's used SQL Data types
int: number, no decimal
decimal (radix, places) example: decimal(18,2): 18 is the number of digits on the left side of decimal, 2 is right right side
datetime: stores dates and times that can be parsed later
varchar/nvchar(length) - string field of specified length - example - varchar(100) for a string 100 letters long or varchar(max) for unlimited
char(length) - for storing data if you know the exact length - example - state like PA/NY could be stored in char(2)
bit: true/false - 1 is true, 0 is false
*/

-- inserted first user
insert into Users (FirstName, LastName, email, password, isteacher) values ('test', 'person1', 'test1@gmail.com', 'test_password', 0)

-- view what you just inserted
select firstname, lastname from users

-- insert students and the one teacher
insert into Users (FirstName, LastName, email, password, isteacher) values ('test', 'person2', 'test2@gmail.com', 'test_password', 0)
insert into Users (FirstName, LastName, email, password, isteacher) values ('test', 'person3', 'test3@gmail.com', 'test_password', 0)
insert into Users (FirstName, LastName, email, password, isteacher) values ('test', 'person4', 'test4@gmail.com', 'test_password', 0)
insert into Users (FirstName, LastName, email, password, isteacher) values ('test', 'person5', 'test5@gmail.com', 'test_password', 0)
insert into Users (FirstName, LastName, email, password, isteacher) values ('test', 'person6', 'test6@gmail.com', 'test_password', 0)
insert into Users (FirstName, LastName, email, password, isteacher) values ('test', 'person7', 'test7@gmail.com', 'test_password', 0)
insert into Users (FirstName, LastName, email, password, isteacher) values ('test', 'person8', 'test8@gmail.com', 'test_password', 0)
insert into Users (FirstName, LastName, email, password, isteacher) values ('test', 'person9', 'test9@gmail.com', 'test_password', 1)

-- view and filter the users you just inserted
select * from users
select * from users where isteacher = 1
select * from users where lastname = 'person2'
select * from users where userid = 1
select * from users where userid >= 6
select * from users where lastname like '%person%'

-- run some updates on the users
update users set lastname = 'teacher' where userid = 9
update users set email = null where userid = 5

-- select with an order by statement
select * from users order by lastname asc, isteacher asc

-- isnull provides default 'n/a' if the email is null
select isnull(email, 'n/a') from users

-- add column capacity (number) to classes table
alter table classes add Capacity int null
go

-- these are some comments
/* 
this is a test script
*/

-- declaring then getting latest inserted record from locations into classes table
declare @location_id int
insert into locations (name) values ('Location #1')
select @location_id = SCOPE_IDENTITY()

INSERT INTO [dbo].[Classes] ([Name], [Description], [Cost], Capacity, [LocationID])
VALUES ('Test Class 1', 'This is a test class', 15.99, null, @location_id)

-- where are we now with our classes table
select * from classes

-- update new capacity field
update classes set capacity = 4 where classid =1

-- escaping single quotes - one "'" with two "''"
update classes set name = ' Test Class 1 - 12/1/2020 - O''Neil ' where classid = 1

-- string functions, how to parse a string
select charindex('-', name) from classes
select substring(name, charindex('-', name)+1, 10) from classes
select ltrim(rtrim(name)) from classes
select convert(varchar(10), timestamp, 101) from classes

alter table usersattendance add AttendanceDate datetime null

-- inserted to test joins
insert into usersattendance (userid, classid, attendancedate) values (1, 1, '12/1/2020')
insert into usersattendance (userid, classid, attendancedate) values (1, 2, '12/1/2020')
insert into usersattendance (userid, classid, attendancedate) values (1, 3, '12/1/2020')

-- first join, joining the usersattendance table to the users and classes to see who attended what classes
select * 
from 
usersattendance ua inner join 
users u on ua.userid = u.userid left outer join 
classes c on ua.classid = c.classid

-- since first insert was mistake, delete all from table
delete from usersattendance

-- re-insert to correct
insert into usersattendance (classid, userid, attendancedate) values (1, 1, '12/1/2020')
insert into usersattendance (classid, userid, attendancedate) values (1, 2, '12/1/2020')
insert into usersattendance (classid, userid, attendancedate) values (1, 3, '12/1/2020')
insert into usersattendance (classid, userid, attendancedate) values (1, 3, '12/1/2020')

-- view data
select * from usersattendance

-- see summed costs for class for each user
select sum(c.cost), u.lastname, u.userid
from 
usersattendance ua inner join 
users u on ua.userid = u.userid inner join 
classes c on ua.classid = c.classid
group by u.lastname, u.userid

-- how many users are there?
select count(userid) from users

-- create the getusers proc, no params
CREATE PROCEDURE GetUsers
AS
BEGIN	
	SET NOCOUNT ON;    
	SELECT userid, firstname, lastname from users
END
GO

-- run it
exec GetUsers

-- create proc to handle both insert and update user
create PROCEDURE UpsertUser
@user_id int,
@first_name varchar(100),
@last_name varchar(100),
@email varchar(100),
@password varchar(100),
@is_teacher bit = 0
AS
BEGIN	
	SET NOCOUNT ON;    
	
	if @user_id > 0
		begin
			update users set FirstName = @first_name, LastName = @last_name, Email = @email, Password = @password, IsTeacher = @is_teacher
			where userid = @user_id
		end
	else
		begin
			insert into users (FirstName, LastName, Email, Password, IsTeacher) values (@first_name, @last_name, @email, @password, @is_teacher)
			select @user_id = SCOPE_IDENTITY()
		end

	select @user_id
END
GO

-- run it
exec UpsertUser 1, 'test', 'person_1', 'test_1@gmail.com', 'test_password', 1

-- finally, view all data from all three tables we added data to
select * from users
select * from usersattendance
select * from classes