-- Fixture legacy dataset for the transform tests (NOT live data).
-- Schema is column-faithful to 01-current-system/schema.md so the
-- runtime column-disposition check (dispositions.py) exercises the real
-- enumeration. Constraints/indexes are omitted (the landed snapshot has
-- none either — pre-data + data only).
--
-- Scenario coverage (06-test-plan §10, unit-testable T-MIG rows):
--   person-party dedupe ......... rian appears in two teams (one UPPERCASED email)
--   operator/client name merge .. client "PlusROI" under Bowden Works merges into
--                                 the PlusROI operator party
--   direct_client edges ......... one per clients row
--   kind-model terms ............ Gary cost 0 + billout 25; rian row w/ NULL billout
--   inactive member ............. Matt (is_active false -> engagement ended_on)
--   baseline entry .............. te-5, dated 2025-12-31 "Transfer baseline from Toggl…"
--   locked rows ................. te-1 on a real invoice; te-6 on "Legacy transfers"
--   straggler candidate ......... te-7 (tests UPDATE it to transferred-not-invoiced)
--   raw_* carry / Blocked row ... te-4 (unresolved, text-triple identity only)
--   historical sources .......... clockify / backfill / toggl batches + manual entry

DROP SCHEMA IF EXISTS legacy CASCADE;
CREATE SCHEMA legacy;

CREATE TABLE legacy.organizations (
    id uuid, name text, slug text, default_hourly_rate_usd numeric(8,2),
    created_at timestamptz
);
CREATE TABLE legacy.organization_members (
    organization_id uuid, user_id uuid, role text, created_at timestamptz
);
CREATE TABLE legacy.teams (
    id uuid, org_id uuid, owner_user_id uuid, name text,
    base_rate_usd numeric(10,2), created_at timestamptz, updated_at timestamptz
);
CREATE TABLE legacy.team_members (
    id uuid, org_id uuid, team_id uuid, email text, display_name text,
    rate_proportion numeric(6,5), hourly_rate_usd numeric(8,2),
    cost_rate_usd numeric(10,2), billout_rate_usd numeric(10,2),
    consolidate_as text, is_active boolean, notes text,
    created_at timestamptz, updated_at timestamptz
);
CREATE TABLE legacy.clockify_imports (
    id uuid, org_id uuid, imported_by uuid, imported_at timestamptz,
    filename text, name text, row_count integer, source text, notes text
);
CREATE TABLE legacy.time_entries (
    id uuid, org_id uuid, import_id uuid, source_user_name text,
    source_user_email text, operator text, client text, project text,
    description text, billable boolean, start_at timestamp, end_at timestamp,
    duration_seconds integer, source_rate_usd numeric(10,4),
    source_amount_usd numeric(10,4), team_member_id uuid, converted_user text,
    converted_duration_seconds integer, billout_cost_usd numeric(10,4),
    billout_amount_usd numeric(12,2), project_id uuid, transferred_at timestamptz,
    transferred_by uuid, transfer_batch_id uuid, invoice_id uuid,
    invoice_applied_at timestamptz, invoice_applied_by uuid, created_at timestamptz
);
CREATE TABLE legacy.comments (
    id uuid, org_id uuid, author_id uuid, body text, resolved_at timestamptz,
    resolved_by uuid, created_at timestamptz
);
CREATE TABLE legacy.operators (
    id uuid, org_id uuid, name text, notes text,
    created_at timestamptz, updated_at timestamptz
);
CREATE TABLE legacy.clients (
    id uuid, org_id uuid, operator_id uuid, name text, notes text,
    created_at timestamptz, updated_at timestamptz
);
CREATE TABLE legacy.projects (
    id uuid, org_id uuid, client_id uuid, name text, income_usd numeric(12,2),
    notes text, billout_adjustment_pct numeric(7,4),
    billout_adjustment_amount numeric(12,2),
    created_at timestamptz, updated_at timestamptz
);
CREATE TABLE legacy.pending_imports (
    id uuid, org_id uuid, uploaded_by uuid, uploaded_at timestamptz,
    expires_at timestamptz, filename text, batch_name text, notes text,
    source text, date_format text, time_format text, parsed_entries jsonb
);
CREATE TABLE legacy.project_rate_overrides (
    id uuid, org_id uuid, project_id uuid, team_member_id uuid,
    override_rate_usd numeric(10,2), override_pct numeric(7,4), notes text,
    created_at timestamptz, updated_at timestamptz
);
CREATE TABLE legacy.invoices (
    id uuid, org_id uuid, name text, status text, operator_id uuid,
    client_id uuid, manual_total_usd numeric(10,2), notes text, created_by uuid,
    created_at timestamptz, invoice_date date, sent_at timestamptz,
    paid_at timestamptz
);
CREATE TABLE legacy.cc_expense_batches (
    id uuid, org_id uuid, name text, created_by uuid, created_at timestamptz
);
CREATE TABLE legacy.cc_expense_lines (
    id uuid, batch_id uuid, line_index integer, raw_line text, expense_date date,
    description text, amount_usd numeric(12,2), balance_usd numeric(12,2),
    project_id uuid, assignment_description text, category text,
    auto_assigned boolean, created_at timestamptz
);
CREATE TABLE legacy.cc_highlight_keywords (
    id uuid, org_id uuid, keyword text, created_at timestamptz
);
CREATE TABLE legacy.cc_auto_rules (
    id uuid, org_id uuid, keyword text, project_id uuid,
    assignment_description text, category text, created_at timestamptz
);
-- auth.users lands as legacy.users; the sync/scrub paths keep only the
-- identity-mapping columns (id/email/created_at).
CREATE TABLE legacy.users (id uuid, email text, created_at timestamptz);

