CREATE SCHEMA test_schema;

CREATE TABLE test_schema.dim_customer (
    customer_id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age SMALLINT NOT NULL,
    country VARCHAR(50) NOT NULL
)
DISTSTYLE ALL
SORTKEY (customer_id);

CREATE TABLE test_schema.dim_customer_incremental (
    customer_id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age SMALLINT NOT NULL,
    country VARCHAR(50) NOT NULL
)
DISTSTYLE ALL
SORTKEY (customer_id);

CREATE TABLE test_schema.dim_product (
    product_id INT NOT NULL,
    product_name VARCHAR(50) NOT NULL,
    price NUMERIC(12,2) NOT NULL
)
DISTSTYLE ALL
SORTKEY (product_id);

CREATE TABLE test_schema.dim_product_incremental (
    product_id INT NOT NULL,
    product_name VARCHAR(50) NOT NULL,
    price NUMERIC(12,2) NOT NULL
)
DISTSTYLE ALL
SORTKEY (product_id);

CREATE TABLE test_schema.sales (
    tx_id BIGINT NOT NULL,
    tx_date DATETIME NOT NULL,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL
)
DISTKEY (customer_id)
SORTKEY (tx_date, customer_id, product_id);

CREATE TABLE test_schema.sales_incremental (
    tx_id BIGINT NOT NULL,
    tx_date DATETIME NOT NULL,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL
)
DISTKEY (customer_id)
SORTKEY (tx_date, customer_id, product_id);

CREATE TABLE test_schema.sales_report (
 sales_year SMALLINT NOT NULL,
   full_name VARCHAR(101) NOT NULL,
   product_name VARCHAR(50) NOT NULL,
   total_sales NUMERIC(14,2) NOT NULL
)
DISTKEY (full_name)
SORTKEY (sales_year, full_name, product_name);

INSERT INTO test_schema.dim_customer (customer_id, first_name, last_name, age, country) VALUES
(1, 'mark', 'ford', 27, 'uk'),
(2, 'lily', 'lin', 30, 'china'),
(3, 'louise', 'ward', 24, 'us');

INSERT INTO test_schema.dim_customer_incremental (customer_id, first_name, last_name, age, country) VALUES
(4, 'john', 'smith', 25, 'us'),
(5, 'maria', 'lovelace', 23, 'turkey');

INSERT INTO test_schema.dim_product (product_id, product_name, price) VALUES
(1, 'bag', 32.50),
(2, 'shirt', 20.00);

INSERT INTO test_schema.dim_product_incremental (product_id, product_name, price) VALUES
(3, 'shoes', '30.00');

INSERT INTO test_schema.sales (tx_id, tx_date, customer_id, product_id, quantity) VALUES
(1, '2024-04-28', 3, 1, 3),
(2, '2024-04-28', 1, 2, 2),
(3, '2024-04-29', 2, 2, 3),
(4, '2024-04-29', 1, 1, 2),
(5, '2024-05-01', 3, 2, 1),
(6, '2024-05-01', 2, 2, 3);

INSERT INTO test_schema.sales_incremental (tx_id, tx_date, customer_id, product_id, quantity) VALUES
(7, '2024-05-02', 3, 1, 1),
(8, '2024-05-02', 1, 3, 2),
(9, '2024-05-02', 4, 3, 2),
(10, '2024-05-03', 5, 2, 2);

CREATE OR REPLACE PROCEDURE test_schema.load_customer_incremental(status OUT varchar)
 LANGUAGE PLPGSQL
AS $$
BEGIN

 INSERT INTO test_schema.dim_customer (
        customer_id,
        first_name,
        last_name,
        age,
        country
    )
    SELECT  a.customer_id,
            a.first_name,
            a.last_name,
            a.age,
            a.country
    FROM test_schema.dim_customer_incremental a
    LEFT JOIN test_schema.dim_customer b
    ON a.customer_id = b.customer_id
    WHERE b.customer_id IS NULL
    ORDER BY a.customer_id;

    status = 'customer data loaded successfully';

COMMIT;
END;
$$;

CREATE OR REPLACE PROCEDURE test_schema.load_product_incremental(status OUT varchar)
 LANGUAGE PLPGSQL
AS $$
BEGIN
    INSERT INTO test_schema.dim_product (
        product_id,
        product_name,
        price
    )
    SELECT  a.product_id,
            a.product_name,
            a.price
    FROM test_schema.dim_product_incremental a
    LEFT JOIN test_schema.dim_product b
    ON a.product_id = b.product_id
    WHERE b.product_id IS NULL
    ORDER BY a.product_id;

    status = 'product data loaded successfully';

COMMIT;
END;
$$;

CREATE OR REPLACE PROCEDURE test_schema.load_sales_incremental(status OUT varchar)
 LANGUAGE PLPGSQL
AS $$
BEGIN
    INSERT INTO test_schema.sales (
        tx_id,
        tx_date,
        customer_id,
        product_id,
        quantity
    )
    SELECT  a.tx_id,
            a.tx_date,
            a.customer_id,
            a.product_id,
            a.quantity
    FROM test_schema.sales_incremental a
    LEFT JOIN test_schema.sales b
    ON a.tx_id = b.tx_id
    WHERE b.tx_id IS NULL
    ORDER BY a.tx_id;

    status = 'sales data loaded successfully';

COMMIT;
END;
$$;

CREATE OR REPLACE PROCEDURE test_schema.generate_sales_report(status OUT varchar)
 LANGUAGE PLPGSQL
AS $$
BEGIN
    TRUNCATE TABLE test_schema.sales_report;

    INSERT INTO test_schema.sales_report(
        sales_year,
        full_name,
        product_name,
        total_sales
    )
    SELECT DATE_PART(YEAR, a.tx_date) AS "sales_year",
            b.first_name + ' ' + b.last_name AS "full_name",
            c.product_name,
            SUM(c.price * a.quantity) AS "total_sales"
    FROM test_schema.sales a
    INNER JOIN test_schema.dim_customer b
    ON a.customer_id = b.customer_id
    INNER JOIN test_schema.dim_product c
    ON a.product_id = c.product_id
    GROUP BY DATE_PART(YEAR, a.tx_date),
                b.first_name + ' ' + b.last_name,
                c.product_name
    ORDER BY DATE_PART(YEAR, a.tx_date),
                b.first_name + ' ' + b.last_name,
                c.product_name;

    status = 'sales data loaded successfully';

COMMIT;
END;