-- MySQL Script generated by MySQL Workbench
-- Fri Feb	3 16:30:07 2023
-- Model: New Model	Version: 1.0
-- MySQL Workbench Forward Engineering


-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table tx_staff
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tx_staff 
(	id				INT				NOT NULL	AUTO_INCREMENT
,	st_firstname	VARCHAR(45)		NOT NULL
,	st_surname		VARCHAR(45)		NOT NULL
,	st_gender		CHAR(1)			NOT NULL	DEFAULT 'F'
,	st_pos			CHAR(2)				NULL
,	st_edu			VARCHAR(10)			NULL
,	st_email		VARCHAR(80)			NULL
,	created_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	updated_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	PRIMARY KEY 	(id)
)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table tx_weeks
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tx_weeks 
(	id				INT				NOT NULL AUTO_INCREMENT
,	week_year		INT				NOT NULL
,	week_nr			INT				NOT NULL
,	week_start		DATE			NOT NULL
,	comments		VARCHAR(500)		NULL
,	created_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	updated_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	PRIMARY KEY 	(id)
)
ENGINE = InnoDB;

DELIMITER $$
-- DROP  TRIGGER weeks_ai $$
CREATE TRIGGER weeks_ai
    AFTER INSERT
    ON tx_weeks FOR EACH ROW
BEGIN
    INSERT INTO tx_days 
	(	week_id, day_nr, day_dow	)
	SELECT
		new.id, DAYOFYEAR(ADDDATE(new.week_start, c.code_order-1)), c.code_order
	FROM
		tx_codes	c
	WHERE
		c.code_type = 'DAY'
	ORDER BY
		c.code_order;
	-- statements
END$$    

DELIMITER ;
-- -----------------------------------------------------
-- Table tx_rules
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tx_rules 
(	id				INT				NOT NULL AUTO_INCREMENT
,	rule_code		VARCHAR(5)		NOT NULL
,	rule_title		VARCHAR(100)	NOT NULL
,	rule_priority	INT				NOT NULL DEFAULT 1
,	rule_order		INT				NOT NULL DEFAULT 1
,	rule_desc		VARCHAR(500)		NULL
,	created_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	updated_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	PRIMARY KEY 	(id)
)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table tx_days
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tx_days 
(	id				INT				NOT NULL AUTO_INCREMENT
,	week_id			INT				NOT NULL
,	day_nr			INT				NOT NULL
,	day_hl			TINYINT(1)			NULL
,	day_dow			TINYINT(1)			NULL
,	day_SHF1		INT				NOT NULL DEFAULT 8
,	day_SHF2		INT				NOT NULL DEFAULT 7
,	day_SHF3		INT				NOT NULL DEFAULT 6
,	created_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	updated_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	PRIMARY KEY 	(id)
,	CONSTRAINT		fk_day_to_week
		FOREIGN KEY (week_id)
		REFERENCES	tx_weeks (id)
		ON DELETE	NO ACTION
		ON UPDATE	NO ACTION
)
ENGINE = InnoDB;

CREATE UNIQUE INDEX day_idx ON tx_days (id ASC) ;

CREATE INDEX idx_day_to_week ON tx_days (week_id ASC) ;


DELIMITER $$
-- DROP  TRIGGER days_ai $$
CREATE TRIGGER days_ai
    AFTER INSERT
    ON tx_days FOR EACH ROW
BEGIN
    INSERT INTO tx_shifts 
	(	day_id, shift_nr, shift_min	)
	SELECT
		new.id, c.code_order, 8
	FROM
		tx_codes	c
	WHERE
		c.code_type = 'SHF'
	ORDER BY
		c.code_order;
	-- statements
END$$    

DELIMITER ;


-- -----------------------------------------------------
-- Table tx_shifts
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tx_shifts
(	id				INT				NOT NULL AUTO_INCREMENT
,	day_id			INT				NOT NULL
,	shift_nr		CHAR(2)			NOT NULL
,	shift_min		INT				NOT NULL
,	created_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	updated_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	PRIMARY KEY 	(id)
,	CONSTRAINT	 	fk_shift_t0_day
		FOREIGN KEY	(day_id)
		REFERENCES	tx_days (id)
		ON DELETE	NO ACTION
		ON UPDATE	NO ACTION
)
ENGINE = InnoDB;

