-- Chain repositories - allow chains to have multiple repositories
-- Similar to contract_repositories but for chains
CREATE TABLE IF NOT EXISTS chain_repositories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    chain_id UUID NOT NULL REFERENCES chains(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,                     -- display name / repo name
    repository_url VARCHAR(512),                    -- NULL for local repos
    local_path VARCHAR(512),                        -- local filesystem path (for local repos)
    source_type VARCHAR(32) NOT NULL DEFAULT 'remote', -- remote/local/managed
    status VARCHAR(32) NOT NULL DEFAULT 'ready',    -- ready/pending/creating/failed
    is_primary BOOLEAN NOT NULL DEFAULT false,      -- primary repo for contract defaults
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- source_type values:
--   'remote'  = existing remote repo (GitHub, GitLab, etc) - has repository_url
--   'local'   = existing local repo - has local_path
--   'managed' = new repo created/managed by Makima daemon - gets repository_url after creation

CREATE INDEX idx_chain_repositories_chain_id ON chain_repositories(chain_id);
-- Only one primary per chain
CREATE UNIQUE INDEX idx_chain_repositories_primary ON chain_repositories(chain_id) WHERE is_primary = true;

-- Remove the old single repository fields from chains table (they're now in chain_repositories)
ALTER TABLE chains DROP COLUMN IF EXISTS repository_url;
ALTER TABLE chains DROP COLUMN IF EXISTS local_path;