-- drop objects
drop table employees cascade constraints;
drop package employees_api;
drop package timecards_api;
drop view employee_timecards;

-- create tables
create table employees (
    id                             number generated by default on null as identity 
                                   constraint employees_id_pk primary key,
    name                           varchar2(255 char)
                                   constraint employees_name_unq unique not null,
    email                          varchar2(255 char)
                                   constraint employees_email_unq unique not null,
    country                        varchar2(6 char) not null,
    employee_type                  varchar2(9 char) constraint employees_employee_type_vc3_ck
                                   check (employee_type/*bug: _vc30_/n*/ in ('FULL TIME','PART TIME')),
    approver                       varchar2(3 char) not null,
    row_version                    integer not null
)
;

-- table index
create index employees_i1 on employees (approver);


-- triggers
create or replace trigger employees_biu
    before insert or update 
    on employees
    for each row
begin
    :new.email := lower(:new.email);   --<-- reordered
    if inserting then
        :new.row_version := 1;
    elsif updating then
        :new.row_version := nvl(:old.row_version,0) + 1;
    end if;
end employees_biu;
    elsif updating then
        :new.row_version := nvl(:old.row_version,0) + 1;
    end if;
    if inserting then
        :new.created := sysdate;
    end if;
end timecards_biu;
 
    /* example:
        begin
        employees_api.insert_row (
            p_id                          => null,
            p_name                        => null,
            p_email                       => null,
            p_country                     => null,
            p_employee_type               => null,
            p_approver                    => null
            );
        end;
    */

    procedure insert_row  (
        p_id                           in number default null,
        p_name                         in varchar2 default null,
        p_email                        in varchar2 default null,
        p_country                      in varchar2 default null,
        p_employee_type                in varchar2 default null,
        p_approver                     in varchar2 default null
    );
    procedure update_row  (
        p_id                           in number default null,
        p_name                         in varchar2 default null,
        p_email                        in varchar2 default null,
        p_country                      in varchar2 default null,
        p_employee_type                in varchar2 default null,
        p_approver                     in varchar2 default null
    );
    procedure delete_row (
        p_id                           in number
    );
end employees_api;
            p_email := c1.email;
            p_country := c1.country;
            p_employee_type := c1.employee_type;
            p_approver := c1.approver;
        end loop;
    end get_row;

 
    procedure insert_row  (
        p_id                           in number default null,
        p_name                         in varchar2 default null,
        p_email                        in varchar2 default null,
        p_country                      in varchar2 default null,
        p_employee_type                in varchar2 default null,
        p_approver                     in varchar2 default null
    )
    is
    begin
        insert into employees (
            id,
            name,
            email,
            country,
            employee_type,
            approver
        ) values (
            p_id,
            p_name,
            p_email,
            p_country,
            p_employee_type,
            p_approver
        );
    end insert_row;

    procedure update_row  (
        p_id                           in number default null,
        p_name                         in varchar2 default null,
        p_email                        in varchar2 default null,
        p_country                      in varchar2 default null,
        p_employee_type                in varchar2 default null,
        p_approver                     in varchar2 default null
    )
    is
    begin
        update  employees set 
            id = p_id,
            name = p_name,
            email = p_email,
            country = p_country,
            employee_type = p_employee_type,
            approver = p_approver
        where id = p_id;
    end update_row;

    procedure delete_row (
        p_id                           in number
    )
    is
    begin
        delete from employees where id = p_id;
    end delete_row;

