-- 02_generate_data.sql
-- Populates demo.ci_job_artifacts_legacy with ~5 million rows (~1 GB of data).
-- Also seeds demo.ci_build_status for the OLTP workload.
--
-- Runtime estimate: 3–6 minutes on the CNPG cluster.
-- Scale: represents 1 GB out of the real 10 TB (1:10000 ratio).
--
-- Data distribution:
--   - Rows span the last 180 days (6 months of history)
--   - 70% of expire_at values are in the past (already expired)
--   - 5% of rows are locked (should be skipped by cleanup)
--   - project_id values spread across 50,000 "projects"
--   - job_id values spread across 1,000,000 "jobs"

\echo 'Generating 5,000,000 rows in demo.ci_job_artifacts_legacy...'
\echo 'This will take 3-6 minutes. Watch progress with: SELECT count(*) FROM demo.ci_job_artifacts_legacy;'

INSERT INTO demo.ci_job_artifacts_legacy
    (job_id, project_id, file_type, size_bytes, file_store,
     checksum, created_at, expire_at, locked)
SELECT
    (random() * 999999 + 1)::BIGINT                             AS job_id,
    (random() * 49999  + 1)::BIGINT                             AS project_id,
    (random() * 9)::SMALLINT                                    AS file_type,
    (random() * 104857600)::BIGINT                              AS size_bytes,  -- up to 100MB per artifact
    1                                                           AS file_store,
    encode(sha256((random()::TEXT || i::TEXT)::BYTEA), 'hex')   AS checksum,
    NOW() - (random() * INTERVAL '180 days')                    AS created_at,
    CASE
        WHEN random() < 0.70
        THEN NOW() - (random() * INTERVAL '90 days')            -- 70% already expired
        ELSE NOW() + (random() * INTERVAL '30 days')            -- 30% not yet expired
    END                                                         AS expire_at,
    random() < 0.05                                             AS locked      -- 5% locked
FROM generate_series(1, 5000000) AS gs(i);

\echo 'Legacy table populated. Running ANALYZE...'
ANALYZE demo.ci_job_artifacts_legacy;

-- Seed the OLTP workload table
\echo 'Seeding OLTP workload table (100,000 rows)...'
INSERT INTO demo.ci_build_status (job_id, status)
SELECT
    (random() * 999999 + 1)::BIGINT,
    CASE (random() * 2)::INT
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'running'
        ELSE 'created'
    END
FROM generate_series(1, 100000);

ANALYZE demo.ci_build_status;

\echo ''
\echo 'Data generation complete. Verify with:'
\echo '  SELECT count(*), pg_size_pretty(sum(pg_column_size(t.*))) FROM demo.ci_job_artifacts_legacy t;'

SELECT
    count(*)                                                                    AS row_count,
    pg_size_pretty(pg_total_relation_size('demo.ci_job_artifacts_legacy'))     AS table_size,
    min(created_at)::DATE                                                       AS earliest_row,
    max(created_at)::DATE                                                       AS latest_row,
    count(*) FILTER (WHERE expire_at < NOW())                                  AS expired_rows,
    count(*) FILTER (WHERE locked = TRUE)                                       AS locked_rows
FROM demo.ci_job_artifacts_legacy;