-- ------------------------------------------------------------------ data

INSERT INTO legacy.organizations VALUES
('01000000-0000-0000-0000-000000000001', 'Bowden Works', 'bowden-works', 14.00,
 '2026-05-20T12:00:00Z');

INSERT INTO legacy.users VALUES
('02000000-0000-0000-0000-000000000001', 'rian@rian.ca',          '2026-05-20T12:00:00Z'),
('02000000-0000-0000-0000-000000000002', 'info@adipramono.com',   '2026-05-20T12:00:00Z'),
('02000000-0000-0000-0000-000000000003', 'ghost@example.com',     '2026-05-20T12:00:00Z');

INSERT INTO legacy.organization_members VALUES
('01000000-0000-0000-0000-000000000001', '02000000-0000-0000-0000-000000000001',
 'owner',   '2026-05-20T12:00:00Z'),
('01000000-0000-0000-0000-000000000001', '02000000-0000-0000-0000-000000000002',
 'manager', '2026-05-20T12:00:00Z'),
('01000000-0000-0000-0000-000000000001', '02000000-0000-0000-0000-000000000003',
 'member',  '2026-05-20T12:00:00Z');

INSERT INTO legacy.teams VALUES
('03000000-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 '02000000-0000-0000-0000-000000000001', 'Rian''s team', 50, '2026-05-23T00:00:00Z',
 '2026-05-23T00:00:00Z'),
('03000000-0000-0000-0000-000000000002', '01000000-0000-0000-0000-000000000001',
 '02000000-0000-0000-0000-000000000002', 'Adi''s team', 35, '2026-05-23T00:00:00Z',
 '2026-05-23T00:00:00Z');

-- rian is in BOTH teams (dedupe by lower(email) — second row uppercased);
-- Gary is the zero-cost billable AI; Matt is inactive w/ consolidate_as.
INSERT INTO legacy.team_members VALUES
('04000000-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 '03000000-0000-0000-0000-000000000001', 'rian@rian.ca', 'Rian Bowden',
 1.0, NULL, 50.00, NULL, NULL, true, NULL,
 '2026-05-21T00:00:00Z', '2026-05-21T00:00:00Z'),
('04000000-0000-0000-0000-000000000002', '01000000-0000-0000-0000-000000000001',
 '03000000-0000-0000-0000-000000000002', 'RIAN@rian.ca', 'Rian Bowden',
 1.0, NULL, 35.00, 75.00, NULL, true, NULL,
 '2026-05-22T00:00:00Z', '2026-05-22T00:00:00Z'),
('04000000-0000-0000-0000-000000000003', '01000000-0000-0000-0000-000000000001',
 '03000000-0000-0000-0000-000000000002', 'info@adipramono.com', 'Adi Pramono',
 0.4, 14.00, 14.00, 35.00, NULL, true, NULL,
 '2026-05-20T12:30:00Z', '2026-05-20T12:30:00Z'),
('04000000-0000-0000-0000-000000000004', '01000000-0000-0000-0000-000000000001',
 '03000000-0000-0000-0000-000000000002', 'gary@rian.ca', 'Gary',
 0.5, NULL, 0.00, 25.00, NULL, true, 'AI worker — zero cost, billable',
 '2026-05-24T00:00:00Z', '2026-05-24T00:00:00Z'),
