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

export const up: Migration['up'] = async (db) => {
  // Resource Inventory table
  await db.query(`
    CREATE TABLE IF NOT EXISTS resource_inventory (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      resource_type VARCHAR(100) NOT NULL,
      provider VARCHAR(50) NOT NULL CHECK (provider IN ('PROXMOX', 'KUBERNETES', 'CLOUDFLARE', 'CEPH', 'MINIO', 'AWS', 'AZURE', 'GCP')),
      provider_id VARCHAR(255) NOT NULL,
      provider_resource_id VARCHAR(255),
      name VARCHAR(255) NOT NULL,
      region VARCHAR(255),
      site_id UUID REFERENCES sites(id) ON DELETE SET NULL,
      metadata JSONB DEFAULT '{}'::jsonb,
      tags JSONB DEFAULT '[]'::jsonb,
      discovered_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      last_synced_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      UNIQUE(provider, provider_id)
    )
  `)

  // Indexes for resource inventory
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resource_inventory_provider ON resource_inventory(provider)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resource_inventory_resource_type ON resource_inventory(resource_type)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resource_inventory_region ON resource_inventory(region)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resource_inventory_site_id ON resource_inventory(site_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resource_inventory_provider_id ON resource_inventory(provider, provider_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resource_inventory_tags ON resource_inventory USING GIN(tags)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resource_inventory_metadata ON resource_inventory USING GIN(metadata)`)

  // Trigger for resource inventory updated_at
  await db.query(`
    CREATE TRIGGER update_resource_inventory_updated_at BEFORE UPDATE ON resource_inventory
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)
}

export const down: Migration['down'] = async (db) => {
  await db.query(`DROP TRIGGER IF EXISTS update_resource_inventory_updated_at ON resource_inventory`)
  await db.query(`DROP INDEX IF EXISTS idx_resource_inventory_metadata`)
  await db.query(`DROP INDEX IF EXISTS idx_resource_inventory_tags`)
  await db.query(`DROP INDEX IF EXISTS idx_resource_inventory_provider_id`)
  await db.query(`DROP INDEX IF EXISTS idx_resource_inventory_site_id`)
  await db.query(`DROP INDEX IF EXISTS idx_resource_inventory_region`)
  await db.query(`DROP INDEX IF EXISTS idx_resource_inventory_resource_type`)
  await db.query(`DROP INDEX IF EXISTS idx_resource_inventory_provider`)
  await db.query(`DROP TABLE IF EXISTS resource_inventory`)
}

