create sequence acknowledgment_seq;

create table acknowledgments (
    ack_id      integer 
                constraint ack_pk primary key,
    shortname   varchar(20)
                constraint acs_shortname_nil
                not null
                constraint acs_shortname_un
                unique,
    title       varchar(400)
                constraint ack_title_nil
                not null,
    description varchar(2000)
                constraint ack_desc_nil
                not null,
    global_p    boolean
                constraint acs_global_nil
                not null,
    ack_mode    varchar(10)
                constraint ack_mode_nil
                not null
                constraint ack_mode_ck
                check (ack_mode in ('checkbox', 'phrase', 'password')),
    ack_phrase  varchar(20),
    group_id    integer
                constraint ack_group_fk
                references groups(group_id),
    due_date    date
);

create index acknowledgments_idx1 on acknowledgments(due_date);

create table acknowledgment_responses (
    ack_id      integer 
                constraint ack_resp_ack_id_nil
                not null
                constraint ack_resp_ack_id_fk
                references acknowledgments(ack_id),
    user_id     integer 
                constraint ack_resp_user_nil
                not null
                constraint ack_resp_user_fk
                references users(user_id),
    ack_date    timestamp 
                default now()
);

create unique index acknowledgments_resp_idx1 on acknowledgment_responses(ack_id,user_id);

create or replace function acknowledgment__new(integer,varchar,varchar,varchar,boolean,varchar,varchar,integer,date)
returns integer as '
declare
    p_object_id     alias for $1;
    p_shortname     alias for $2;
    p_title         alias for $3;
    p_description   alias for $4;
    p_global        alias for $5;
    p_ack_mode      alias for $6;
    p_ack_phrase    alias for $7;
    p_group_id      alias for $8;
    p_due_date      alias for $9;
    v_object_id     integer;
begin
    if p_object_id is null then
        v_object_id := nextval(''acknowledgment_seq'');
    else
        v_object_id := p_object_id;
    end if;
    
    insert into acknowledgments
    (ack_id, shortname, title, description, global_p, ack_mode, ack_phrase, group_id, due_date)
    values
    (v_object_id, p_shortname, p_title, p_description, p_global, p_ack_mode, p_ack_phrase, p_group_id, p_due_date);

    return v_object_id;
    
end;' language 'plpgsql';

create or replace function acknowledgment__edit(integer,varchar,varchar,varchar,boolean,varchar,varchar,integer,date)
returns integer as '
declare
    p_object_id     alias for $1;
    p_shortname     alias for $2;
    p_title         alias for $3;
    p_description   alias for $4;
    p_global        alias for $5;
    p_ack_mode      alias for $6;
    p_ack_phrase    alias for $7;
    p_group_id      alias for $8;
    p_due_date      alias for $9;
    v_object_id     integer;
begin
    update acknowledgments
    set shortname = p_shortname, 
    title = p_title, 
    description = p_description, 
    global_p = p_global, 
    ack_mode = p_ack_mode, 
    ack_phrase = p_ack_phrase, 
    group_id = p_group_id, 
    due_date = p_due_date
    where ack_id = p_object_id;

    return v_object_id;
    
end;' language 'plpgsql';

create or replace function acknowledgment__delete(integer)
returns integer as '
declare
    p_object_id     alias for $1;
begin
    delete from acknowledgment_responses
    where ack_id = p_object_id;

    delete from acknowledgments
    where ack_id = p_object_id;
    
    return 1;
    
end;' language 'plpgsql';

create or replace function acknowledgment__reset(integer)
returns integer as '
declare
    p_object_id     alias for $1;
begin
    delete from acknowledgment_responses
    where ack_id = p_object_id;
    
    return 1;
    
end;' language 'plpgsql';

create or replace function acknowledgment__new_response(integer,integer)
returns integer as '
declare
    p_object_id     alias for $1;
    p_user_id       alias for $2;
begin
    insert into acknowledgment_responses
    (ack_id, user_id)
    values 
    (p_object_id, p_user_id);
    
    return 1;
    
end;' language 'plpgsql';

create or replace view acknowledgments_all_view as
select 
    ack.ack_id, ack.shortname, ack.title, ack.due_date, ack.ack_mode, ack.group_id, 
    (select count(*) from acknowledgment_responses where ack_id = ack.ack_id) as responses
from 
    acknowledgments ack
order by 
    ack.ack_id;

create or replace view acknowledgments_view as
select 
    ack.ack_id, ack.shortname, ack.title, ack.due_date, ack.ack_mode, ack.group_id, 
    (select count(*) from acknowledgment_responses where ack_id = ack.ack_id) as responses
from 
    acknowledgments ack
where 
    ack.due_date > now()::date
order by 
    ack.ack_id;