-- pzmm-conflict-typing migration (Plan: docs/plans/2026-05-04-pzmm-conflict-and-typing.md)
--
-- Adds:
--   - mod_files: per-mod manifest of conflict-eligible asset files (lua/txt/xml/json/ini)
--                with sha1 fingerprint. Used by /api/conflicts to flag rel_paths claimed
--                by ≥2 mods with non-equal content.
--   - mod_parsed.mod_types: ordered tag list from detect_mod_types content fingerprinting
--                (Maps, Vehicles, Weapons, Traits, …). Consumed by derive_category.
--   - mod_parsed.files_manifest_built: graceful-degradation flag. False until the
--                worker has run the single-pass media/ walk that produces both the
--                manifest rows and mod_types. Old cached rows stay false until they
--                organically re-parse.
--
-- Backfill strategy: organic only — time_updated bumps trigger re-parse, which
-- now also runs the manifest walk. /api/conflicts surfaces missing_manifests so
-- the frontend can communicate gaps without lying.

CREATE TABLE IF NOT EXISTS mod_files (
    workshop_id  TEXT NOT NULL,
    mod_id       TEXT NOT NULL,
    rel_path     TEXT NOT NULL,
    sha1         TEXT NOT NULL,
    size_bytes   INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (workshop_id, mod_id, rel_path),
    FOREIGN KEY (workshop_id, mod_id)
        REFERENCES mod_parsed (workshop_id, mod_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS mod_files_rel_path_idx ON mod_files (rel_path);
CREATE INDEX IF NOT EXISTS mod_files_mod_idx ON mod_files (workshop_id, mod_id);

ALTER TABLE mod_parsed
    ADD COLUMN IF NOT EXISTS mod_types            TEXT[]  NOT NULL DEFAULT '{}',
    ADD COLUMN IF NOT EXISTS files_manifest_built BOOLEAN NOT NULL DEFAULT FALSE;