DatabaseCreation: seat_generator.sql

BEGIN;

-- Use a DO block so we can iterate stadiums and use their capacity directly
DO $$
DECLARE
    s              RECORD;
    rows_per_stad  INT;
    seats_per_row  INT;
    inserted_count INT;
BEGIN
    FOR s IN
        SELECT st.stadium_id, st.capacity
        FROM Stadium st
        WHERE NOT EXISTS (
            SELECT 1 FROM Seat se WHERE se.stadium_id = st.stadium_id
        )
        ORDER BY st.stadium_id
    LOOP
        -- Pick a roughly square-ish grid; minimum 1 row.
        rows_per_stad := GREATEST(1, CEIL(SQRT(s.capacity::numeric / 20.0))::int);
        seats_per_row := GREATEST(1, CEIL(s.capacity::numeric / rows_per_stad)::int);

        INSERT INTO Seat (stadium_id, row, number)
        SELECT
            s.stadium_id,
            r,
            n
        FROM generate_series(1, rows_per_stad)               AS r
        CROSS JOIN generate_series(1, seats_per_row)         AS n
        ORDER BY r, n
        LIMIT s.capacity;

        GET DIAGNOSTICS inserted_count = ROW_COUNT;
        RAISE NOTICE 'Stadium % : capacity=% , inserted=% seats',
            s.stadium_id, s.capacity, inserted_count;
    END LOOP;
END $$;

COMMIT;