CREATE OR REPLACE FORCE VIEW "VW_PRICING_LIST" ("TYPE", "PRICING_ITEM_ID", "PRICING_CATALOUGE_ID", "PRICING_ITEM_NAME", "TYPE_ID", "PRICING_ITEM_TYPE_NAME", "USER_ID", "PAYMENT_STATUS", "PATIENT_ID")
                                AS
  SELECT PC.PRICING_OBJECT_TYPE AS TYPE,
    PI.PRICING_ITEM_ID,
    PC.PRICING_CATALOUGE_ID,
    PI.PRICING_ITEM_NAME,
    PI.PRICING_ITEM_TYPE_ID AS TYPE_ID,
    PT.PRICING_ITEM_TYPE_NAME,
    UP.USER_ID,
    ph.payment_status,
    NVL(ppt.patient_id,0) AS patient_id
  FROM PRICING_ITEM PI,
    PRICING_CATALOUGE PC,
    PRICING_ITEM_TYPE_LKP PT,
    PRICING_PURCHASE_HISTORY PH,
    PRICING_PURCHASE_TRANSACTION PPT,
    USER_PROFILE UP
  WHERE PC.PRICING_OBJECT_ID   = PI.PRICING_ITEM_ID
  AND PC.PRICING_OBJECT_TYPE   = 'item'
  AND PI.PRICING_ITEM_TYPE_ID  = PT.PRICING_ITEM_TYPE_ID
  AND PH.USER_ID               = UP.USER_ID
  AND PH.PACKAGE_ID            = PPT.PACKAGE_ID
  AND PPT.PRICING_CATALOGUE_ID = PC.PRICING_CATALOUGE_ID
  UNION
  SELECT PC.PRICING_OBJECT_TYPE,
    PP.PRICING_PACKAGE_ID,
    PC.PRICING_CATALOUGE_ID,
    PP.PRICING_PACKAGE_NAME,
    0,
    PP.PRICING_PACKAGE_TYPE,
    UP.USER_ID,
    ph.payment_status,
    NVL(ppt.patient_id,0) AS patient_id
  FROM PRICING_PACKAGE PP,
    PRICING_CATALOUGE PC,
    PRICING_PURCHASE_HISTORY PH,
    PRICING_PURCHASE_TRANSACTION PPT,
    USER_PROFILE UP
  WHERE PC.PRICING_OBJECT_ID   = PP.PRICING_PACKAGE_ID
  AND PC.PRICING_OBJECT_TYPE   = 'plan'
  AND PC.PRICING_PLAN_STATUS   = 1
  AND PH.USER_ID               = UP.USER_ID
  AND PH.PACKAGE_ID            = PPT.PACKAGE_ID
  AND PPT.PRICING_CATALOGUE_ID = PC.PRICING_CATALOUGE_ID;

CREATE TABLE "SITE_SETTINGS"
  (
    "ID"            NUMBER(10,0) NOT NULL ENABLE,
    "VARIABLE_NAME" VARCHAR2(500 CHAR),
    "VALUE"         VARCHAR2(500 CHAR)
  );
/
 ALTER TABLE "SITE_SETTINGS" ADD CONSTRAINT "SITE_SETTINGS_PK" PRIMARY KEY
(
  "ID"
);
/
CREATE SEQUENCE "SITE_SETTINGS_ID_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;


CREATE OR REPLACE TRIGGER "SITE_SETTINGS_ID_TRG" before
  INSERT ON "SITE_SETTINGS" FOR EACH row BEGIN IF inserting THEN IF :NEW."ID" IS NULL THEN
  SELECT SITE_SETTINGS_ID_SEQ.nextval INTO :NEW."ID" FROM dual;
END IF;
END IF;
END;
/
ALTER TRIGGER "SITE_SETTINGS_ID_TRG" ENABLE;


insert into site_settings (variable_name, value) values('baseurl','http://strengthforcaring.jnj.cl.datapipe.net');

