-- Migration 008: Artifact Dependencies
-- Adds support for declaring dependencies between artifacts
-- Part of Package Dependency Management feature (#76)

-- Create artifact_dependencies table
CREATE TABLE IF NOT EXISTS artifact_dependencies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    artifact_id VARCHAR(64) NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
    dependency_project VARCHAR(255) NOT NULL,
    dependency_package VARCHAR(255) NOT NULL,
    version_constraint VARCHAR(255),
    tag_constraint VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    -- Exactly one of version_constraint or tag_constraint must be set
    CONSTRAINT check_constraint_type CHECK (
        (version_constraint IS NOT NULL AND tag_constraint IS NULL) OR
        (version_constraint IS NULL AND tag_constraint IS NOT NULL)
    ),

    -- Each artifact can only have one dependency on a specific project/package
    CONSTRAINT unique_artifact_dependency UNIQUE (artifact_id, dependency_project, dependency_package)
);

-- Index for fast lookups by artifact_id (get all deps for an artifact)
CREATE INDEX IF NOT EXISTS idx_artifact_dependencies_artifact_id
    ON artifact_dependencies(artifact_id);

-- Index for reverse dependency lookups (find what depends on a package)
CREATE INDEX IF NOT EXISTS idx_artifact_dependencies_target
    ON artifact_dependencies(dependency_project, dependency_package);

-- Index for finding dependencies with specific version constraints
CREATE INDEX IF NOT EXISTS idx_artifact_dependencies_version
    ON artifact_dependencies(dependency_project, dependency_package, version_constraint)
    WHERE version_constraint IS NOT NULL;

-- Index for finding dependencies with specific tag constraints
CREATE INDEX IF NOT EXISTS idx_artifact_dependencies_tag
    ON artifact_dependencies(dependency_project, dependency_package, tag_constraint)
    WHERE tag_constraint IS NOT NULL;

COMMENT ON TABLE artifact_dependencies IS 'Stores dependencies declared by artifacts on other packages';
COMMENT ON COLUMN artifact_dependencies.artifact_id IS 'The artifact that declares this dependency';
COMMENT ON COLUMN artifact_dependencies.dependency_project IS 'Project name of the dependency';
COMMENT ON COLUMN artifact_dependencies.dependency_package IS 'Package name of the dependency';
COMMENT ON COLUMN artifact_dependencies.version_constraint IS 'Exact version required (mutually exclusive with tag_constraint)';
COMMENT ON COLUMN artifact_dependencies.tag_constraint IS 'Tag name required (mutually exclusive with version_constraint)';