-- SEOWORK Data Analyst Professional — module 8 metrics lab
-- Synthetic deterministic PostgreSQL dataset. It contains no production data.

CREATE SCHEMA IF NOT EXISTS seowork_lab;

DROP TABLE IF EXISTS seowork_lab.metric_daily;
DROP TABLE IF EXISTS seowork_lab.metric_registry;

CREATE TABLE seowork_lab.metric_daily (
    metric_date date NOT NULL,
    channel text NOT NULL CHECK (channel IN ('organic', 'paid', 'partner')),
    device text NOT NULL CHECK (device IN ('mobile', 'desktop')),
    visitors integer NOT NULL CHECK (visitors > 0),
    signups integer NOT NULL CHECK (signups BETWEEN 0 AND visitors),
    activated_7d integer NOT NULL CHECK (activated_7d BETWEEN 0 AND signups),
    paid_30d integer NOT NULL CHECK (paid_30d BETWEEN 0 AND activated_7d),
    retained_30d integer NOT NULL CHECK (retained_30d BETWEEN 0 AND paid_30d),
    revenue_uah numeric(14,2) NOT NULL CHECK (revenue_uah >= 0),
    marketing_spend_uah numeric(14,2) NOT NULL CHECK (marketing_spend_uah >= 0),
    refunds_uah numeric(14,2) NOT NULL CHECK (refunds_uah >= 0),
    support_tickets integer NOT NULL CHECK (support_tickets >= 0),
    p95_response_ms integer NOT NULL CHECK (p95_response_ms > 0),
    PRIMARY KEY (metric_date, channel, device)
);

WITH base AS (
    SELECT
        d::date AS metric_date,
        c.channel,
        v.device,
        (d::date - DATE '2026-01-01')::integer AS day_no,
        CASE c.channel WHEN 'organic' THEN 220 WHEN 'paid' THEN 380 ELSE 120 END AS channel_lift,
        CASE v.device WHEN 'mobile' THEN 170 ELSE 0 END AS device_lift
    FROM generate_series(DATE '2026-01-01', DATE '2026-04-30', INTERVAL '1 day') AS d
    CROSS JOIN (VALUES ('organic'), ('paid'), ('partner')) AS c(channel)
    CROSS JOIN (VALUES ('mobile'), ('desktop')) AS v(device)
), traffic AS (
    SELECT *,
        760 + channel_lift + device_lift + day_no * 3
        + ((day_no * 17 + channel_lift + device_lift) % 91) AS visitors
    FROM base
), funnel AS (
    SELECT *,
        FLOOR(visitors * (
            CASE channel WHEN 'organic' THEN 0.105 WHEN 'paid' THEN 0.083 ELSE 0.126 END
            + CASE device WHEN 'desktop' THEN 0.012 ELSE 0 END
            + CASE WHEN metric_date >= DATE '2026-03-01' THEN 0.009 ELSE 0 END
        ))::integer AS signups
    FROM traffic
), activation AS (
    SELECT *,
        FLOOR(signups * (
            CASE channel WHEN 'organic' THEN 0.66 WHEN 'paid' THEN 0.58 ELSE 0.71 END
            + CASE device WHEN 'desktop' THEN 0.035 ELSE 0 END
        ))::integer AS activated_7d
    FROM funnel
), payment AS (
    SELECT *,
        FLOOR(activated_7d * (
            CASE channel WHEN 'organic' THEN 0.34 WHEN 'paid' THEN 0.29 ELSE 0.39 END
            + CASE WHEN metric_date >= DATE '2026-04-01' THEN 0.025 ELSE 0 END
        ))::integer AS paid_30d
    FROM activation
), retention AS (
    SELECT *,
        FLOOR(paid_30d * (
            CASE channel WHEN 'organic' THEN 0.69 WHEN 'paid' THEN 0.61 ELSE 0.73 END
            - CASE device WHEN 'mobile' THEN 0.025 ELSE 0 END
        ))::integer AS retained_30d
    FROM payment
)
INSERT INTO seowork_lab.metric_daily (
    metric_date, channel, device, visitors, signups, activated_7d, paid_30d,
    retained_30d, revenue_uah, marketing_spend_uah, refunds_uah,
    support_tickets, p95_response_ms
)
SELECT
    metric_date,
    channel,
    device,
    visitors,
    signups,
    activated_7d,
    paid_30d,
    retained_30d,
    ROUND((paid_30d * (1180 + ((day_no * 29) % 420)))::numeric, 2),
    ROUND((CASE channel WHEN 'paid' THEN visitors * 7.80 WHEN 'partner' THEN paid_30d * 155 ELSE visitors * 0.55 END)::numeric, 2),
    ROUND((paid_30d * (CASE WHEN day_no % 13 = 0 THEN 96 ELSE 31 END))::numeric, 2),
    GREATEST(1, FLOOR(paid_30d * (CASE device WHEN 'mobile' THEN 0.17 ELSE 0.11 END)))::integer,
    620 + CASE device WHEN 'mobile' THEN 410 ELSE 130 END + ((day_no * 31) % 380)
