import { Migration } from '../migrate.js'

export const up: Migration['up'] = async (db) => {
  // Resource relationships table
  await db.query(`
    CREATE TABLE IF NOT EXISTS resource_relationships (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      source_resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
      target_resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
      relationship_type VARCHAR(100) NOT NULL,
      metadata JSONB DEFAULT '{}'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      UNIQUE(source_resource_id, target_resource_id, relationship_type)
    )
  `)

  // Indexes for resource relationships
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resource_relationships_source ON resource_relationships(source_resource_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resource_relationships_target ON resource_relationships(target_resource_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resource_relationships_type ON resource_relationships(relationship_type)`)
}

export const down: Migration['down'] = async (db) => {
  await db.query(`DROP INDEX IF EXISTS idx_resource_relationships_type`)
  await db.query(`DROP INDEX IF EXISTS idx_resource_relationships_target`)
  await db.query(`DROP INDEX IF EXISTS idx_resource_relationships_source`)
  await db.query(`DROP TABLE IF EXISTS resource_relationships`)
}

