-- SEOWORK Academy · Data Analyst Professional · Module 10
-- PostgreSQL practice model. Safe to rerun in a training database.

DROP SCHEMA IF EXISTS seowork_model_lab CASCADE;
CREATE SCHEMA seowork_model_lab;

CREATE TABLE dim_date (
    date_key integer PRIMARY KEY,
    full_date date NOT NULL UNIQUE,
    year_number smallint NOT NULL,
    quarter_number smallint NOT NULL CHECK (quarter_number BETWEEN 1 AND 4),
    month_number smallint NOT NULL CHECK (month_number BETWEEN 1 AND 12),
    month_name text NOT NULL,
    year_month text NOT NULL,
    day_of_week smallint NOT NULL CHECK (day_of_week BETWEEN 1 AND 7),
    is_weekend boolean NOT NULL
);

INSERT INTO dim_date
SELECT
    to_char(d, 'YYYYMMDD')::integer,
    d,
    extract(year FROM d)::smallint,
    extract(quarter FROM d)::smallint,
    extract(month FROM d)::smallint,
    to_char(d, 'TMMonth'),
    to_char(d, 'YYYY-MM'),
    extract(isodow FROM d)::smallint,
    extract(isodow FROM d) IN (6, 7)
FROM generate_series(date '2025-01-01', date '2026-12-31', interval '1 day') AS g(d);

CREATE TABLE dim_customer (
    customer_key integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id text NOT NULL UNIQUE,
    customer_name text NOT NULL,
    segment text NOT NULL CHECK (segment IN ('SMB', 'Mid-market', 'Enterprise')),
    region text NOT NULL
);

INSERT INTO dim_customer (customer_id, customer_name, segment, region)
SELECT
    'C' || lpad(n::text, 3, '0'),
    'Клієнт ' || n,
    (ARRAY['SMB', 'Mid-market', 'Enterprise'])[1 + (n - 1) % 3],
    (ARRAY['Центр', 'Захід', 'Південь', 'Схід'])[1 + (n - 1) % 4]
FROM generate_series(1, 24) AS g(n);

CREATE TABLE dim_product (
    product_key integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id text NOT NULL UNIQUE,
    product_name text NOT NULL,
    category text NOT NULL,
    standard_cost numeric(12,2) NOT NULL CHECK (standard_cost >= 0)
);

INSERT INTO dim_product (product_id, product_name, category, standard_cost)
SELECT
    'P' || lpad(n::text, 3, '0'),
    'Продукт ' || n,
    (ARRAY['Analytics', 'Automation', 'Support'])[1 + (n - 1) % 3],
    (40 + n * 7.5)::numeric(12,2)
FROM generate_series(1, 12) AS g(n);

CREATE TABLE dim_channel (
    channel_key smallint PRIMARY KEY,
    channel_name text NOT NULL UNIQUE
);

INSERT INTO dim_channel VALUES
    (1, 'Organic'), (2, 'Paid'), (3, 'Referral'), (4, 'Direct');

-- Grain: exactly one row per order line. order_id alone is not unique.
CREATE TABLE fact_sales (
    sales_key bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id text NOT NULL,
    line_number smallint NOT NULL,
    order_date_key integer NOT NULL REFERENCES dim_date(date_key),
    ship_date_key integer REFERENCES dim_date(date_key),
    customer_key integer NOT NULL REFERENCES dim_customer(customer_key),
    product_key integer NOT NULL REFERENCES dim_product(product_key),
    channel_key smallint NOT NULL REFERENCES dim_channel(channel_key),
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
    discount_amount numeric(12,2) NOT NULL CHECK (discount_amount >= 0),
    revenue numeric(14,2) NOT NULL,
    cost_amount numeric(14,2) NOT NULL,
    UNIQUE (order_id, line_number)
);

INSERT INTO fact_sales (
    order_id, line_number, order_date_key, ship_date_key,
    customer_key, product_key, channel_key,
    quantity, unit_price, discount_amount, revenue, cost_amount
)
SELECT
    'O' || lpad(n::text, 5, '0'),
    line_no,
    to_char(order_date, 'YYYYMMDD')::integer,
    to_char(order_date + ((n + line_no) % 6), 'YYYYMMDD')::integer,
    1 + (n - 1) % 24,
    1 + (n + line_no - 2) % 12,
    1 + (n - 1) % 4,
    quantity,
    unit_price,
    discount,
    round(quantity * unit_price - discount, 2),
    round(quantity * (40 + (1 + (n + line_no - 2) % 12) * 7.5), 2)
FROM generate_series(1, 180) AS g(n)
CROSS JOIN LATERAL generate_series(1, 1 + n % 3) AS l(line_no)
CROSS JOIN LATERAL (SELECT date '2025-01-01' + ((n * 3) % 540) AS order_date) AS d
CROSS JOIN LATERAL (SELECT 1 + (n + line_no) % 5 AS quantity) AS q
CROSS JOIN LATERAL (SELECT (90 + (n % 12) * 15)::numeric(12,2) AS unit_price) AS p
CROSS JOIN LATERAL (SELECT CASE WHEN n % 7 = 0 THEN 10::numeric ELSE 0::numeric END AS discount) AS x;

-- Different grain: one row per month and channel. Do not join it to fact_sales.
CREATE TABLE fact_targets (
    month_date_key integer NOT NULL REFERENCES dim_date(date_key),
    channel_key smallint NOT NULL REFERENCES dim_channel(channel_key),
    revenue_target numeric(14,2) NOT NULL CHECK (revenue_target >= 0),
    PRIMARY KEY (month_date_key, channel_key)
);

INSERT INTO fact_targets
SELECT
    to_char(m, 'YYYYMMDD')::integer,
    c.channel_key,
    (9000 + extract(month FROM m) * 350 + c.channel_key * 500)::numeric(14,2)
FROM generate_series(date '2025-01-01', date '2026-12-01', interval '1 month') AS g(m)
CROSS JOIN dim_channel AS c;

-- Quality gates expected by the lesson.
SELECT 'fact_sales_grain_duplicates' AS check_name, count(*) AS failures
FROM (
    SELECT order_id, line_number FROM fact_sales
    GROUP BY order_id, line_number HAVING count(*) > 1
) AS d
UNION ALL
SELECT 'orphan_customer_keys', count(*)
FROM fact_sales f LEFT JOIN dim_customer d USING (customer_key)
WHERE d.customer_key IS NULL
UNION ALL
SELECT 'orphan_product_keys', count(*)
FROM fact_sales f LEFT JOIN dim_product d USING (product_key)
WHERE d.product_key IS NULL
UNION ALL
SELECT 'ship_before_order', count(*)
FROM fact_sales WHERE ship_date_key < order_date_key;

