DatabaseCreation: ticket_generator.sql

BEGIN;
CREATE INDEX IF NOT EXISTS idx_seat_stadium ON Seat (stadium_id);
CREATE INDEX IF NOT EXISTS idx_ticket_match ON Ticket (match_id);
CREATE UNIQUE INDEX IF NOT EXISTS uq_ticket_seat_match ON Ticket (seat_id, match_id);
COMMIT;

DROP TABLE IF EXISTS tmp_stadium_seats;
CREATE TEMP TABLE tmp_stadium_seats AS
SELECT stadium_id, COUNT(*) AS seat_count
FROM Seat
GROUP BY stadium_id;

DO $$
DECLARE
    m RECORD;
    seats INT;
    fill_rate NUMERIC;
    scan_rate NUMERIC;
    n_to_insert INT;
    sample_pct NUMERIC;
    total BIGINT;
BEGIN
    FOR m IN SELECT match_id, stadium_id FROM "match" LOOP
        SELECT COUNT(*) INTO total FROM Ticket;
        EXIT WHEN total >= 15000000;

        SELECT seat_count INTO seats
        FROM tmp_stadium_seats
        WHERE stadium_id = m.stadium_id;

        IF seats IS NULL THEN CONTINUE; END IF;

        -- Pick a fill rate that targets ~10-15M total. Adjust the range based on
        -- (target_rows / total_matches / avg_seats_per_stadium).
        fill_rate := 0.40 + random() * 0.20;
        scan_rate := 0.30 + random() * 0.50;
        n_to_insert := GREATEST(1, (seats * fill_rate)::int);

        sample_pct := LEAST(100, fill_rate * 100 * 1.5);

        INSERT INTO Ticket (seat_id, match_id, is_scanned, price)
        SELECT picked.seat_id,
               m.match_id,
               (random() < scan_rate),
               CEIL(10 + random() * 190)
        FROM (
            SELECT se.seat_id
            FROM Seat se TABLESAMPLE BERNOULLI (sample_pct)
            WHERE se.stadium_id = m.stadium_id
            LIMIT n_to_insert
        ) picked
        ON CONFLICT (seat_id, match_id) DO NOTHING;
    END LOOP;
END $$;

SELECT COUNT(*) FROM Ticket;