-- Contracts table (hierarchical - contracts can contain sub-contracts)
CREATE TABLE IF NOT EXISTS contracts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id UUID NOT NULL REFERENCES owners(id) ON DELETE CASCADE,
    parent_contract_id UUID REFERENCES contracts(id) ON DELETE CASCADE,  -- NULL = root contract
    name VARCHAR(255) NOT NULL,
    description TEXT,
    phase VARCHAR(32) NOT NULL DEFAULT 'research',  -- research/specify/plan/execute/review
    status VARCHAR(32) NOT NULL DEFAULT 'active',   -- active/completed/archived
    version INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_contracts_owner_id ON contracts(owner_id);
CREATE INDEX idx_contracts_parent_contract_id ON contracts(parent_contract_id);
CREATE INDEX idx_contracts_status ON contracts(status);
CREATE INDEX idx_contracts_phase ON contracts(phase);

-- Contract repositories (1-to-many: contract has multiple repos)
-- Supports: existing remote (GitHub, etc), existing local, or new repos created by daemon
CREATE TABLE IF NOT EXISTS contract_repositories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    contract_id UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,                     -- display name / repo name for creation
    repository_url VARCHAR(512),                    -- NULL until created (for new 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 task 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

-- status values:
--   'ready'    = repo is usable (existing repos start here)
--   'pending'  = waiting for daemon to create
--   'creating' = daemon is creating the repo
--   'failed'   = creation failed

CREATE INDEX idx_contract_repositories_contract_id ON contract_repositories(contract_id);
CREATE INDEX idx_contract_repositories_status ON contract_repositories(status);
-- Only one primary per contract
CREATE UNIQUE INDEX idx_contract_repositories_primary ON contract_repositories(contract_id) WHERE is_primary = true;

-- Add contract_id to files table (one-to-one)
ALTER TABLE files ADD COLUMN IF NOT EXISTS contract_id UUID REFERENCES contracts(id) ON DELETE SET NULL;
ALTER TABLE files ADD COLUMN IF NOT EXISTS contract_phase VARCHAR(32);
CREATE INDEX IF NOT EXISTS idx_files_contract_id ON files(contract_id);

-- Add contract_id to tasks table (already nullable for backward compat)
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS contract_id UUID REFERENCES contracts(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_tasks_contract_id ON tasks(contract_id);

-- Contract events for audit trail
CREATE TABLE IF NOT EXISTS contract_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    contract_id UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
    event_type VARCHAR(64) NOT NULL,  -- phase_change, file_added, task_added, status_change
    previous_phase VARCHAR(32),
    new_phase VARCHAR(32),
    event_data JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_contract_events_contract_id ON contract_events(contract_id);