--------------
-- dim_date --
--------------

SET LANGUAGE Russian
use shipping_storage

DECLARE @startday datetime = '2018-01-01T12:00:00'
DECLARE @endday datetime = '2026-01-01T12:00:00'
DECLARE @inc datetime
SET @inc = @startday
DECLARE @keyDate bigint

WHILE @inc <= @endday
BEGIN
SET @keyDate = ( SELECT DATEPART(hour, @inc) +
                        DATEPART(day, @inc) * 100 +
                        DATEPART(month, @inc) * 10000 +
                        DATEPART(year, @inc) * 1000000 )

    INSERT INTO shipping_storage.dbo.dim_date
    (date_key, date, year, quarter, month, week, day, hour)
    VALUES
    ( @keyDate,
      @inc,
      year(@inc),
      datepart(quarter, @inc),
      month(@inc),
      datepart(week,@inc),
      day(@inc),
      datename(hour, @inc) )
    SET @inc = DATEADD(DAY, 1, @inc)
END
GO

-------------------
-- dim_geography --
-------------------

SET LANGUAGE Russian
use shipping_storage

INSERT INTO shipping_storage.dbo.dim_geography
(region_name, region_code, postal_code, city_name)
VALUES
(N'Саратовская область', 64, 410065, N'Саратов'),
(N'Московская область', 77, 410065, N'Подольск'),
(N'Московская область', 77, 410065, N'Москва')
GO


------------------
-- dim_wrapping --
------------------

INSERT INTO shipping_storage.dbo.dim_wrapping
(name, price)
VALUES (N'Жёсткая', 100),
       (N'Полужёсткая', 100),
       (N'Мягкая', 100),
       (N'Воздушно-Пузырьковая', 100),
       (N'Рулон', 100)
GO


----------------------
-- dim_payment_kind --
----------------------

INSERT INTO shipping_storage.dbo.dim_payment_kind
(name)
VALUES (N'Наличными'),
       (N'Картой'),
       (N'Картой МИР'),
       (N'Чеком')
GO


-----------------------------
-- dim_customer_demography --
-----------------------------

SET LANGUAGE Russian
use shipping_storage

DECLARE @n INT
SET @n = 1

WHILE @n <= 1000
BEGIN
    INSERT INTO shipping_storage.dbo.dim_customer_demography
    (age, marital_status, number_of_children)
    VALUES (20, N'Не женат', 0)
    SET @n = @n + 1
END
GO

------------------
-- dim_customer --
------------------

SET LANGUAGE Russian
use shipping_storage

DECLARE @n INT
SET @n = 1

WHILE @n <= 1000
BEGIN
    INSERT INTO shipping_storage.dbo.dim_customer
    (demography_key, name, email, birthday, gender, phone_number, tax_number, geography_key, entry_date, discount, discount_start, discount_end, flag)
    VALUES (@n, N'Иван Улитин', 'ivan@ulitin.ru', 2019043012, N'Мужской', N'+7900112233', 11223344, 1, 2022043012, 0.2, 2022042512, 2022043012, 1)
    SET @n = @n + 1
END
GO

------------------
-- dim_vehicles --
------------------

SET LANGUAGE Russian
USE shipping_storage

INSERT INTO shipping_storage.dbo.dim_vehicles
(name, tonnage, gas_consumption, on_route, route_start, route_end, flag)
VALUES
(N'Камаз', 100, 100, 0, 2022042512, 2022042612, 0)
GO


-------------------
-- fact_delivery --
-------------------

SET LANGUAGE Russian
USE shipping_storage

DECLARE @startday datetime = '2018-01-01T12:00:00'
DECLARE @endday datetime = '2026-01-01T12:00:00'
DECLARE @inc DATETIME
SET @inc = @startday
DECLARE @keyDate BIGINT

DECLARE @n INT
SET @n = 1

WHILE @inc < @endday
BEGIN
    SET @keyDate = ( SELECT DATEPART(hour, @inc) +
                        DATEPART(day, @inc) * 100 +
                        DATEPART(month, @inc) * 10000 +
                        DATEPART(year, @inc) * 1000000 )

    INSERT INTO shipping_storage.dbo.fact_delivery
    (wrapping_key, payment_kind_key, volume, weight, customer_key, order_date, expected_delivery_date, factual_delivery_date, geography_from_key, geography_to_key, vehicle_key, subtotal, discount, tax_amount, total)
    VALUES
    (1, 1, 10, 10, @n % 1000 + 1, @keyDate, @keyDate, @keyDate, 1, 1, 1, 1000, 0, 200, 1200)

    SET @inc = DATEADD(DAY, 1, @inc)
    SET @n = @n + 1
END
GO

-- DELETE FROM shipping_storage.dbo.archive_fact_delivery