migrations

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    migration_id character varying(150) NOT NULL,
    product_version character varying(32) NOT NULL,
    CONSTRAINT pk___ef_migrations_history PRIMARY KEY (migration_id)
);

CREATE TABLE asp_net_roles (
    id text NOT NULL,
    name character varying(127) NULL,
    normalized_name character varying(127) NULL,
    concurrency_stamp text NULL,
    CONSTRAINT pk_roles PRIMARY KEY (id)
);

CREATE TABLE asp_net_users (
    id text NOT NULL,
    user_name character varying(127) NULL,
    normalized_user_name character varying(127) NULL,
    email character varying(127) NULL,
    normalized_email character varying(127) NULL,
    email_confirmed boolean NOT NULL,
    password_hash text NULL,
    security_stamp text NULL,
    concurrency_stamp text NULL,
    phone_number text NULL,
    phone_number_confirmed boolean NOT NULL,
    two_factor_enabled boolean NOT NULL,
    lockout_end timestamp with time zone NULL,
    lockout_enabled boolean NOT NULL,
    access_failed_count integer NOT NULL,
    display_username text NULL,
    organisation_id integer NULL,
    CONSTRAINT pk_users PRIMARY KEY (id)
);

CREATE TABLE organisations (
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    name text NULL,
    phone_number text NULL,
    email text NULL,
    address_line1 text NULL,
    address_line2 text NULL,
    area text NULL,
    city text NULL,
    county text NULL,
    post_code text NULL,
    country text NULL,
    is_deleted boolean NOT NULL,
    created_at timestamp without time zone NOT NULL,
    modified_at timestamp without time zone NULL,
    CONSTRAINT pk_organisations PRIMARY KEY (id)
);

CREATE TABLE asp_net_role_claims (
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    role_id text NOT NULL,
    claim_type text NULL,
    claim_value text NULL,
    CONSTRAINT pk_role_claims PRIMARY KEY (id),
    CONSTRAINT fk_role_claims_asp_net_roles_identity_role_id FOREIGN KEY (role_id) REFERENCES asp_net_roles (id) ON DELETE CASCADE
);

CREATE TABLE asp_net_user_claims (
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    user_id text NOT NULL,
    claim_type text NULL,
    claim_value text NULL,
    CONSTRAINT pk_user_claims PRIMARY KEY (id),
    CONSTRAINT fk_user_claims_asp_net_users_application_user_id FOREIGN KEY (user_id) REFERENCES asp_net_users (id) ON DELETE CASCADE
);

CREATE TABLE asp_net_user_logins (
    login_provider character varying(127) NOT NULL,
    provider_key character varying(127) NOT NULL,
    provider_display_name text NULL,
    user_id text NOT NULL,
    CONSTRAINT pk_user_logins PRIMARY KEY (login_provider, provider_key),
    CONSTRAINT fk_user_logins_asp_net_users_application_user_id FOREIGN KEY (user_id) REFERENCES asp_net_users (id) ON DELETE CASCADE
);

CREATE TABLE asp_net_user_roles (
    user_id character varying(127) NOT NULL,
    role_id character varying(127) NOT NULL,
    CONSTRAINT pk_user_roles PRIMARY KEY (user_id, role_id),
    CONSTRAINT fk_user_roles_asp_net_roles_identity_role_id FOREIGN KEY (role_id) REFERENCES asp_net_roles (id) ON DELETE CASCADE,
    CONSTRAINT fk_user_roles_asp_net_users_application_user_id FOREIGN KEY (user_id) REFERENCES asp_net_users (id) ON DELETE CASCADE
);

CREATE TABLE asp_net_user_tokens (
    user_id character varying(127) NOT NULL,
    login_provider character varying(127) NOT NULL,
    name character varying(127) NOT NULL,
    value text NULL,
    CONSTRAINT pk_user_tokens PRIMARY KEY (user_id, login_provider, name),
    CONSTRAINT fk_user_tokens_asp_net_users_application_user_id FOREIGN KEY (user_id) REFERENCES asp_net_users (id) ON DELETE CASCADE
);

CREATE TABLE customers (
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    title text NULL,
    first_name text NULL,
    surname text NULL,
    email text NULL,
    home_phone_number text NULL,
    work_phone_number text NULL,
    mobile_phone_number text NULL,
    is_deleted boolean NOT NULL,
    created_at timestamp without time zone NOT NULL,
    modified_at timestamp without time zone NULL,
    organisation_id integer NOT NULL,
    CONSTRAINT pk_customers PRIMARY KEY (id),
    CONSTRAINT fk_customers_organisations_organisation_id FOREIGN KEY (organisation_id) REFERENCES organisations (id) ON DELETE CASCADE
);

CREATE TABLE addresses (
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    address_line1 text NULL,
    address_line2 text NULL,
    area text NULL,
    city text NULL,
    county text NULL,
    post_code text NULL,
    country text NULL,
    is_deleted boolean NOT NULL,
    created_at timestamp without time zone NOT NULL,
    modified_at timestamp without time zone NULL,
    customer_id integer NOT NULL,
    CONSTRAINT pk_addresses PRIMARY KEY (id),
    CONSTRAINT fk_addresses_customers_customer_id FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
);

CREATE TABLE invoices (
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    customer_id integer NOT NULL,
    is_deleted boolean NOT NULL,
    created_at timestamp without time zone NOT NULL,
    modified_at timestamp without time zone NULL,
    CONSTRAINT pk_invoices PRIMARY KEY (id),
    CONSTRAINT fk_invoices_customers_customer_id FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
);

CREATE TABLE vehicles (
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    registration_number text NULL,
    make text NULL,
    model text NULL,
    year text NULL,
    fuel text NULL,
    colour text NULL,
    vin text NULL,
    mot_expiry_date timestamp without time zone NULL,
    mot_certificate_number text NULL,
    next_service_due timestamp without time zone NULL,
    customer_id integer NOT NULL,
    is_deleted boolean NOT NULL,
    created_at timestamp without time zone NOT NULL,
    modified_at timestamp without time zone NULL,
    CONSTRAINT pk_vehicles PRIMARY KEY (id),
    CONSTRAINT fk_vehicles_customers_customer_id FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
);

CREATE INDEX ix_addresses_customer_id ON addresses (customer_id);

CREATE INDEX ix_role_claims_role_id ON asp_net_role_claims (role_id);

CREATE UNIQUE INDEX "RoleNameIndex" ON asp_net_roles (normalized_name);

CREATE INDEX ix_user_claims_user_id ON asp_net_user_claims (user_id);

CREATE INDEX ix_user_logins_user_id ON asp_net_user_logins (user_id);

CREATE INDEX ix_user_roles_role_id ON asp_net_user_roles (role_id);

CREATE INDEX "EmailIndex" ON asp_net_users (normalized_email);

CREATE UNIQUE INDEX "UserNameIndex" ON asp_net_users (normalized_user_name);

CREATE INDEX ix_customers_organisation_id ON customers (organisation_id);

CREATE INDEX ix_invoices_customer_id ON invoices (customer_id);

CREATE INDEX ix_vehicles_customer_id ON vehicles (customer_id);

INSERT INTO "__EFMigrationsHistory" (migration_id, product_version)
VALUES ('20200809113540_InitialCreate', '3.1.6');