WITH sheets AS (
  SELECT 'gold.population' AS sheet, count(*) AS rows FROM xlsx_gold_standard_v12_b56445cb_4
  UNION ALL SELECT 'gold.tender_level', count(*) FROM xlsx_gold_standard_v12_b56445cb_2
  UNION ALL SELECT 'gold.bid_level', count(*) FROM xlsx_gold_standard_v12_b56445cb_1
  UNION ALL SELECT 'inventory_v3.tender_level', count(*) FROM xlsx_inventory_v3_4b022d33_5
  UNION ALL SELECT 'inventory_v3.bid_level', count(*) FROM xlsx_inventory_v3_4b022d33_4
  UNION ALL SELECT 'inventory.files', count(*) FROM xlsx_inventory_830a48f1_1
)
SELECT * FROM sheets ORDER BY rows DESC;

SELECT
  source_year,
  kibs_text_class,
  gold_standard,
  count(*) AS tenders,
  sum(CASE WHEN lower(coalesce(has_report_bool,'')) IN ('true','1','yes','jā','ja') THEN 1 ELSE 0 END) AS has_report,
  round(avg(try_cast(total_docs AS DOUBLE)), 2) AS avg_docs,
  sum(CASE WHEN lower(coalesce(meat_rich,'')) IN ('true','1','yes','jā','ja') THEN 1 ELSE 0 END) AS meat_rich_n,
  sum(CASE WHEN lower(coalesce(likely_framework,'')) IN ('true','1','yes','jā','ja') THEN 1 ELSE 0 END) AS likely_framework_n
FROM xlsx_gold_standard_v12_b56445cb_4
GROUP BY 1,2,3
ORDER BY try_cast(source_year AS INT), kibs_text_class, gold_standard;

SELECT
  procedure_type,
  count(*) AS tenders,
  round(avg(try_cast(days_to_prepare AS DOUBLE)), 1) AS avg_days_to_prepare,
  median(try_cast(days_to_prepare AS DOUBLE)) AS median_days_to_prepare,
  round(avg(try_cast(total_bids AS DOUBLE)), 2) AS avg_total_bids,
  median(try_cast(total_bids AS DOUBLE)) AS median_total_bids,
  sum(CASE WHEN try_cast(total_bids AS INT)=1 THEN 1 ELSE 0 END) AS single_bid_tenders,
  round(avg(try_cast(award_amount AS DOUBLE)), 0) AS avg_award_amount
FROM xlsx_inventory_v3_4b022d33_5
GROUP BY 1
ORDER BY tenders DESC;

WITH t AS (
  SELECT
    tender_id,
    procedure_type,
    try_cast(days_to_prepare AS DOUBLE) AS days_to_prepare,
    try_cast(total_bids AS DOUBLE) AS total_bids,
    try_cast(award_amount AS DOUBLE) AS award_amount,
    general_agreement
  FROM xlsx_inventory_v3_4b022d33_5
  WHERE try_cast(days_to_prepare AS DOUBLE) IS NOT NULL
    AND try_cast(total_bids AS DOUBLE) IS NOT NULL
)
SELECT
  CASE
    WHEN days_to_prepare < 10 THEN '<10 days'
    WHEN days_to_prepare < 20 THEN '10-19 days'
    WHEN days_to_prepare < 30 THEN '20-29 days'
    ELSE '30+ days'
  END AS prep_window,
  count(*) AS tenders,
  round(avg(total_bids), 2) AS avg_bids,
  median(total_bids) AS median_bids,
  sum(CASE WHEN total_bids=1 THEN 1 ELSE 0 END) AS single_bidder,
  string_agg(tender_id, ', ' ORDER BY tender_id) AS tender_ids
FROM t
GROUP BY 1
ORDER BY min(days_to_prepare);

WITH docs AS (
  SELECT
    procurement_id AS tender_id,
    count(*) AS n_files,
    count(DISTINCT lower(file_type)) AS n_file_types,
    sum(try_cast(bytes AS BIGINT)) AS total_bytes,
    string_agg(DISTINCT lower(file_type), ', ' ORDER BY lower(file_type)) AS file_types
  FROM xlsx_inventory_v3_4b022d33_2
  GROUP BY procurement_id
), tenders AS (
  SELECT
    tender_id,
    procedure_type,
    try_cast(days_to_prepare AS DOUBLE) AS days_to_prepare,
    try_cast(total_bids AS DOUBLE) AS total_bids,
    try_cast(award_amount AS DOUBLE) AS award_amount
  FROM xlsx_inventory_v3_4b022d33_5
)
SELECT
  t.tender_id,
  t.procedure_type,
  t.days_to_prepare,
  t.total_bids,
  d.n_files,
  d.n_file_types,
  round(d.total_bytes / 1000000.0, 2) AS mb_files,
  d.file_types
FROM tenders t
LEFT JOIN docs d USING (tender_id)
ORDER BY d.n_files DESC NULLS LAST, t.total_bids ASC;