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

export const up: Migration['up'] = async (db) => {
  // Policies table
  await db.query(`
    CREATE TABLE IF NOT EXISTS policies (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      name VARCHAR(255) NOT NULL,
      description TEXT,
      policy_type VARCHAR(100) NOT NULL CHECK (policy_type IN ('TAGGING', 'COMPLIANCE', 'SECURITY', 'COST_OPTIMIZATION')),
      enabled BOOLEAN NOT NULL DEFAULT true,
      severity VARCHAR(50) NOT NULL DEFAULT 'MEDIUM' CHECK (severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),
      rule JSONB NOT NULL,
      scope JSONB DEFAULT '{}'::jsonb,
      metadata JSONB DEFAULT '{}'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Policy evaluations table
  await db.query(`
    CREATE TABLE IF NOT EXISTS policy_evaluations (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      policy_id UUID NOT NULL REFERENCES policies(id) ON DELETE CASCADE,
      resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
      status VARCHAR(50) NOT NULL CHECK (status IN ('COMPLIANT', 'NON_COMPLIANT', 'ERROR')),
      findings JSONB DEFAULT '[]'::jsonb,
      evaluated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      UNIQUE(policy_id, resource_id)
    )
  `)

  // Policy violations table
  await db.query(`
    CREATE TABLE IF NOT EXISTS policy_violations (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      policy_id UUID NOT NULL REFERENCES policies(id) ON DELETE CASCADE,
      resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
      severity VARCHAR(50) NOT NULL,
      message TEXT NOT NULL,
      remediation TEXT,
      status VARCHAR(50) NOT NULL DEFAULT 'OPEN' CHECK (status IN ('OPEN', 'ACKNOWLEDGED', 'RESOLVED', 'SUPPRESSED')),
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      resolved_at TIMESTAMP WITH TIME ZONE,
      resolved_by UUID REFERENCES users(id)
    )
  `)

  // Indexes for policies
  await db.query(`CREATE INDEX IF NOT EXISTS idx_policies_type ON policies(policy_type)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_policies_enabled ON policies(enabled)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_policy_evaluations_policy ON policy_evaluations(policy_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_policy_evaluations_resource ON policy_evaluations(resource_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_policy_evaluations_status ON policy_evaluations(status)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_policy_violations_policy ON policy_violations(policy_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_policy_violations_resource ON policy_violations(resource_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_policy_violations_status ON policy_violations(status)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_policy_violations_severity ON policy_violations(severity)`)

  // Trigger for policies updated_at
  await db.query(`
    CREATE TRIGGER update_policies_updated_at BEFORE UPDATE ON policies
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)
}

export const down: Migration['down'] = async (db) => {
  await db.query(`DROP TRIGGER IF EXISTS update_policies_updated_at ON policies`)
  await db.query(`DROP INDEX IF EXISTS idx_policy_violations_severity`)
  await db.query(`DROP INDEX IF EXISTS idx_policy_violations_status`)
  await db.query(`DROP INDEX IF EXISTS idx_policy_violations_resource`)
  await db.query(`DROP INDEX IF EXISTS idx_policy_violations_policy`)
  await db.query(`DROP INDEX IF EXISTS idx_policy_evaluations_status`)
  await db.query(`DROP INDEX IF EXISTS idx_policy_evaluations_resource`)
  await db.query(`DROP INDEX IF EXISTS idx_policy_evaluations_policy`)
  await db.query(`DROP INDEX IF EXISTS idx_policies_enabled`)
  await db.query(`DROP INDEX IF EXISTS idx_policies_type`)
  await db.query(`DROP TABLE IF EXISTS policy_violations`)
  await db.query(`DROP TABLE IF EXISTS policy_evaluations`)
  await db.query(`DROP TABLE IF EXISTS policies`)
}

