#Reset weekly capacity
#=====================
CREATE PROCEDURE UpdateCapacity()
BEGIN
	DECLARE days INT DEFAULT 1;
	WHILE days <= 5 DO
		INSERT INTO
		capacity (id_user, capacity_date)
		select id, DATE_ADD(CURDATE(), INTERVAL days DAY) from users where id_org_level in (6,8,10,11,12,13,14) AND status != 'Exited';
		SET days = days + 1;
	END WHILE;
	
	INSERT INTO
	capacity (id_user, capacity, capacity_date)
	select id, 0, DATE_ADD(CURDATE(), INTERVAL 6 DAY) from users where id_org_level in (6,8,10,11,12,13,14) AND status != 'Exited';
	
	INSERT INTO
	capacity (id_user, capacity, capacity_date)
	select id, 0, DATE_ADD(CURDATE(), INTERVAL 7 DAY) from users where id_org_level in (6,8,10,11,12,13,14) AND status != 'Exited';
	
	INSERT INTO
	capacity_next_week (id_user, week_no)
	select id, DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 7 DAY), '%u') from users where id_org_level in (6,8,10,11,12,13,14) AND status != 'Exited';
END

#=====
#VIEWS
#=====

#Total and average capacity of each associate
#============================================
CREATE VIEW associate_capacity_weekly_totals_and_daily_averages as
SELECT
id_user,
DATE_FORMAT(c.capacity_date, '%u') as capacity_week_no,
SUM(capacity) as weekly_total,
AVG(capacity) as daily_avg
FROM
capacity c, users u
WHERE
c.id_user = u.id AND
DATE_FORMAT(c.capacity_date, '%a') != 'Sat' AND
DATE_FORMAT(c.capacity_date, '%a') != 'Sun' AND
u.status = 'Available'
GROUP BY c.id_user, capacity_week_no

#Departmental weekly capacity totals and averages
#================================================
CREATE VIEW dept_capacity_weekly_totals_and_averages AS
SELECT d.id, t.capacity_week_no week_no, d.department, SUM(t.weekly_total) dept_weekly_total, AVG(t.weekly_total) dept_weekly_avg
FROM
associate_capacity_weekly_totals_and_daily_averages t, users u, departments d
WHERE
t.id_user = u.id AND
u.id_department = d.id AND
u.status = 'Available'
GROUP BY
t.capacity_week_no, d.department, d.id

#Departmental weekly billable and total hours averages
#=====================================================
CREATE VIEW dept_billables_and_total_hours_weekly_averages AS
SELECT
d.id, a.week_no, AVG(a.billable_hours) billables_average, AVG(a.total_hours) totals_average
FROM
departments d, associate_hours a, users u
WHERE
a.id_user = u.id AND
u.id_department = d.id AND
u.status = 'Available'
GROUP BY
d.id, a.week_no

#Associate weekly capacity, billable and total hours
#===================================================
CREATE VIEW associate_weekly_capacity_billables_and_total_hours AS
SELECT
t.id_user, t.capacity_week_no week_no, t.weekly_total weekly_capacity, a.billable_hours, a.total_hours
FROM
associate_capacity_weekly_totals_and_daily_averages t, associate_hours a
WHERE
t.capacity_week_no = a.week_no AND
t.id_user = a.id_user

#Associate 3-Week averages (capacity, billables and total hours) and variance
#============================================================================
CREATE VIEW associate_three_week_averages AS
SELECT
id_user, 
AVG(weekly_capacity) avg_capacity, 
AVG(billable_hours) avg_billable_hours,
AVG(total_hours) avg_total_hours,
AVG(billable_hours) -
(SELECT
AVG(billables_average)
FROM
dept_billables_and_total_hours_weekly_averages d, associate_hours a, departments dp, users u
WHERE

d.week_no 
BETWEEN 
IF((DATE_FORMAT(CURDATE(), '%u') - (SELECT MAX(d.week_no))) = 1, DATE_FORMAT(CURDATE(), '%u') - 3, DATE_FORMAT(CURDATE(), '%u') - 4) 
AND 
IF((DATE_FORMAT(CURDATE(), '%u') - (SELECT MAX(d.week_no))) = 1, DATE_FORMAT(CURDATE(), '%u') - 1, DATE_FORMAT(CURDATE(), '%u') - 2) 

AND 
d.id = dp.id AND
a.id_user = u.id AND
u.status = 'Available' AND
u.id_department = dp.id) billable_variance,

AVG(total_hours) -
(SELECT
AVG(totals_average)
FROM
dept_billables_and_total_hours_weekly_averages d, associate_hours a, departments dp, users u
WHERE

d.week_no 
BETWEEN 
IF((DATE_FORMAT(CURDATE(), '%u') - (SELECT MAX(d.week_no))) = 1, DATE_FORMAT(CURDATE(), '%u') - 3, DATE_FORMAT(CURDATE(), '%u') - 4) 
AND 
IF((DATE_FORMAT(CURDATE(), '%u') - (SELECT MAX(d.week_no))) = 1, DATE_FORMAT(CURDATE(), '%u') - 1, DATE_FORMAT(CURDATE(), '%u') - 2)

AND 
d.id = dp.id AND
a.id_user = u.id AND
u.status = 'Available' AND
u.id_department = dp.id) totals_variance
FROM
associate_weekly_capacity_billables_and_total_hours where 