CREATE OR REPLACE FORCE VIEW "VW_USER_CALENDAR_ITERATION" ("USER_CALENDER_HEADER_ID", "PATIENT_ID", "EVENT_TYPE_LKP_ID", "TITLE", "CATEGORY_ID", "CATEGORY_NAME", "START_TIME", "QUANTITY", "COST", "TO_TIME", "WHEN_TO_END", "END_TIME", "RECURRENT_ID", "NOTES", "ASSIGNED_TO", "CONTACT_WITH", "CONTACT_LKP_ID", "CONTACT_WHERE", "CONTACT_NAME", "USER_NAME", "RECURRENT_TYPE", "EVENT_TYPE", "DRUG_ID", "SIDE_EFFECTS", "CURRENTLY_USED", "SPECIAL_INSTRUCTION", "DOSAGE", "TIMES_PERDAY", "TIMINGS", "STATUS", "CREATED_BY", "CREATED_DATE", "UPDATED_BY", "UPDATED_DATE", "WEEKDAY", "MONTHDAY", "FIRST_NAME", "MIDDLE_INITIAL", "LAST_NAME", "PICTURE_PATH", "PATIENT_NAME", "PATIENT_LAST_NAME", "NOTES_COUNT", "ADD_TO_CALENDAR", "ISTIMEBOUND")
AS
  SELECT UCH."USER_CALENDER_HEADER_ID",
    UCH."PATIENT_ID",
    NVL(UCH."EVENT_TYPE_LKP_ID", 0) AS EVENT_TYPE_LKP_ID,
    UCH."TITLE",
    0                             AS CATEGORY_ID,
    ''                            AS CATEGORY_NAME,
    NVL(UCH."START_TIME", '')     AS START_TIME,
    0                             AS QUANTITY,
    0                             AS COST,
    NVL(UCH."TO_TIME", '')        AS TO_TIME,
    NVL(UCH."WHEN_TO_END", '')    AS WHEN_TO_END,
    NVL(UCH."END_TIME", '')       AS END_TIME,
    NVL(UCH."RECURRENT_ID", 0)    AS RECURRENT_ID,
    NVL(UCH."NOTES", '')          AS NOTES,
    NVL(UCH."ASSIGNED_TO", 0)     AS ASSIGNED_TO,
    NVL(UCH."CONTACT_WITH", '')   AS CONTACT_WITH,
    NVL(UCH."CONTACT_LKP_ID", 0)  AS CONTACT_LKP_ID,
    NVL(UCH."CONTACT_WHERE", '')  AS CONTACT_WHERE,
    CL."FIRST_NAME"               AS CONTACT_NAME,
    UP."FIRST_NAME"               AS USER_NAME,
    NVL(RTL."RECURRENT_TYPE", '') AS RECURRENT_TYPE,
    NVL(ETL."EVENT_TYPE", '')     AS EVENT_TYPE,
    0                             AS DRUG_ID,
    ''                            AS SIDE_EFFECTS,
    0                             AS CURRENTLY_USED,
    ''                            AS SPECIAL_INSTRUCTION,
    ''                            AS DOSAGE,
    0                             AS TIMES_PERDAY,
    ''                            AS TIMINGS,
    UCH."EVENT_LINE_STATUS"       AS STATUS,
    UCH."CREATED_BY",
    UCH."CREATED_DATE",
    UCH."UPDATED_BY",
    UCH."UPDATED_DATE",
    TO_CHAR(UCH."START_TIME",'D')  AS WEEKDAY,
    TO_CHAR(UCH."START_TIME",'MM') AS MONTHDAY,
    UP."FIRST_NAME"                AS FIRST_NAME,
    UP."MIDDLE_INITIAL"            AS MIDDLE_INITIAL,
    UP."LAST_NAME"                 AS LAST_NAME,
    P."PICTURE_PATH"               AS PICTURE_PATH,
    P."NAME"                       AS PATIENT_NAME,
    P."LAST_NAME"                  AS PATIENT_LAST_NAME,
    (SELECT COUNT(*)
    FROM NOTES_LNK
    WHERE USER_CALENDER_HEADER_ID = UCH.USER_CALENDER_HEADER_ID
    ) AS NOTES_COUNT,
    UCH.ADD_TO_CALENDAR,
    uch.istimebound
  FROM User_Calender_Header UCH
  LEFT OUTER JOIN CONTACT_LKP CL
  ON CL.CONTACT_LKP_ID = UCH.CONTACT_LKP_ID
  LEFT OUTER JOIN USER_PROFILE UP
  ON UP.USER_ID = UCH.ASSIGNED_TO
  LEFT OUTER JOIN RECURRENT_TYPE_LKP RTL
  ON RTL.RECURRENT_ID = UCH.RECURRENT_ID
  LEFT OUTER JOIN EVENT_TYPE_LKP ETL
  ON ETL.EVENT_TYPE_LKP_ID = UCH.EVENT_TYPE_LKP_ID
  LEFT OUTER JOIN PATIENT_PROFILE P
  ON UCH.PATIENT_ID = P.PATIENT_ID
  UNION
  SELECT ITD."ITEMDETAIL_ID",
    ITD."PATIENT_ID",
    -1                                AS EVENT_TYPE_LKP_ID,
    IL."ITEM_NAME"                    AS TITLE,
    IL."ITEM_CATEGORY_ID"             AS CATEGORY_ID,
    NVL(ICL."ITEM_CATEGORY_NAME", '') AS CATEGORY_NAME,
    NVL(ITD."START_DATE", '')         AS START_DATE,
    ITD."QUANTITY"                    AS QUANTITY,
    ITD."ITEM_COST"                   AS COST,
    NVL(ITD."TO_TIME",'')             AS TO_TIME,
    NVL(ITD."WHEN_TO_END", '')        AS WHEN_TO_END,
    NVL(ITD."END_TIME", '')           AS END_TIME,
    ITD."RECURRENT_ID"                AS RECURRENT_ID,
    ''                                AS NOTES,
    NVL(ITD."ASSIGNED_USER_ID", 0)    AS ASSIGNED_USER_ID,
    ''                                AS CONTACT_WITH,
    0                                 AS CONTACT_LKP_ID,
    NVL(ITD."STORE_LOCATION", '')     AS CONTACT_WHERE,
    ''                                AS CONTACT_NAME,
    UP."FIRST_NAME"                   AS USER_NAME,
    NVL(RTL."RECURRENT_TYPE", '')     AS RECURRENT_TYPE,
    'Item'                            AS EVENT_TYPE,
    0                                 AS DRUG_ID,
    ''                                AS SIDE_EFFECTS,
    0                                 AS CURRENTLY_USED,
    ''                                AS SPECIAL_INSTRUCTION,
    ''                                AS DOSAGE,
    0                                 AS TIMES_PERDAY,
    ''                                AS TIMINGS,
    ITD."IS_ACTIVE"                   AS STATUS,
    ITD."CREATED_BY",
    ITD."CREATED_DATE",
    ITD."UPDATED_BY",
    ITD."UPDATED_DATE",
    TO_CHAR(ITD."START_DATE",'D')  AS WEEKDAY,
    TO_CHAR(ITD."START_DATE",'MM') AS MONTHDAY,
    UP."FIRST_NAME"                AS FIRST_NAME,
    UP."MIDDLE_INITIAL"            AS MIDDLE_INITIAL,
    UP."LAST_NAME"                 AS LAST_NAME,
    P."PICTURE_PATH"               AS PICTURE_PATH,
    P."NAME"                       AS PATIENT_NAME,
    P."LAST_NAME"                  AS PATIENT_LAST_NAME,
    0                              AS NOTES_COUNT,
    ITD.ADD_TO_CALENDAR,
    itd.istimebound
  FROM ITEM_DETAIL ITD
  LEFT OUTER JOIN ITEM_LKP IL
  ON IL.ITEM_LKP_ID = ITD.ITEM_LKP_ID
  LEFT OUTER JOIN PATIENT_PROFILE P
  ON ITD.PATIENT_ID = P.PATIENT_ID
  LEFT OUTER JOIN ITEM_CATEGORY_LKP ICL
  ON ICL.ITEM_CATEGORY_ID = IL.ITEM_CATEGORY_ID
  LEFT OUTER JOIN USER_PROFILE UP
  ON UP.USER_ID = ITD.ASSIGNED_USER_ID
  LEFT OUTER JOIN RECURRENT_TYPE_LKP RTL
  ON RTL.RECURRENT_ID   = ITD.RECURRENT_ID
  WHERE ITD.ISTIMEBOUND = 1
  UNION
  SELECT PL.PRESCRIPTION_ID,
    PL.PATIENT_ID,
    -2                       AS EVENT_TYPE_LKP_ID,
    PL."DRUG_NAME"           AS TITLE,
    0                        AS CATEGORY_ID,
    ''                       AS CATEGORY_NAME,
    NVL(PL."START_DATE", '') AS START_DATE,
    0                        AS QUANTITY,
    0                        AS COST,
    NVL(PL."END_DATE", '')   AS TO_TIME,
    'Until'                  AS WHEN_TO_END,
    NVL(PL."END_DATE", '')   AS END_TIME,
    RL.RECURRENT_ID          AS RECURRENT_ID,
    NVL(PL."NOTES",'')       AS NOTES,
    0                        AS ASSIGNED_USER_ID,
    ''                       AS CONTACT_WITH,
    0                        AS CONTACT_LKP_ID,
    NVL(PL."PHARMACY", '')   AS CONTACT_WHERE,
    ''                       AS CONTACT_NAME,
    ''                       AS USER_NAME,
    RL.RECURRENT_TYPE        AS RECURRENT_TYPE,
    'Prescription'           AS EVENT_TYPE,
    0                        AS DRUG_ID,
    ''                       AS SIDE_EFFECTS,
    PL.CURRENTLY_USED        AS CURRENTLY_USED,
    ''                       AS SPECIAL_INSTRUCTION,
    PL."DOSAGE"              AS DOSAGE,
    NVL(PL."TIMES_PERDAY",0) AS TIMES_PERDAY,
    NVL(PL."TIMINGS",'')     AS TIMINGS,
    0                        AS STATUS,
    PL."CREATED_BY",
    PL."CREATED_DATE",
    PL."UPDATED_BY",
    PL."UPDATED_DATE",
    TO_CHAR(PL."START_DATE",'D')  AS WEEKDAY,
    TO_CHAR(PL."START_DATE",'MM') AS MONTHDAY,
    ''                            AS FIRST_NAME,
    ''                            AS MIDDLE_INITIAL,
    ''                            AS LAST_NAME,
    P."PICTURE_PATH"              AS PICTURE_PATH,
    P."NAME"                      AS PATIENT_NAME,
    P."LAST_NAME"                 AS PATIENT_LAST_NAME,
    0                             AS NOTES_COUNT,
    PL.ADD_TO_CALENDAR,
    0 AS istimebound
  FROM PRESCRIPTION_LNK PL
  LEFT OUTER JOIN PATIENT_PROFILE P
  ON PL.PATIENT_ID = P.PATIENT_ID
  LEFT OUTER JOIN RECURRENT_TYPE_LKP RL
  ON RL.RECURRENT_TYPE = 'Daily';


