Event ticketing

CREATE TYPE order_state AS ENUM ('pending', 'paid', 'refunded', 'cancelled');

CREATE TABLE organizers (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL,
    email text NOT NULL UNIQUE
);

CREATE TABLE venues (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL,
    city text NOT NULL,
    capacity int
);

CREATE TABLE events (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    organizer_id uuid NOT NULL,
    venue_id uuid,
    title text NOT NULL,
    starts_at timestamptz NOT NULL,
    ends_at timestamptz,
    published boolean DEFAULT false NOT NULL
);
CREATE INDEX events_organizer_id_idx ON events (organizer_id);
CREATE INDEX events_venue_id_idx ON events (venue_id);

CREATE TABLE ticket_types (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    event_id uuid NOT NULL,
    name text NOT NULL,
    price numeric(10,2) NOT NULL,
    quantity int NOT NULL,
    sale_ends_at timestamptz,
    CHECK (quantity >= 0)
);
CREATE INDEX ticket_types_event_id_idx ON ticket_types (event_id);

CREATE TABLE buyers (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL,
    full_name text NOT NULL
);

CREATE TABLE ticket_orders (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    buyer_id uuid NOT NULL,
    state order_state DEFAULT 'pending' NOT NULL,
    total numeric(10,2) NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX ticket_orders_buyer_id_idx ON ticket_orders (buyer_id);

CREATE TABLE tickets (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    order_id uuid NOT NULL,
    ticket_type_id uuid NOT NULL,
    code text NOT NULL UNIQUE,
    checked_in_at timestamptz
);
CREATE INDEX tickets_type_idx ON tickets (ticket_type_id);
CREATE INDEX tickets_order_id_idx ON tickets (order_id);

ALTER TABLE events ADD FOREIGN KEY (organizer_id) REFERENCES organizers (id);

ALTER TABLE events ADD FOREIGN KEY (venue_id) REFERENCES venues (id);

ALTER TABLE ticket_types ADD FOREIGN KEY (event_id) REFERENCES events (id) ON DELETE CASCADE;

ALTER TABLE ticket_orders ADD FOREIGN KEY (buyer_id) REFERENCES buyers (id);

ALTER TABLE tickets ADD FOREIGN KEY (order_id) REFERENCES ticket_orders (id) ON DELETE CASCADE;

ALTER TABLE tickets ADD FOREIGN KEY (ticket_type_id) REFERENCES ticket_types (id);