db.sql

-- This script was generated by the ERD tool in pgAdmin 4.
-- Please log an issue at https://github.com/pgadmin-org/pgadmin4/issues/new/choose if you find any bugs, including reproduction steps.
BEGIN;


CREATE TABLE IF NOT EXISTS public.account_identities
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    identity_key character varying COLLATE pg_catalog."default",
    identity_secret_key character varying COLLATE pg_catalog."default",
    account_id uuid,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    key_size smallint NOT NULL,
    key_type_id smallint,
    CONSTRAINT account_identties_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.account_passwords
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    name character varying(250) COLLATE pg_catalog."default" NOT NULL,
    content character varying COLLATE pg_catalog."default" NOT NULL,
    account_id uuid NOT NULL,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone,
    website character varying COLLATE pg_catalog."default",
    CONSTRAINT account_passwords_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.account_secrets
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    description character varying(300) COLLATE pg_catalog."default",
    content character varying COLLATE pg_catalog."default" NOT NULL,
    account_id uuid NOT NULL,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone,
    website character varying COLLATE pg_catalog."default",
    CONSTRAINT account_secrets_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.accounts
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    email character varying(200) COLLATE pg_catalog."default" NOT NULL,
    subscription_id uuid,
    account_type_id uuid,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone,
    enabled boolean NOT NULL DEFAULT true,
    CONSTRAINT accounts_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.associated_account_access_keys
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    account_id uuid NOT NULL,
    access_key character varying(250) COLLATE pg_catalog."default" NOT NULL,
    created_at timestamp without time zone NOT NULL,
    name character varying COLLATE pg_catalog."default",
    last_used_at timestamp without time zone,
    device_type_id smallint,
    CONSTRAINT associated_account_access_keys_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.device_types
(
    id smallint NOT NULL,
    name character varying(60) COLLATE pg_catalog."default",
    CONSTRAINT device_types_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.event_types
(
    id bigint NOT NULL,
    name character varying(60) COLLATE pg_catalog."default",
    CONSTRAINT event_types_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.events
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    description character varying COLLATE pg_catalog."default",
    device_id uuid,
    created_at timestamp without time zone NOT NULL,
    event_type_id bigint NOT NULL,
    CONSTRAINT events_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.key_types
(
    id smallint NOT NULL,
    name character varying(30) COLLATE pg_catalog."default",
    description character varying(100) COLLATE pg_catalog."default",
    has_size boolean DEFAULT true,
    CONSTRAINT key_types_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.mfa_method_types
(
    id smallint NOT NULL,
    name character varying(100) COLLATE pg_catalog."default",
    CONSTRAINT mfa_method_types_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.mfa_methods
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    type_id smallint NOT NULL,
    value character varying COLLATE pg_catalog."default" NOT NULL,
    user_id uuid NOT NULL,
    address character varying COLLATE pg_catalog."default",
    CONSTRAINT mfa_methods_pkey PRIMARY KEY (id, type_id, value)
);

CREATE TABLE IF NOT EXISTS public.rac_devices
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    address character varying(40) COLLATE pg_catalog."default" NOT NULL,
    key character varying COLLATE pg_catalog."default" NOT NULL,
    created_by uuid NOT NULL,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone,
    updated_by uuid,
    CONSTRAINT rac_devices_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.subscriptions
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    created_at timestamp without time zone NOT NULL,
    next_billing_date timestamp without time zone NOT NULL,
    enabled boolean NOT NULL,
    CONSTRAINT subscriptions_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.time_based_algorithms
(
    id smallint NOT NULL,
    name character varying(60) COLLATE pg_catalog."default",
    CONSTRAINT time_based_algorithms_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.time_based_code_types
(
    id smallint NOT NULL,
    name character varying(60) COLLATE pg_catalog."default",
    CONSTRAINT time_based_code_types_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.time_based_codes
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    website character varying(200) COLLATE pg_catalog."default" NOT NULL,
    email character varying(200) COLLATE pg_catalog."default" NOT NULL,
    secret character varying COLLATE pg_catalog."default" NOT NULL,
    type_id smallint NOT NULL,
    expiration smallint NOT NULL,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone,
    algorithm_id smallint,
    CONSTRAINT time_based_codes_pkey PRIMARY KEY (id)
);

ALTER TABLE IF EXISTS public.account_identities
    ADD CONSTRAINT "account_Identities_account_id" FOREIGN KEY (account_id)
    REFERENCES public.accounts (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.account_identities
    ADD CONSTRAINT "account_Identities_key_type_id" FOREIGN KEY (key_type_id)
    REFERENCES public.key_types (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.account_passwords
    ADD CONSTRAINT account_passwords_account_id FOREIGN KEY (account_id)
    REFERENCES public.accounts (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.account_secrets
    ADD CONSTRAINT account_secrets_account_id FOREIGN KEY (account_id)
    REFERENCES public.accounts (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.accounts
    ADD CONSTRAINT accounts_subscription_id FOREIGN KEY (subscription_id)
    REFERENCES public.subscriptions (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.associated_account_access_keys
    ADD CONSTRAINT associated_account_access_keys_account_id FOREIGN KEY (account_id)
    REFERENCES public.accounts (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.associated_account_access_keys
    ADD CONSTRAINT associated_account_access_keys_device_type FOREIGN KEY (device_type_id)
    REFERENCES public.device_types (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.events
    ADD CONSTRAINT events_device_id FOREIGN KEY (device_id)
    REFERENCES public.associated_account_access_keys (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.events
    ADD CONSTRAINT events_event_type_id FOREIGN KEY (event_type_id)
    REFERENCES public.event_types (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.mfa_methods
    ADD CONSTRAINT mfa_methods_type_id FOREIGN KEY (type_id)
    REFERENCES public.mfa_method_types (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.mfa_methods
    ADD CONSTRAINT mfa_methods_user_id_fkey FOREIGN KEY (user_id)
    REFERENCES public.accounts (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.rac_devices
    ADD CONSTRAINT rac_devices_created_by FOREIGN KEY (created_by)
    REFERENCES public.accounts (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.rac_devices
    ADD CONSTRAINT rac_updated_by FOREIGN KEY (updated_by)
    REFERENCES public.accounts (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.time_based_codes
    ADD CONSTRAINT time_based_code_algorithm_id FOREIGN KEY (algorithm_id)
    REFERENCES public.time_based_algorithms (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.time_based_codes
    ADD CONSTRAINT time_based_code_type_id FOREIGN KEY (type_id)
    REFERENCES public.time_based_code_types (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;

END;