--         File: doc_plan_code_02.sql
--      Version: 1.0
-- Last Changed: 2020-04-20
--           by: Damir; https://www.damirsystems.com
--      Project: Doctor Planning
--
--  Description: Second part of the code.
--               An example of two-week balanced workload where
--               Fleming works 6 shifts, and all others
--               (4+5) = 9 shifts each.
--
--           DB: PostgreSQL



-- ----------------------------
-- ###  Balancing Workload  ###
-- ----------------------------

-- Table for the first week (A) of the pair (A,B)
--
CREATE TABLE plan_a (
        pln      bigint       NOT NULL
     , "MON-1"  doc_initials NOT NULL
	   , "MON-2"  doc_initials NOT NULL
 	   , "MON-3"  doc_initials NOT NULL
	   , "TUE-1"  doc_initials NOT NULL
	   , "TUE-2"  doc_initials NOT NULL
 	   , "TUE-3"  doc_initials NOT NULL
	   , "WED-1"  doc_initials NOT NULL
	   , "WED-2"  doc_initials NOT NULL
 	   , "WED-3"  doc_initials NOT NULL
	   , "THU-1"  doc_initials NOT NULL
	   , "THU-2"  doc_initials NOT NULL
 	   , "THU-3"  doc_initials NOT NULL
	   , "FRI-1"  doc_initials NOT NULL
	   , "FRI-2"  doc_initials NOT NULL
 	   , "FRI-3"  doc_initials NOT NULL
	   , "SAT-1"  doc_initials NOT NULL
	   , "SAT-2"  doc_initials NOT NULL
 	   , "SAT-3"  doc_initials NOT NULL
	   , "SUN-1"  doc_initials NOT NULL
	   , "SUN-2"  doc_initials NOT NULL
 	   , "SUN-3"  doc_initials NOT NULL

, CONSTRAINT
    pk_plan_a PRIMARY KEY (pln)
);


-- Table for the second week (B) of the pair (A,B)
--
CREATE TABLE plan_b (
        pln      bigint       NOT NULL
     , "MON-1"  doc_initials NOT NULL
	   , "MON-2"  doc_initials NOT NULL
 	   , "MON-3"  doc_initials NOT NULL
	   , "TUE-1"  doc_initials NOT NULL
	   , "TUE-2"  doc_initials NOT NULL
 	   , "TUE-3"  doc_initials NOT NULL
	   , "WED-1"  doc_initials NOT NULL
	   , "WED-2"  doc_initials NOT NULL
 	   , "WED-3"  doc_initials NOT NULL
	   , "THU-1"  doc_initials NOT NULL
	   , "THU-2"  doc_initials NOT NULL
 	   , "THU-3"  doc_initials NOT NULL
	   , "FRI-1"  doc_initials NOT NULL
	   , "FRI-2"  doc_initials NOT NULL
 	   , "FRI-3"  doc_initials NOT NULL
	   , "SAT-1"  doc_initials NOT NULL
	   , "SAT-2"  doc_initials NOT NULL
 	   , "SAT-3"  doc_initials NOT NULL
	   , "SUN-1"  doc_initials NOT NULL
	   , "SUN-2"  doc_initials NOT NULL
 	   , "SUN-3"  doc_initials NOT NULL

, CONSTRAINT
    pk_plan_b PRIMARY KEY (pln)
);

-- Populate plan A, (af, sf, hh, be, cg) = (3,4,4,5,5)
--
INSERT INTO plan_a (
       pln
    , "MON-1", "MON-2", "MON-3"
    , "TUE-1", "TUE-2", "TUE-3"
    , "WED-1", "WED-2", "WED-3"
    , "THU-1", "THU-2", "THU-3"
    , "FRI-1", "FRI-2", "FRI-3"
    , "SAT-1", "SAT-2", "SAT-3"
    , "SUN-1", "SUN-2", "SUN-3"
    )
SELECT
       pln
    , "MON-1", "MON-2", "MON-3"
    , "TUE-1", "TUE-2", "TUE-3"
    , "WED-1", "WED-2", "WED-3"
    , "THU-1", "THU-2", "THU-3"
    , "FRI-1", "FRI-2", "FRI-3"
    , "SAT-1", "SAT-2", "SAT-3"
    , "SUN-1", "SUN-2", "SUN-3"
FROM stats
JOIN schedule USING (pln)
WHERE af  = 3
  AND sf  = 4
  AND hh  = 4
  AND be  = 5
  AND cg  = 5
;

