views.sql

CREATE VIEW HOUR_RATE_BI (
WITH
    hourrate
    AS
    (
        SELECT (jsonb_each(config.value)).key AS date,
            (jsonb_each(config.value)).value AS rate
        FROM config
        WHERE ((config.key)
    ::text='price/hour_rate'::text)
    ), available_schedules AS
(
        SELECT schedule.sche_id,
    schedule.doct_id,
    schedule.room_id,
    schedule.start_at,
    schedule.end_at,
    schedule.plan,
    schedule.info,
    schedule.created_at,
    schedule.deleted_at
FROM schedule
    )
, schedules_rated AS
(
        SELECT als.sche_id,
    als.doct_id,
    als.room_id,
    als.start_at,
    als.end_at,
    als.plan,
    als.info,
    als.created_at,
    als.deleted_at,
    hr.date,
    CASE
                WHEN ((als.plan)::text = 'Turn'::text)
THEN
(hr.rate ->> 'turn'::text)
                WHEN
((als.plan)::text = 'Flex'::text) THEN
(hr.rate ->> 'flex'::text)
                ELSE NULL::text
END AS rate,
        row_number
() OVER
(PARTITION BY als.sche_id ORDER BY hr.date DESC) AS rn,
(date_part
('epoch'::text,
(als.end_at - als.start_at)) /
(60)::double precision) AS minutes
        FROM
(available_schedules als
            JOIN hourrate hr ON
((als.start_at >=
(hr.date)::timestamp without time zone)))
    ), all_schedules AS
(
        SELECT sr.sche_id,
    sr.doct_id,
    sr.room_id,
    sr.start_at,
    sr.end_at,
    sr.plan,
    sr.info,
    sr.created_at,
    sr.deleted_at,
    sr.date,
    sr.rate,
    sr.rn,
    sr.minutes,
    dr.name AS doctor_name,
    rm.label AS room_name
FROM ((schedules_rated sr
    JOIN doctor dr  USING (doct_id))
    LEFT JOIN room rm  USING (room_id))
WHERE (sr.rn = 1)
    )
SELECT all_schedules.sche_id,
    all_schedules.doct_id,
    all_schedules.doctor_name,
    all_schedules.room_id,
    all_schedules.room_name,
    all_schedules.start_at,
    all_schedules.end_at,
    all_schedules.plan,
    all_schedules.created_at,
    all_schedules.deleted_at,
    (all_schedules.rate)
::jsonb AS minute_rate,
    all_schedules.minutes AS duration_in_minutes
    FROM all_schedules;
)