CREATE UNIQUE INDEX shift_pk ON tx_shifts (id ASC);
CREATE INDEX shift_t0_day_idx ON tx_shifts (day_id ASC) ;

DELIMITER $$
-- DROP  TRIGGER days_ai $$
CREATE TRIGGER shifts_ai
    AFTER INSERT
    ON tx_shifts FOR EACH ROW
BEGIN
    INSERT INTO tx_posts 
	(	shift_id, staff_id	)
	SELECT
		new.id, 0
	FROM
		tx_posts_h	p;
END$$    

DELIMITER ;
-- -----------------------------------------------------
-- Table tx_posts
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tx_posts 
(	id				INT				NOT NULL AUTO_INCREMENT
,	shift_id		INT				NOT NULL
,	staff_id		INT				NOT NULL DEFAULT 0
,	created_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	updated_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	PRIMARY KEY		(id)
,	CONSTRAINT		fk_post_to_shift
		FOREIGN KEY (shift_id)
		REFERENCES	tx_shifts (id)
		ON DELETE	NO ACTION
		ON UPDATE	NO ACTION
,	CONSTRAINT		fk_post_to_staff
		FOREIGN KEY (staff_id)
		REFERENCES	tx_staff (id)
		ON DELETE	NO ACTION
		ON UPDATE	NO ACTION
)
ENGINE = InnoDB;

CREATE INDEX post_to_shift_idx ON tx_posts (shift_id ASC) ;

CREATE INDEX post_to_staff_idx ON tx_posts (staff_id ASC) ;

-- -----------------------------------------------------
-- Table tx_posts_h
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tx_posts_h
(	id				INT				NOT NULL AUTO_INCREMENT
,	dow_id			INT				NOT NULL DEFAULT 1
,	PRIMARY KEY		(id)
)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS tx_posts 
(	id				INT				NOT NULL AUTO_INCREMENT
,	shift_id		INT				NOT NULL
,	staff_id		INT				NOT NULL DEFAULT 0
,	run_id			INT				 	NULL
,	created_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	updated_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	PRIMARY KEY		(id)
,	CONSTRAINT		fk_post_to_shift
		FOREIGN KEY (shift_id)
		REFERENCES	tx_shifts (id)
		ON DELETE	NO ACTION
		ON UPDATE	NO ACTION
,	CONSTRAINT		fk_post_to_staff
		FOREIGN KEY (staff_id)
		REFERENCES	tx_staff (id)
		ON DELETE	NO ACTION
		ON UPDATE	NO ACTION
)
ENGINE = InnoDB;

CREATE INDEX idx_posts_run ON tx_posts (run_id ASC) ;

-- -----------------------------------------------------
-- Table tx_runs
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tx_runs
(	id				INT				NOT NULL AUTO_INCREMENT
,	week_id			INT				NOT NULL
,	run_used		INT				NOT NULL DEFAULT 0
,	run_title		VARCHAR(100)		NULL
,	run_user		VARCHAR(80)			NULL
,	created_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	updated_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	PRIMARY KEY		(id)
)
ENGINE = InnoDB;

DELIMITER $$
-- DROP  TRIGGER days_ai $$
CREATE TRIGGER runs_bd
    BEFORE DELETE
    	ON tx_runs FOR EACH ROW
BEGIN
    DELETE FROM 
		tx_drafts 
	WHERE
		run_id = old.id;
END$$    

DELIMITER ;


-- -----------------------------------------------------
-- Table tx_drafts
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tx_drafts
(	id				INT				NOT NULL AUTO_INCREMENT
,	run_id			INT				NOT NULL
,	draft_id		INT
,	day_id			INT
,	shift_id		INT				NOT NULL
,	staff_id		INT				NOT NULL DEFAULT 0
,	created_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	updated_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	PRIMARY KEY		(id)
,	CONSTRAINT		fk_draft_to_run
		FOREIGN KEY (run_id)
		REFERENCES	tx_runs (id)
		ON DELETE	NO ACTION
		ON UPDATE	NO ACTION
)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table tx_holidays
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tx_holidays 
(	id				INT				NOT NULL AUTO_INCREMENT
,	staff_id		INT				NOT NULL
,	day_id			INT				NOT NULL
,	created_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	updated_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	PRIMARY KEY		(id)
,	CONSTRAINT		fk_holidays_to_staff
		FOREIGN KEY (staff_id)
		REFERENCES	tx_staff (id)
		ON DELETE	NO ACTION
		ON UPDATE	NO ACTION
,	CONSTRAINT		fk_holidays_to_days
		FOREIGN KEY (day_id)
		REFERENCES	tx_days (id)
		ON DELETE	NO ACTION
		ON UPDATE	NO ACTION
)
ENGINE = InnoDB;

