drop trigger people_insert;
drop trigger rooms_insert;
drop trigger events_insert;

drop table attend;
drop table events;
drop table rooms;
drop table people;

drop sequence people_id_seq;
drop sequence rooms_id_seq;
drop sequence events_id_seq;

create table people (
  id integer,
  lastname varchar(50) not null,
  firstname varchar(50) not null,
  email varchar(100) unique,
  primary key (id));
  
create table rooms (
  id integer primary key,
  name varchar(50) not null,
  description varchar(4000));

create table events (
  id                integer primary key,
  event_type        integer not null,
  begin_date_time   date not null,
  end_date          date,
  duration          interval day to second,
  title             varchar(255) not null,
  description       varchar(4000),
  room_id           integer,
  date_of_month     integer, -- for montly event by date
  week_of_month     integer, -- for monthy event by week, weekday
  on_sunday         char(1),
  on_monday         char(1),
  on_tuesday        char(1),
  on_wednesday      char(1),
  on_thursday       char(1),
  on_friday         char(1),
  on_saturday       char(1),
  foreign key (room_id) references rooms(id),
  constraint check_event_type
    check (event_type >= 0 and event_type <= 5),  
  constraint check_on_sunday
    check (on_sunday='y' or on_sunday='n'),
  constraint check_on_monday
    check (on_monday='y' or on_monday='n'),
  constraint check_on_tuesday
    check (on_tuesday='y' or on_tuesday='n'),
  constraint check_on_wednesday
    check (on_wednesday='y' or on_wednesday='n'),
  constraint check_on_thursday
    check (on_thursday='y' or on_thursday='n'),
  constraint check_on_friday
    check (on_friday='y' or on_friday='n'),
  constraint check_on_saturday
    check (on_saturday='y' or on_saturday='n'),
  constraint check_date_of_month
    check (date_of_month>=1 and date_of_month<=31),
  constraint check_week_of_month
    check (week_of_month >= -5 and week_of_month <= 5)
);
  
create table attend (
  person_id integer,
  event_id integer,
  status integer default 0 not null,
  primary key(person_id, event_id),
  foreign key (person_id) references people(id),
  foreign key (event_id) references events(id),
  constraint check_status
    check (status >= 0 and status <= 2));
  
create sequence people_id_seq
  increment by 1
  start with 1001;

create or replace trigger people_insert
before insert on people
for each row
begin
  select people_id_seq.nextval
  into :new.id
  from dual;
end;
.

create sequence events_id_seq;

create or replace trigger events_insert
before insert on events
for each row
begin
  select events_id_seq.nextval
  into :new.id
  from dual;
end;
.

create sequence rooms_id_seq;

create or replace trigger rooms_insert
before insert on rooms
for each row
begin
  select rooms_id_seq.nextval
  into :new.id
  from dual;
end;
.