#!/bin/awk

BEGIN{
  print "-- PostgreSQL-Statements generated by awk"

  print "";

  print "CREATE TABLE IF NOT EXISTS credit_master ("
  print "  lfdnr       INTEGER         PRIMARY KEY,";
  print "  kreditbe    NUMERIC(11,2)   NOT NULL,";
  print "  rsv         BOOLEAN         NOT NULL,";
  print "  geschlec    BOOLEAN         NOT NULL,";
  print "  age         NUMERIC(2)      NOT NULL,";
  print "  telefon     BOOLEAN         NOT NULL,";
  print "  kinderza    NUMERIC(2)      NOT NULL,";
  print "  anschrif    BOOLEAN         NOT NULL,";
  print "  buerge      BOOLEAN         NOT NULL,";
  print "  arbeitsd    NUMERIC(2)      NOT NULL,";
  print "  einkomme    NUMERIC(7,2)    NOT NULL,";
  print "  verfeink    NUMERIC(7,2)    NOT NULL,";
  print "  miete       NUMERIC(7,2)    NOT NULL,";
  print "  pkw         BOOLEAN         NOT NULL,";
  print "  vertraeg    BOOLEAN         NOT NULL,";
  print "  kredit      BOOLEAN         NOT NULL,";
  print "  ausgaben    NUMERIC(7,2)    NOT NULL,";
  print "  kunde       BOOLEAN         NOT NULL,";
  print "  dauerkun    INTEGER         NOT NULL,";
  print "  sparguth    BOOLEAN         NOT NULL,";
  print "  wertpapi    BOOLEAN         NOT NULL,";
  print "  rueckzah    BOOLEAN         NOT NULL";
  print ");";

  print "";

  print "INSERT INTO credit_master (";
  print "  lfdnr,";
  print "  kreditbe,";
  print "  rsv,";
  print "  geschlec,";
  print "  age,";
  print "  telefon,";
  print "  kinderza,";
  print "  anschrif,";
  print "  buerge,";
  print "  arbeitsd,";
  print "  einkomme,";
  print "  verfeink,";
  print "  miete,";
  print "  pkw,";
  print "  vertraeg,";
  print "  kredit,";
  print "  ausgaben,";
  print "  kunde,";
  print "  dauerkun,";
  print "  sparguth,";
  print "  wertpapi,";
  print "  rueckzah";
  print ") VALUES";
}

FNR > 1 {
  $4 == "ja" ? $4 = "'1'" : $4 = "'0'";       # RSV
  $5 == "männlich" ? $5 = "'1'" : $5 = "'0'"; # geschlec
  $7 == "ja" ? $7 = "'1'" : $7 = "'0'";       # telefon
  $9 == "ja" ? $9 = "'1'" : $9 = "'0'";       # anschrif
  $10 == "ja" ? $10 = "'1'" : $10 = "'0'";    # buerge
  $15 == "ja" ? $15 = "'1'" : $15 = "'0'";    # pkw
  $16 == "ja" ? $16 = "'1'" : $16 = "'0'";    # vertrag
  $17 == "ja" ? $17 = "'1'" : $17 = "'0'";    # kredit
  $19 == "ja" ? $19 = "'1'" : $19 = "'0'";    # kunde
  $21 == "ja" ? $21 = "'1'" : $21 = "'0'";    # sparguth
  $22 == "ja" ? $22 = "'1'" : $22 = "'0'";    # wertpapi
  $23 == "ja" ? $23 = "'1'" : $23 = "'0'";    # rueckzah

  printf "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s),\n", $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23;
}