-- Task 1: Enterprise Data Cleaning
-- A customer_records table imported from a legacy CRM with classic dirty-data issues:
--   1) Inconsistent gender strings: 'm','Male','MALE','female','F','f','unknown','N/A'
--   2) Broken date formats: 'March 5 1990', '05-03-1990', '1990/03/05', proper ISO 8601
--   3) Missing/null values in required columns (country)
--   4) Duplicate email addresses

CREATE TABLE IF NOT EXISTS customer_records (
    id          INTEGER PRIMARY KEY,
    full_name   TEXT NOT NULL,
    email       TEXT,
    gender      TEXT,
    birth_date  TEXT,
    country     TEXT,
    signup_date TEXT
);

INSERT INTO customer_records VALUES
(1,  'Alice Johnson',   'alice@example.com',   'Female',   '1990-03-05',       'USA',       '2022-01-10'),
(2,  'Bob Smith',       'bob@example.com',     'm',        'March 5 1985',     'UK',        '2022-01-15'),
(3,  'Carol White',     'carol@example.com',   'FEMALE',   '05-07-1992',       'Canada',    '2022-02-01'),
(4,  'Dan Brown',       'dan@example.com',     'male',     '1988/11/23',       'Australia', '2022-02-14'),
(5,  'Eve Davis',       'eve@example.com',     'F',        'January 1, 1995',  'USA',       '2022-03-01'),
(6,  'Frank Miller',    'frank@example.com',   'M',        '1993-06-15',       NULL,        '2022-03-10'),
(7,  'Grace Lee',       'grace@example.com',   'female',   '1991-09-30',       'Germany',   '2022-04-05'),
(8,  'Henry Wilson',    'henry@example.com',   'unknown',  '1987-12-12',       'France',    '2022-04-20'),
(9,  'Iris Clark',      'iris@example.com',    'N/A',      '1996-07-08',       'India',     '2022-05-01'),
(10, 'Jack Taylor',     'jack@example.com',    'MALE',     '15-04-1984',       NULL,        '2022-05-15'),
(11, 'Karen Martinez',  'karen@example.com',   'f',        '1999-02-28',       'Brazil',    '2022-06-01'),
(12, 'Liam Anderson',   'alice@example.com',   'male',     '1997-10-10',       'Mexico',    '2022-06-20'),
(13, 'Mia Thomas',      'mia@example.com',     'Female',   'September 9 2000', 'Japan',     '2022-07-01'),
(14, 'Noah Jackson',    'noah@example.com',    'male',     '1994-03-21',       'USA',       '2022-07-15'),
(15, 'Olivia Harris',   'olivia@example.com',  'FEMALE',   '2001/01/01',       'UK',        '2022-08-01'),
(16, 'Paul Martin',     'paul@example.com',    'M',        '12-12-1989',       NULL,        '2022-08-10'),
(17, 'Quinn Garcia',    'quinn@example.com',   'unknown',  '1992-11-11',       'Spain',     '2022-09-01'),
(18, 'Rose Rodriguez',  'rose@example.com',    'f',        '1990-05-25',       'Italy',     '2022-09-15'),
(19, 'Sam Lewis',       'sam@example.com',     'MALE',     'July 4, 1986',     'Canada',    '2022-10-01'),
(20, 'Tina Walker',     null,                  'Female',   '1998-08-18',       'USA',       '2022-10-20');

-- Ground truth for Task 1 grader (not exposed to agent)
-- DIRTY rows: gender not in ('Male','Female'), date not ISO-8601, country IS NULL, duplicate email
-- Expected transformations:
--   gender -> normalize to 'Male' or 'Female' or 'Unknown'
--   birth_date -> ISO-8601 YYYY-MM-DD
--   country NULL -> 'Unknown'