--Script for Loading HRVHS Patrons and adding Freshman/Grad Year Stat Cats--

BEGIN;

--DROP TABLE IF EXISTS hrvhs_freshman_load;
--DROP TABLE IF EXISTS hrvhs_freshman_load_ids;
--DROP TABLE IF EXISTS hrvhs_stat_cat_1;
--DROP TABLE IF EXISTS hrvhs_stat_cat_2;


-- Hints --
-- test db perm group = 16, org_unit = 246
-- statcat1 = Class Standing
-- statcat2 = Grad Year

    --Create main student staging table.
    CREATE TABLE hrvhs_freshman_load (
		h_studentid text, -- Student ID # // ident_value
		h_usrname text, -- will concat 'hrv+studentid' later
		h_first_name text,
		h_middle_name text,
		h_last_name text,
		h_dob date,
		-- h_day_phone
		h_home_ou int DEFAULT 214, -- Hood River High School = 246
		h_profile int DEFAULT 90, -- STUDENT - HIGH SCHOOL
		h_ident_type int DEFAULT 2, -- Student ID
		h_net_access_level int DEFAULT 2,
		h_passwd text, -- student id
		--
		h_address_type text,
		h_street1 text,
		h_street2 text,
		h_city text,
		h_state text,
		h_country text,
		h_postal_code text,
		--
		h_statcat1 text,
		h_statcat2 text
    );

    --Copy records from your import text file
    COPY hrvhs_freshman_load (
		h_studentid, -- Student ID
		h_first_name,
		h_middle_name,
		h_last_name,
		h_dob,
		--h_dayphone,
		--
		h_address_type,
		h_street1,
		h_street2,
		h_city,
		h_state,
		h_country,
		h_postal_code,
		--
		h_statcat1,
		h_statcat2
		)
        FROM '/home/bmills/hrvhs_freshman_load.csv' WITH CSV HEADER;

	--Create the usrname(barcode) entry from the student id (ident_value)
	UPDATE hrvhs_freshman_load SET h_usrname='hrv'||h_studentid;

	-- Set password=student ID for now
	UPDATE hrvhs_freshman_load SET h_passwd=h_studentid;

    --Insert records from the hrvhs_freshman_load table into the actor.usr table.
    INSERT INTO actor.usr (
		profile,
		usrname,
		passwd,
		ident_type,
		ident_value,
		first_given_name,
		family_name,
		home_ou,
		dob,
		-- day_phone,
		net_access_level)
	SELECT
		h_profile,
		h_usrname,
		h_passwd,
		h_ident_type,
		h_studentid,
		h_first_name,
		h_last_name,
		h_home_ou,
		h_dob,
		-- h_dayphone,
		h_net_access_level
		FROM hrvhs_freshman_load;

		-- Make our lives easier and create a table of actor.usr ids of the incoming students for later consulation
		CREATE TABLE hrvhs_freshman_load_ids(
			h_actor_usr_id int,
			h_usrname text,
			h_studentid text,
			h_create_time date DEFAULT now(),
			h_class_and_year text DEFAULT '2020 - Freshman'
		);
		-- Load it up
		INSERT INTO hrvhs_freshman_load_ids(h_actor_usr_id,h_usrname,h_studentid)
		SELECT au.id, hrvhs.h_usrname,hrvhs.h_studentid
		FROM hrvhs_freshman_load hrvhs
		INNER JOIN actor.usr au
		ON hrvhs.h_usrname=au.usrname;

    --Load barcodes into actor.card table from the usrname entries.
    INSERT INTO actor.card (usr, barcode)
        SELECT h_actor_usr_id,h_usrname
        FROM hrvhs_freshman_load_ids;

    --Update actor.usr.card field with actor.card.id to associate active card with the user:
	--Play it safe and only update when User ID is in the hrvhs id table
	UPDATE actor.usr au
	    SET card = ac.id
	    FROM actor.card ac
	    WHERE ac.usr = au.id AND au.id IN (SELECT h_actor_usr_id FROM hrvhs_freshman_load_ids);

    --Update actor.usr_address from staging table.
    INSERT INTO actor.usr_address (usr, street1, street2, city, state, country, post_code)
    SELECT au.id,
		hrvhs.h_street1,
		hrvhs.h_street2,
		hrvhs.h_city,
		hrvhs.h_state,
		hrvhs.h_country,
		hrvhs.h_postal_code
		FROM hrvhs_freshman_load hrvhs
		INNER JOIN actor.usr au ON au.usrname=hrvhs.h_usrname;


   --Update actor.usr mailing address with id from actor.usr_address table:
    UPDATE actor.usr au
        SET mailing_address = aua.id, billing_address = aua.id
        FROM actor.usr_address aua
        WHERE au.id = aua.usr
			AND au.id IN (SELECT h_actor_usr_id FROM hrvhs_freshman_load_ids);

	--Insert Stat Cats (Freshman, 2020 Grad Date) -- "Freshman,43 | 2020,69"
	--80 (Grad Year), 81 (Class) on test server

	-- Create Scratch Tables for load
	-- Since I know of no nice way to do multiple inserts with one value dynamically changing

	--School Level--
	CREATE TABLE hrvhs_stat_cat_1 (
		stat_cat_entry text DEFAULT 'Freshman',
		stat_cat int DEFAULT 43,
		target_usr int
	);
	INSERT INTO hrvhs_stat_cat_1 (target_usr)
	SELECT h_actor_usr_id FROM hrvhs_freshman_load_ids;

	INSERT INTO actor.stat_cat_entry_usr_map (stat_cat_entry,stat_cat,target_usr)
	SELECT stat_cat_entry,stat_cat,target_usr FROM hrvhs_stat_cat_1;

	--Grad Year--
	CREATE TABLE hrvhs_stat_cat_2 (
		stat_cat_entry text DEFAULT '2020',
		stat_cat int DEFAULT 69,
		target_usr int
	);
	INSERT INTO hrvhs_stat_cat_2 (target_usr)
	SELECT h_actor_usr_id FROM hrvhs_freshman_load_ids;

	INSERT INTO actor.stat_cat_entry_usr_map (stat_cat_entry,stat_cat,target_usr)
	SELECT stat_cat_entry,stat_cat,target_usr FROM hrvhs_stat_cat_2;

	-- DONE! --

    COMMIT;