-- bingo tables as created by the asxpio migrations (004–016), for tests.
-- Refresh when asxpio adds a bingo migration: run them into an empty DB and
-- pg_dump -s -O -x -t bingo_datacenters -t bingo_incidents.
CREATE TABLE public.bingo_datacenters (
    id integer NOT NULL,
    slug text NOT NULL,
    "position" integer NOT NULL,
    name_ru text NOT NULL,
    name_en text,
    name_uk text,
    city_ru text NOT NULL,
    city_en text,
    city_uk text,
    operator text NOT NULL,
    mw text,
    status text DEFAULT 'operational'::text NOT NULL,
    year integer,
    photo_key text,
    photo_source_url text,
    photo_credit text,
    info_url text,
    created_at timestamp without time zone NOT NULL,
    photo_variants boolean DEFAULT false NOT NULL,
    mw_type text,
    photo_og boolean DEFAULT false NOT NULL,
    updated_at timestamp without time zone,
    about_ru text,
    about_en text,
    about_uk text,
    provider text
);

ALTER TABLE public.bingo_datacenters ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.bingo_datacenters_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE public.bingo_incidents (
    id integer NOT NULL,
    datacenter_id integer NOT NULL,
    occurred_on date NOT NULL,
    kind text NOT NULL,
    title_ru text NOT NULL,
    title_en text,
    title_uk text,
    url text,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone
);

ALTER TABLE public.bingo_incidents ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.bingo_incidents_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

ALTER TABLE ONLY public.bingo_datacenters
    ADD CONSTRAINT bingo_datacenters_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.bingo_datacenters
    ADD CONSTRAINT bingo_datacenters_slug_key UNIQUE (slug);

ALTER TABLE ONLY public.bingo_incidents
    ADD CONSTRAINT bingo_incidents_pkey PRIMARY KEY (id);

CREATE INDEX bingo_incidents_datacenter_id_index ON public.bingo_incidents USING btree (datacenter_id);

ALTER TABLE ONLY public.bingo_incidents
    ADD CONSTRAINT bingo_incidents_datacenter_id_fkey FOREIGN KEY (datacenter_id) REFERENCES public.bingo_datacenters(id) ON DELETE CASCADE;