-- create tables
create table med_coverage_plan (
    id                             number generated by default on null as identity 
                                   constraint med_coverage_plan_id_pk primary key,
    name                           varchar2(30 char),
    row_version                    integer not null,
    created                        date not null,
    created_by                     varchar2(255 char) not null,
    updated                        date not null,
    updated_by                     varchar2(255 char) not null
)
;

create table med_coverage_plan_option (
    id                             number generated by default on null as identity 
                                   constraint med_coverage_plan_id_pk2 primary key,
    coverage_plan_id               number
                                   constraint med_coverage_coverage_plan_fk
                                   references med_coverage_plan on delete cascade,
    name                           varchar2(40 char),
    reimburse_percentage           number,
    row_version                    integer not null,
    created                        date not null,
    created_by                     varchar2(255 char) not null,
    updated                        date not null,
    updated_by                     varchar2(255 char) not null
)
;

-- table index
create index med_coverage_plan_i1 on med_coverage_plan_option (coverage_plan_id);

create table med_countries (
    iso_a2                         varchar2(3 char) not null constraint med_countries_iso_a2_pk primary key,
    country_name                   varchar2(255 char),
    row_version                    integer not null,
    created                        date not null,
    created_by                     varchar2(255 char) not null,
    updated                        date not null,
    updated_by                     varchar2(255 char) not null
)
;

create table med_users (
    id                             number generated by default on null as identity 
                                   constraint med_users_id_pk primary key,
    country                        varchar2(3 char)
                                   constraint med_users_country_fk
                                   references med_countries on delete cascade,
    coverage_plan_id               number
                                   constraint med_users_coverage_plan_id_fk
                                   references med_coverage_plan on delete cascade,
    coverage_plan_option_id        number
                                   constraint med_users_coverage_plan_opt_fk
                                   references med_coverage_plan_option on delete cascade,
    username                       varchar2(320 char),
    home_latitude                  number,
    home_longitude                 number,
    address                        varchar2(80 char),
    city                           varchar2(80 char),
    state                          varchar2(80 char),
    postal_code                    varchar2(80 char),
    payment_method                 varchar2(6 char) constraint med_users_payment_method_ck
                                   check (payment_method in ('BANK','DEBIT','PAYPAL')),
    paypal_name                    varchar2(200 char),
    debit_card_number              varchar2(16 char),
    bank_account_number            varchar2(40 char),
    bank_routing_number            varchar2(40 char),
    photo                          blob,
    push_subscription              varchar2(4000 char),
    row_version                    integer not null,
    created                        date not null,
    created_by                     varchar2(255 char) not null,
    updated                        date not null,
    updated_by                     varchar2(255 char) not null
)
;

-- table index
create index med_users_i1 on med_users (country);
create index med_users_i112 on med_users (coverage_plan_id);
create index med_users_i123 on med_users (coverage_plan_option_id);

create table med_user_sources (
    id                             number generated by default on null as identity 
                                   constraint med_user_sources_id_pk primary key,
    user_id                        number
                                   constraint med_user_sources_user_id_fk
                                   references med_users on delete cascade not null,
    country                        varchar2(3 char)
                                   constraint med_user_sources_country_fk
                                   references med_countries on delete cascade,
    source_name                    varchar2(100 char),
    latitude                       number,
    longitude                      number,
    address                        varchar2(80 char),
    city                           varchar2(80 char),
    state                          varchar2(80 char),
    postal_code                    varchar2(80 char),
    row_version                    integer not null,
    created                        date not null,
    created_by                     varchar2(255 char) not null,
    updated                        date not null,
    updated_by                     varchar2(255 char) not null
)
;

-- table index
create index med_user_sources_i1 on med_user_sources (user_id);
create index med_user_sources_i2 on med_user_sources (country);

create table med_user_claims (
    id                             number generated by default on null as identity 
                                   constraint med_user_claims_id_pk primary key,
    receipt_from_id                number
                                   constraint med_user_clai_receipt_from_fk
                                   references med_user_sources on delete cascade not null,
    claimant                       number
                                   constraint med_user_claims_claimant_fk
                                   references med_users on delete cascade,
    receipt_date                   date not null,
    receipt_amount                 number not null,
    status                         varchar2(80 char),
    payment_txn_id                 number,
    reimburse_percentage           number,
    row_version                    integer not null,
    created                        date not null,
    created_by                     varchar2(255 char) not null,
    updated                        date not null,
    updated_by                     varchar2(255 char) not null
)
;

-- table index
create index med_user_claims_i1 on med_user_claims (receipt_from_id);
create index med_user_claims_i2 on med_user_claims (claimant);


create table med_user_claim_docs (
    id                             number generated by default on null as identity 
                                   constraint med_user_claim_doc_id_pk primary key,
    claim                          number
                                   constraint med_user_claim_doc_claim_fk
                                   references med_user_claims on delete cascade,
    image                          blob,
    status                         varchar2(80 char),
    latitude                       number,
    longitude                      number,
    row_version                    integer not null,
    created                        date not null,
    created_by                     varchar2(255 char) not null,
    updated                        date not null,
    updated_by                     varchar2(255 char) not null
)
;

-- table index
create index med_user_claim_doc_i1 on med_user_claim_docs (claim);

