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

export const up: Migration['up'] = async (db) => {
  // Resource classifications table - per DoD Manual 5200.01
  await db.query(`
    CREATE TABLE IF NOT EXISTS resource_classifications (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      resource_type VARCHAR(100) NOT NULL,
      resource_id UUID NOT NULL,
      classification_level VARCHAR(50) NOT NULL DEFAULT 'UNCLASSIFIED'
        CHECK (classification_level IN ('UNCLASSIFIED', 'CUI', 'CONFIDENTIAL', 'SECRET', 'TOP_SECRET')),
      category VARCHAR(100),
      markings JSONB DEFAULT '[]'::jsonb,
      handling_instructions JSONB DEFAULT '[]'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      UNIQUE(resource_type, resource_id)
    )
  `)

  // Security incidents table - per NIST SP 800-53 IR-1 through IR-8
  await db.query(`
    CREATE TABLE IF NOT EXISTS security_incidents (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      category VARCHAR(100) NOT NULL
        CHECK (category IN ('UNAUTHORIZED_ACCESS', 'DATA_BREACH', 'MALWARE', 'DOS', 'INSIDER_THREAT', 'PHISHING', 'SYSTEM_COMPROMISE', 'OTHER')),
      severity VARCHAR(50) NOT NULL
        CHECK (severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),
      status VARCHAR(50) NOT NULL DEFAULT 'DETECTED'
        CHECK (status IN ('DETECTED', 'ANALYZING', 'CONTAINED', 'ERADICATED', 'RECOVERED', 'CLOSED')),
      title VARCHAR(255) NOT NULL,
      description TEXT NOT NULL,
      detected_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      contained_at TIMESTAMP WITH TIME ZONE,
      eradicated_at TIMESTAMP WITH TIME ZONE,
      recovered_at TIMESTAMP WITH TIME ZONE,
      closed_at TIMESTAMP WITH TIME ZONE,
      affected_resources JSONB DEFAULT '[]'::jsonb,
      impact TEXT,
      root_cause TEXT,
      remediation TEXT,
      lessons_learned TEXT,
      notes TEXT,
      reported_to_dod BOOLEAN NOT NULL DEFAULT false,
      dod_report_id VARCHAR(255),
      created_by UUID REFERENCES users(id) ON DELETE SET NULL,
      assigned_to UUID REFERENCES users(id) ON DELETE SET NULL,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Indexes
  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_resource_classifications_level ON resource_classifications(classification_level)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_resource_classifications_resource ON resource_classifications(resource_type, resource_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_security_incidents_status ON security_incidents(status)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_security_incidents_severity ON security_incidents(severity)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_security_incidents_category ON security_incidents(category)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_security_incidents_detected_at ON security_incidents(detected_at DESC)
  `)

  // Update timestamp triggers
  await db.query(`
    CREATE TRIGGER update_resource_classifications_updated_at BEFORE UPDATE ON resource_classifications
      FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)

  await db.query(`
    CREATE TRIGGER update_security_incidents_updated_at BEFORE UPDATE ON security_incidents
      FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)
}

export const down: Migration['down'] = async (db) => {
  await db.query('DROP TABLE IF EXISTS security_incidents CASCADE')
  await db.query('DROP TABLE IF EXISTS resource_classifications CASCADE')
}

