k2qmijux

1create schema if not exists pricing_intelligence;
2
3use pricing_intelligence;
4
5create table if not exists units
6(
7    id                 int auto_increment primary key,
8    slug               varchar(5)  not null,
9    display_text       varchar(10) not null,
10    base_unit_id       double      not null,
11    scale_of_base_unit int         not null,
12    foreign key (base_unit_id) references units (id)
13);
14
15insert into units
16    (id, slug, display_text, base_unit_id, scale_of_base_unit)
17values (1, 'grams', 'grams', 1, 1),
18       (2, 'kilograms', 'kilograms', 1, 1000),
19       (3, 'ml', 'millilitres', 3, 1),
20       (4, 'l', 'litres', 3, 1000),
21       (5, 'units', 'units', 5, 1);
22
23create table if not exists competitors
24(
25    id           int auto_increment primary key,
26    slug         varchar(5)  not null,
27    display_text varchar(10) not null
28);
29
30insert into competitors
31    (slug, display_text)
32values ('loblaws', 'loblaws'),
33       ('metro', 'metro'),
34       ('voila', 'voila'),
35       ('walmart', 'walmart'),
36       ('iga', 'iga');
37
38create table if not exists product_competitor_url
39(
40    id            int auto_increment primary key,
41    product_id    int         not null,
42    competitor_id int         not null,
43    url           varchar(10) not null unique,
44    foreign key (product_id) references products (id),
45    foreign key (competitor_id) references competitors (id),
46    constraint competitor_product_unique unique (product_id, competitor_id)
47);
48
49create table if not exists products
50(
51    id                  int auto_increment primary key,
52    SKU                 int    null unique,
53    product_name        text   null,
54    product_description text   null,
55    price               double null,
56    sold_by_unit_id     int    not null,
57    sold_by_volume      double null,
58    modified_date       date   null,
59    foreign key (sold_by_unit_id) references units (id)
60);
61
62create table if not exists scrape_results
63(
64    id                                      int unsigned auto_increment primary key,
65
66    product_competitor_url_id               int                                 not null,
67    product_id                              int                                 not null,
68
69    competitor_price                        double                              null,
70    competitor_price_without_promotion      double                              null,
71    competitor_sold_by_volume               double                              null,
72    competitor_sold_by_unit                 varchar(255)                        null,
73    promotional_info                        varchar(255)                        null,
74
75    goodfood_price_at_scrape_time           double                              null,
76    goodfood_sold_by_volume_at_scrape_time  double                              null,
77    goodfood_sold_by_unit_at_scrape_time_id int                                 not null,
78
79    data_source                             varchar(20)                         null,
80    scrape_time                             datetime  default CURRENT_TIMESTAMP,
81
82    created_at                              timestamp default CURRENT_TIMESTAMP not null,
83    updated_at                              datetime  default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
84
85    index data_source (data_source),
86
87    constraint datasource_url_unique unique (product_competitor_url_id, data_source),
88
89    foreign key (product_id) REFERENCES products (id),
90    foreign key (product_competitor_url_id) REFERENCES product_competitor_url (id),
91    foreign key (goodfood_sold_by_unit_at_scrape_time_id) REFERENCES units (id)
92);