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

export const up: Migration['up'] = async (db) => {
  // Industry Controls table
  await db.query(`
    CREATE TABLE IF NOT EXISTS industry_controls (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      industry VARCHAR(50) NOT NULL CHECK (industry IN (
        'FINANCIAL', 'TELECOMMUNICATIONS', 'HEALTHCARE',
        'GOVERNMENT', 'MANUFACTURING', 'RETAIL', 'EDUCATION'
      )),
      pillar VARCHAR(50) NOT NULL,
      control_code VARCHAR(100) NOT NULL,
      name VARCHAR(255) NOT NULL,
      description TEXT,
      compliance_frameworks TEXT[] DEFAULT '{}',
      requirements TEXT[] DEFAULT '{}',
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      UNIQUE(industry, pillar, control_code)
    )
  `)

  // WAF Assessments table
  await db.query(`
    CREATE TABLE IF NOT EXISTS waf_assessments (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      resource_id UUID REFERENCES resources(id) ON DELETE CASCADE,
      industry VARCHAR(50) NOT NULL,
      pillar_scores JSONB DEFAULT '{}'::jsonb,
      findings JSONB DEFAULT '[]'::jsonb,
      risks JSONB DEFAULT '[]'::jsonb,
      recommendations JSONB DEFAULT '[]'::jsonb,
      assessed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      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_industry_controls_industry ON industry_controls(industry)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_industry_controls_pillar ON industry_controls(industry, pillar)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_waf_assessments_resource ON waf_assessments(resource_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_waf_assessments_industry ON waf_assessments(industry)
  `)

  // Seed Financial Industry Controls
  await db.query(`
    INSERT INTO industry_controls (industry, pillar, control_code, name, description, compliance_frameworks, requirements)
    VALUES
    ('FINANCIAL', 'SECURITY', 'PCI-DSS-1', 'PCI-DSS Compliance', 'Payment card industry data security', ARRAY['PCI-DSS'], ARRAY['Encrypt cardholder data', 'Restrict access']),
    ('FINANCIAL', 'SECURITY', 'SOX-1', 'SOX Financial Controls', 'Sarbanes-Oxley financial reporting controls', ARRAY['SOX'], ARRAY['Financial audit trail', 'Access controls']),
    ('FINANCIAL', 'RELIABILITY', 'FIN-REL-1', 'Financial System Availability', 'High availability for financial systems', ARRAY[], ARRAY['99.99% uptime', 'Disaster recovery']),
    ('TELECOMMUNICATIONS', 'SECURITY', 'CALEA-1', 'CALEA Compliance', 'Lawful intercept capabilities', ARRAY['CALEA'], ARRAY['Intercept capability', 'Audit logging']),
    ('TELECOMMUNICATIONS', 'RELIABILITY', 'TEL-REL-1', 'Network Availability', 'Telecom network reliability', ARRAY[], ARRAY['99.999% uptime', 'Redundancy'])
    ON CONFLICT (industry, pillar, control_code) DO NOTHING
  `)

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

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

export const down: Migration['down'] = async (db) => {
  await db.query(`DROP TABLE IF EXISTS waf_assessments`)
  await db.query(`DROP TABLE IF EXISTS industry_controls`)
}