CREATE OR REPLACE FORCE VIEW "VW_PATIENT_MEASUREMENT"  
AS
  SELECT l.patient_measurement_lnk_id,
    l.measured_by,
    l.measurement_name,
    l.measurement_time,
    l.notes,
    l.measurement,
    l.fetch_from_contact,
    measurement_type.measurement_type_lkp_id,
    measurement_type.measurement_type,
    measurement_unit.measurement_unit_lkp_id,
    measurement_unit.measurement_unit,
    p_profile.patient_id,
    p_profile.name,
    CASE
      WHEN (l.measured_by IS NOT NULL
      AND l.measured_by    >0)
      THEN cl.first_name
      ELSE measure_by
    END AS measured_user_name
  FROM patient_measurement_lnk l
  LEFT JOIN contact_lkp cl
  ON cl.contact_lkp_id = l.measured_by
  LEFT JOIN MEASUREMENT_LNK m_lnk
  ON l.measurement_lnk_id=m_lnk.measurement_lnk_id
  LEFT JOIN MEASUREMENT_TYPE_LKP measurement_type
  ON m_lnk.measurement_type_lkp_id=measurement_type.measurement_type_lkp_id
  LEFT JOIN MEASUREMENT_UNIT_LKP measurement_unit
  ON m_lnk.measurement_unit_lkp_id=measurement_unit.measurement_unit_lkp_id
  LEFT JOIN PATIENT_PROFILE p_profile
  ON L.PATIENT_ID=p_profile.PATIENT_ID;


CREATE OR REPLACE FORCE VIEW vw_user_role_patient_details
as
select urpl.user_role_patient_id, urpl.user_id, urpl.patient_id, urpl.network_role_id, up.user_email, up.first_name, p.name, urpl.is_active, p.is_active as patient_is_active, p.is_deleted from user_role_patient_lnk urpl left join user_profile up on up.user_id = urpl.user_id left join patient_profile p on p.patient_id = urpl.patient_id;

insert into permission_lkp (permission_name, permission_description, created_by, created_date, updated_by, updated_date) values ('AssignedTo', 'Assigned To',1,sysdate,1,sysdate);

commit;
