/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import data.Beat;
import data.BeatTransition;
import data.IInstrument;
import data.IMotif;
import data.INote;
import data.INoteAmount;
import data.ITonality;
import data.MotifData;
import data.MultiBeat;
import data.MultiMotifData;
import data.NoteData;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author smuRf
 */
public class DatabaseInterface {
    private Database database;
    
    public DatabaseInterface(String user, String pass, String url)
    {
        database = new Database(user, pass, url);
    }
    
    public boolean isDatabaseConnectionUp()
    {
        return database.isConnectionUp();
    }
    
    private boolean exists(IMotif motif)
    {
        ArrayList<Integer> motifIds = new ArrayList();
        ArrayList<INote> iNotes = new ArrayList();
        ResultSet rs = database.select("SELECT id FROM pbmc.motif");
        try {
            while(rs.next())
            {
                motifIds.add(rs.getInt("id"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        boolean ret = false;
        if(!motifIds.isEmpty())
        {
            for(int id:motifIds)
            {
                iNotes.clear();
                rs = database.select("SELECT * FROM pbmc.note WHERE motif_id="+id+" ORDER BY id");
                try {
                    while(rs.next())
                    {
                        iNotes.add(new INote(rs.getString("note"), rs.getDouble("duration"), rs.getInt("real_duration"), rs.getInt("pitch"), rs.getDouble("rhythm_value"), rs.getInt("dynamic")));
                    }
                    
                    if(!iNotes.isEmpty())
                    {
                        if(motif.equals(iNotes))
                        {
                            ret = true;
                            break;
                        }
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return ret;
    }

    public void closeConnection() 
    {
        database.closeConnection();
        //System.err.println("disconnected from database");
    }
    
    public HashMap<String, Integer> getCategories(String username)
    {
        ResultSet rs = database.select("SELECT id FROM pbmc.user WHERE name='" + username + "'");
        HashMap<String, Integer> categories = new HashMap();
        
        try {
            if(!username.isEmpty())
            {
                rs.first();
                int userId = rs.getInt("id");
                rs = database.select("SELECT * FROM pbmc.category WHERE user_id='" + userId + "'");
            }
            else
            {
               rs = database.select("SELECT * FROM pbmc.category ORDER BY category_name ASC");
            }
        
            while(rs.next()) {
                categories.put(rs.getString("category_name"), rs.getInt("id"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return categories;
    }
    
    public int getCategoryIdForName(String name)
    {
        int id = -1;
        try {
            ResultSet rs = database.select("SELECT id FROM pbmc.category WHERE category_name='"+name+"'");
            if(rs.next())
                id = rs.getInt("id");
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return id;
    }
    
    public String getCreatorOfCategory(int categoryId)
    {
        String username = "";
        ResultSet rs = database.select("SELECT user_id FROM pbmc.category WHERE id='"+categoryId+"'");
        try {
            while(rs.next())
            {
                int userId = rs.getInt("user_id");
                rs = database.select("SELECT name FROM pbmc.user WHERE id='"+userId+"'");
                rs.first();
                username = rs.getString("name");
                break;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return username;
    }
    
    public String getCreatorOfMotif(int motifId)
    {
        String username = "";
        ResultSet rs = database.select("SELECT user_id FROM pbmc.motif WHERE id='"+motifId+"'");
        try {
            while(rs.next())
            {
                int userId = rs.getInt("user_id");
                rs = database.select("SELECT name FROM pbmc.user WHERE id='"+userId+"'");
                rs.first();
                username = rs.getString("name");
                break;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return username;
    }
    
    public ArrayList<Integer> getMotifsIdsOfCategoryId(int categoryId)
    {
        ArrayList<Integer> ids = new ArrayList();
        
        try {
            ResultSet rs;
            
            rs = database.select("SELECT id FROM pbmc.motif WHERE category_id='"+categoryId+"'");
            
            while(rs.next())
            {
                ids.add(rs.getInt("id"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return ids;
    }
    
    public int getIdForUsername(String name)
    {
        ResultSet rs = database.select("SELECT id FROM pbmc.user WHERE name='"+name+"'");
        try {
            while(rs.next())
                return rs.getInt("id");
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return -1;
    }
    
    public ArrayList<Integer> getMotifsIds(String username)
    {
        ArrayList<Integer> ids = new ArrayList();
        
        try {
            ResultSet rs;
            
            if(username==null)
                rs = database.select("SELECT id FROM pbmc.motif");
            else
            {
                rs = database.select("SELECT id FROM pbmc.motif WHERE user_id='"+getIdForUsername(username)+"'");
            }
            
            while(rs.next())
            {
                ids.add(rs.getInt("id"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return ids;
    }
    
    public void setNewPassword(String username, String password)
    {
        try {
            ResultSet rs = database.select("SELECT id from pbmc.user WHERE name='"+username+"'");
            int id=-1;
            
            while(rs.next())
                id = rs.getInt("id");
            
            database.update("UPDATE pbmc.user SET password=MD5('"+password+"') WHERE id="+id);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public boolean login(String user, String pass)
    {
        ResultSet rs = database.select("SELECT * FROM pbmc.user WHERE name='"+user+"' AND password=MD5('"+pass+"')");
        try {
            while(rs.next())
            {
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public boolean login(String user, byte[] pass)
    {
        ResultSet rs = database.select("SELECT password FROM pbmc.user WHERE name='"+user+"'");
        try {
            while(rs.next())
            {
                byte[] array = rs.getBytes("password");
                boolean ret = true;
                for(int i = 0; i < array.length; i++)
                {
                    if(array[i] != pass[i])
                    {
                        ret = false;
                        break;
                    }
                }
                return ret;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public String getMotifName(int id)
    {
        String name = "";
        
        try {
            ResultSet rs = database.select("SELECT name FROM pbmc.motif WHERE id="+id);
            
            while(rs.next())
            {
                name = rs.getString("name");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return name;
    }
    
    public double getMotifTempo(int id)
    {
        double tempo = -1;
        
        try {
            ResultSet rs = database.select("SELECT tempo FROM pbmc.motif WHERE id="+id);
            
            while(rs.next())
            {
                tempo = rs.getDouble("tempo");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return tempo;
    }
    
    public int getMotifPPQN(int id)
    {
        int ppqn = -1;
        
        try {
            ResultSet rs = database.select("SELECT ppqn FROM pbmc.motif WHERE id="+id);
            
            while(rs.next())
            {
                ppqn = rs.getInt("ppqn");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ppqn;
    }
    
    public int getMotifDefaultInstrument(int id)
    {
        int instrument = -1;
        
        try {
            ResultSet rs = database.select("SELECT default_instrument FROM pbmc.motif WHERE id="+id);
            
            while(rs.next())
            {
                instrument = rs.getInt("default_instrument");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return instrument;
    }
    
    public ArrayList<INote> getMotifDetails(int id)
    {
        ArrayList<INote> notes = new ArrayList();
        
        try {
            ResultSet rs = database.select("SELECT * FROM pbmc.note WHERE motif_id="+id+" ORDER BY id");
                    
            while(rs.next())
            {
                int dynamic = rs.getInt("dynamic");
                double rhythmValue = rs.getDouble("rhythm_value");
                double duration = rs.getDouble("duration");
                double realDuration = rs.getDouble("real_duration");
                int pitch = rs.getInt("pitch");
                String note = rs.getString("note");

                INote iNote;
                if(pitch == Integer.MIN_VALUE) // REST
                    iNote = new INote("REST", duration, realDuration, rhythmValue, dynamic);
                else
                    iNote = new INote(note, (pitch/12)-1, duration, realDuration, pitch, rhythmValue, dynamic);

                notes.add(iNote);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return notes;
    }
    
    public int insertCategory(String name, String username)
    {
        try {
            ResultSet rs = database.select("SELECT category_name FROM pbmc.category WHERE category_name='"+name+"'");
            int id;
            if(!rs.next())
            {
                rs = database.select("SELECT id FROM pbmc.user WHERE name='"+username+"'");
                rs.first();
                int userId = rs.getInt("id");
                id = database.insert("INSERT INTO pbmc.category(user_id,category_name) VALUES("+userId+",'"+name+"')");
            }
            else
                id = -1;
            return id;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }
    
    public void insertMotif(IMotif motif, String username, int categoryId)
    {
        try {
            if(!exists(motif))
            {
                ResultSet rs = database.select("SELECT id FROM pbmc.user WHERE name='"+username+"'");
                int userId = -1;
                while(rs.next())
                    userId = rs.getInt("id");

                int id = database.insert("INSERT INTO pbmc.motif(user_id,category_id,name,tempo,default_instrument,ppqn) VALUES("+userId+","+categoryId+",'"+motif.getName()+"',"+motif.getTempo()+","+motif.getDefaultInstrument()+","+motif.getIScore().getPPQN()+")");

                for(INote iNote:motif.getNotes())
                {
                    database.insert("INSERT INTO pbmc.note(motif_id,note,pitch,dynamic,rhythm_value,duration,real_duration) VALUES("+id+",'"+iNote.getINote()+"',"+iNote.getPitch()+","+iNote.getDynamic()+","+iNote.getRhythmValue()+","+iNote.getDuration()+","+iNote.getRealDuration()+")");
                }
                System.err.println("motif added to database");
            }
            else
            {
                System.err.println("motif already exists in database");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /**
     * fuegt ein Motife in die DB ein, ohne zu pruefen, ob dieses schon existiert
     * @param motif das Motif
     * @param username der Benutzername
     * @param categoryId die Kategorie, zu der das Motif gehoert
     * @param userId die Benutzer-ID
     */
    public void insertMotifWithoutExistCheck(IMotif motif, String username, int categoryId, int userId)
    {
        int id = database.insert("INSERT INTO pbmc.motif(user_id,category_id,name,tempo,default_instrument,ppqn) VALUES("+userId+","+categoryId+",'"+motif.getName()+"',"+motif.getTempo()+","+motif.getDefaultInstrument()+","+motif.getIScore().getPPQN()+")");

        for(INote iNote:motif.getNotes())
        {
            database.insert("INSERT INTO pbmc.note(motif_id,note,pitch,dynamic,rhythm_value,duration,real_duration) VALUES("+id+",'"+iNote.getINote()+"',"+iNote.getPitch()+","+iNote.getDynamic()+","+iNote.getRhythmValue()+","+iNote.getDuration()+","+iNote.getRealDuration()+")");
        }
    }
    
    /**
     * legt eine neue Kategorie an und liefert deren ID bzw. falls diese schon vorhanden ist, liefert die ID der Kategorie
     * @param name Name der Kategorie
     * @param username Benutzername
     * @return ID der angelegten oder vorhandenen Kategorie
     */
    public int insertOrGetCategory(String name, String username)
    {
        try {
            ResultSet rs = database.select("SELECT id FROM pbmc.category WHERE category_name='"+name+"'");
            int id;
            if(!rs.next())
            {
                rs = database.select("SELECT id FROM pbmc.user WHERE name='"+username+"'");
                rs.first();
                int userId = rs.getInt("id");
                id = database.insert("INSERT INTO pbmc.category(user_id,category_name) VALUES("+userId+",'"+name+"')");
            }
            else
            {
                rs.first();
                id = rs.getInt("id");
            }
            return id;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }
    
    /**
     * fuegt eine neue Note mit Auftrittswahrscheinlichkeit zu einer Kategorie hinzu
     * @param note das NoteAmountObjekt
     */
    public int insertNoteAmount(INoteAmount note)
    {
        int id = database.insert("INSERT INTO pbmc.note_amount(name,octave,amount,pitch,category_id) VALUES('"
                    +note.getName()+"'," +note.getOctave() +"," +note.getAmount() +"," +note.getPitch() +"," +note.getCategory() +")");
        return id;
    }
    
    /**
     * loescht alle alten Eintraege der Notenhaeufigkeiten einer Kategorie
     * @param category ID der Kategorie
     */
    public void deleteNoteAmount(int category){
        database.execute("DELETE FROM pbmc.note_amount WHERE category_id=" +category);
    }
    
    /**
     * loescht alle alten Eintraege der Instrumente einer Kategorie
     * @param category ID der Kategorie
     */
    public void deleteInstrument(int category){
        database.execute("DELETE FROM pbmc.instrument WHERE category_id=" +category);
    }
    
    /**
     * loescht alle alten Eintraege der Tonarten einer Kategorie
     * @param category ID der Kategorie
     */
    public void deleteTonality(int category){
        database.execute("DELETE FROM pbmc.tonality WHERE category_id=" +category);
    }
    
    /**
     * liefert eine ArrayList mit INoteAmount-Objekten der uebergebenen Kategorie
     * @param category_id die ID der Kategorie
     * @return ArrayList mit INoteAmount-Objekten
     */
    public ArrayList<INoteAmount> getNoteAmounts(int category_id)
    {
       ArrayList<INoteAmount> notes = new ArrayList();
        
        try {
            ResultSet rs = database.select("SELECT * FROM pbmc.note_amount WHERE category_id="+category_id+" ORDER BY amount DESC");
                    
            while(rs.next())
            {
                INoteAmount ina = new INoteAmount();
                ina.setName(rs.getString("name"));
                ina.setOctave(rs.getInt("octave"));
                ina.setAmount(rs.getInt("amount"));
                ina.setCategory(category_id);
                notes.add(ina);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return notes;
    }
    
    /**
     * liefert eine ArrayList mit INoteAmount-Objekten der uebergebenen Kategorie
     * gruppiert nach Note (alle C nur 1 Wert, alle C#, ...)
     * @param category_id die ID der Kategorie
     * @return ArrayList mit INoteAmount-Objekten
     */
    public ArrayList<INoteAmount> getNoteAmountsGroupByNote(int category_id)
    {
       ArrayList<INoteAmount> notes = new ArrayList();
        
        try {
            ResultSet rs = database.select("SELECT na.name, SUM(na.amount) AS Sum FROM pbmc.note_amount na WHERE category_id = " +category_id +" GROUP BY na.name");
                    
            while(rs.next())
            {
                INoteAmount ina = new INoteAmount();
                ina.setName(rs.getString("name"));
                ina.setAmount(rs.getInt("Sum"));
                ina.setCategory(category_id);
                notes.add(ina);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return notes;
    }
    
    /**
     * fuegt eine neue Instrumentkategorie zu einer Kategorie hinzu bzw. updated den Wert, falls dieser schon existiert
     * @param instrument das InstrumentObjekt
     */
    public int insertInstrument(IInstrument instrument)
    {
        int id = database.insert("INSERT INTO pbmc.instrument(name,instr_count,category_id) VALUES('"
                    +instrument.getInstrument()+"'," +instrument.getCount() +"," +instrument.getCategory() +")");
        return id;
    }
    
    /**
     * liefert eine ArrayList mit IInstrument-Objekten der uebergebenen Kategorie
     * @param category_id die ID der Kategorie
     * @return ArrayList mit IInstrument-Objekten
     */
    public ArrayList<IInstrument> getInstruments(int category_id)
    {
       ArrayList<IInstrument> instruments = new ArrayList();
        
        try {
            ResultSet rs = database.select("SELECT * FROM pbmc.instrument WHERE category_id="+category_id+" ORDER BY instr_count DESC");
                    
            while(rs.next())
            {
                IInstrument i = new IInstrument();
                i.setInstrument(rs.getString("name"));
                i.setCount(rs.getInt("instr_count"));
                i.setCategory(category_id);
                instruments.add(i);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return instruments;
    }
    
    /**
     * fuegt eine neue Tonart zu einer Kategorie hinzu bzw. updated den Wert, falls diese schon existiert
     * @param tonality das TonalityObjekt
     */
    public int insertTonality(ITonality tonality)
    {
        String s = "1";
        if (tonality.isMajor()) {
           s = "0";
        }
        int id = database.insert("INSERT INTO pbmc.tonality(music_key,major,key_count,category_id) VALUES('"
                    +tonality.getKey()+"'," +s +"," +tonality.getCount() +"," +tonality.getCategory() +")");
        return id;
    }
    
    /**
     * liefert eine ArrayList mit ITonality-Objekten der uebergebenen Kategorie
     * @param category_id die ID der Kategorie
     * @return ArrayList mit ITonality-Objekten
     */
    public ArrayList<ITonality> getTonalities(int category_id)
    {
       ArrayList<ITonality> tonalities = new ArrayList();
        
        try {
            ResultSet rs = database.select("SELECT * FROM pbmc.tonality WHERE category_id="+category_id+" ORDER BY key_count DESC");
                    
            while(rs.next())
            {
                ITonality t = new ITonality();
                t.setKey(rs.getString("music_key"));
                if (rs.getInt("major") == 0) {
                  t.setMajor(true);
                }
                else
                {
                   t.setMajor(false);
                }
                t.setCount(rs.getInt("key_count"));
                t.setCategory(category_id);
                tonalities.add(t);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return tonalities;
    }
    
    /**
     * liefert eine Liste der Kategorien sortiert nach Name absteigend
     * @return Liste der Kategorien
     */
    public LinkedHashMap<String, Integer> getCategoriesOrdered()
    {
        ResultSet rs = database.select("SELECT * FROM pbmc.category ORDER BY category_name ASC");
        LinkedHashMap<String, Integer> categories = new LinkedHashMap();
        
        try {
           while(rs.next()) {
                categories.put(rs.getString("category_name"), rs.getInt("id"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return categories;
    }
    
    /**
     * legt eine neue PolyNote an und liefert deren ID bzw. falls diese schon vorhanden ist, liefert die ID der PolyNote
     * @param pitch Pitch der Note
     * @param duration quantisierte Dauer der Note
     * @return ID der angelegten oder vorhandenen PolyNote
     */
    public int insertOrGetPolyNote(int pitch, double duration)
    {
        try {
            ResultSet rs = database.select("SELECT * FROM pbmc.polynote WHERE pitch = " +pitch +" AND duration = " +duration);
            int id;
            if(!rs.next())
            {
                id = database.insert("INSERT INTO pbmc.polynote(pitch,duration) VALUES(" +pitch +"," +duration +")");
            }
            else
            {
                rs.first();
                id = rs.getInt("id");
            }
            return id;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }
    
    /**
     * speichert ein monophones Motiv in der Datenbank
     * @param motif das monophone Motiv
     * @param categoryId Kategorie, zu der das Motiv gehoert
     * @param submotifs true: alle Vorkommnisse werden in der DB angelegt, false: nur die Vorlage wird abgelegt
     */
    public void insertMonophonMotif(MotifData motif, int categoryId, boolean submotifs){
        ArrayList<Beat> alb = motif.getMotif();
        String key = motif.getKey();
        int major = 1;
        if (key.substring(0, key.indexOf(" ")).equalsIgnoreCase("major")){
            major = 0;
        }
        key = key.substring(key.indexOf(" ")+1,key.length());
        
        //Anzahl der Noten im Takt speichern
        int iNoteCount[] = new int[alb.size()];
        int id = 0;
        for (Beat b : alb){
            iNoteCount[id] = b.getBeat().size();
            id++;
            if (!submotifs){
                break;
            }
        }
        
        try {
            Connection con = database.getConnection();
            con.setAutoCommit(false);
            id = 0;
            for (Beat b : alb){
                //neues Motif anlegen mit Categorie
                String queryMotif = "INSERT INTO pbmc.polymotif(category_id, instrument, numerator, denominator, music_key, major) VALUES (?,?,?,?,?,?)";
                String queryBeat = "INSERT INTO pbmc.polybeat(tempo, interesting_measure, notecount) VALUES (?,?,?)";
                String queryBeatInMotif = "INSERT INTO pbmc.beatinmotif(motif_id, beat_id) VALUES (?,?)";
                String queryNoteInBeat = "INSERT INTO pbmc.noteinbeat(polybeat_id, polynote_id, position) VALUES (?,?,?)";

                //Insert Motif
                PreparedStatement psMotif = con.prepareStatement(queryMotif, Statement.RETURN_GENERATED_KEYS);
                psMotif.setInt(1, categoryId);
                psMotif.setInt(2, b.getInstrument());
                psMotif.setInt(3, motif.getNumerator());
                psMotif.setInt(4, motif.getDenominator());
                psMotif.setString(5, key);
                psMotif.setInt(6, major);
                psMotif.executeUpdate();
                ResultSet rs = psMotif.getGeneratedKeys();
                rs.next();
                int motifId = rs.getInt(1);
                //Insert Beat
                PreparedStatement psBeat = con.prepareStatement(queryBeat, Statement.RETURN_GENERATED_KEYS);
                psBeat.setInt(1, 0);
                psBeat.setInt(2, 0);
                psBeat.setInt(3, iNoteCount[id]);
                psBeat.executeUpdate();
                ResultSet rs2 = psBeat.getGeneratedKeys();
                rs2.next();
                int beatId = rs2.getInt(1);
                //Insert Beat in Motif
                PreparedStatement psBeatInMotif = con.prepareStatement(queryBeatInMotif, Statement.RETURN_GENERATED_KEYS);
                psBeatInMotif.setInt(1, motifId);
                psBeatInMotif.setInt(2, beatId);
                psBeatInMotif.executeUpdate();
                
                int pos = 0;
                PreparedStatement psNoteInBeat = con.prepareStatement(queryNoteInBeat);
                for (NoteData nd : b.getBeat()){
                    //jede Note in DB speichern, wenn sie nicht schon existiert
                    int noteId = insertOrGetPolyNote(nd.getPitch(), nd.getQuantizedDuration());
                    psNoteInBeat.setInt(1, beatId);
                    psNoteInBeat.setInt(2, noteId);
                    psNoteInBeat.setInt(3, pos);
                    psNoteInBeat.addBatch();
                    pos++;
                }
                psNoteInBeat.executeBatch();
                if (!submotifs){
                    break;
                }
                id++;
            }
            con.commit();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /**
     * speichert ein polyphones Motiv in der Datenbank
     * @param motif das polyphone Motiv
     * @param categoryId Kategorie, zu der das Motiv gehoert
     * @param submotifs true: alle Vorkommnisse werden in der DB angelegt, false: nur die Vorlage wird abgelegt
     */
    public void insertPolyphonMotif(MultiMotifData motif, int categoryId, boolean submotifs){
        ArrayList<MultiBeat> alb = motif.getMotif();
        String key = motif.getKey();
        int major = 1;
        if (key.substring(0, key.indexOf(" ")).equalsIgnoreCase("major")){
            major = 0;
        }
        key = key.substring(key.indexOf(" ")+1,key.length());
        
        //Anzahl der Noten im Takt speichern
        LinkedList<Integer> iNoteCount = new LinkedList<>();
        for (MultiBeat mb : alb){
            NoteData[][] ndBeat = mb.getBeat();
            for (int i=0; i<ndBeat.length; i++){
                iNoteCount.add(ndBeat[i].length);
            }
            if (!submotifs){
                break;
            }
        }
        
        try {
            Connection con = database.getConnection();
            con.setAutoCommit(false);
            int id = 0;
            for (MultiBeat mb : alb){
                //neues Motif anlegen mit Categorie
                String queryMotif = "INSERT INTO pbmc.polymotif(category_id, instrument, numerator, denominator, music_key, major) VALUES (?,?,?,?,?,?)";
                String queryBeat = "INSERT INTO pbmc.polybeat(tempo, interesting_measure, notecount) VALUES (?,?,?)";
                String queryBeatInMotif = "INSERT INTO pbmc.beatinmotif(motif_id, beat_id) VALUES (?,?)";
                String queryNoteInBeat = "INSERT INTO pbmc.noteinbeat(polybeat_id, polynote_id, position) VALUES (?,?,?)";

                //Insert Motif
                PreparedStatement psMotif = con.prepareStatement(queryMotif, Statement.RETURN_GENERATED_KEYS);
                psMotif.setInt(1, categoryId);
                psMotif.setInt(2, mb.getInstrument());
                psMotif.setInt(3, motif.getNumerator());
                psMotif.setInt(4, motif.getDenominator());
                psMotif.setString(5, key);
                psMotif.setInt(6, major);
                psMotif.executeUpdate();
                ResultSet rs = psMotif.getGeneratedKeys();
                rs.next();
                int motifId = rs.getInt(1);
                
                NoteData[][] ndBeat = mb.getBeat();
                //neuen Beat anlegen
                for (int i=0; i<ndBeat.length; i++){
                    //Insert Beat
                    PreparedStatement psBeat = con.prepareStatement(queryBeat, Statement.RETURN_GENERATED_KEYS);
                    psBeat.setInt(1, 0);
                    psBeat.setInt(2, 0);
                    psBeat.setInt(3, iNoteCount.get(id));
                    psBeat.executeUpdate();
                    ResultSet rs2 = psBeat.getGeneratedKeys();
                    rs2.next();
                    int beatId = rs2.getInt(1);
                    //Insert Beat in Motif
                    PreparedStatement psBeatInMotif = con.prepareStatement(queryBeatInMotif, Statement.RETURN_GENERATED_KEYS);
                    psBeatInMotif.setInt(1, motifId);
                    psBeatInMotif.setInt(2, beatId);
                    psBeatInMotif.executeUpdate();
                    
                    PreparedStatement psNoteInBeat = con.prepareStatement(queryNoteInBeat);
                    for (int j=0; j<ndBeat[i].length; j++){
                        //jede Note in DB speichern, wenn sie nicht schon existiert
                        int noteId = insertOrGetPolyNote(ndBeat[i][j].getPitch(), ndBeat[i][j].getQuantizedDuration());
                        psNoteInBeat.setInt(1, beatId);
                        psNoteInBeat.setInt(2, noteId);
                        psNoteInBeat.setInt(3, j);
                        psNoteInBeat.addBatch();
                    }
                    psNoteInBeat.executeBatch();
                    id++;
                }
                if (!submotifs){
                    break;
                }
            }
            con.commit();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /**
     * erstellt einen neuen Taktuebergang fuer die Kategorie bzw. ueberschreibt diesen, falls er schon existiert
     * @param bt ArrayList mit den Taktuebergaengen
     * @param categoryId ID der zugehoerigen Kategorie
     */
    public void insertBeatTransition(ArrayList<BeatTransition> bt, int categoryId){
        try {
            database.execute("DELETE FROM pbmc.beattransition WHERE category_id = " +categoryId);
            String query = "INSERT INTO pbmc.beattransition(from_note, to_note, probability, category_id) VALUES (?,?,?,?)"; 
            Connection con = database.getConnection();
            con.setAutoCommit(false);
            PreparedStatement ps = con.prepareStatement(query);
            for (BeatTransition t : bt){
                ps.setString(1, t.getFrom());
                ps.setString(2, t.getTo());
                ps.setInt(3, t.getProbability());
                ps.setInt(4, categoryId);
                ps.addBatch();
            }
            ps.executeBatch();
            con.commit();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /**
     * liefert die Wahrscheinlichkeiten der Taktuebergaenge der angegebenen Kategorie
     * @param categoryId ID der zugehoerigen Kategorie
     * @return Array mit den Wahrscheinlichkeiten der Taktuebergaenge
     * (Zeilen-/Spaltenreihenfolge 0-10 : C, C#, D, Eb, E, F, F#, G, Ab, A, Bb, B)
     */
    public int[][] getBeatTransition(int categoryId){
        int[][] beatTransitions = new int[12][12];
        String query = "SELECT * FROM pbmc.beattransition WHERE category_id = " +categoryId;
        
        //alle Werte mit 0 initialisieren
        for (int i=0; i<beatTransitions.length; i++){
            for (int j=0; j<beatTransitions[i].length; j++){
                beatTransitions[i][j] = 0;
            }
        }
        
        ResultSet rs = database.select(query);
        try {
            while (rs.next()){
                String from = rs.getString("from_note");
                String to = rs.getString("to_note");
                int prob = rs.getInt("probability"), row, column;
                
                row = getIDForNote(from);
                column = getIDForNote(to);
                beatTransitions[row][column] = prob;
            }
            return beatTransitions;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    /**
     * liefert zur uebergebenen Note die ID i Taktuebergangsarray
     * @param s Note (zB.: C, C#, D, Eb, ...)
     * @return ID im Array
     */
    private int getIDForNote(String s){
        switch (s){
            case "C":
                return 0;
            case "C#":
                return 1;
            case "D":
                return 2;
            case "Eb":
                return 3;
            case "E":
                return 4;
            case "F":
                return 5;
            case "F#":
                return 6;
            case "G":
                return 7;
            case "Ab":
                return 8;
            case "A":
                return 9;
            case "Bb":
                return 10;
            case "B":
                return 11;
        }
        return -1;
    }
    
    /**
     * liefert das monophone Motif aus der Datenbank
     * @param motif_id ID des Motifs
     * @return das Motif als Beat (Takt)
     */
    public Beat getMonophonMotif(int motif_id){
        String query = "SELECT * FROM pbmc.polymotif pm, pbmc.beatinmotif bim, pbmc.polybeat pb, pbmc.noteinbeat nib, pbmc.polynote pn" 
                +" WHERE pm.id=bim.motif_id AND pb.id=bim.beat_id AND nib.polybeat_id=pb.id AND pn.id=nib.polynote_id AND pm.id=" +motif_id;
        ResultSet rs = database.select(query);
        try {
            int count = 0;
            NoteData nd;
            Beat b = new Beat();
            while (rs.next()){
                count++;
                if (count == 1){
                    b.setInstrument(rs.getInt("instrument"));
                }
                nd = new NoteData();
                nd.setPitch(rs.getInt("pitch"));
                nd.setDuration(rs.getDouble("duration"));
                nd.setQuantizedDuration(nd.getDuration());
                b.addNote(nd);
            }
            //Resultset was empty
            if (count == 0){
                return null;
            } else {
                return b;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    /**
     * liefert das polyphone Motif aus der Datenbank
     * @param motif_id ID des Motifs
     * @return das Motif als MultiBeat (polyphoner Takt)
     */
    public MultiBeat getPolyphonMotif(int motif_id){
        String query = "SELECT * FROM pbmc.polymotif pm, pbmc.beatinmotif bim, pbmc.polybeat pb, pbmc.noteinbeat nib, pbmc.polynote pn" 
                +" WHERE pm.id=bim.motif_id AND pb.id=bim.beat_id AND nib.polybeat_id=pb.id AND pn.id=nib.polynote_id AND pm.id=" +motif_id;
        ResultSet rs = database.select(query);
        try {
            int count = 0;
            int oldpolybeatid = -1;
            int row = -1, pos = 0;
            int instrument = 0;
            String key = "C";
            boolean major = true;
            NoteData[][] ndarr = new NoteData[getMultiBeatRowCount(motif_id)][getMultiBeatNoteCount(motif_id)];
            NoteData nd;
            while (rs.next()){
                count++;
                if (count == 1){
                    instrument = rs.getInt("instrument");
                    key = rs.getString("music_key");
                    if (rs.getInt("major") != 0){
                        major = false;
                    }
                }
                int polybeatid = rs.getInt("polybeat_id");
                if (polybeatid != oldpolybeatid){ //neuer Takt im MultiBeat
                    row++;
                    pos = 0;
                    oldpolybeatid = polybeatid;
                }
                nd = new NoteData();
                nd.setPitch(rs.getInt("pitch"));
                nd.setDuration(rs.getDouble("duration"));
                nd.setQuantizedDuration(nd.getDuration());
                ndarr[row][pos] = nd;
                pos++;
            }
            //Resultset was empty
            if (count == 0){
                return null;
            } else {
                MultiBeat mb = new MultiBeat();
                mb.setInstrument(instrument);
                mb.setBeat(ndarr);
                mb.setKey(key);
                mb.setMajor(major);
                MultiBeat mbNew = removeOnlyRestFromMultiBeat(mb);
                return mbNew;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    /**
     * liefert den Numerator und Denominator des Motifs
     * @param motif_id ID des Motifs
     * @return Numerator (ID: 0) und Denominator (ID: 1)
     */
    public int[] getMotifNumeratorAndDenominator(int motif_id){
        int[] iArr = {4,4};
        String query = "SELECT numerator, denominator FROM pbmc.polymotif WHERE id = " +motif_id;
        ResultSet rs = database.select(query);
        try {
            if (rs.next()){
                iArr[0] = rs.getInt(1);
                iArr[1] = rs.getInt(2);
                return iArr;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    /**
     * liefert den Numerator und Denominator des Motifs
     * @param category_id ID der Kategorie
     * @return Numerator (ID: 0) und Denominator (ID: 1)
     */
    public int[] getCategoryNumeratorAndDenominator(int category_id){
        int[] iArr = {4,4};
        String query = "SELECT numerator, denominator FROM pbmc.polymotif WHERE category_id = " +category_id +" ORDER BY Rand() LIMIT 1";
        ResultSet rs = database.select(query);
        try {
            if (rs.next()){
                iArr[0] = rs.getInt(1);
                iArr[1] = rs.getInt(2);
                return iArr;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    /**
     * liefert die Tonart des Motifs
     * @param motif_id ID des Motifs
     * @return Tonart (zB.: C Major, A Minor, ...)
     */
    public String getMotifKey(int motif_id){
        String query = "SELECT music_key, major FROM pbmc.polymotif WHERE id = " +motif_id;
        ResultSet rs = database.select(query);
        try {
            if (rs.next()){
                String s = rs.getString(1);
                if (rs.getInt(2) == 0){//major
                    s += " major";
                } else {
                    s += " minor";
                }
                return s;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    /**
     * loescht Takte des MultiBeats, welche nur aus Pausen bestehen
     * @param mb ein MultiBeat (polyphoner Takt)
     * @return der korrekte MultiBeat
     */
    private MultiBeat removeOnlyRestFromMultiBeat(MultiBeat mb){
        NoteData[][] ndArr =  mb.getBeat();
        boolean[] bArr = new boolean[ndArr.length];
        int removeRowCount = 0;
        
        for (int i=0; i<bArr.length; i++){
            bArr[i] = false;
        }
        for (int i=0; i<ndArr.length; i++){
            boolean onlyRest = true;
            for (int j=0; j<ndArr[i].length; j++){
                if (ndArr[i][j].getPitch() != Integer.MIN_VALUE){
                    onlyRest = false;
                    break;
                }
            }
            if (onlyRest){
                bArr[i] = true;
                removeRowCount++;
            }
        }
        
        //Zeilen loeschen
        int newRow = 0;
        if (ndArr.length-removeRowCount > 0){
            NoteData[][] ndArrNew = new NoteData[ndArr.length-removeRowCount][ndArr[0].length];
            for (int i=0; i<ndArr.length; i++){
                if (!bArr[i]){
                    for (int j=0; j<ndArr[i].length; j++){
                        ndArrNew[newRow][j] = ndArr[i][j];
                    }
                    newRow++;
                }
            }
            MultiBeat mbNew = new MultiBeat(ndArrNew);
            mbNew.setInstrument(mb.getInstrument());
            mbNew.setKey(mb.getKey());
            mbNew.setMajor(mb.isMajor());
            return mbNew;
        } else {
            MultiBeat mbNew = new MultiBeat(ndArr);
            mbNew.setInstrument(mb.getInstrument());
            mbNew.setKey(mb.getKey());
            mbNew.setMajor(mb.isMajor());
            return mbNew;
        }
    }
    
    /**
     * liefert die Anzahl der Noten (Spaltenanzahl) fuer das MultiBeatArray
     * @param motif_id ID des Motifs
     * @return Anzahl der Noten
     */
    private int getMultiBeatNoteCount(int motif_id){
        String query = "SELECT MAX(nib.position) FROM pbmc.polymotif pm, pbmc.beatinmotif bim, pbmc.polybeat pb, pbmc.noteinbeat nib, pbmc.polynote pn" 
                +" WHERE pm.id=bim.motif_id AND pb.id=bim.beat_id AND nib.polybeat_id=pb.id AND pn.id=nib.polynote_id AND pm.id=" +motif_id;
        ResultSet rs = database.select(query);
        try {
            if (rs.next()){
                return rs.getInt(1)+1;
            } else {
                return 0;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
    
    /**
     * liefert die Anzahl der Phrasen (Zeilenanzahl) fuer das MultiBeatArray
     * @param motif_id
     * @return Anzahl der Phrasen
     */
    private int getMultiBeatRowCount(int motif_id){
        String query = "SELECT Count(DISTINCT(bim.beat_id)) FROM pbmc.polymotif pm, pbmc.beatinmotif bim, pbmc.polybeat pb, pbmc.noteinbeat nib, pbmc.polynote pn" 
                +" WHERE pm.id=bim.motif_id AND pb.id=bim.beat_id AND nib.polybeat_id=pb.id AND pn.id=nib.polynote_id AND pm.id=" +motif_id;
        ResultSet rs = database.select(query);
        try {
            if (rs.next()){
                return rs.getInt(1);
            } else {
                return 0;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
    
    /**
     * gibt an, ob ein Motif polyphon oder monophon ist
     * @param motif_id ID des Motifs
     * @return true ... polyphone, false ... monophone
     */
    public boolean isPolyphonMotif(int motif_id){
            String query = "SELECT DISTINCT(pb.id) FROM pbmc.polymotif pm, pbmc.beatinmotif bim, pbmc.polybeat pb WHERE pm.id=bim.motif_id AND pb.id=bim.beat_id AND pm.id=" +motif_id;
            ResultSet rs = database.select(query);
        try {
            int count = 0;
            while (rs.next())
            {
                count++;
                if (count > 1){ //polyphone
                    return true;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false; //monophone
    }
    
    /**
     * liefert eine LinkedList mit den IDs der Motife der uebergebenen Kategorie
     * @param category_id ID der Kategorie
     * @return Liste der IDs der Motife der Kategorie
     */
    public LinkedList<Integer> getAllMotifsForCategory(int category_id){
        String query = "SELECT id FROM pbmc.polymotif WHERE category_id = " +category_id +" ORDER BY id";
        ResultSet rs = database.select(query);
        try {
            LinkedList<Integer> motifIds = new LinkedList<>();
            while(rs.next()){
                motifIds.add(rs.getInt(1));
            }
            return motifIds;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    /**
     * liefert eine LinkedList mit den IDs der Motife der uebergebenen Kategorie und Tempos
     * @param category_id ID der Kategorie
     * @param tempo Geschwindigkeit des Motifs (0...langsam, 1...normal, 2...schnell)
     * @return Liste der IDs der Motife der Kategorie
     */
    public LinkedList<Integer> getTempoMotifsForCategory(int category_id, int tempo){
        String query = "SELECT pm.id, MAX(pb.tempo) AS Tempo FROM pbmc.polymotif pm, pbmc.polybeat pb, pbmc.beatinmotif bim "
                    +"WHERE bim.beat_id = pb.id AND pm.id = bim.motif_id AND pm.category_id = " +category_id
                    +" GROUP BY pm.id HAVING Tempo = " +tempo +" ORDER BY pm.id";
        ResultSet rs = database.select(query);
        try {
            LinkedList<Integer> motifIds = new LinkedList<>();
            while(rs.next()){
                motifIds.add(rs.getInt(1));
            }
            return motifIds;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    /**
     * liefert eine LinkedList mit den IDs der Motife der uebergebenen Kategorie und Interessantheitsmass
     * @param category_id ID der Kategorie
     * @param interesting Interessantheitsmass des Motifs (0...wenig interessant, 1...interessant, 2...sehr interessant)
     * @return Liste der IDs der Motife der Kategorie
     */
    public LinkedList<Integer> getInterestingMotifsForCategory(int category_id, int interesting){
        String query = "SELECT pm.id, MAX(pb.interesting_measure) AS Interesting FROM pbmc.polymotif pm, pbmc.polybeat pb, pbmc.beatinmotif bim "
                    +"WHERE bim.beat_id = pb.id AND pm.id = bim.motif_id AND pm.category_id = " +category_id
                    +" GROUP BY pm.id HAVING Interesting = " +interesting +" ORDER BY pm.id";
        ResultSet rs = database.select(query);
        try {
            LinkedList<Integer> motifIds = new LinkedList<>();
            while(rs.next()){
                motifIds.add(rs.getInt(1));
            }
            return motifIds;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    /**
     * liefert eine LinkedList mit den IDs der Motife der uebergebenen Kategorie, Tempos und Interessantheitsmass
     * @param category_id ID der Kategorie
     * @param tempo Geschwindigkeit des Motifs (0...langsam, 1...normal, 2...schnell)
     * @param interesting Interessantheitsmass des Motifs (0...wenig interessant, 1...interessant, 2...sehr interessant)
     * @return Liste der IDs der Motife der Kategorie
     */
    public LinkedList<Integer> getSpecificMotifsForCategory(int category_id, int tempo, int interesting){
        String query = "SELECT pm.id, MAX(pb.tempo) AS Tempo, MAX(pb.interesting_measure) AS Interesting FROM pbmc.polymotif pm, pbmc.polybeat pb, pbmc.beatinmotif bim "
                    +"WHERE bim.beat_id = pb.id AND pm.id = bim.motif_id AND pm.category_id = " +category_id
                    +" GROUP BY pm.id HAVING Tempo = " +tempo +" AND Interesting = " +interesting +" ORDER BY pm.id";
        ResultSet rs = database.select(query);
        try {
            LinkedList<Integer> motifIds = new LinkedList<>();
            while(rs.next()){
                motifIds.add(rs.getInt(1));
            }
            return motifIds;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    /**
     * liefert die maximale und minimale Notenanzahl der Takte einer Kategorie
     * @param category_id ID der Kategorie
     * @return zweidimensionales Integer-Array mit Minimum (ID: 0) und Maximum (ID: 1)
     */
    public int[] getMinAndMaxNoteCount(int category_id){
        String query = "SELECT MIN(groups.notecount) as min, MAX(groups.notecount) AS max FROM "
                        +"(SELECT pm.id as motif, pb.id as beat, notecount FROM pbmc.polymotif pm "
                        +"JOIN pbmc.beatinmotif bim ON pm.id = bim.motif_id "
                        +"JOIN pbmc.polybeat pb ON pb.id = bim.beat_id "
                        +"WHERE pm.category_id = " +category_id
                        +" GROUP BY pm.id, pb.id) as groups";
        ResultSet rs = database.select(query);
        try {
            int[] minmax = {0,0};
            while(rs.next()){
                minmax[0] = rs.getInt(1); //Minimum
                minmax[1] = rs.getInt(2); //Maximum
            }
            return minmax;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    /**
     * aktualisiert das Tempo der Takte
     * @param category_id
     */
    public void setBeatTempo(int category_id){
        int[] minmax = getMinAndMaxNoteCount(category_id); //Minimum (ID: 0) und Maximum (ID: 1)
        int range = (minmax[1]-minmax[0])/3; //Bereich dritteln (schnell, normal, langsam)

        try {
            Connection con = database.getConnection();
            con.setAutoCommit(false);
            
            //normale Takte updaten
            int border = minmax[1] - (2*range);
            int border2 = border + range;
            int tempo = 1;
            Statement stmt = con.createStatement();
            
            String tempbeatid = "CREATE TEMPORARY TABLE pbmc.tempbeatid (beat INTEGER PRIMARY KEY) AS "
                                +"SELECT pm.id AS motif, pb.id AS beat, pb.notecount FROM pbmc.polymotif pm "
                                +"JOIN pbmc.beatinmotif bim ON pm.id=bim.motif_id "
                                +"JOIN pbmc.polybeat pb ON pb.id=bim.beat_id "
                                +"WHERE pm.category_id = " +category_id
                                +" GROUP BY pm.id, pb.id "
                                +"HAVING pb.notecount BETWEEN " +border +" AND " +border2;
            String queryGetIDs = "SELECT beat FROM pbmc.tempbeatid";
            stmt.execute(tempbeatid);
            con.commit();

            ResultSet rs = stmt.executeQuery(queryGetIDs);
            String queryUpdate = "UPDATE pbmc.polybeat pb SET pb.tempo = ? WHERE pb.id = ?";
            PreparedStatement psUpdate = con.prepareStatement(queryUpdate);
            while (rs.next()){
                psUpdate.setInt(1, tempo);
                psUpdate.setInt(2, rs.getInt(1));
                psUpdate.executeUpdate();
            }
            
            //schnelle Takte updaten
            int tempo2 = 2;
            Statement stmt2 = con.createStatement();
            
            String tempbeatid2 = "CREATE TEMPORARY TABLE pbmc.tempbeatid2 (beat INTEGER PRIMARY KEY) AS "
                                +"SELECT pm.id AS motif, pb.id AS beat, pb.notecount FROM pbmc.polymotif pm "
                                +"JOIN pbmc.beatinmotif bim ON pm.id=bim.motif_id "
                                +"JOIN pbmc.polybeat pb ON pb.id=bim.beat_id "
                                +"WHERE pm.category_id = " +category_id
                                +" GROUP BY pm.id, pb.id "
                                +"HAVING Notecount > " +border2;
            String queryGetIDs2 = "SELECT beat FROM pbmc.tempbeatid2";
            stmt2.execute(tempbeatid2);
            con.commit();
            
            ResultSet rs2 = stmt2.executeQuery(queryGetIDs2);
            String queryUpdate2 = "UPDATE pbmc.polybeat pb SET pb.tempo = ? WHERE pb.id = ?";
            PreparedStatement psUpdate2 = con.prepareStatement(queryUpdate2);
            while (rs2.next()){
                psUpdate2.setInt(1, tempo2);
                psUpdate2.setInt(2, rs2.getInt(1));
                psUpdate2.executeUpdate();
            }
            
            //langsame (restlichen) Takte sind bereits auf 0 gesetzt (Defaultwert in DB)
            con.commit();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /**
     * aktualisiert das Interessantheitsmass der Takte;
     * die vorher bestimmten Notenhaeufigkeiten werden dafuer herangezogen;
     * beinhaltet ein Takt eine oder mehrere Noten, welche haeufig in der Kategorie vertreten sind
     * gilt es als interessanter
     * @param category_id
     */
    public void setBeatInterestingMeasures(int category_id){
        try {
            Connection con = database.getConnection();
            con.setAutoCommit(false);
            
            //normale Takte updaten
            int border = 5;
            int border2 = 9;
            int measure = 1;
            Statement stmt = con.createStatement();
            
            String temppitch = "CREATE TEMPORARY TABLE pbmc.temppitch (id INTEGER PRIMARY KEY) AS"
                                +" SELECT na.id FROM pbmc.note_amount na WHERE na.category_id = " +category_id 
                                +" AND na.amount BETWEEN " +border +" AND "+border2;
            String tempbeat = "CREATE TEMPORARY TABLE pbmc.tempbeat (id INTEGER PRIMARY KEY) AS"
                                +" SELECT DISTINCT(polybeat_id) AS id FROM pbmc.temppitch tp JOIN pbmc.noteinbeat nib ON tp.id = nib.polynote_id;";
            String queryGetIDs = "SELECT beat_id AS id FROM pbmc.tempbeat tb "
                                +"JOIN pbmc.beatinmotif bim ON bim.beat_id = tb.id "
                                +"JOIN pbmc.polymotif pm ON pm.id = bim.motif_id "
                                +"WHERE category_id = " +category_id;
            String emptycheck = "SELECT * FROM pbmc.temppitch";
            stmt.execute(temppitch);
            con.commit();
            
            ResultSet rsempty = stmt.executeQuery(emptycheck);
            if (rsempty.next()){ //wenn es keine Werte in temptabelle gibt => kein update
                //zweite temporaere tabelle anlegen
                stmt.execute(tempbeat);
                con.commit();
                ResultSet rs = stmt.executeQuery(queryGetIDs);

                String queryUpdate = "UPDATE pbmc.polybeat pb SET pb.interesting_measure = ? WHERE pb.id = ?";
                PreparedStatement psUpdate = con.prepareStatement(queryUpdate);
                while (rs.next()){
                    psUpdate.setInt(1, measure);
                    psUpdate.setInt(2, rs.getInt(1));
                    psUpdate.executeUpdate();
                }
            }
            
            //interessante Takte updaten
            int measure2 = 2;
            Statement stmt2 = con.createStatement();
            
            String temppitch2 = "CREATE TEMPORARY TABLE pbmc.temppitch2 (id INTEGER PRIMARY KEY) AS"
                                +" SELECT na.id FROM pbmc.note_amount na WHERE na.category_id = " +category_id 
                                +" AND na.amount > "+border2;
            String tempbeat2 = "CREATE TEMPORARY TABLE pbmc.tempbeat2 (id INTEGER PRIMARY KEY) AS"
                                +" SELECT DISTINCT(polybeat_id) AS id FROM pbmc.temppitch2 tp JOIN pbmc.noteinbeat nib ON tp.id = nib.polynote_id;";
            String queryGetIDs2 = "SELECT beat_id AS id FROM pbmc.tempbeat2 tb "
                                +"JOIN pbmc.beatinmotif bim ON bim.beat_id = tb.id "
                                +"JOIN pbmc.polymotif pm ON pm.id = bim.motif_id "
                                +"WHERE category_id = " +category_id;
            String emptycheck2 = "SELECT * FROM pbmc.temppitch2";
            stmt2.execute(temppitch2);
            con.commit();
            
            ResultSet rsempty2 = stmt2.executeQuery(emptycheck2);
            if (rsempty2.next()){ //wenn es keine Werte in temptabelle gibt => kein update
                //zweite temporaere tabelle anlegen
                stmt.execute(tempbeat2);
                con.commit();
                ResultSet rs2 = stmt2.executeQuery(queryGetIDs2);

                String queryUpdate2 = "UPDATE pbmc.polybeat pb SET pb.interesting_measure = ? WHERE pb.id = ?";
                PreparedStatement psUpdate2 = con.prepareStatement(queryUpdate2);
                while (rs2.next()){
                    psUpdate2.setInt(1, measure2);
                    psUpdate2.setInt(2, rs2.getInt(1));
                    psUpdate2.executeUpdate();
                }
            }
            
            //wenig interessante (restlichen) Takte sind bereits auf 0 gesetzt (Defaultwert in DB)
            con.commit();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /**
     * liefert die ID eines zufaelligen Motifs einer bestimmten Kategorie und
     * eines bestimmten Instruments (MIDI-ID)
     * @param category_id ID der Kategorie
     * @param instrument ID des Instruments
     * @param count Anzahl der Werte
     * @param tempo Tempo des Motifs (0..langsam, 1..normal, 2..schnell)
     * @param major true wenn major, false wenn minor
     * @param numerator Numerator zB.: 4 (bei 4/4-Takt), 3 (bei 3/4-Takt), ...
     * @return Array der IDs zufaelliger Motife (koennen mono- oder polyphon sein)
     */
    public int[] getRandomMotifForCategoryAndInstrument(int category_id, int instrument, int count, int tempo, boolean major, int numerator){
        //zufaellige ID eines Motifs der Kategorie holen
        int[] iArr = new int[count];
        for (int i=0; i<iArr.length; i++){
            iArr[i] = -1;
        }
        int m = 0;
        if (!major){
            m = 1;
        }
        String num;
        if (numerator == 4){
            num = "(pm.numerator = 4 AND pm.denominator = 4) OR (pm.numerator = 2 AND pm.denominator = 2) OR (pm.numerator = 8 AND pm.denominator = 8)";
        } else if (numerator == 3){
            num = "(pm.numerator = 3 AND pm.denominator = 4) OR (pm.numerator = 6 AND pm.denominator = 8) OR (pm.numerator = 8 AND pm.denominator = 8)";
        } else if (numerator == 2){
            num = "(pm.numerator = 2 AND pm.denominator = 4) OR (pm.numerator = 4 AND pm.denominator = 8) OR (pm.numerator = 8 AND pm.denominator = 8)";
        } else {
            num = "(pm.numerator = 4 AND pm.denominator = 4) OR (pm.numerator = 2 AND pm.denominator = 2) OR (pm.numerator = 8 AND pm.denominator = 8)";
        }
        String query = "SELECT pm.id, MAX(tempo) AS tempo FROM pbmc.polymotif pm "
                +"JOIN pbmc.beatinmotif bim ON pm.id=bim.motif_id "
                +"JOIN pbmc.polybeat pb ON bim.beat_id=pb.id "
                +"WHERE pm.category_id = " +category_id +" AND pm.instrument = " +instrument +" AND pm.major = " +m +" AND " +num
                +" GROUP BY pm.id HAVING tempo = " +tempo
                +" ORDER BY Rand() LIMIT " +count;
        ResultSet rs = database.select(query);
        try {
            int i=0;
            while (rs.next()){
                iArr[i] = rs.getInt(1);
                i++;
            }
            return iArr;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return iArr;
    }
    
    /**
     * liefert die ID eines zufaelligen Motifs einer bestimmten Kategorie und
     * eines bestimmten Instruments (MIDI-ID)
     * @param category_id ID der Kategorie
     * @param instrument_from ID des Instruments ersten Instruments der Instrumentengruppe
     * @param instrument_to ID des Instruments letzten Instruments der Instrumentengruppe
     * @param count Anzahl der Werte
     * @param tempo Tempo des Motifs (0..langsam, 1..normal, 2..schnell)
     * @param major true wenn major, false wenn minor
     * @param numerator Numerator zB.: 4 (bei 4/4-Takt), 3 (bei 3/4-Takt), ...
     * @return Array der IDs zufaelliger Motife (koennen mono- oder polyphon sein)
     */
    public int[] getRandomMotifForCategoryAndInstrumentGroup(int category_id, int instrument_from, int instrument_to, int count, int tempo, boolean major, int numerator){
        //zufaellige ID eines Motifs der Kategorie holen
        int[] iArr = new int[count];
        for (int i=0; i<iArr.length; i++){
            iArr[i] = -1;
        }
        int m = 0;
        if (!major){
            m = 1;
        }
        String num;
        if (numerator == 4){
            num = "(pm.numerator = 4 AND pm.denominator = 4) OR (pm.numerator = 2 AND pm.denominator = 2) OR (pm.numerator = 8 AND pm.denominator = 8)";
        } else if (numerator == 3){
            num = "(pm.numerator = 3 AND pm.denominator = 4) OR (pm.numerator = 6 AND pm.denominator = 8) OR (pm.numerator = 8 AND pm.denominator = 8)";
        } else if (numerator == 2){
            num = "(pm.numerator = 2 AND pm.denominator = 4) OR (pm.numerator = 4 AND pm.denominator = 8) OR (pm.numerator = 8 AND pm.denominator = 8)";
        } else {
            num = "(pm.numerator = 4 AND pm.denominator = 4) OR (pm.numerator = 2 AND pm.denominator = 2) OR (pm.numerator = 8 AND pm.denominator = 8)";
        }
        String query = "SELECT pm.id, MAX(tempo) AS tempo FROM pbmc.polymotif pm "
                +"JOIN pbmc.beatinmotif bim ON pm.id=bim.motif_id "
                +"JOIN pbmc.polybeat pb ON bim.beat_id=pb.id "
                +"WHERE pm.category_id = " +category_id +" AND pm.instrument BETWEEN " +instrument_from +" AND " +instrument_to 
                +" AND pm.major = " +m +" AND " +num
                +" GROUP BY pm.id HAVING tempo = " +tempo
                +" ORDER BY Rand() LIMIT " +count;
        ResultSet rs = database.select(query);
        try {
            int i=0;
            while (rs.next()){
                iArr[i] = rs.getInt(1);
                i++;
            }
            return iArr;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return iArr;
    }
    
    /**
     * liefert die ID eines verwendeten Instruments einer bestimmten Kategorie,
     * welches zwischen 'from' und 'to' liegt (MIDI-ID)
     * from und to sind die Grenzen der Instrumentengruppe
     * zB.: 0-7 bei Piano, 8-15 bei Chromatic Percussion, etc.
     * @param category_id ID der Kategorie
     * @param from untere Schranke der ID
     * @param to obere Schranke der ID
     * @return ID eines Instruments
     */
    public int getRandomInstrumentFromCategory(int category_id, int from, int to){
        //zufaellige ID eines Motifs der Kategorie holen
        String query = "SELECT instrument FROM pbmc.polymotif WHERE category_id = " +category_id 
                +" AND instrument BETWEEN "+from 
                +" AND " +to +" ORDER BY Rand() LIMIT 1";
        ResultSet rs = database.select(query);
        try {
            if(rs.next()){
                return rs.getInt(1);
            } else {
                return -1;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseInterface.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return -1;
    }
}