CREATE INDEX holidays_to_staff_idx ON tx_holidays (staff_id ASC) ;

CREATE INDEX holidays_to_days_idx ON tx_holidays (day_id ASC) ;

-- -----------------------------------------------------
-- Table tx_wishes
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tx_wishes 
(	id				INT				NOT NULL AUTO_INCREMENT
,	wish_type		TINYINT(1)			NULL
,	staff_id		INT				NOT NULL
,	shift_id		INT				NOT NULL
,	created_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	updated_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	PRIMARY KEY		(id)
,	CONSTRAINT		fk_wishes_to_staff
		FOREIGN KEY (staff_id)
		REFERENCES	tx_staff (id)
		ON DELETE	NO ACTION
		ON UPDATE	NO ACTION
,	CONSTRAINT		fk_wishes_to_shifts
		FOREIGN KEY (shift_id)
		REFERENCES	tx_shifts (id)
		ON DELETE	NO ACTION
		ON UPDATE	NO ACTION
)
ENGINE = InnoDB;

CREATE INDEX wishes_to_staff_idx ON tx_wishes (staff_id ASC) ;

CREATE INDEX wishes_to_shifts_idx ON tx_wishes (shift_id ASC) ;


-- -----------------------------------------------------
-- Table tx_codes
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tx_codes 
(	code_type		VARCHAR(5)		NOT NULL
,	code_id			VARCHAR(5)		NOT NULL
,	code_desc		VARCHAR(100)		NULL
,	code_desc_f		VARCHAR(100)		NULL
,	code_desc_pl	VARCHAR(100)		NULL
,	code_order		INT					NULL
,	created_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	updated_at		DATETIME		NOT NULL DEFAULT CURRENT_TIMESTAMP
,	PRIMARY KEY 	(code_type, code_id)
)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- View v_code_edu
-- -----------------------------------------------------
CREATE	OR REPLACE VIEW v_code_edu AS
SELECT
	code_id
,	code_desc
,	code_order
FROM
	tx_codes
WHERE
	code_type = 'EDU'
ORDER BY
	code_order;

-- -----------------------------------------------------
-- View v_code_pos
-- -----------------------------------------------------
CREATE	OR REPLACE VIEW v_code_pos AS
SELECT
	code_id
,	code_desc
,	code_desc_f
,	code_desc_pl
,	code_order
FROM
	tx_codes
WHERE
	code_type = 'POS'
ORDER BY
	code_order;

-- -----------------------------------------------------
-- View v_code_day
-- -----------------------------------------------------
CREATE	OR REPLACE VIEW v_code_day AS
SELECT
	code_order		AS code_id
,	code_desc
,	code_desc_pl
,	code_order
FROM
	tx_codes
WHERE
	code_type = 'DAY'
ORDER BY
	code_order;

-- -----------------------------------------------------
-- View v_code_shf
-- -----------------------------------------------------
CREATE	OR REPLACE VIEW v_code_shf AS
SELECT
	code_id
,	code_desc
,	code_desc_pl
,	code_order
FROM
	tx_codes
WHERE
	code_type = 'SHF'
ORDER BY
	code_order;

-- -----------------------------------------------------
-- View v_shifts
-- -----------------------------------------------------
CREATE	OR REPLACE VIEW v_shifts AS
SELECT
	w.week_year			AS shift_year
,	w.week_nr			AS week_nr
,	w.id				AS week_id
,	d.day_nr			AS day_nr
,	d.day_dow			AS day_dow
,	d.id				AS day_id
,	ADDDATE(w.week_start, day_dow-1)	AS shift_date
,	s.shift_nr			AS shift_nr
,	cd.code_desc		AS day_name
,	cs.code_desc		AS shift_hours
,	cs.code_desc_pl		AS shift_desc
,	s.shift_min			AS shift_min
,	s.id				AS shift_id
,	s.created_at		AS created_at
,	s.updated_at		AS updated_at
FROM
		tx_shifts	s
	JOIN
		v_code_shf	cs
			ON	shift_nr = cs.code_order
	JOIN
	tx_days		d
			ON	s.day_id = d.id
	JOIN
		v_code_day	cd
			ON	d.day_dow = cd.code_order
	JOIN
		tx_weeks	w
			ON	d.week_id = w.id;

