/*
 * SEOWORK Data Analyst Professional — Module 6 quality lab.
 * PostgreSQL 14+. Synthetic data only. Run in a disposable learning database.
 */

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

CREATE TABLE seowork_lab.raw_customer_events (
    source_row_id integer PRIMARY KEY,
    source_system text,
    customer_ref text,
    event_ts_text text,
    region_text text,
    email_text text,
    amount_text text,
    status_text text,
    loaded_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO seowork_lab.raw_customer_events
    (source_row_id, source_system, customer_ref, event_ts_text, region_text, email_text, amount_text, status_text)
VALUES
    (1,  'crm', ' C-001 ', '2026-01-10 09:15:00+02', 'Київ',   'Olena@example.com ', '1250.00', 'paid'),
    (2,  'crm', 'C-002',   '2026-01-10 09:40:00+02', 'kyiv',   'petro@example.com',  '890,50',  'PAID'),
    (3,  'web', 'C-003',   '2026-01-11T11:05:00Z',   'KYIV',   '',                   '410.00',  'completed'),
    (4,  'web', NULL,      '2026-01-11T12:00:00Z',   'Львів',  'ira@example.com',    '700.00',  'paid'),
    (5,  'crm', 'C-004',   '2026-02-31 08:00:00',    'Lviv',   'bad-email',          '-40.00',  'paid'),
    (6,  'crm', 'C-005',   '2026-01-12 10:30:00+02', 'ЛЬВІВ',  'max@example.com',    'N/A',     'cancelled'),
    (7,  'web', 'C-006',   '2026-01-12T09:00:00Z',   'Одеса',  'anna@example.com',   '199.99',  'paid'),
    (8,  'web', 'C-006',   '2026-01-12T09:00:00Z',   'Одеса',  'anna@example.com',   '199.99',  'paid'),
    (9,  'crm', 'C-007',   NULL,                      'Odesa',  'oleh@example.com',   '320.00',  'paid'),
    (10, 'crm', 'C-008',   '2028-05-01 12:00:00+03', 'Одесса', 'sara@example.com',   '520.00',  'paid'),
    (11, 'web', 'C-009',   '2026-01-13T10:10:00Z',   'Черкаси','nina@example.com',   '9500.00', 'paid'),
    (12, 'web', 'C-010',   '2026-01-13T10:20:00Z',   'Cherkasy','nina2@example.com','75.00',   'unknown'),
    (13, 'crm', 'C-011',   '2026-01-13 14:20:00+02', 'Черкаси','',                   '0',       'cancelled'),
    (14, 'crm', ' c-012',  '2026-01-14 08:20:00+02', 'Київ ',  'roman@example.com',  '125.75',  'paid'),
    (15, 'web', 'C-013',   '2026-01-14T08:00:00Z',   NULL,     'vita@example.com',   '240.00',  'paid');

COMMENT ON TABLE seowork_lab.raw_customer_events IS
    'Synthetic training rows with deliberate missing values, duplicates, invalid formats, inconsistent reference values and anomalies.';

SELECT COUNT(*) AS raw_rows,
       COUNT(customer_ref) AS rows_with_customer_ref,
       COUNT(DISTINCT customer_ref) AS distinct_raw_customer_refs
FROM seowork_lab.raw_customer_events;
