//////////////////////////////////////////////////////////////////////////////////////////////
// in Datenbankzugriff
//////////////////////////////////////////////////////////////////////////////////////////////
public class Datenbankzugriff extends SQLiteOpenHelper {
    /**
     * @param context Die Bezugsaktivity
     * @param dbname Datenbank Name
     */
    public Datenbankzugriff(Context context, String dbname) {

        super(context, dbname, null, 1);
    }

    /**
     * Wird beim Erzeugen einer neuen DB automatisch aufgerufen
     * Aufruf erfolgt aber erst beim Einfuegen des ersten Datensatzes
     *
     * @param sqLiteDatabase die zu bearbetende Datenbank
     */
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String sql = "CREATE TABLE Tiere ("
                + "ID INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "Tier NTEXT, "
                + "Beine INTEGER);";
        sqLiteDatabase.execSQL(sql);

    }

    // Änderungen einführen beim versionswechsel!
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        Log.v("XXXXX Datenbank",String.format("Upgrade von Version %d nach Version %d",
                oldVersion,newVersion) );

        if ((oldVersion == 1) && (newVersion) ==4 ) {
            String sql ="ALTER TABLE Tiere ADD COLUMN Gattung NTEXT;";
            sqLiteDatabase.execSQL(sql);
        }
    }

    // ---------------------------------------------------
    // EIGENE METHODEN
    // ---------------------------------------------------

    public void ausfuehren(String sql) {
        // einen Link zur Datenbank abfragen, mit dem geschrieben werden kann
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL(sql);
        db.close();
    }

    public ArrayList<String> auslesen(String sql) {
        ArrayList<String> zeilen = new ArrayList<String>();

        SQLiteDatabase db = getReadableDatabase();

        Cursor resultSet = db.rawQuery(sql, null);

        if(resultSet.moveToFirst()) {
            do {
                int id = resultSet.getInt(0);
                String tier = resultSet.getString(1);
                int beine = resultSet.getInt(2);

                String zeile = String.format("Id: %d; Tier: %s; Beine: %d",id,tier,beine);
                zeilen.add(zeile);

            } while (resultSet.moveToNext());
        }
        db.close();

        return zeilen;
    }

}



//////////////////////////////////////////////////////////////////////////////////////////////
// in MainActivity
//////////////////////////////////////////////////////////////////////////////////////////////
public class MainActivity extends AppCompatActivity {

    private Button btnSpeichern, btnAuslesen, btnAendern, btnLoeschen;
    private EditText edTier, edBeine, edID;
    private TextView tv;
    private Datenbankzugriff datenbankzugriff;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        init();
        datenbankzugriff = new Datenbankzugriff(this,"w2t4.sqlite");

    }

    private void init() {
        MyOCL ocl = new MyOCL();

        btnSpeichern = findViewById(R.id.btnSpeichern);
        btnSpeichern.setOnClickListener(ocl);

        btnAuslesen = findViewById(R.id.btnAuslesen);
        btnAuslesen.setOnClickListener(ocl);

        btnAendern = findViewById(R.id.btnAendern);
        btnAendern.setOnClickListener(ocl);

        btnLoeschen = findViewById(R.id.btnLoeschen);
        btnLoeschen.setOnClickListener(ocl);

        edTier = findViewById(R.id.edTier);
        edBeine = findViewById(R.id.edBeine);
        edID = findViewById(R.id.edID);

        tv = findViewById(R.id.tv);

    }

    private class MyOCL implements View.OnClickListener {
        @Override
        public void onClick(View view) {
            switch (view.getId()) {
                case  R.id.btnSpeichern:
                    speichern();
                    break;
                case  R.id.btnAuslesen:
                    auslesen();
                    break;
                case  R.id.btnAendern:
                    aendern();
                    break;
                case  R.id.btnLoeschen:
                    loeschen();
                    break;
            }

        }
    }

    private void speichern() {
        String t =edTier.getText().toString().trim();
        String b =edBeine.getText().toString().trim();

        if ((t.length()>0)&&(b.length()>0)) {
            String sql = String.format(
                "INSERT INTO Tiere (Tier, Beine) VALUES ('%s',%s); ", t,b);
            datenbankzugriff.ausfuehren(sql);
            edTier.setText("");
            edBeine.setText("");
        }

    }
    private void auslesen() {
        ArrayList<String> zeilen = datenbankzugriff.auslesen(
                "SELECT ID, Tier, Beine FROM Tiere;");

        tv.setText("");
        for (String str: zeilen)
            tv.append(str + "\n");
    }

    private void aendern() {
        String id = edID.getText().toString().trim();
        String t = edTier.getText().toString().trim();
        String b = edBeine.getText().toString().trim();


        if ((id.length()>0)&&(t.length()>0)&&(b.length()>0)) {
            String sql = String.format("UPDATE Tiere SET Tier='%s', Beine='%s' "
                        +"WHERE ID = %s",t,b,id);
            datenbankzugriff.ausfuehren(sql);

            edID.setText("");
            edTier.setText("");
            edBeine.setText("");
        }
    }

    private void loeschen() {
        String id = edID.getText().toString().trim();

        if (id.length()>0) {
            String sql = "DELETE FROM Tiere where ID = " + id + ";";
            datenbankzugriff.ausfuehren(sql);
            edID.setText("");
        }
    }
}