FROM retention;

CREATE TABLE seowork_lab.metric_registry (
    metric_key text PRIMARY KEY,
    metric_name text NOT NULL,
    business_question text NOT NULL,
    numerator text NOT NULL,
    denominator text,
    unit text NOT NULL,
    grain text NOT NULL,
    inclusion_rule text NOT NULL,
    exclusion_rule text NOT NULL,
    window_rule text NOT NULL,
    source_table text NOT NULL,
    owner_role text NOT NULL,
    refresh_cadence text NOT NULL,
    target_value numeric(14,4),
    target_direction text CHECK (target_direction IN ('increase', 'decrease', 'range')),
    guardrail_for text,
    version text NOT NULL
);

INSERT INTO seowork_lab.metric_registry VALUES
('signup_rate', 'Signup rate', 'Чи перетворюємо релевантний трафік на реєстрації?', 'SUM(signups)', 'SUM(visitors)', '%', 'date × channel × device', 'Усі tracked visits у межах періоду', 'Внутрішній QA-трафік', 'Calendar period', 'seowork_lab.metric_daily', 'Product analyst', 'daily', 0.1100, 'increase', NULL, '1.0'),
('activation_rate_7d', '7-day activation rate', 'Чи отримує новий користувач першу цінність за 7 днів?', 'SUM(activated_7d)', 'SUM(signups)', '%', 'signup cohort × channel × device', 'Signups із повним 7-day observation window', 'Незавершені cohorts', '7 days from signup', 'seowork_lab.metric_daily', 'Product analyst', 'weekly', 0.6500, 'increase', NULL, '1.0'),
('paid_conversion_30d', '30-day paid conversion', 'Яка частка activated users оплачує протягом 30 днів?', 'SUM(paid_30d)', 'SUM(activated_7d)', '%', 'activation cohort × channel × device', 'Activated users із повним 30-day window', 'Незавершені cohorts', '30 days from activation', 'seowork_lab.metric_daily', 'Revenue analyst', 'weekly', 0.3400, 'increase', NULL, '1.0'),
('cost_per_paid', 'Cost per paid user', 'Скільки marketing spend припадає на paid user?', 'SUM(marketing_spend_uah)', 'SUM(paid_30d)', 'UAH/user', 'period × channel × device', 'Атрибутовані витрати і paid users', 'Непідтверджені invoice rows', 'Same reporting period', 'seowork_lab.metric_daily', 'Marketing analyst', 'weekly', 620.0000, 'decrease', NULL, '1.0'),
('refund_rate', 'Refund rate', 'Чи не зростають повернення разом із revenue?', 'SUM(refunds_uah)', 'SUM(revenue_uah)', '%', 'period × channel × device', 'Підтверджені refunds і gross revenue', 'Cancelled test payments', 'Refund posted in period', 'seowork_lab.metric_daily', 'Finance analyst', 'weekly', 0.0400, 'decrease', 'revenue_uah', '1.0'),
('support_rate', 'Support tickets per paid user', 'Чи не погіршується user burden?', 'SUM(support_tickets)', 'SUM(paid_30d)', 'tickets/user', 'period × channel × device', 'Product-related support tickets', 'Spam and duplicate tickets', 'Ticket created in period', 'seowork_lab.metric_daily', 'Service analyst', 'weekly', 0.1500, 'decrease', 'paid_conversion_30d', '1.0');

COMMENT ON TABLE seowork_lab.metric_daily IS 'Synthetic daily funnel, revenue and guardrail observations for SEOWORK DAP module 8.';
COMMENT ON TABLE seowork_lab.metric_registry IS 'Training metric passports: definitions, owners, windows, targets and guardrails.';
