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

export const up: Migration['up'] = async (db) => {
  // Metrics table for time-series data
  await db.query(`
    CREATE TABLE IF NOT EXISTS metrics (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
      metric_type VARCHAR(50) NOT NULL,
      value DECIMAL(20, 4) NOT NULL,
      timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
      labels JSONB,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Create unique index for time-series queries
  await db.query(`
    CREATE UNIQUE INDEX IF NOT EXISTS idx_metrics_resource_type_time 
    ON metrics(resource_id, metric_type, timestamp DESC)
  `)

  // Additional indexes for querying
  await db.query(`CREATE INDEX IF NOT EXISTS idx_metrics_resource_id ON metrics(resource_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_metrics_metric_type ON metrics(metric_type)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_metrics_timestamp ON metrics(timestamp DESC)`)
}

export const down: Migration['down'] = async (db) => {
  await db.query(`DROP INDEX IF EXISTS idx_metrics_timestamp`)
  await db.query(`DROP INDEX IF EXISTS idx_metrics_metric_type`)
  await db.query(`DROP INDEX IF EXISTS idx_metrics_resource_id`)
  await db.query(`DROP INDEX IF EXISTS idx_metrics_resource_type_time`)
  await db.query(`DROP TABLE IF EXISTS metrics`)
}