-- -----------------------------------------------------
-- View v_posts
-- -----------------------------------------------------

CREATE OR REPLACE VIEW v_posts AS
SELECT
	p.id			as id
,	p.id			as draft_id
,	p.shift_id		as shift_id
,	p.staff_id		as staff_id 
,	concat(s.st_firstname, ' ', s.st_surname) as st_name
,	s.st_position
,	s.st_pos
,	s.st_edu
,	s.st_education
FROM
	tx_posts		p
	JOIN
	v_staff			s
		ON		s.id		=	p.staff_id;

-- -----------------------------------------------------
-- View v_days
-- -----------------------------------------------------
CREATE	OR REPLACE VIEW v_days AS
SELECT
	w.week_year		AS day_year
,	w.week_nr		AS week_nr
,	w.id			AS week_id
,	d.day_nr		AS day_nr
,	d.day_hl		AS day_hl
,	d.day_dow		AS day_dow
,	ADDDATE(w.week_start, d.day_dow-1)	AS day_date
,	cd.code_desc	AS day_name
,	d.id			AS day_id
,	d.id			AS id
,	d.created_at	AS created_at
,	d.updated_at	AS updated_at
FROM
	tx_days		d
	JOIN
	v_code_day	cd
		ON cd.code_id = d.day_dow
	JOIN
	tx_weeks	w
		ON	d.week_id = w.id;

-- -----------------------------------------------------
-- View v_weeks
-- -----------------------------------------------------
CREATE	OR REPLACE VIEW v_weeks AS
SELECT
	w.week_year		AS week_year
,	w.id			AS id
,	w.week_nr		AS week_nr
,	w.week_start	AS week_start
,	w.comments		AS comments
,	w.id			AS week_id
,	w.created_at	AS created_at
,	w.updated_at	AS updated_at
FROM
	tx_weeks	w;

CREATE OR REPLACE VIEW v_weeks_done  AS
SELECT DISTINCT 
	w.id			AS week_id
,	w.week_start	AS week_start
,	DATE_FORMAT(w.week_start, "%d-%m-%Y")
					AS week_date
FROM
	tx_posts		p
	JOIN
	tx_shifts		s
		ON	p.shift_id	=	s.id
	JOIN
	tx_days			d
		ON	s.day_id	=	d.id
	JOIN
	tx_weeks		w
		ON	d.week_id	=	w.id ;

-- -----------------------------------------------------
-- View v_staff
-- -----------------------------------------------------
CREATE	OR REPLACE VIEW v_staff AS
SELECT
	s.id			AS id
,	s.st_firstname	AS st_firstname
,	s.st_surname	AS st_surname
,	s.st_pos		AS st_pos
,	s.st_gender		AS st_gender
,	CASE
	WHEN st_gender = 'F' THEN cp.code_desc_f
						 ELSE cp.code_desc		
	END AS st_position
,	s.st_edu		AS st_edu
,	ce.code_desc	AS st_education
,	s.st_email		AS st_email
,	s.created_at	AS created_at
,	s.updated_at	AS updated_at
,	s.id			AS staff_id
,	cp.code_order	AS code_order
FROM
	tx_staff	s
JOIN
	v_code_edu	ce
		ON st_edu = ce.code_id
JOIN
	v_code_pos	cp
		ON st_pos = cp.code_id
WHERE
	s.id > 0;

-- -----------------------------------------------------
-- View v_holidays
-- -----------------------------------------------------
CREATE	OR REPLACE VIEW v_holidays AS
SELECT
	h.id			AS hd_id
,	d.day_id		AS day_id
,	d.day_date		AS day_date
,	DATE_FORMAT(d.day_date, "%d-%m-%Y")
					AS day_date_desc
