ALTER TABLE trust_cache ADD COLUMN status TEXT NOT NULL DEFAULT '';
ALTER TABLE trust_cache ADD COLUMN score REAL;

-- Convert legacy cache values before the JSON column disappears.
UPDATE trust_cache SET status = COALESCE(json_extract(result, '$.status'), ''), score = json_extract(result, '$.score');

INSERT INTO trust_cache_paths(cache_key, position, did)
SELECT c.cache_key, CAST(p.key AS INTEGER), p.value
FROM trust_cache c, json_each(c.result, '$.path') p;

INSERT INTO trust_cache_scores(cache_key, position, did, assertion, source, list_uri, score, comment)
SELECT c.cache_key, CAST(p.key AS INTEGER),
    COALESCE(json_extract(p.value, '$.did'), ''),
    json_extract(p.value, '$.assertion'),
    COALESCE(json_extract(p.value, '$.source'), ''),
    COALESCE(json_extract(p.value, '$.list_uri'), ''),
    COALESCE(json_extract(p.value, '$.score'), 0),
    COALESCE(json_extract(p.value, '$.comment'), '')
FROM trust_cache c, json_each(c.result, '$.path_scores') p;

ALTER TABLE trust_cache DROP COLUMN result;