schema.sql

create table public.users (
  user_id uuid not null default gen_random_uuid (),
  name text not null,
  email text not null,
  joined date null default CURRENT_DATE,
  constraint users_pkey primary key (user_id),
  constraint users_email_key unique (email)
) TABLESPACE pg_default;

create table public.workdays (
  workday_id date not null default CURRENT_DATE,
  opening_time timestamp with time zone not null default (now() AT TIME ZONE 'utc'::text),
  closing_time timestamp with time zone null,
  notes text null default ''::text,
  constraint workdays_pkey primary key (workday_id)
) TABLESPACE pg_default;

create table public.user_logs (
  user_id uuid not null default auth.uid (),
  workday_id date not null default CURRENT_DATE,
  checkin_time timestamp with time zone not null default (now() AT TIME ZONE 'utc'::text),
  checkout_time timestamp with time zone null,
  notes text null default ''::text,
  constraint user_logs_pkey primary key (user_id, workday_id),
  constraint user_logs_user_id_fkey foreign KEY (user_id) references users (user_id),
  constraint user_logs_workday_id_fkey foreign KEY (workday_id) references workdays (workday_id)
) TABLESPACE pg_default;

create table public.task_logs (
  workday_id date not null default CURRENT_DATE,
  user_id uuid not null default auth.uid (),
  name text not null,
  id bigint generated by default as identity not null,
  tags text[] not null,
  constraint task_logs_pkey primary key (id),
  constraint task_logs_user_id_fkey foreign KEY (user_id) references users (user_id),
  constraint task_logs_workday_id_fkey foreign KEY (workday_id) references workdays (workday_id)
) TABLESPACE pg_default;

create table public.entry_logs (
  entry_id time without time zone not null default (now() AT TIME ZONE 'utc'::text),
  workday_id date not null default CURRENT_DATE,
  user_id uuid not null default auth.uid (),
  entry boolean not null,
  constraint entry_logs_pkey primary key (entry_id, workday_id),
  constraint entry_logs_user_id_fkey foreign KEY (user_id) references users (user_id),
  constraint entry_logs_workday_id_fkey foreign KEY (workday_id) references workdays (workday_id)
) TABLESPACE pg_default;

create or replace function set_opening_time () RETURNS TRIGGER as $$
BEGIN
  -- Set the opening time to the current timestamp if it is NULL
  IF NEW.opening_time IS NULL THEN
    NEW.opening_time := NOW();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for insert operation on the workdays table
create trigger set_opening_time_trigger BEFORE INSERT on public.workdays for EACH row
execute FUNCTION set_opening_time ();

create or replace function set_closing_time () RETURNS TRIGGER as $$
BEGIN
  -- Set the closing time to the current timestamp on update
  NEW.closing_time := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for update operation on the workdays table
create trigger set_closing_time_trigger BEFORE
update on public.workdays for EACH row
execute FUNCTION set_closing_time ();

create or replace function handle_checkin () returns trigger as $$
declare
    already_exists boolean;
begin
    select exists (
        select 1 from user_logs 
        where user_id = new.user_id and workday_id = new.workday_id
    ) into already_exists;

    if not already_exists then
        insert into user_logs (user_id, workday_id, checkin_time)
        values (new.user_id, new.workday_id, now());
    end if;

    return new;
end;
$$ language plpgsql;

create or replace function handle_checkout () returns trigger as $$
begin
    update user_logs
    set checkout_time = now()
    where user_id = new.user_id and workday_id = new.workday_id;
    return new;
end;
$$ language plpgsql;

create trigger trigger_checkin
after insert on entry_logs for each row
execute procedure handle_checkin ();

create trigger trigger_checkout
after insert on entry_logs for each row when (new.entry = false) -- Assuming `entry = false` means user left
execute procedure handle_checkout ();