,	h.staff_id		AS staff_id
,	concat(s.st_firstname, ' ', s.st_surname)	AS st_name
,	d.day_year		AS hd_year
,	d.week_nr		AS week_nr
,	d.week_id		AS week_id
,	d.day_nr		AS day_nr
,	d.day_hl		AS day_hl
,	d.day_dow		AS day_dow
,	d.day_name		AS day_name
,	h.id			AS id
,	coalesce(h.staff_id,0)		AS is_hl
,	h.created_at	AS created_at
,	h.updated_at	AS updated_at
FROM
	v_days		d
	JOIN
	tx_holidays	h
		ON d.day_id = h.day_id
	JOIN
	tx_staff	s
		ON s.id = h.staff_id;

-- -----------------------------------------------------
-- View v_holidays
-- -----------------------------------------------------
CREATE	OR REPLACE VIEW v_wishes AS
SELECT
	w.id				AS id
,	w.id				AS wish_id
,	w.wish_type			AS wish_type
,	s.shift_year		AS shift_year
,	s.week_id			AS week_id
,	s.day_nr			AS day_nr
,	s.day_dow			AS day_dow
,	s.day_id			AS day_id
,	s.shift_date		AS shift_date
,	s.shift_nr			AS shift_nr
,	s.day_name			AS day_name
,	s.shift_hours		AS shift_hours
,	s.shift_desc		AS shift_desc
,	s.shift_min			AS shift_min
,	s.shift_id			AS shift_id
,	w.staff_id			AS staff_id
,	concat(st.st_firstname, ' ', st.st_surname) as st_name
FROM
	tx_wishes		w
	JOIN
	v_shifts		s
		ON w.shift_id = s.shift_id
	JOIN
	tx_staff		st
		ON st.id = w.staff_id;		


