-- bingo tables as created by the asxpio migrations (004–017), 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 -t 'bingo_monitor_*'.

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
);

CREATE TABLE public.bingo_monitor_buckets (
    bucket_at timestamp without time zone NOT NULL,
    rounds integer DEFAULT 0 NOT NULL,
    control_checks integer DEFAULT 0 NOT NULL,
    control_ok integer DEFAULT 0 NOT NULL
);

CREATE TABLE public.bingo_monitor_samples (
    datacenter_id integer NOT NULL,
    bucket_at timestamp without time zone NOT NULL,
    checks integer DEFAULT 0 NOT NULL,
    ok integer DEFAULT 0 NOT NULL,
    rtt_ms_sum integer DEFAULT 0 NOT NULL
);

CREATE TABLE public.bingo_monitor_targets (
    id integer NOT NULL,
    datacenter_id integer,
    host text NOT NULL,
    port integer DEFAULT 443 NOT NULL,
    label text,
    source_url text,
    active boolean DEFAULT true NOT NULL,
    last_ok boolean,
    last_rtt_ms integer,
    last_checked_at timestamp without time zone,
    last_change_at timestamp without time zone,
    created_at timestamp without time zone NOT NULL
);

ALTER TABLE public.bingo_monitor_targets ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.bingo_monitor_targets_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);

ALTER TABLE ONLY public.bingo_monitor_buckets
    ADD CONSTRAINT bingo_monitor_buckets_pkey PRIMARY KEY (bucket_at);

ALTER TABLE ONLY public.bingo_monitor_samples
    ADD CONSTRAINT bingo_monitor_samples_pkey PRIMARY KEY (datacenter_id, bucket_at);

ALTER TABLE ONLY public.bingo_monitor_targets
    ADD CONSTRAINT bingo_monitor_targets_pkey PRIMARY KEY (id);

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

CREATE INDEX bingo_monitor_targets_datacenter_id_index ON public.bingo_monitor_targets 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;

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

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