NvKfXwLU

1create schema if not exists app_public;
2create schema if not exists app_private;
3
4drop type if exists size cascade;
5drop type if exists location_type cascade;
6drop type if exists inventory_status cascade;
7drop type if exists brand_tier cascade;
8
9create type size as enum ('XS', 'S', 'M', 'L', 'XL');
10create type location_type as enum ('office', 'warehouse', 'cleaner', 'customer');
11create type inventory_status as enum ('non_reservable', 'reservable', 'reserved');
12create type brand_tier as enum ('tier_0', 'tier_1', 'tier_2', 'niche', 'upcoming', 'retro', 'boutique');
13
14create table if not exists app_public.images
15(
16    id              serial primary key,
17    title           text,
18    caption         text,
19    original_url    text        not null,
20    resized_url     text        not null,
21    original_height int,
22    original_width  int,
23    created_at      timestamptz not null default now(),
24    updated_at      timestamptz not null default now()
25);
26
27create table if not exists app_public.colors
28(
29    id       varchar(80) primary key,
30    name     text,
31    hex_code char(8) not null
32);
33
34create table if not exists app_public.categories
35(
36    id          serial primary key,
37    name        text not null,
38    description text
39);
40
41create table if not exists app_public.brands
42(
43    id               serial primary key,
44    name             text        not null,
45    tier             brand_tier  not null,
46    website_url      text,
47    logo_image_id    int references app_public.images,
48    description      text,
49    since            date,
50    is_primary_brand bool                 default false,
51    brand_code       varchar(4)  not null,
52    created_at       timestamptz not null default now(),
53    updated_at       timestamptz not null default now()
54);
55
56create table if not exists app_public.products
57(
58    id           serial primary key,
59    name         text unique not null,
60    description  text,
61    brand_id     int         not null references app_public.brands,
62    retail_price money,
63    category_id  int         not null references app_public.categories,
64    created_at   timestamptz not null default now(),
65    updated_at   timestamptz not null default now()
66);
67
68create table if not exists app_public.product_variants
69(
70    id           serial primary key,
71    upc          int,
72    sku          text,
73    product_id   int         not null references app_public.products,
74    retail_price money,
75    color_id     varchar(80) not null references app_public.colors,
76    size         size        not null,
77    weight       int,
78    height       int,
79    created_at   timestamptz not null default now(),
80    updated_at   timestamptz not null default now()
81);
82
83create table if not exists app_public.product_variant_inventory_levels
84(
85    id                               serial primary key,
86    product_id                       int not null references app_public.products,
87    product_variant_id               int not null references app_public.product_variants,
88    non_reservable_physical_products int,
89    reservable_physical_products     int,
90    reserved_physical_products       int
91);
92
93create table if not exists app_public.locations
94(
95    id            serial primary key,
96    location_type location_type not null,
97    name          text,
98    description   text,
99    address1      text          not null,
100    address2      text          not null,
101    city          text          not null,
102    state         char(2)       not null,
103    zip           int           not null,
104    lat           numeric,
105    lng           numeric
106);