gistfile1.txt

create database warehouse;
go;
use warehouse;
drop table if exists fact_sale_tbl;
drop table if exists time_tbl;
drop table if exists customer_tbl;
drop table if exists store_tbl;
drop table if exists stored_merchan_tbl;
drop table if exists merchandise_tbl;
go;

create table time_tbl
(
    id      INT PRIMARY KEY IDENTITY (1, 1),
    day     INT,
    month   INT,
    quarter INT,
    year    INT,
    full_date date
)

create table customer_tbl
(
    id               INT PRIMARY KEY,
    type             NVARCHAR(30),
    country_code     INT,
    name             NVARCHAR(400),
    first_ordered_at DATE,
    courier          NVARCHAR(200),
    address          NVARCHAR(200),
    sign_up_at       date,
    --CONSTRAINT fk_cs_stime FOREIGN KEY (sign_up_at) REFERENCES time_tbl(id),
)


create table store_tbl
(
    id                INT PRIMARY KEY,
    phone             NVARCHAR(200),
    country_code      INT,
    office_name       NVARCHAR(400),
    office_address    NVARCHAR(400),
    state             NVARCHAR(100),
    store_created_at  date,
    office_created_at date,
    --CONSTRAINT fk_s_stime FOREIGN KEY (store_created_at) REFERENCES time_tbl(id),
    --CONSTRAINT fk_s_otime FOREIGN KEY (office_created_at) REFERENCES time_tbl(id),
);

create table merchandise_tbl
(
    id           INT PRIMARY KEY,
    description  NVARCHAR(500),
    sz           NVARCHAR(100),
    weight       INT,
    price        INT,
    producted_at date
    --CONSTRAINT fk_m_time FOREIGN KEY (producted_at) REFERENCES time_tbl(id),
);

create table stored_merchan_tbl
(
    id         INT PRIMARY KEY IDENTITY (1, 1),
    store_id   INT,
    merchan_id INT,
    quatity    INT,
    stored_at  date

        CONSTRAINT fk_sm_store FOREIGN KEY (store_id) REFERENCES store_tbl (id),
    CONSTRAINT fk_sm_merchan FOREIGN KEY (merchan_id) REFERENCES merchandise_tbl (id),
    --CONSTRAINT fk_sm_time FOREIGN KEY (stored_at) REFERENCES time_tbl(id),
);

create table fact_sale_tbl
(
    id            INT PRIMARY KEY IDENTITY (1, 1),
    time_id       INT,
    store_id      INT,
    mechandise_id INT,
    order_id      INT,
    customer_id   INT,
    quantity      INT,
    price         FLOAT,
    total_price   FLOAT

        CONSTRAINT fk_fs_time FOREIGN KEY (time_id) REFERENCES time_tbl (id),
    CONSTRAINT fk_fs_mechan FOREIGN KEY (mechandise_id) REFERENCES merchandise_tbl (id),
    CONSTRAINT fk_fs_custo FOREIGN KEY (customer_id) REFERENCES customer_tbl (id),
    CONSTRAINT fk_fs_stor FOREIGN KEY (store_id) REFERENCES store_tbl (id)
);
go;

CREATE UNIQUE NONCLUSTERED INDEX [UIX_tbltime_day_month_year]
    ON warehouse.dbo.time_tbl
        ([day] ASC, [month] ASC, [year] ASC) WITH (IGNORE_DUP_KEY = ON);
go;
INSERT warehouse.dbo.time_tbl (year, month, day, quarter, full_date)
    (SELECT YEAR(cus.time)        year,
            MONTH(cus.time)       month,
            DAY(cus.time)         day,
            (MONTH(cus.time) / 3) quarter,
            cus.time
     from cus_order cus
    );

select count(id)
from warehouse.dbo.time_tbl;

INSERT warehouse.dbo.customer_tbl(id, country_code, name, courier, first_ordered_at, sign_up_at, type, address)
    (select c.id                                                    id,
            c.city_code                                             country_code,
            c.name                                                  name,
            tc.courier                                              courier,
            c.first_order_at                                        first_order_at,
            iif(tc.time is not null, tc.time, pc.time)              time,
            iif(tc.cus_id is not null and pc.cus_id is not null, 'dual',
                iif(tc.cus_id is not null, 'travel', 'postoffice')) type,
            pc.office_address                                       address
     from customer c
              full join travel_customer tc on c.id = tc.cus_id
              full join postoffice_customer pc on c.id = pc.cus_id);


-- INSERT warehouse.dbo.customer_tbl(id, country_code, name, address, first_ordered_at, sign_up_at, type)
--     (select     c.id + 50         id,
--                 c.city_code       country_code,
--                 c.name            name,
--                 pc.office_address address,
--                 c.first_order_at  first_order_at,
--                 pc.time           sign_up_at,
--          type = 'postoffice'
--      from customer c
--               join postoffice_customer pc on c.id = pc.cus_id);

INSERT warehouse.dbo.store_tbl(id, phone, country_code, office_name, office_address, state, store_created_at,
                               office_created_at) (select s.id      id,
                                                          s.phone   phone,
                                                          o.id      country_code,
                                                          o.name    office_name,
                                                          o.address office_address,
                                                          o.state   state,
                                                          s.time    store_created_at,
                                                          o.time    office_created_at
                                                   from store s
                                                            inner join office o on s.office_id = o.id)


insert warehouse.dbo.merchandise_tbl (id, description, sz, weight, price, producted_at) (select * from merchandise);

insert warehouse.dbo.stored_merchan_tbl (store_id, merchan_id, quatity, stored_at) (select store_id, merchandise_id, quantity, time from stored_merchan)


insert warehouse.dbo.fact_sale_tbl (customer_id, mechandise_id, order_id, store_id, quantity, price, total_price,
                                    time_id)
    (select od.cus_id                    customer_id,
            pm.merchan_id                mechandise_id,
            od.id                        order_id,
            st.id                        store_id,
            sm.quantity                  quantity,
            mer.price,
            sm.quantity * mer.price      total,
            (select top 1 t.id
             from warehouse.dbo.time_tbl t
             where t.year = YEAR(od.time)
               AND t.month = MONTH(od.time)
               AND t.day = DAY(od.time)) time_id
     from cus_order od
              inner join placed_merchan pm on pm.order_id = od.id
              inner join stored_merchan sm on sm.merchandise_id = pm.merchan_id
              inner join merchandise mer on mer.id = pm.merchan_id
              inner join store st on st.id = store_id where exists(select * from warehouse.dbo.customer_tbl wdc where wdc.id = od.cus_id ));