-- Add repository file path linking to files
-- Files can now be linked to specific file paths in repositories

ALTER TABLE files ADD COLUMN repo_file_path VARCHAR(500) NULL;
ALTER TABLE files ADD COLUMN repo_synced_at TIMESTAMPTZ NULL;
ALTER TABLE files ADD COLUMN repo_sync_status VARCHAR(50) DEFAULT 'none';
-- Status: 'none' (not linked), 'synced' (up to date), 'modified' (local changes), 'conflict'

-- Index for efficient lookup of files by repo path within a contract
CREATE INDEX idx_files_repo_file_path ON files(contract_id, repo_file_path)
WHERE repo_file_path IS NOT NULL;

COMMENT ON COLUMN files.repo_file_path IS 'Path to the file in the repository (e.g., README.md, docs/design.md)';
COMMENT ON COLUMN files.repo_synced_at IS 'When the file was last synced from the repository';
COMMENT ON COLUMN files.repo_sync_status IS 'Sync status: none, synced, modified, conflict';