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

export const up: Migration['up'] = async (db) => {
  // Regions table
  await db.query(`
    CREATE TABLE IF NOT EXISTS regions (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      name VARCHAR(255) NOT NULL,
      code VARCHAR(50) UNIQUE NOT NULL,
      country VARCHAR(100),
      latitude DECIMAL(10, 8),
      longitude DECIMAL(11, 8),
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      metadata JSONB
    )
  `)

  // Update sites to reference regions
  await db.query(`
    ALTER TABLE sites 
    ADD COLUMN IF NOT EXISTS region_id UUID REFERENCES regions(id) ON DELETE SET NULL
  `)

  // Well-Architected Framework Pillars
  await db.query(`
    CREATE TABLE IF NOT EXISTS pillars (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      code VARCHAR(50) UNIQUE NOT NULL,
      name VARCHAR(255) NOT NULL,
      description TEXT,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Controls
  await db.query(`
    CREATE TABLE IF NOT EXISTS controls (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      pillar_id UUID NOT NULL REFERENCES pillars(id) ON DELETE CASCADE,
      code VARCHAR(50) NOT NULL,
      name VARCHAR(255) NOT NULL,
      description TEXT,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      UNIQUE(pillar_id, code)
    )
  `)

  // Findings
  await db.query(`
    CREATE TABLE IF NOT EXISTS findings (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      control_id UUID NOT NULL REFERENCES controls(id) ON DELETE CASCADE,
      resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
      status VARCHAR(50) NOT NULL CHECK (status IN ('PASS', 'FAIL', 'WARNING', 'INFO', 'NOT_APPLICABLE')),
      severity VARCHAR(50) NOT NULL CHECK (severity IN ('CRITICAL', 'HIGH', 'MEDIUM', 'LOW', 'INFO')),
      title VARCHAR(255) NOT NULL,
      description TEXT,
      recommendation TEXT,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Risks
  await db.query(`
    CREATE TABLE IF NOT EXISTS risks (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      resource_id UUID NOT NULL REFERENCES resource_inventory(id) ON DELETE CASCADE,
      pillar_id UUID REFERENCES pillars(id) ON DELETE SET NULL,
      severity VARCHAR(50) NOT NULL CHECK (severity IN ('CRITICAL', 'HIGH', 'MEDIUM', 'LOW', 'INFO')),
      title VARCHAR(255) NOT NULL,
      description TEXT,
      mitigation TEXT,
      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_regions_code ON regions(code)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_sites_region_id ON sites(region_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_findings_resource ON findings(resource_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_findings_control ON findings(control_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_findings_status ON findings(status)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_risks_resource ON risks(resource_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_risks_pillar ON risks(pillar_id)`)

  // Triggers
  await db.query(`
    CREATE TRIGGER update_findings_updated_at BEFORE UPDATE ON findings
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)
  await db.query(`
    CREATE TRIGGER update_risks_updated_at BEFORE UPDATE ON risks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)
}

export const down: Migration['down'] = async (db) => {
  await db.query(`DROP TRIGGER IF EXISTS update_risks_updated_at ON risks`)
  await db.query(`DROP TRIGGER IF EXISTS update_findings_updated_at ON findings`)
  await db.query(`DROP INDEX IF EXISTS idx_risks_pillar`)
  await db.query(`DROP INDEX IF EXISTS idx_risks_resource`)
  await db.query(`DROP INDEX IF EXISTS idx_findings_status`)
  await db.query(`DROP INDEX IF EXISTS idx_findings_control`)
  await db.query(`DROP INDEX IF EXISTS idx_findings_resource`)
  await db.query(`DROP INDEX IF EXISTS idx_sites_region_id`)
  await db.query(`DROP INDEX IF EXISTS idx_regions_code`)
  await db.query(`DROP TABLE IF EXISTS risks`)
  await db.query(`DROP TABLE IF EXISTS findings`)
  await db.query(`DROP TABLE IF EXISTS controls`)
  await db.query(`DROP TABLE IF EXISTS pillars`)
  await db.query(`ALTER TABLE sites DROP COLUMN IF EXISTS region_id`)
  await db.query(`DROP TABLE IF EXISTS regions`)
}