('04000000-0000-0000-0000-000000000005', '01000000-0000-0000-0000-000000000001',
 '03000000-0000-0000-0000-000000000002', 'matmoncoo@gmail.com', 'Matthew Monti-Cooper',
 0.7, NULL, NULL, NULL, 'Tingang', false, 'left in June',
 '2026-05-23T00:00:00Z', '2026-05-23T00:00:00Z');

INSERT INTO legacy.operators VALUES
('05000000-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 'Bowden Works', NULL, '2026-05-24T00:00:00Z', '2026-05-24T00:00:00Z'),
('05000000-0000-0000-0000-000000000002', '01000000-0000-0000-0000-000000000001',
 'PlusROI', 'partner agency', '2026-05-24T00:00:01Z', '2026-05-24T00:00:01Z');

-- client "PlusROI" under Bowden Works collides with the PlusROI operator
-- name -> MERGES into that party (05 step 2.1).
INSERT INTO legacy.clients VALUES
('06000000-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 '05000000-0000-0000-0000-000000000002', 'Brentwood', NULL,
 '2026-05-24T00:00:02Z', '2026-05-24T00:00:02Z'),
('06000000-0000-0000-0000-000000000002', '01000000-0000-0000-0000-000000000001',
 '05000000-0000-0000-0000-000000000001', 'PlusROI', NULL,
 '2026-05-24T00:00:03Z', '2026-05-24T00:00:03Z'),
('06000000-0000-0000-0000-000000000003', '01000000-0000-0000-0000-000000000001',
 '05000000-0000-0000-0000-000000000001', 'Internal', NULL,
 '2026-05-24T00:00:04Z', '2026-05-24T00:00:04Z');

INSERT INTO legacy.projects VALUES
('07000000-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 '06000000-0000-0000-0000-000000000001', 'Site Rebuild', 12000.00, 'big one',
 10, 250.00, '2026-05-24T00:01:00Z', '2026-05-24T00:01:00Z'),
('07000000-0000-0000-0000-000000000002', '01000000-0000-0000-0000-000000000001',
 '06000000-0000-0000-0000-000000000002', 'Admin', NULL, NULL,
 NULL, NULL, '2026-05-24T00:01:01Z', '2026-05-24T00:01:01Z'),
('07000000-0000-0000-0000-000000000003', '01000000-0000-0000-0000-000000000001',
 '06000000-0000-0000-0000-000000000003', '10 Hour General Support Block', NULL, NULL,
 NULL, NULL, '2026-05-24T00:01:02Z', '2026-05-24T00:01:02Z');

INSERT INTO legacy.project_rate_overrides VALUES
('08000000-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 '07000000-0000-0000-0000-000000000001', '04000000-0000-0000-0000-000000000003',
 20.00, NULL, 'Adi on Site Rebuild', '2026-05-24T00:02:00Z', '2026-05-24T00:02:00Z'),
('08000000-0000-0000-0000-000000000002', '01000000-0000-0000-0000-000000000001',
 '07000000-0000-0000-0000-000000000001', NULL,
 NULL, -20, 'project-wide discount', '2026-05-24T00:02:01Z', '2026-05-24T00:02:01Z');

INSERT INTO legacy.clockify_imports VALUES
('09000000-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 '02000000-0000-0000-0000-000000000002', '2026-03-07T08:00:00Z',
 'clockify-march.csv', 'March week 1', 3, 'clockify', NULL),
('09000000-0000-0000-0000-000000000002', '01000000-0000-0000-0000-000000000001',
 '02000000-0000-0000-0000-000000000001', '2026-01-20T08:00:00Z',
 'Time Sheet _ Time Log _ Hours _ Tingang.id _ Adi.xlsx', 'Initial 2026 migration',
 1, 'backfill', NULL),
('09000000-0000-0000-0000-000000000003', '01000000-0000-0000-0000-000000000001',
 NULL, '2026-02-25T08:00:00Z', 'toggl-feb.csv', 'Feb toggl', 1, 'toggl', NULL);

INSERT INTO legacy.time_entries VALUES
-- te-1: resolved + attached to a real invoice (lock carries via a line)
('0a000000-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 '09000000-0000-0000-0000-000000000001', 'Adi Pramono', 'info@adipramono.com',
 'PlusROI', 'Brentwood', 'Site Rebuild', 'March work A', true,
 '2026-03-01 10:00:00', '2026-03-01 11:00:00', 3600, 12.5000, 12.5000,
 '04000000-0000-0000-0000-000000000003', 'Adi Pramono', 3600, 14.00, 35.00,
 '07000000-0000-0000-0000-000000000001', NULL, NULL, NULL,
 '0b000000-0000-0000-0000-000000000001', '2026-05-25T10:00:00Z',
 '02000000-0000-0000-0000-000000000001', '2026-03-07T08:00:01Z'),
-- te-2: resolved, unlocked
('0a000000-0000-0000-0000-000000000002', '01000000-0000-0000-0000-000000000001',
 '09000000-0000-0000-0000-000000000001', 'Adi Pramono', 'info@adipramono.com',
 'PlusROI', 'Brentwood', 'Site Rebuild', 'March work B', true,
 '2026-03-02 09:00:00', '2026-03-02 09:30:00', 1800, NULL, NULL,
 '04000000-0000-0000-0000-000000000003', 'Adi Pramono', 1800, 7.00, 17.50,
 '07000000-0000-0000-0000-000000000001', NULL, NULL, NULL, NULL, NULL, NULL,
 '2026-03-07T08:00:02Z'),
-- te-3: Gary (cost 0 stamp is real money, not missing)
('0a000000-0000-0000-0000-000000000003', '01000000-0000-0000-0000-000000000001',
 '09000000-0000-0000-0000-000000000001', 'Gary', 'gary@rian.ca',
 'Bowden Works', 'PlusROI', 'Admin', 'automation run', true,
 '2026-04-10 14:00:00', '2026-04-10 16:00:00', 7200, NULL, NULL,
 '04000000-0000-0000-0000-000000000004', 'Gary', 3600, 0.00, 50.00,
 '07000000-0000-0000-0000-000000000002', NULL, NULL, NULL, NULL, NULL, NULL,
 '2026-04-11T08:00:00Z'),
-- te-4: unresolved/Blocked — the raw text triple is its ONLY identity
('0a000000-0000-0000-0000-000000000004', '01000000-0000-0000-0000-000000000001',
 '09000000-0000-0000-0000-000000000001', 'Unknown Person', 'unknown@example.com',
 'PlusROI', NULL, 'Mystery', 'who did this', NULL,
 '2026-03-05 08:00:00', NULL, 900, NULL, NULL,
 NULL, NULL, NULL, NULL, NULL,
 NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2026-03-07T08:00:03Z'),
-- te-5: the load-bearing baseline sentinel (02 §2 prepaid_block)
('0a000000-0000-0000-0000-000000000005', '01000000-0000-0000-0000-000000000001',
 NULL, 'Rian Bowden', 'rian@rian.ca',
 'Bowden Works', 'Internal', '10 Hour General Support Block',
 'Transfer baseline from Toggl, totals as of March 31, 2026', false,
 '2025-12-31 00:00:00', NULL, 36000, NULL, NULL,
 '04000000-0000-0000-0000-000000000002', 'Rian Bowden', 36000, NULL, 100.00,
 '07000000-0000-0000-0000-000000000003', NULL, NULL, NULL, NULL, NULL, NULL,
 '2026-04-01T08:00:00Z'),
-- te-6: legacy-transferred row, already on the 'Legacy transfers' invoice
('0a000000-0000-0000-0000-000000000006', '01000000-0000-0000-0000-000000000001',
 '09000000-0000-0000-0000-000000000002', 'Rian Bowden', 'rian@rian.ca',
 'Bowden Works', 'PlusROI', 'Admin', 'legacy admin work', true,
 '2026-01-15 09:00:00', '2026-01-15 10:00:00', 3600, NULL, NULL,
 '04000000-0000-0000-0000-000000000001', 'Rian Bowden', 3600, 50.00, NULL,
 '07000000-0000-0000-0000-000000000002', '2026-02-01T00:00:00Z',
 '02000000-0000-0000-0000-000000000001', '0f0f0f0f-0000-0000-0000-00000000000f',
 '0b000000-0000-0000-0000-000000000002', '2026-02-01T00:00:00Z',
 '02000000-0000-0000-0000-000000000001', '2026-01-20T08:00:01Z'),
-- te-7: the straggler CANDIDATE (tests UPDATE it to transferred-not-invoiced)
('0a000000-0000-0000-0000-000000000007', '01000000-0000-0000-0000-000000000001',
 '09000000-0000-0000-0000-000000000003', 'Rian Bowden', 'rian@rian.ca',
 'PlusROI', 'Brentwood', 'Site Rebuild', 'feb toggl work', true,
 '2026-02-20 13:00:00', '2026-02-20 14:30:00', 5400, NULL, NULL,
 '04000000-0000-0000-0000-000000000002', 'Rian Bowden', 5400, 52.50, 90.00,
 '07000000-0000-0000-0000-000000000001', NULL, NULL, NULL, NULL, NULL, NULL,
 '2026-02-25T08:00:01Z');

INSERT INTO legacy.invoices VALUES
-- operator chip != Bowden Works -> to_party = PlusROI party
('0b000000-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 'PlusROI 2026-05', 'paid', '05000000-0000-0000-0000-000000000002', NULL,
 5000.00, 'manual override kept', '02000000-0000-0000-0000-000000000001',
 '2026-05-25T09:00:00Z', '2026-05-31', '2026-06-01T00:00:00Z', '2026-06-15T00:00:00Z'),
-- the per-org 'Legacy transfers' sentinel from the 2026-05-25 backfill
('0b000000-0000-0000-0000-000000000002', '01000000-0000-0000-0000-000000000001',
 'Legacy transfers', 'paid', NULL, NULL, NULL,
 'Backfill: rows locked by the old transfer flow', NULL,
 '2026-05-25T00:00:00Z', NULL, NULL, NULL),
-- no operator chip, no entries; client chip -> to_party = Brentwood
('0b000000-0000-0000-0000-000000000003', '01000000-0000-0000-0000-000000000001',
 'March work', 'open', NULL, '06000000-0000-0000-0000-000000000001',
 NULL, NULL, '02000000-0000-0000-0000-000000000002',
 '2026-06-02T00:00:00Z', '2026-05-31', NULL, NULL);

INSERT INTO legacy.pending_imports VALUES
('0e0e0e0e-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 '02000000-0000-0000-0000-000000000002', '2026-07-01T00:00:00Z',
 '2026-07-02T00:00:00Z', 'stale.csv', 'stale batch', NULL, 'clockify',
 'MM/DD/YYYY', 'h:mm A', '[]');

INSERT INTO legacy.cc_expense_batches VALUES
('0c000000-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 'May statement', '02000000-0000-0000-0000-000000000001', '2026-06-01T00:00:00Z');

INSERT INTO legacy.cc_expense_lines VALUES
('0d000000-0000-0000-0000-000000000001', '0c000000-0000-0000-0000-000000000001',
 0, '05/02 ADOBE *CC 418.22', '2026-05-02', 'ADOBE *CC', 418.22, NULL,
 '07000000-0000-0000-0000-000000000001', 'Adobe seats', 'software', false,
 '2026-06-01T00:00:01Z'),
('0d000000-0000-0000-0000-000000000002', '0c000000-0000-0000-0000-000000000001',
 1, '05/10 FIGMA 100.00', '2026-05-10', 'FIGMA', 100.00, NULL,
 '07000000-0000-0000-0000-000000000001', NULL, 'software', true,
 '2026-06-01T00:00:02Z'),
('0d000000-0000-0000-0000-000000000003', '0c000000-0000-0000-0000-000000000001',
 2, '05/12 STAPLES 61.90 1000.00', '2026-05-12', 'STAPLES', 61.90, 1000.00,
 NULL, NULL, NULL, false, '2026-06-01T00:00:03Z');

INSERT INTO legacy.cc_highlight_keywords VALUES
('0e000000-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 'adobe', '2026-06-01T00:00:00Z'),
('0e000000-0000-0000-0000-000000000002', '01000000-0000-0000-0000-000000000001',
 'staples', '2026-06-01T00:00:00Z');

INSERT INTO legacy.cc_auto_rules VALUES
('0f000000-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 'adobe', '07000000-0000-0000-0000-000000000001', 'Adobe seats', 'software',
 '2026-06-01T00:00:00Z');

INSERT INTO legacy.comments VALUES
('11000000-0000-0000-0000-000000000001', '01000000-0000-0000-0000-000000000001',
 '02000000-0000-0000-0000-000000000001', 'remember to re-check May', NULL, NULL,
 '2026-06-03T00:00:00Z'),
-- author is the unmapped ghost profile -> author_id migrates as NULL
('11000000-0000-0000-0000-000000000002', '01000000-0000-0000-0000-000000000001',
 '02000000-0000-0000-0000-000000000003', 'old note', '2026-06-04T00:00:00Z',
 '02000000-0000-0000-0000-000000000002', '2026-06-02T00:00:00Z');
