DROP VIEW IF EXISTS work_pattern_view;
DROP VIEW IF EXISTS work_shifts_view;
DROP VIEW IF EXISTS employees_with_work_pattern;
DROP VIEW IF EXISTS work_pattern_enabled_dates;

DROP VIEW IF EXISTS `work_schedule_period`;
CREATE VIEW `work_schedule_period`
AS
SELECT wsp.id,
       wsp.name,
       wsp.from_date,
       IFNULL(wsp.to_date, DATE_ADD(CURDATE(), INTERVAL 10 YEAR)) AS to_date,
       wsp.definition
FROM (SELECT wsh.id,
             wsh.name,
             wsh.from_date,
             wsh.definition,
             (SELECT DATE_ADD(wsh1.from_date, INTERVAL -1 DAY)
              FROM `ohrm_work_schedule_history` wsh1
              WHERE wsh1.id = wsh.id
                AND wsh1.from_date > wsh.from_date
              ORDER BY wsh1.from_date
              LIMIT 1) AS to_date
      FROM ohrm_work_schedule_history wsh) AS wsp;

DROP VIEW IF EXISTS `employee_daily_work_schedule`;
CREATE VIEW `employee_daily_work_schedule`
AS
SELECT work_schedule_id,
       emp_number                                                                           AS work_schedule_emp_number,
       IF(DATE(work_schedule_effective_from_date) < DATE(job_record_effective_from_date),
          job_record_effective_from_date, work_schedule_effective_from_date)                AS from_date,
       IF(DATE(work_schedule_effective_to_date) >
          DATE(IFNULL(DATE_ADD(job_record_effective_to_date, INTERVAL -1 DAY), CURRENT_DATE())),
          IFNULL(DATE_ADD(job_record_effective_to_date, INTERVAL -1 DAY), CURRENT_DATE()),
          IFNULL(work_schedule_effective_to_date, DATE_ADD(CURDATE(), INTERVAL 10 YEAR)))   AS to_date,
       JSON_UNQUOTE(JSON_EXTRACT(definition, CONCAT('$.days."', lower(day), '".day_type'))) AS day_type,
       lower(day)                                                                           AS day_offset,
       JSON_UNQUOTE(IF(JSON_EXTRACT(definition,
                                    CONCAT('$.days."', lower(day), '".shift_start_time')) = 'null', NULL,
                       JSON_EXTRACT(definition,
                                    CONCAT('$.days."', lower(day), '".shift_start_time')))) AS shift_start,
       JSON_UNQUOTE(JSON_EXTRACT(definition, CONCAT('$.days."', lower(day), '".duration'))) AS duration,
       JSON_UNQUOTE(IF(JSON_EXTRACT(definition,
                                    CONCAT('$.days."', lower(day), '".shift_end_time')) = 'null', NULL,
                       JSON_EXTRACT(definition,
                                    CONCAT('$.days."', lower(day), '".shift_end_time'))))   AS shift_end,
       JSON_UNQUOTE(IF(JSON_EXTRACT(definition,
                                    CONCAT('$.days."', lower(day), '".break_start_time')) = 'null', NULL,
                       JSON_EXTRACT(definition,
                                    CONCAT('$.days."', lower(day), '".break_start_time')))) AS break_start,
       JSON_UNQUOTE(IF(JSON_EXTRACT(definition,
                                    CONCAT('$.days."', lower(day), '".break_end_time')) = 'null', NULL,
                       JSON_EXTRACT(definition,
                                    CONCAT('$.days."', lower(day), '".break_end_time'))))   AS break_end
FROM (SELECT jrh.id                 AS job_history_id,
             jrh.`work_schedule_id` AS work_schedule_id,
             jrh.`employee_number`  AS emp_number,
             jrh.effective_date     AS job_record_effective_from_date,
             (SELECT jrh1.effective_date
              FROM `ohrm_job_record_history` jrh1
              WHERE jrh1.employee_number = jrh.employee_number
                AND jrh1.effective_date > jrh.effective_date
                AND jrh1.work_schedule_id <> jrh.work_schedule_id
              ORDER BY jrh1.effective_date
              LIMIT 1)              AS job_record_effective_to_date,
             wsp.from_date          AS work_schedule_effective_from_date,
             wsp.to_date            AS work_schedule_effective_to_date,
             wsp.name               AS work_schedule_history_name,
             wsp.definition         AS definition
      FROM `ohrm_job_record_history` jrh
               LEFT JOIN `ohrm_work_schedule` ws ON ws.id = jrh.`work_schedule_id`
               LEFT JOIN `work_schedule_period` wsp ON ws.id = wsp.id
      WHERE jrh.effective_date IS NOT NULL
        AND wsp.from_date <= IFNULL((SELECT jrh1.effective_date
                                     FROM `ohrm_job_record_history` jrh1
                                     WHERE jrh1.employee_number = jrh.employee_number
                                       AND jrh1.effective_date > jrh.effective_date
                                       AND jrh1.work_schedule_id <> jrh.work_schedule_id
                                     ORDER BY jrh1.effective_date
                                     LIMIT 1), CURRENT_DATE())
        AND (wsp.to_date IS NULL OR wsp.to_date >= jrh.effective_date)
      GROUP BY jrh.employee_number, jrh.`work_schedule_id`, job_record_effective_to_date,
               work_schedule_effective_from_date) AS ws_history
         CROSS JOIN (SELECT 'Monday' AS day
                     UNION ALL
                     SELECT 'Tuesday'
                     UNION ALL
                     SELECT 'Wednesday'
                     UNION ALL
                     SELECT 'Thursday'
                     UNION ALL
                     SELECT 'Friday'
                     UNION ALL
                     SELECT 'Saturday'
                     UNION ALL
                     SELECT 'Sunday') AS days
ORDER BY job_record_effective_from_date, work_schedule_effective_from_date;

DROP VIEW IF EXISTS work_pattern_enabled_dates;
CREATE VIEW work_pattern_enabled_dates
AS
SELECT wpv.work_schedule_emp_number AS emp_number, MIN(wpv.from_date) as Min_from_date
FROM (SELECT work_schedule_emp_number,
             Date(from_date) As `from_date`
      FROM (SELECT *
            FROM `employee_daily_work_schedule` wsh
            ORDER by wsh.work_schedule_emp_number, wsh.from_date DESC) p
      GROUP BY p.work_schedule_emp_number, Date(p.from_date)) wpv
GROUP BY wpv.work_schedule_emp_number;
