-- Room Reservation Package
--
-- @author Deds Castillo (deds@infiniteinfo.com)
-- @creation-date 2002-08-28
-- @cvs-id $Id: reservations-create.sql,v 1.1 2004/03/15 03:39:30 carolinem Exp $
--
-- Total rewrite though some
-- concepts were taken from the old
-- ACS3.x Room Reservation package

-- let's do the table stuff
create table rr_reservations (
    reservation_id                  integer
                                    constraint rr_r_reservation_id_nn
                                    not null
                                    constraint rr_r_reservation_id_fk
                                    references acs_events (event_id)
                                    constraint rr_r_reservation_id_pk
                                    primary key,
    room_id                         integer
                                    constraint rr_reservations_room_id_nn
                                    not null
                                    constraint rr_reservations_room_id_fk
                                    references rr_rooms (room_id),
    reserving_user                  integer
                                    constraint rr_r_reserving_user_nn
                                    not null
                                    constraint rr_r_reserving_user_fk
                                    references users (user_id),
    attendees                       integer,
    note                            varchar(400),
    contact_person                  varchar(200),
    contact_email                   varchar(100),
    contact_phone                   varchar(50),
    status                          varchar(100)
                                    default 'pending'
                                    constraint rr_reservations_status_nn
                                    not null
                                    constraint rr_reservations_status_ck
                                    check (status in ('pending','approved','rejected','canceled')),
    reason                          varchar(400),
    action_user                     integer
                                    constraint rr_reservations_action_user_fk
                                    references users (user_id),
    action_date                     timestamp
);

-- let's do the views stuff
create view rr_reservations_approve
as
    select *
    from rr_reservations
    where status = 'approved';

create view rr_reservations_reject
as
    select *
    from rr_reservations
    where status = 'rejected';

create view rr_reservations_pending
as
    select *
    from rr_reservations
    where status = 'pending';

create view rr_reservations_cancel
as
    select *
    from rr_reservations
    where status = 'canceled';

-- let's create our object
create function inline_0 ()
returns integer as'
begin
    perform acs_object_type__create_type(
        ''rr_reservation'',               -- object_type
        ''Room Reservation'',             -- pretty_name
        ''Room Reservations'',            -- pretty_plural
        ''acs_event'',                    -- supertype
        ''rr_reservations'',              -- table_name
        ''reservation_id'',               -- id_column
        ''rr_reservations'',              -- package_name
        ''f'',                            -- abstract_p
        null,                             -- type_extensions_table
        null                              -- name_method
    );

    return null;
end;' language 'plpgsql';

select inline_0();
drop function inline_0();