PGPASSWORD='$PG_USER_POSTGRES' psql -h 127.0.0.1 -p 5433 -U postgres -d api -c "\COPY (SELECT id, cart_id, payment_order_id, info::jsonb, created_at FROM s_donations.receipts) TO 'receipts.csv' WITH CSV HEADER"

CREATE TABLE IF NOT EXISTS temp_receipts (
    id BIGINT,
    cart_id BIGINT,
    payment_order_id BIGINT,
    info JSONB,
    created_at TIMESTAMPTZ
);

PGPASSWORD='$PG_USER_POSTGRES' psql -h 127.0.0.1 -p 5433 -U postgres -d strapi -h 127.0.0.1 -p 5433 -c "\COPY temp_receipts FROM 'receipts.csv' WITH CSV HEADER"

WITH new_donations AS (
    -- Step 1: Insert into donations without row number, and then assign row numbers in the next step
    INSERT INTO donations (document_id, receipt_id, total_amount, donated_at, donation_type, created_at, updated_at)
    SELECT
        r.id,
        r.id,
        (r.info->>'total_amount')::decimal,
        r.created_at - interval '4 hours',
        r.info->>'donation_type',
        now(), now()
    FROM temp_receipts r
    RETURNING id AS donation_id, receipt_id
),
new_cmp AS (
    -- Step 2: Insert into components_fields_project_donations without row number, and assign row numbers in the next step
    INSERT INTO components_fields_project_donations (project_id, project_name, amount)
    SELECT
        (pd->>'project_id')::text,  
        left((pd->'project_name'->>'ar')::text, 255),
        (pd->>'amount')::decimal
    FROM temp_receipts r
    CROSS JOIN LATERAL jsonb_array_elements(r.info->'project_donations') AS pd
    RETURNING id AS cmp_id, project_id
),
ranked_donations AS (
    -- Step 3: Assign row numbers to donations
    SELECT donation_id, ROW_NUMBER() OVER () AS rn FROM new_donations
),
ranked_cmp AS (
    -- Step 4: Assign row numbers to components_fields_project_donations
    SELECT cmp_id, ROW_NUMBER() OVER () AS rn FROM new_cmp
)
-- Step 5: Insert into donations_cmps using the row numbers to match donations with components
INSERT INTO donations_cmps (entity_id, cmp_id, component_type, field)
SELECT
    d.donation_id,  -- donation_id from ranked_donations
    p.cmp_id,       -- cmp_id from ranked_cmp
    'fields.project-donation',
    'projects'
FROM ranked_donations d
JOIN ranked_cmp p ON d.rn = p.rn;  -- Match by row number order