-- 施設テーブルの作成
CREATE TABLE facilities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    number_of_rooms INTEGER NOT NULL CHECK (number_of_rooms > 0)
);

-- 予約テーブルの作成
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    facility_id INTEGER NOT NULL REFERENCES facilities(id),
    user_first_name VARCHAR(4) NOT NULL,
    user_last_name VARCHAR(4) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    transportation_method VARCHAR(20) NOT NULL CHECK (transportation_method IN ('無し', '施設送迎', '家族送迎')),
    provisional_reservation BOOLEAN NOT NULL DEFAULT FALSE,
    CHECK (end_date >= start_date),
    CHECK (end_date - start_date < INTERVAL '10 days')
);

-- 予約数管理テーブルの作成
CREATE TABLE reservation_counts (
    id SERIAL PRIMARY KEY,
    facility_id INTEGER NOT NULL REFERENCES facilities(id),
    year_month VARCHAR(7) NOT NULL, -- 'YYYY-MM'形式
    reservation_counts INTEGER[] NOT NULL,
    UNIQUE (facility_id, year_month)
);

-- 関数: 予約数配列を更新（更新��一元化）
CREATE OR REPLACE FUNCTION update_reservation_counts()
RETURNS TRIGGER AS $$
DECLARE
    current_date DATE;
    end_date DATE;
    ym TEXT;
    day INTEGER;
    cnt INTEGER;
BEGIN
    IF TG_OP = 'INSERT' THEN
        cnt := 1;
    ELSIF TG_OP = 'DELETE' THEN
        cnt := -1;
    ELSE
        RETURN NULL;
    END IF;

    current_date := TG_OP = 'DELETE' ? OLD.start_date : NEW.start_date;
    end_date := TG_OP = 'DELETE' ? OLD.end_date : NEW.end_date;

    WHILE current_date <= end_date LOOP
        ym := TO_CHAR(current_date, 'YYYY-MM');
        day := EXTRACT(DAY FROM current_date)::INTEGER;

        -- 行が存在しない場合は挿入
        INSERT INTO reservation_counts (facility_id, year_month, reservation_counts)
        SELECT 
            COALESCE(NEW.facility_id, OLD.facility_id),
            ym,
            ARRAY_FILL(0, ARRAY[EXTRACT(DAY FROM (DATE_TRUNC('month', current_date) + INTERVAL '1 month - 1 day'))::INTEGER])::INTEGER[]
        WHERE NOT EXISTS (
            SELECT 1 FROM reservation_counts
            WHERE facility_id = COALESCE(NEW.facility_id, OLD.facility_id)
              AND year_month = ym
        );

        -- 配列を更新
        UPDATE reservation_counts
        SET reservation_counts = ARRAY[
            SELECT 
                CASE 
                    WHEN i = day THEN reservation_counts[i] + cnt 
                    ELSE reservation_counts[i] 
                END
            FROM generate_subscripts(reservation_counts, 1) AS i
        ]
        WHERE facility_id = COALESCE(NEW.facility_id, OLD.facility_id)
          AND year_month = ym;

        current_date := current_date + INTERVAL '1 day';
    END LOOP;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- トリガー: 予約の挿入時に予約数を更新
CREATE TRIGGER trg_reservation_insert
AFTER INSERT ON reservations
FOR EACH ROW
EXECUTE FUNCTION update_reservation_counts();

-- トリガー: 予約の削除時に予約数を更新
CREATE TRIGGER trg_reservation_delete
AFTER DELETE ON reservations
FOR EACH ROW
EXECUTE FUNCTION update_reservation_counts();