/*
 * SEOWORK Data Analyst Professional — Project 1 lab dataset.
 * PostgreSQL 14+. Run only in a disposable learning database/schema.
 * The script creates and replaces the isolated schema seowork_lab.
 */

DROP SCHEMA IF EXISTS seowork_lab CASCADE;
CREATE SCHEMA seowork_lab;

CREATE TABLE customers (
    customer_id integer PRIMARY KEY,
    registered_at date NOT NULL,
    region text NOT NULL,
    segment text NOT NULL
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(customer_id),
    ordered_at timestamp NOT NULL,
    channel text NOT NULL,
    status text NOT NULL,
    net_amount numeric(12,2) NOT NULL CHECK (net_amount >= 0)
);

CREATE TABLE payments (
    payment_id integer PRIMARY KEY,
    order_id integer NOT NULL REFERENCES orders(order_id),
    paid_at timestamp NOT NULL,
    amount numeric(12,2) NOT NULL CHECK (amount >= 0),
    payment_status text NOT NULL
);

INSERT INTO customers (customer_id, registered_at, region, segment)
SELECT n,
       DATE '2024-01-01' + ((n * 17) % 620),
       (ARRAY['north','center','south','west'])[(n % 4) + 1],
       (ARRAY['micro','small','medium'])[(n % 3) + 1]
FROM generate_series(1, 240) AS g(n);

INSERT INTO orders (order_id, customer_id, ordered_at, channel, status, net_amount)
SELECT n,
       ((n * 37) % 240) + 1,
       TIMESTAMP '2025-01-01 09:00:00' + ((n * 19) % 420) * INTERVAL '1 day' + (n % 9) * INTERVAL '1 hour',
       (ARRAY['organic','paid_search','social','partner'])[(n % 4) + 1],
       CASE WHEN n % 13 = 0 THEN 'cancelled' WHEN n % 17 = 0 THEN 'refunded' ELSE 'completed' END,
       (45 + ((n * 83) % 1900))::numeric(12,2)
FROM generate_series(1, 1800) AS g(n);

INSERT INTO payments (payment_id, order_id, paid_at, amount, payment_status)
SELECT n,
       n,
       o.ordered_at + INTERVAL '2 hours',
       CASE WHEN o.status = 'refunded' THEN 0 ELSE o.net_amount END,
       CASE WHEN o.status = 'cancelled' THEN 'failed' WHEN o.status = 'refunded' THEN 'refunded' ELSE 'paid' END
FROM orders AS o
JOIN generate_series(1, 1800) AS g(n) ON g.n = o.order_id;

ANALYZE seowork_lab.customers;
ANALYZE seowork_lab.orders;
ANALYZE seowork_lab.payments;

-- Control values for reconciliation (do not hard-code them in the analysis).
SELECT COUNT(*) AS customers FROM customers;
SELECT COUNT(*) AS orders, COUNT(DISTINCT order_id) AS unique_orders FROM orders;
SELECT COUNT(*) AS payments, SUM(amount) FILTER (WHERE payment_status = 'paid') AS paid_amount FROM payments;
