-- Deploy guix-build-coordinator:create_unprocessed_builds_with_derived_priorities to sqlite

BEGIN;

CREATE TABLE unprocessed_builds_with_derived_priorities (
  build_id INTEGER PRIMARY KEY REFERENCES builds (id),
  derived_priority INTEGER NOT NULL,
  all_inputs_built BOOLEAN NOT NULL
);

INSERT INTO unprocessed_builds_with_derived_priorities WITH RECURSIVE unprocessed_builds AS (
  SELECT id
  FROM builds
  WHERE processed = 0
    AND canceled = 0
    AND (
      deferred_until IS NULL
      OR
      deferred_until < datetime('now')
    )
), builds_with_derived_priority(
                 id, derivation_id, derived_priority
               ) AS (
  SELECT builds.id, builds.derivation_id, builds.priority
  FROM builds
  INNER JOIN unprocessed_builds
    ON builds.id = unprocessed_builds.id
UNION
  SELECT builds.id, builds.derivation_id,
         max(builds.priority, builds_with_derived_priority.derived_priority)
  FROM builds_with_derived_priority
  INNER JOIN derivation_outputs
    ON builds_with_derived_priority.derivation_id =
       derivation_outputs.derivation_id
  INNER JOIN derivation_outputs AS all_derivation_outputs
    ON all_derivation_outputs.output_id = derivation_outputs.output_id
  INNER JOIN derivation_inputs
    ON derivation_inputs.derivation_output_id = all_derivation_outputs.id
  INNER JOIN builds
    ON builds.processed = 0
   AND builds.derivation_id = derivation_inputs.derivation_id
), data AS (
  SELECT builds.id AS build_id, MAX(derived_priority)
  FROM builds_with_derived_priority
  INNER JOIN builds
    ON builds.id = builds_with_derived_priority.id
  WHERE builds.processed = 0
  GROUP BY builds.id
)
SELECT data.*,
       NOT EXISTS (
         SELECT 1
         FROM derivation_inputs
         INNER JOIN derivation_outputs
           ON derivation_inputs.derivation_output_id = derivation_outputs.id
         INNER JOIN unbuilt_outputs
           ON unbuilt_outputs.output_id = derivation_outputs.output_id
         WHERE builds.derivation_id = derivation_inputs.derivation_id
       )
FROM data
INNER JOIN builds ON builds.id = data.build_id;

CREATE INDEX unprocessed_builds_with_derived_priorities_derived_priorities_index_for_all_inputs_built
  ON unprocessed_builds_with_derived_priorities (derived_priority)
  WHERE all_inputs_built = 1;

COMMIT;