-- how to add a field
if not exists( select * from #fields where table_name = '$TableName' and Field_Name='InfoKunde'  )
    then ALTER TABLE $TableName ADD COLUMN InfoKunde BOOLEAN DEFAULT FALSE DESCRIPTION 'Info Kunde';
end if;

if not exists( select * from #fields where table_name = '$TableName' and Field_Name='ShopAuftrag'  )
    then ALTER TABLE $TableName ADD COLUMN ShopAuftrag BOOLEAN DEFAULT FALSE DESCRIPTION 'Auftrag aus dem Webshop';
end if;

if not exists( select * from #fields where table_name = '$TableName' and Field_Name='ShopNeuAuftrag'  )
    then ALTER TABLE $TableName ADD COLUMN ShopNeuAuftrag BOOLEAN DEFAULT FALSE DESCRIPTION 'Neuer Auftrag aus dem Webshop';
end if;

if not exists( select * from #fields where table_name = '$TableName' and Field_Name='ShopUpdate'  )
    then ALTER TABLE $TableName ADD COLUMN ShopUpdate BOOLEAN DEFAULT FALSE DESCRIPTION 'Shop Auftrag Update Flag';
end if;

if not exists( select * from #fields where table_name = '$TableName' and Field_Name='ShopFreigabe'  )
    then ALTER TABLE $TableName ADD COLUMN ShopFreigabe BOOLEAN DEFAULT FALSE DESCRIPTION 'Shop Auftrag Freigabe Flag';
end if;

if not exists( select * from #fields where table_name = '$TableName' and Field_Name='ShopStatus'  )
    then ALTER TABLE $TableName ADD COLUMN ShopStatus SHORTSTRING(80) DEFAULT '' DESCRIPTION 'Status des Webshop Auftrages';
end if;

if not exists( select * from #fields where table_name = '$TableName' and Field_Name='ShopImportDatum'  )
    then ALTER TABLE $TableName ADD COLUMN ShopImportDatum DATE DEFAULT NULL DESCRIPTION 'Datum des Imports des Webshop Auftrages';
end if;


if not exists( select * from #fields where table_name = '$TableName' and Field_Name='ShopData'  )
   then ALTER TABLE $TableName ADD COLUMN "ShopData" CLOB DESCRIPTION 'Shop Import Daten';
end if;

if not exists( select * from #fields where table_name = '$TableName' and Field_Name='ShopTrackingNumber'  )
   then ALTER TABLE $TableName ADD COLUMN "ShopTrackingNumber" SHORTSTRING(200) DEFAULT '' DESCRIPTION 'Tracking Nummer';
end if;

if not exists( select * from #fields where table_name = '$TableName' and Field_Name='ShopOrderId'  )
   then ALTER TABLE $TableName ADD COLUMN "ShopOrderId" SHORTSTRING(200) DEFAULT '' DESCRIPTION 'Shop Auftrag ID';
end if;

-- how to drop a field
--if exists( select * from #fields where table_name = '$TableName' and Field_Name='neuerShopArtikel'  )
--   then ALTER TABLE $TableName DROP COLUMN neuerShopArtikel ;
--end if;

