-- 施設テーブル
create table facility (
  id serial primary key,
  name text not null,
  room_count integer not null
);

-- 予約テーブル
create table reservation (
  id serial primary key,
  facility_id integer references facility(id) not null,
  start_date date not null,
  end_date date not null,
  last_name text not null,
  first_name text not null,
  pickup text not null,
  provisional boolean not null
);

-- 予約数管理テーブル
create table reservation_count (
  facility_id integer references facility(id) not null,
  year_month text not null,
  counts integer[] not null,
  primary key (facility_id, year_month)
);

-- 予約数管理テーブルを更新する関数
create or replace function update_reservation_count(
  p_facility_id integer,
  p_start_date date,
  p_end_date date,
  p_operation integer -- 1: 予約, -1: キャンセル
) returns void as $$
declare
  v_current_date date;
  v_year_month text;
  v_counts integer[];
  v_days_in_month integer;
begin
  v_current_date := p_start_date;

  while v_current_date <= p_end_date loop
    v_year_month := to_char(v_current_date, 'YYYY-MM');

    -- 予約数管理テーブルから該当月のデータを取得
    select counts into v_counts
    from reservation_count
    where facility_id = p_facility_id and year_month = v_year_month;

    -- データが存在しない場合は新規作成
    if not found then
      v_days_in_month := extract(days from date_trunc('month', v_current_date) + interval '1 month - 1 day');
      v_counts := array_fill(0, array[v_days_in_month]);

      insert into reservation_count (facility_id, year_month, counts)
      values (p_facility_id, v_year_month, v_counts);
    end if;

    -- 予約数を更新
    v_counts[extract(day from v_current_date)] := v_counts[extract(day from v_current_date)] + p_operation;

    -- 予約数管理テーブルを更新
    update reservation_count
    set counts = v_counts
    where facility_id = p_facility_id and year_month = v_year_month;

    v_current_date := v_current_date + interval '1 day';
  end loop;
end;
$$ language plpgsql;

-- 予約テーブルへの追加・削除時に予約数管理テーブルを更新するト��ガー
create or replace function reservation_trigger() returns trigger as $$
begin
  if (TG_OP = 'INSERT') then
    perform update_reservation_count(NEW.facility_id, NEW.start_date, NEW.end_date, 1);
  elsif (TG_OP = 'DELETE') then
    perform update_reservation_count(OLD.facility_id, OLD.start_date, OLD.end_date, -1);
  end if;
  return null;
end;
$$ language plpgsql;

create trigger reservation_insert_trigger
after insert on reservation
for each row
execute procedure reservation_trigger();

create trigger reservation_delete_trigger
after delete on reservation
for each row
execute procedure reservation_trigger();