end employees_api;
 
    /* example:
        begin
        timecards_api.insert_row (
            p_id                          => null,
            p_employee_id                 => null,
            p_status                      => null,
            p_week_of                     => null,
            p_sunday                      => null,
            p_monday                      => null,
            p_tuesday                     => null,
            p_wednesday                   => null,
            p_thursday                    => null,
            p_friday                      => null,
            p_saturday                    => null,
            p_comments                    => null,
            p_confirmation_code_v50       => null
            );
        end;
    */

    procedure insert_row  (
        p_id                           in number default null,
        p_employee_id                  in number default null,
        p_status                       in varchar2 default null,
        p_week_of                      in date default null,
        p_sunday                       in integer default null,
        p_monday                       in integer default null,
        p_tuesday                      in integer default null,
        p_wednesday                    in integer default null,
        p_thursday                     in integer default null,
        p_friday                       in integer default null,
        p_saturday                     in integer default null,
        p_comments                     in varchar2 default null,
        p_confirmation_code_v50        in varchar2 default null
    );
    procedure update_row  (
        p_id                           in number default null,
        p_employee_id                  in number default null,
        p_status                       in varchar2 default null,
        p_week_of                      in date default null,
        p_sunday                       in integer default null,
        p_monday                       in integer default null,
        p_tuesday                      in integer default null,
        p_wednesday                    in integer default null,
        p_thursday                     in integer default null,
        p_friday                       in integer default null,
        p_saturday                     in integer default null,
        p_comments                     in varchar2 default null,
        p_confirmation_code_v50        in varchar2 default null
    );
    procedure delete_row (
        p_id                           in number
    );
end timecards_api;
            p_status := c1.status;
            p_week_of := c1.week_of;
            p_sunday := c1.sunday;
            p_monday := c1.monday;
            p_tuesday := c1.tuesday;
            p_wednesday := c1.wednesday;
            p_thursday := c1.thursday;
            p_friday := c1.friday;
            p_saturday := c1.saturday;
            p_comments := c1.comments;
            p_confirmation_code_v50 := c1.confirmation_code_v50;
        end loop;
    end get_row;
    end insert_row;
    end update_row;
    end delete_row;