-- Populate plan B, (af, sf, hh, be, cg) = (3,5,5,4,4)
--
INSERT INTO plan_b (
       pln
    , "MON-1", "MON-2", "MON-3"
    , "TUE-1", "TUE-2", "TUE-3"
    , "WED-1", "WED-2", "WED-3"
    , "THU-1", "THU-2", "THU-3"
    , "FRI-1", "FRI-2", "FRI-3"
    , "SAT-1", "SAT-2", "SAT-3"
    , "SUN-1", "SUN-2", "SUN-3"
    )
SELECT
       pln
    , "MON-1", "MON-2", "MON-3"
    , "TUE-1", "TUE-2", "TUE-3"
    , "WED-1", "WED-2", "WED-3"
    , "THU-1", "THU-2", "THU-3"
    , "FRI-1", "FRI-2", "FRI-3"
    , "SAT-1", "SAT-2", "SAT-3"
    , "SUN-1", "SUN-2", "SUN-3"
FROM stats
JOIN schedule USING (pln)
WHERE af  = 3
  AND sf  = 5
  AND hh  = 5
  AND be  = 4
  AND cg  = 4
;


-- Indices to help searching for pairs that start and end
-- the same, to simplify (R08) enforcement
--
CREATE INDEX plan_a_ix1 ON plan_a ("MON-1", "MON-2", "SUN-3");
CREATE INDEX plan_b_ix1 ON plan_b ("MON-1", "MON-2", "SUN-3");


-- How many pairs can be found
-- that start and end the same,
-- to simplify (R08) enforcement?
--
-- (about 3 min to finish)
--
select count(1)
FROM plan_a AS a
JOIN plan_b AS b
            ON b."MON-1" = a."MON-1"
           AND b."MON-2" = a."MON-2"
           AND b."SUN-3" = a."SUN-3"
;
-- Answer: 1,122,295,808


-- Show five two-week (A,B) plans
-- where Fleming works in total 6 shifts, and
-- all other doctors work 9 shifts each.
--
-- (runs fast)
--
SELECT
      a."MON-1" AS "A-MON-1"
    , a."MON-2" AS "A-MON-2"
    , a."MON-3" AS "A-MON-3"
    , a."TUE-1" AS "A-TUE-1"
    , a."TUE-2" AS "A-TUE-2"
    , a."TUE-3" AS "A-TUE-3"
    , a."WED-1" AS "A-WED-1"
    , a."WED-2" AS "A-WED-2"
    , a."WED-3" AS "A-WED-3"
    , a."THU-1" AS "A-THU-1"
    , a."THU-2" AS "A-THU-2"
    , a."THU-3" AS "A-THU-3"
    , a."FRI-1" AS "A-FRI-1"
    , a."FRI-2" AS "A-FRI-2"
    , a."FRI-3" AS "A-FRI-3"
    , a."SAT-1" AS "A-SAT-1"
    , a."SAT-2" AS "A-SAT-2"
    , a."SAT-3" AS "A-SAT-3"
    , a."SUN-1" AS "A-SUN-1"
    , a."SUN-2" AS "A-SUN-2"
    , a."SUN-3" AS "A-SUN-3"

    , b."MON-1" AS "B-MON-1"
    , b."MON-2" AS "B-MON-2"
    , b."MON-3" AS "B-MON-3"
    , b."TUE-1" AS "B-TUE-1"
    , b."TUE-2" AS "B-TUE-2"
    , b."TUE-3" AS "B-TUE-3"
    , b."WED-1" AS "B-WED-1"
    , b."WED-2" AS "B-WED-2"
    , b."WED-3" AS "B-WED-3"
    , b."THU-1" AS "B-THU-1"
    , b."THU-2" AS "B-THU-2"
    , b."THU-3" AS "B-THU-3"
    , b."FRI-1" AS "B-FRI-1"
    , b."FRI-2" AS "B-FRI-2"
    , b."FRI-3" AS "B-FRI-3"
    , b."SAT-1" AS "B-SAT-1"
    , b."SAT-2" AS "B-SAT-2"
    , b."SAT-3" AS "B-SAT-3"
    , b."SUN-1" AS "B-SUN-1"
    , b."SUN-2" AS "B-SUN-2"
    , b."SUN-3" AS "B-SUN-3"
FROM plan_a AS a
JOIN plan_b AS b
            ON b."MON-1" = a."MON-1"
           AND b."MON-2" = a."MON-2"
           AND b."SUN-3" = a."SUN-3"
LIMIT 5
;