create table med_user_claim_notes (
    id                             number generated by default on null as identity 
                                   constraint med_user_claim_not_id_pk primary key,
    claim                          number
                                   constraint med_user_claim_not_claim_fk
                                   references med_user_claims on delete cascade,
    message                        varchar2(4000 char),
    row_version                    integer not null,
    created                        date not null,
    created_by                     varchar2(255 char) not null,
    updated                        date not null,
    updated_by                     varchar2(255 char) not null
)
;

-- table index
create index med_user_claim_not_i1 on med_user_claim_notes (claim);

create table med_user_notifications (
    id                             number generated by default on null as identity 
                                   constraint med_user_notificat_id_pk primary key,
    username                       varchar2(80 char),
    title                          varchar2(80 char),
    body                           varchar2(2000 char),
    delivered                      varchar2(1 char),
    row_version                    integer not null,
    created                        date not null,
    created_by                     varchar2(255 char) not null,
    updated                        date not null,
    updated_by                     varchar2(255 char) not null
)
;

create table med_lead_meetings (
    id                             number generated by default on null as identity 
                                   constraint med_lead_meetings_id_pk primary key,
    staff_contact                  varchar2(80 char),
    lead_email                     varchar2(80 char),
    proposed_meeting_start         date,
    proposed_meeting_end           date,
    notes                          varchar2(4000 char),
    confirmed                      varchar2(1 char) default /*->*/on null/*<-*/ 'N',
    row_version                    integer not null,
    created                        date not null,
    created_by                     varchar2(255 char) not null,
    updated                        date not null,
    updated_by                     varchar2(255 char) not null
)
;


-- triggers
create or replace trigger med_coverage_plan_biu
    before insert or update 
    on med_coverage_plan
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := nvl(:old.row_version,0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end med_coverage_plan_biu;
/

create or replace trigger med_coverage_plan_option_biu
    before insert or update 
    on med_coverage_plan_option
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := nvl(:old.row_version,0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end med_coverage_plan_option_biu;
/

create or replace trigger med_countries_biu
    before insert or update 
    on med_countries
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := nvl(:old.row_version,0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end med_countries_biu;
/

create or replace trigger med_users_biu
    before insert or update 
    on med_users
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := nvl(:old.row_version,0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end med_users_biu;
/

create or replace trigger med_user_sources_biu
    before insert or update 
    on med_user_sources
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := nvl(:old.row_version,0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end med_user_sources_biu;
/

create or replace trigger med_user_claims_biu
    before insert or update 
    on med_user_claims
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := nvl(:old.row_version,0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end med_user_claims_biu;
/

create or replace trigger med_user_claim_docs_biu
    before insert or update 
    on med_user_claim_docs
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := nvl(:old.row_version,0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end med_user_claim_docs_biu;
/

create or replace trigger med_user_claim_notes_biu
    before insert or update 
    on med_user_claim_notes
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := nvl(:old.row_version,0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end med_user_claim_notes_biu;
/

create or replace trigger med_user_notifications_biu
    before insert or update 
    on med_user_notifications
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := nvl(:old.row_version,0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end med_user_notifications_biu;
/

create or replace trigger med_lead_meetings_biu
    before insert or update 
    on med_lead_meetings
    for each row
begin
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := nvl(:old.row_version,0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end med_lead_meetings_biu;
/

-- load data
 
-- Generated by Quick SQL Saturday April 30, 2022  09:07:34
 
/*
# MediPay Data Model
coverage_plan
  name                           vc30

coverage_plan_option
  name                           vc40
  coverage_plan_id               number /fk coverage_plan
  reimburse_percentage           number

countries
  iso_a2                         vc3 /pk
  country_name                   vc255

users
  username                       vc320
  home_latitude                  number
  home_longitude                 number
  country                        vc80 /fk countries
  address                        vc80
  city                           vc80
  state                          vc80
  postal_code                    vc80
  payment_method                 vc6 /check BANK,DEBIT,PAYPAL
  paypal_name                    vc200
  debit_card_number              vc16
  bank_account_number            vc40
  bank_routing_number            vc40
  photo                          blob
  coverage_plan_id               number /fk coverage_plan
  coverage_plan_option_id        number /fk coverage_plan  
  push_subscription              vc4000

user_sources
  user_id                        number /nn /fk users
  source_name                    vc100
  latitude                       number
  longitude                      number
  country                        vc80 /fk countries
  address                        vc80
  city                           vc80
  state                          vc80
  postal_code                    vc80

user_claims
  receipt_from_id                number /nn /fk user_sources
  receipt_date                   date  /nn
  receipt_amount                 number  /nn
  claimant                       number  /fk users
  status                         vc80
  payment_txn_id                 number
  reimburse_percentage           number

user_claim_docs
  image                          blob
  status                         vc80
  claim                          number  /fk user_claims
  latitude                       number
  longitude                      number

user_claim_notes
  message                        vc4000
  claim                          number  /fk user_claims

user_notifications
  username                       vc80
  title                          vc80
  body                           vc2000
  delivered                      vc1

lead_meetings
  staff_contact                  vc80
  lead_email                     vc80
  proposed_meeting_start         date
  proposed_meeting_end           date
  notes                          vc4000
  confirmed                      vc1 /default N

# settings = { prefix: "MED", semantics: "CHAR", auditCols: true, rowVersion: true, DB: "12C", language: "EN", APEX: true }
*/