end timecards_api;

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    2,
    'Dean Bollich',
    'dean.bollich@aaac.com',
    'CANADA',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    3,
    'Milo Manoni',
    'milo.manoni@aaad.com',
    'MEXICO',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    4,
    'Laurice Karl',
    'laurice.karl@aaae.com',
    'US',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    5,
    'August Rupel',
    'august.rupel@aaaf.com',
    'CANADA',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    6,
    'Salome Guisti',
    'salome.guisti@aaag.com',
    'MEXICO',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    7,
    'Lovie Ritacco',
    'lovie.ritacco@aaah.com',
    'US',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    8,
    'Chaya Greczkowski',
    'chaya.greczkowski@aaai.com',
    'CANADA',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    9,
    'Twila Coolbeth',
    'twila.coolbeth@aaaj.com',
    'MEXICO',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    10,
    'Carlotta Achenbach',
    'carlotta.achenbach@aaak.com',
    'US',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    11,
    'Jeraldine Audet',
    'jeraldine.audet@aaal.com',
    'CANADA',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    12,
    'August Arouri',
    'august.arouri@aaam.com',
    'MEXICO',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    13,
    'Ward Stepney',
    'ward.stepney@aaan.com',
    'US',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    14,
    'Ayana Barkhurst',
    'ayana.barkhurst@aaao.com',
    'CANADA',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    15,
    'Luana Berends',
    'luana.berends@aaap.com',
    'MEXICO',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    16,
    'Lecia Alvino',
    'lecia.alvino@aaaq.com',
    'US',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    17,
    'Joleen Himmelmann',
    'joleen.himmelmann@aaar.com',
    'CANADA',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    18,
    'Monty Kinnamon',
    'monty.kinnamon@aaas.com',
    'MEXICO',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    19,
    'Dania Grizzard',
    'dania.grizzard@aaat.com',
    'US',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    20,
    'Inez Yamnitz',
    'inez.yamnitz@aaau.com',
    'CANADA',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    21,
    'Fidel Creps',
    'fidel.creps@aaav.com',
    'MEXICO',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    22,
    'Luis Pothoven',
    'luis.pothoven@aaaw.com',
    'US',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    23,
    'Bernardo Phoenix',
    'bernardo.phoenix@aaax.com',
    'CANADA',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    24,
    'Carolyne Centore',
    'carolyne.centore@aaay.com',
    'MEXICO',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    25,
    'Cornell Pratico',
    'cornell.pratico@aaaz.com',
    'US',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    26,
    'Sam Rueb',
    'sam.rueb@aaa0.com',
    'CANADA',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    27,
    'Shakita Sablock',
    'shakita.sablock@aaa1.com',
    'MEXICO',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    28,
    'Sandy Heffren',
    'sandy.heffren@aaa2.com',
    'US',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    29,
    'Tressa Coppens',
    'tressa.coppens@aaa3.com',
    'CANADA',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    30,
    'Shira Arocho',
    'shira.arocho@aaa4.com',
    'MEXICO',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    31,
    'Carter Sacarello',
    'carter.sacarello@aaa5.com',
    'US',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    32,
    'Linda Merida',
    'linda.merida@aaa6.com',
    'CANADA',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    33,
    'Winfred Sohn',
    'winfred.sohn@aaa7.com',
    'MEXICO',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    34,
    'Nestor Caparros',
    'nestor.caparros@aaa8.com',
    'US',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    35,
    'Brooks Craker',
    'brooks.craker@aaa9.com',
    'CANADA',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    36,
    'Ruthann Nixa',
    'ruthann.nixa@aaba.com',
    'MEXICO',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    37,
    'Kenny Campobasso',
    'kenny.campobasso@aabb.com',
    'US',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    38,
    'Hildred Donnel',
    'hildred.donnel@aabc.com',
    'CANADA',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    39,
    'Luther Ardinger',
    'luther.ardinger@aabd.com',
    'MEXICO',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    40,
    'Rhoda Hasfjord',
    'rhoda.hasfjord@aabe.com',
    'US',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    41,
    'Cori Ablin',
    'cori.ablin@aabf.com',
    'CANADA',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    42,
    'Reinaldo Feltner',
    'reinaldo.feltner@aabg.com',
    'MEXICO',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    43,
    'Coralee Acerno',
    'coralee.acerno@aabh.com',
    'US',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    44,
    'Katherine Tagg',
    'katherine.tagg@aabi.com',
    'CANADA',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    45,
    'Lenore Sullivan',
    'lenore.sullivan@aabj.com',
    'MEXICO',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    46,
    'erda Sheer',
    'erda.sheer@aabk.com',
    'US',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    47,
    'Pete Chevis',
    'pete.chevis@aabl.com',
    'CANADA',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    48,
    'Joseph Wilke',
    'joseph.wilke@aabm.com',
    'MEXICO',
    'PART TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    49,
    'Nella Rupnick',
    'nella.rupnick@aabn.com',
    'US',
    'FULL TIME',
    'TBD'
);

insert into employees (
    id,
    name,
    email,
    country,
    employee_type,
    approver
) values (
    50,
    'Azalee Goodwater',
    'azalee.goodwater@aabo.com',
    'CANADA',
    'PART TIME',
    'TBD'
);

commit;

alter table employees
modify id generated always as identity restart start with 51;

commit;
 
 
-- Generated by Quick SQL Wednesday March 15, 2023  22:12:39
 
/*
employees /insert 50 /api
    name /nn /unique
    email /nn /lower /unique
    country vc100 /nn /values US, Canada, Mexico
    employee type vc30 /n /check full time, part time 
    approver vc255 /nn /index /values tbd
    timecards /api /auditcols /insert 200
        status /check SUBMITTED, APPROVED, DECLINED /default SUBMITTED
        week of date /nn
        sunday int /default 0 /between 0 and 24
        monday int /default 0 /between 0 and 24
        tuesday int /default 0 /between 0 and 24
        wednesday int /default 0 /between 0 and 24
        thursday int /default 0 /between 0 and 24
        friday int /default 0 /between 0 and 24
        saturday int /default 0 /between 0 and 24
        comments
        confirmation code v50 /values null

view employee_timecards employees timecards

# settings = { semantics: "CHAR", API: true, rowVersion: true, drop: true, language: "EN", APEX: true }
*/
