/*
 * SEOWORK Data Analyst Professional — Module 7 statistics lab.
 * PostgreSQL 14+. Synthetic deterministic observations only.
 * Run in a disposable learning database.
 */

CREATE SCHEMA IF NOT EXISTS seowork_lab;
DROP TABLE IF EXISTS seowork_lab.stat_observations;

CREATE TABLE seowork_lab.stat_observations (
    observation_id integer PRIMARY KEY,
    observed_date date NOT NULL,
    segment text NOT NULL,
    channel text NOT NULL,
    variant text NOT NULL,
    load_time_ms integer NOT NULL,
    converted boolean NOT NULL,
    order_value numeric(12,2) NOT NULL,
    satisfaction smallint NOT NULL CHECK (satisfaction BETWEEN 1 AND 5),
    simple_sample boolean NOT NULL
);

WITH base AS (
    SELECT n,
           DATE '2026-01-01' + ((n * 7) % 90) AS observed_date,
           CASE WHEN n % 5 < 3 THEN 'new' ELSE 'returning' END AS segment,
           (ARRAY['organic','paid_search','partner'])[(n % 3) + 1] AS channel,
           CASE WHEN n % 2 = 0 THEN 'B' ELSE 'A' END AS variant,
           450 + ((n * 73) % 1800)
             + CASE WHEN n % 5 < 3 THEN 280 ELSE 0 END
             + CASE WHEN n % 97 = 0 THEN 5000 ELSE 0 END AS load_time_ms,
           (n * 37) % 100 AS score
    FROM generate_series(1, 600) AS g(n)
), classified AS (
    SELECT *,
           score < (
               17
               + CASE WHEN segment = 'returning' THEN 13 ELSE 0 END
               + CASE WHEN variant = 'B' THEN 4 ELSE 0 END
               - CASE WHEN load_time_ms > 1800 THEN 7 ELSE 0 END
           ) AS converted
    FROM base
)
INSERT INTO seowork_lab.stat_observations
    (observation_id, observed_date, segment, channel, variant, load_time_ms, converted, order_value, satisfaction, simple_sample)
SELECT n,
       observed_date,
       segment,
       channel,
       variant,
       load_time_ms,
       converted,
       CASE WHEN converted
            THEN (55 + ((n * 41) % 650) + CASE WHEN segment = 'returning' THEN 220 ELSE 0 END)::numeric(12,2)
            ELSE 0::numeric(12,2)
       END,
       GREATEST(1, LEAST(5,
           5 - (load_time_ms / 800)
             + CASE WHEN converted THEN 1 ELSE 0 END
       ))::smallint,
       ((n * 47) % 20) = 0
FROM classified;

ANALYZE seowork_lab.stat_observations;

SELECT COUNT(*) AS observations,
       AVG(load_time_ms)::numeric(12,2) AS mean_load_ms,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY load_time_ms) AS median_load_ms,
       AVG(converted::int)::numeric(8,4) AS conversion_rate,
       COUNT(*) FILTER (WHERE simple_sample) AS sample_size
FROM seowork_lab.stat_observations;
