CREATE TABLE sales_data1 (
    sales_date DATE,
    product_id INT,
    month_id INT,
    region_id INT,
    sales INT,
    cost INT,
    profit INT
);
GO

CREATE TABLE sales_data2 (
    sales_date DATE,
    product_id INT,
    month_id INT,
    region_id INT,
    sales INT,
    cost INT,
    profit INT
);
GO

-- sales_data1へのデータの挿入
INSERT INTO sales_data1 (sales_date, product_id, month_id, region_id, sales, cost, profit) VALUES
('2023-01-01',1,1,1,50000,30000,20000),
('2023-01-02',1,1,1,60000,35000,25000),
('2023-01-03',1,1,1,55000,32000,23000),
('2023-01-04',1,1,1,58000,36000,22000),
('2023-01-05',1,1,2,62000,40000,22000),
('2023-01-06',1,1,2,57000,39000,18000),
('2023-01-07',1,1,2,59000,37000,22000),
('2023-01-08',1,1,2,61000,41000,20000),
('2023-01-09',1,1,2,63000,42000,21000),
('2023-01-10',1,1,1,64000,43000,21000),
('2023-01-11',1,1,1,65000,44000,21000),
('2023-01-12',1,1,1,66000,45000,21000);

-- sales_data2へのデータの挿入
INSERT INTO sales_data2 (sales_date, product_id, month_id, region_id, sales, cost, profit) VALUES
('2023-02-01',2,2,1,40000,25000,15000),
('2023-02-02',2,2,1,45000,28000,17000),
('2023-02-03',2,2,2,46000,29000,17000),
('2023-02-04',2,2,2,47000,31000,16000),
('2023-02-05',2,2,2,48000,32000,16000),
('2023-02-06',2,2,1,49000,33000,16000),
('2023-02-07',2,2,1,50000,34000,16000),
('2023-02-08',2,2,1,52000,36000,16000),
('2023-02-09',2,2,1,53000,37000,16000),
('2023-02-10',2,2,2,54000,38000,16000),
('2023-02-11',2,2,2,55000,39000,16000),
('2023-02-12',2,2,2,56000,40000,16000);


CREATE TABLE sales_work (
    sales_date DATE,
    product_id INT,
    month_id INT,
    region_id INT,
    sales INT,
    cost INT,
    profit INT
);
GO

CREATE TABLE sales (
    sales_date DATE,
    product_id INT,
    month_id INT,
    region_id INT,
    sales INT,
    cost INT,
    profit INT
);
GO


CREATE PROCEDURE LoadSalesData
AS
BEGIN
    INSERT INTO sales_work SELECT * FROM sales_data1;
    INSERT INTO sales SELECT * FROM sales_work;
    
    TRUNCATE TABLE sales_work;

    INSERT INTO sales_work SELECT * FROM sales_data2;
    INSERT INTO sales SELECT * FROM sales_work;
END
GO

-- ストアドプロシージャを実行
EXEC LoadSalesData;


-- salesテーブルにデータが入っているか確認するためのやつ
-- SELECT * FROM sales;


-- 時間テーブル作成
CREATE TABLE time_data (
    month_id INT,
    month DATE,
    quarter VARCHAR(2),
    year INT
);
GO

INSERT INTO time_data (month_id, month, quarter, year) VALUES
(1,'2023-01-01','Q1',2023),
(2,'2023-02-01','Q1',2023),
(3,'2023-03-01','Q1',2023),
(4,'2023-04-01','Q2',2023),
(5,'2023-05-01','Q2',2023),
(6,'2023-06-01','Q2',2023),
(7,'2023-07-01','Q3',2023),
(8,'2023-08-01','Q3',2023),
(9,'2023-09-01','Q3',2023),
(10,'2023-10-01','Q4',2023),
(11,'2023-11-01','Q4',2023),
(12,'2023-12-01','Q4',2023);


-- 商品テーブルを作成
CREATE TABLE product_data (
    product_id INT,
    product_name VARCHAR(255),
    category VARCHAR(255)
);
GO

INSERT INTO product_data (product_id, product_name, category) VALUES
(1,'product_A','home appliance'),
(2,'product_B','furniture');

-- 地域テーブルを作成
CREATE TABLE region_data (
    region_id INT,
    region_name VARCHAR(255),
    country VARCHAR(255)
);
GO

INSERT INTO region_data (region_id, region_name, country) VALUES
(1,'Tokyo','Japan'),
(2,'Osaka','Japan');

GO

-- Viewを作成（ディメンションテーブルとファクトテーブルを結合し、必要なデータのみSELECTに含める）
CREATE VIEW sales_view AS
SELECT 
    sales.sales_date, 
    time_data.month, 
    time_data.quarter, 
    time_data.year, 
    product_data.product_name, 
    product_data.category, 
    region_data.region_name, 
    region_data.country,
    sales.sales, 
    sales.cost, 
    sales.profit
FROM
    sales
INNER JOIN time_data
    ON sales.month_id = time_data.month_id
INNER JOIN product_data
    ON sales.product_id = product_data.product_id
INNER JOIN region_data
    ON sales.region_id = region_data.region_id;
GO
-- 最終的な出力
SELECT
    *
FROM
    sales_view