week_no 
BETWEEN 
IF((DATE_FORMAT(CURDATE(), '%u') - (SELECT MAX(week_no))) = 1, DATE_FORMAT(CURDATE(), '%u') - 3, DATE_FORMAT(CURDATE(), '%u') - 4) 
AND 
IF((DATE_FORMAT(CURDATE(), '%u') - (SELECT MAX(week_no))) = 1, DATE_FORMAT(CURDATE(), '%u') - 1, DATE_FORMAT(CURDATE(), '%u') - 2)

GROUP BY
id_user

#Associate current week capacity
#=====================================================
CREATE VIEW associate_current_week_capacity AS
SELECT
u.id, u.name, d.id id_department, d.department, o.level, DATE_FORMAT(c.capacity_date, '%u') week_no,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Mon' then c.capacity END) as Mon,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Tue' then c.capacity END) as Tue,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Wed' then c.capacity END) as Wed,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Thu' then c.capacity END) as Thu,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Fri' then c.capacity END) as Fri,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Sat' then c.capacity END) as Sat,
MAX(CASE WHEN DATE_FORMAT(c.capacity_date, '%a') = 'Sun' then c.capacity END) as Sun
FROM 
users as u, capacity as c, departments as d, org_levels as o
WHERE
u.id = c.id_user AND
u.id_department = d.id AND
u.status = 'Available' AND
u.id_org_level = o.id AND
DATE_FORMAT(c.capacity_date, '%u') = DATE_FORMAT(CURDATE(), '%u')
GROUP BY u.name, u.id, week_no ORDER BY u.name

#Dashboard data
#==============
CREATE VIEW dashboard_data AS
SELECT
c.id, c.name, c.level, c.Mon, c.Tue, c.Wed, c.Thu, c.Fri, c.Sat, c.Sun, SUBSTRING(nw.capacity, 1, 1) next_week,
a.avg_capacity, a.avg_billable_hours, a.avg_total_hours, a.billable_variance, a.totals_variance,
t.billable_hours, t.total_hours,
c.id_department
FROM
associate_current_week_capacity c, associate_three_week_averages a, associate_weekly_capacity_billables_and_total_hours t, capacity_next_week nw
WHERE
c.id = a.id_user AND
c.id = t.id_user AND
a.id_user = t.id_user AND
c.id = nw.id_user AND
nw.week_no = DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 7 DAY), '%u') AND
t.week_no = IF((DATE_FORMAT(CURDATE(), '%u') - (SELECT MAX(week_no) FROM associate_weekly_capacity_billables_and_total_hours)) = 1, DATE_FORMAT(CURDATE(), '%u') - 1, DATE_FORMAT(CURDATE(), '%u') - 2)
ORDER BY c.name

# Matters report
# ==============
CREATE VIEW matters_report AS
SELECT
m.id, m.details, m.requirement, DATE_FORMAT(m.created_at, '%Y-%m-%d') matter_date, DATE_FORMAT(m.created_at, '%u') week_no, m.added_by,
(SELECT GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') FROM users, matter_partners where users.id = matter_partners.id_partner and matter_partners.id_matter = m.id) partners,
(SELECT GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') FROM users, assignments where users.id = assignments.assigned_to AND assignments.id_matter = m.id) associates
FROM matters m
ORDER BY m.created_at DESC

# associates report
CREATE VIEW report_associates AS
SELECT a.id_matter, GROUP_CONCAT(u.name ORDER BY u.name ASC SEPARATOR ', ') associates, DATE_FORMAT(a.date_assigned, '%u') week_no
FROM
users u, assignments a where u.id = a.assigned_to
GROUP BY a.id_matter, week_no
ORDER BY a.id_matter DESC, week_no DESC

# partners report
CREATE VIEW report_partners AS
SELECT mp.id_matter, m.details, m.requirement, GROUP_CONCAT(u.name ORDER BY u.name ASC SEPARATOR ', ') partners
FROM users u, matter_partners mp, matters m
WHERE
u.id = mp.id_partner AND
m.id = mp.id_matter
GROUP BY mp.id_matter, m.details, m.requirement
ORDER BY mp.id_matter DESC

# assignments
CREATE VIEW report_assignments AS
SELECT
rp.*, ra.associates, ra.week_no 
FROM report_partners rp, report_associates ra
WHERE
rp.id_matter = ra.id_matter

SELECT
AVG(billables_average) billables_average, AVG(totals_average) totals_average
FROM dept_billables_and_total_hours_weekly_averages b
WHERE
week_no 
BETWEEN 
IF((DATE_FORMAT(CURDATE(), '%u') - (SELECT MAX(b.week_no))) = 1, DATE_FORMAT(CURDATE(), '%u') - 3, DATE_FORMAT(CURDATE(), '%u') - 4) 
AND 
IF((DATE_FORMAT(CURDATE(), '%u') - (SELECT MAX(b.week_no))) = 1, DATE_FORMAT(CURDATE(), '%u') - 1, DATE_FORMAT(CURDATE(), '%u') - 2) 
AND  b.id = 7
ORDER BY b.week_no DESC
LIMIT 3

SELECT
id_user, week_no, billable_hours, total_hours
from associate_hours
WHERE
week_no 
BETWEEN 
IF((DATE_FORMAT(CURDATE(), '%u') - (SELECT MAX(week_no))) = 1, DATE_FORMAT(CURDATE(), '%u') - 3, DATE_FORMAT(CURDATE(), '%u') - 4) 
AND 
IF((DATE_FORMAT(CURDATE(), '%u') - (SELECT MAX(week_no))) = 1, DATE_FORMAT(CURDATE(), '%u') - 2, DATE_FORMAT(CURDATE(), '%u') - 2)
ORDER BY id_user ASC, week_no DESC