ALTER TABLE hosts ADD COLUMN auth_type TEXT NOT NULL DEFAULT 'apikey';
ALTER TABLE hosts ADD COLUMN auth_token TEXT NOT NULL DEFAULT '';

CREATE TABLE managed_resources_v2 (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    batch_id INTEGER NOT NULL,
    host_id INTEGER NOT NULL,
    resource_type TEXT NOT NULL,
    host_resource_id TEXT NOT NULL,
    resource_name TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_managed_resources_batch FOREIGN KEY (batch_id) REFERENCES import_batches(id) ON DELETE CASCADE,
    CONSTRAINT fk_managed_resources_host FOREIGN KEY (host_id) REFERENCES hosts(id) ON DELETE CASCADE,
    CONSTRAINT uq_managed_resources_host_scoped UNIQUE (host_id, resource_type, host_resource_id)
);

INSERT INTO managed_resources_v2 (id, batch_id, host_id, resource_type, host_resource_id, resource_name, created_at)
SELECT mr.id, mr.batch_id, ib.host_id, mr.resource_type, mr.host_resource_id, mr.resource_name, mr.created_at
FROM managed_resources mr
JOIN import_batches ib ON ib.id = mr.batch_id;

DROP TABLE managed_resources;
ALTER TABLE managed_resources_v2 RENAME TO managed_resources;