-- -----------------------------------------------------
-- Data for table tx_codes
-- -----------------------------------------------------
START TRANSACTION;
DELETE FROM tx_posts_h;
INSERT INTO tx_posts_h (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
DELETE FROM tx_codes;
INSERT INTO tx_codes 
		(code_type,code_id,code_desc,code_desc_f,code_desc_pl,code_order	)
 VALUES ('EDU',	'TE',	'τεχνολογικής εκπαίδευσης'					,	NULL						,	NULL						,	10	)
	,	('EDU',	'YE',	'υποχρεωτικής εκπαίδευσης'				,	NULL						,	NULL						,	2	)
	,	('EDU',	'DE',	'διετούς εκπαίδευσης'					,	NULL						,	NULL						,	6	)
	,	('EDU',	'PE',	'πανεπιστημιακής εκπαίδευσης '				,	NULL						,	NULL						,	4	)
	,	('POS',	'PR',	'προϊστάμενος'							,	'προϊστάμενη'				,	NULL						,	1	)
	,	('POS',	'N1',	'νοσηλευτής'							,	'νοσηλεύτρια'				,	'νοσηλευτές'				,	3	)
	,	('POS',	'N2',	'νοσηλευτής ΔΦ'							,	'νοσηλεύτρια ΔΦ'			,	'νοσηλευτές ΔΦ'				,	5	)
	,	('POS',	'N3',	'ειδικευόμενος νοσηλευτής'				,	'ειδικευόμενη νοσηλεύτρια'	,	'ειδικευόμενοι νοσηλευτές'	,	9	)
	,	('POS',	'SU',	'επικουρικό προσωπικό'					,	'επικουρικό προσωπικό'		,	'επικουρικό προσωπικό'		,	7	)
	,	('POS',	'AS',	'βοηθός ΥΕ'								,	'βοηθός ΥΕ'					,	'βοηθοί ΥΕ'					,	11	)
	,	('POS',	'NO',	'νοσοκόμος'								,	'νοσοκόμοα'					,	'νοσοκόμοι'					,	13	)
	,	('SHF',	'01',	'07:00 - 15:00'							,	'πρωί'						,	NULL						,	1	)
	,	('SHF',	'02',	'15:00 - 23:00'							,	'απόγευμα'					,	NULL						,	2	)
	,	('SHF',	'03',	'23:00 - 07:00'							,	'νύχτα'						,	NULL						,	3	)
	,	('DAY',	'MON',	'Δευτέρα'								,	NULL						,	NULL						,	1	)
	,	('DAY',	'TUE',	'Τρίτη'									,	NULL						,	NULL						,	2	)
	,	('DAY',	'WED',	'Τετάρτη'								,	NULL						,	NULL						,	3	)
	,	('DAY',	'THU',	'Πέμπτη'								,	NULL						,	NULL						,	4	)
	,	('DAY',	'FRI',	'Παρασκευή'								,	NULL						,	NULL						,	5	)
	,	('DAY',	'SAT',	'Σάββατο'								,	NULL						,	NULL						,	6	)
	,	('DAY',	'SUN',	'Κυριακή'								,	NULL						,	NULL						,	7	);
COMMIT;
START TRANSACTION;
DELETE FROM tx_rules;
INSERT INTO tx_rules (rule_code	,	rule_title	,	rule_desc	,	rule_order) VALUES 
	('R01'	,	'Οχι νύχτα-πρωί',	'Δεν γίνεται να μπεί πρωινή βάρδια όταν ο εργαζόμενος έκανε νυχτερινή βάρδια την προηγούμενη'	,	1),
	('R02'	,	'Οχι συνεχόμενες',	'Δεν γίνεται να κάνει κάποιος συνεχόμενες βάρδιες'	,	1),
	('R03'	,	'Μόνιμος Νοσηλευτής','Πρέπει να υπάρχει τουλάχιστον ένας μόνιμος ανά βάρδια'	,	1),
	('R04'	,	'Οχι εκπαιδευόμενοι τή νύχτα'	,	'Οι εκπαιδευόμενοι νοσηλευτές δεν κάνουν νυχτερινές βάρδιες'	,	1),
	('R05'	,	'Οχι απόγευμα και διπλή βάρδια'	,	'Οχι πρωί-νύχτα μετά απο απογευματινή βάρδια'	,	1),
	('R06'	,	'Συνεχόμενες νυχτερινές βάρδιες'	,	'Οι νυχτερινές βάρδιες - αν είναι πάνω απο 1 - να είναι συνεχόμενες'	,	1);
COMMIT;
START TRANSACTION;

CREATE OR REPLACE VIEW v_drafts AS
SELECT
	dr.id			as id
,	dr.id			as draft_id
,	dr.run_id		as run_id
,	dr.shift_id		as shift_id
,	dr.staff_id		as staff_id 
,	concat(s.st_firstname, ' ', s.st_surname) as st_name
,	s.st_position
,	s.st_pos
,	s.st_edu
,	s.st_education
FROM
	tx_drafts		dr
	JOIN
	v_staff			s
		ON		s.id		=	dr.staff_id;

CREATE TABLE sec_users 
(
	id				INT				NOT NULL
,	username		VARCHAR(50)		NOT NULL
,	password		VARCHAR(256)		NULL
,	role			VARCHAR(20)		NOT NULL	DEFAULT 'user (u.v.)'
,	usr_ip_address	VARCHAR(45)			NULL
,	created_at		DATETIME		NOT NULL	DEFAULT CURRENT_TIMESTAMP
,	updated_at		DATETIME		NOT NULL	DEFAULT CURRENT_TIMESTAMP
,	staff_id		INT					NULL
,	is_active tinyint(1)						DEFAULT 0
,	remember_token	VARCHAR(255)		NULL
,	last_login		DATETIME			NULL
,	updated_by		VARCHAR(45)		NOT NULL
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO sec_users (id, username, password, role, usr_ip_address, created_at, updated_at, staff_id, is_active, remember_token, last_login, updated_by) VALUES
(21, 'poutanaki', '$2y$10$wO32.T8ZQJEd1jezUTnYnOgU6iMBG.AoATDgv5waUIUASkDxwiNRa', 'admin', NULL, '2013-03-20 23:28:10', '2023-02-15 10:53:18', 3, 1, 'iEGB6Pf4WgwDTMuyPSZSiXGH36XDzCAmfisUJEHiqeUL0zG0WTIZl1lAaJFb', '2023-02-15 10:53:18', 'theo');


ALTER TABLE sec_users
	ADD PRIMARY KEY (id)
,	ADD UNIQUE KEY usr_name (username)
,	ADD UNIQUE KEY person_id_UNIQUE (staff_id)


ALTER TABLE sec_users
	MODIFY id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;


CREATE OR REPLACE VIEW v_users AS 
SELECT
	s.id				AS staff_id
,	u.id				AS id
,	u.username			AS username
,	u.password			AS password
,	u.role				AS role
,	u.usr_ip_address	AS usr_ip_address
,	s.st_firstname		AS st_firstname
,	s.st_surname		AS st_surname
,	s.st_email			AS st_email
,	date_format(u.last_login,'%d-%m-%Y %H:%i') AS last_login
,	date_format(u.created_at,'%d-%m-%Y %H:%i') AS created_at
,	date_format(u.updated_at,'%d-%m-%Y %H:%i') AS updated_at
FROM
	tx_staff		s
	JOIN
	sec_users		u
		ON	s.id = u.staff_id;


CREATE OR REPLACE VIEW r_staff_shifts AS
SELECT
	concat(st.st_firstname, ' ', st.st_surname)	AS st_name
,	s.shift_nr									AS shift_nr	
,	d.day_dow									AS day_dow
,	w.week_start								AS week_start
,	p.id										AS post_id	
,	s.id										AS shift_id	
,	d.id										AS day_id	
,	w.id										AS week_id	
,	st.id										AS staff_id	
FROM
	tx_posts	p
	JOIN
	tx_shifts	s
		ON	p.shift_id = s.id
	JOIN
	tx_days		d
		ON	s.day_id = d.id
	JOIN
	tx_weeks	w
		ON	d.week_id = w.id
	JOIN
	tx_staff	st
		ON	p.staff_id = st.id
WHERE
	st.id > 0;


CREATE OR REPLACE VIEW r_shifts_grp AS
SELECT
	st_name
,	staff_id
,	week_id
,	week_start	
,	count(post_id)	total_shifts
,	COALESCE(MAX(CASE WHEN day_dow = 1 AND shift_nr = 1 THEN 'Y' END),'N') mon_1
,	COALESCE(MAX(CASE WHEN day_dow = 1 AND shift_nr = 2 THEN 'Y' END),'N') mon_2
,	COALESCE(MAX(CASE WHEN day_dow = 1 AND shift_nr = 3 THEN 'Y' END),'N') mon_3
,	COALESCE(MAX(CASE WHEN day_dow = 2 AND shift_nr = 1 THEN 'Y' END),'N') tue_1
,	COALESCE(MAX(CASE WHEN day_dow = 2 AND shift_nr = 2 THEN 'Y' END),'N') tue_2
,	COALESCE(MAX(CASE WHEN day_dow = 2 AND shift_nr = 3 THEN 'Y' END),'N') tue_3
,	COALESCE(MAX(CASE WHEN day_dow = 3 AND shift_nr = 1 THEN 'Y' END),'N') wed_1
,	COALESCE(MAX(CASE WHEN day_dow = 3 AND shift_nr = 2 THEN 'Y' END),'N') wed_2
,	COALESCE(MAX(CASE WHEN day_dow = 3 AND shift_nr = 3 THEN 'Y' END),'N') wed_3
,	COALESCE(MAX(CASE WHEN day_dow = 4 AND shift_nr = 1 THEN 'Y' END),'N') thu_1
,	COALESCE(MAX(CASE WHEN day_dow = 4 AND shift_nr = 2 THEN 'Y' END),'N') thu_2
,	COALESCE(MAX(CASE WHEN day_dow = 4 AND shift_nr = 3 THEN 'Y' END),'N') thu_3
,	COALESCE(MAX(CASE WHEN day_dow = 5 AND shift_nr = 1 THEN 'Y' END),'N') fri_1
,	COALESCE(MAX(CASE WHEN day_dow = 5 AND shift_nr = 2 THEN 'Y' END),'N') fri_2
,	COALESCE(MAX(CASE WHEN day_dow = 5 AND shift_nr = 3 THEN 'Y' END),'N') fri_3
,	COALESCE(MAX(CASE WHEN day_dow = 6 AND shift_nr = 1 THEN 'Y' END),'N') sat_1
,	COALESCE(MAX(CASE WHEN day_dow = 6 AND shift_nr = 2 THEN 'Y' END),'N') sat_2
,	COALESCE(MAX(CASE WHEN day_dow = 6 AND shift_nr = 3 THEN 'Y' END),'N') sat_3
,	COALESCE(MAX(CASE WHEN day_dow = 7 AND shift_nr = 1 THEN 'Y' END),'N') sun_1
,	COALESCE(MAX(CASE WHEN day_dow = 7 AND shift_nr = 2 THEN 'Y' END),'N') sun_2
,	COALESCE(MAX(CASE WHEN day_dow = 7 AND shift_nr = 3 THEN 'Y' END),'N') sun_3
FROM
	r_staff_shifts
GROUP BY
	week_id
,	staff_id ;
