package db;

import edutainmentclient.Main;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLDecoder;
import java.nio.channels.FileChannel;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Gonzalo
 */
public class DBAccessImplementation implements DBAccess {

    private static DBAccessImplementation instance = null;
    private Connection conn = null;
    private String getLastScoIdSql = "SELECT scoid FROM user_position WHERE ((courseid like ?) and (userid like ?));";
    private String getScoPathSql = "SELECT scopath FROM user_position WHERE scoid like ?;";
    private String getScoPointSql = "SELECT scopoint FROM user_position WHERE ((scoid like ?) and (userid like ?));";
    private String storeLastScoSql = "INSERT INTO user_position VALUES (?, ?, ?, ?, ?, ?);";
    private String deleteLastScoSql = "DELETE FROM user_position WHERE ((courseid like ?) and (userid like ?));";
    private String getAllPointsSql = "SELECT * FROM user_position;";
    private String deleteAllPointsSql = "DELETE FROM user_position;";
    private PreparedStatement getLastScoIdStmt;
    private PreparedStatement getScoPathStmt;
    private PreparedStatement getScoPointStmt;
    private PreparedStatement storeLastScoStmt;
    private PreparedStatement deleteLastScoStmt;
    private PreparedStatement getAllPointsStmt;
    private PreparedStatement deleteAllPointsStmt;

    private DBAccessImplementation() {
        init();
    }

    public static DBAccessImplementation getInstance() {
        if (instance == null) {
            instance = new DBAccessImplementation();
        }
        return instance;
    }

    private void init() {
        //String FICHERO_CONFIGURACION = System.getProperty("user.dir") + File.separator + "config" + File.separator + "db" + File.separator + "options.properties";
        //System.out.println("Fichero de configuración " + FICHERO_CONFIGURACION);
        InputStream is = null;
       
           // is = new FileInputStream(new File(FICHERO_CONFIGURACION));
            is = Main.class.getResourceAsStream("/config/options.properties");
       
        Properties options = new Properties();
        try {
            options.load(is);
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        String url = "jdbc:mysql://localhost:3307/osami";
        String user = "osami", pass = "osami";
        url = options.getProperty("url", url);
        user = options.getProperty("user", user);
        pass = options.getProperty("pass", pass);
       // System.out.println(url + " " + user + " " + pass);

        PreparedStatement a;
        try {
            try {
                //Class.forName("org.sqlite.JDBC");
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(url, user, pass);
                //conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/osami", "osami", "osami");

            } catch (SQLException ex) {
                System.out.println("error1");
                ex.printStackTrace();
            } catch (ClassNotFoundException ex) {
                ex.printStackTrace();
            }

            getLastScoIdStmt = conn.prepareStatement(getLastScoIdSql);
            getScoPathStmt = conn.prepareStatement(getScoPathSql);
            getScoPointStmt = conn.prepareStatement(getScoPointSql);
            storeLastScoStmt = conn.prepareStatement(storeLastScoSql);
            deleteLastScoStmt = conn.prepareStatement(deleteLastScoSql);
            getAllPointsStmt = conn.prepareStatement(getAllPointsSql);
            deleteAllPointsStmt = conn.prepareStatement(deleteAllPointsSql);
        } catch (SQLException ex) {
            Logger.getLogger(DBAccessImplementation.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public String getLastScoId(String courseId, String userId) {
        System.out.println("getLastScoId para: " + courseId + " " + userId);
        try {
            getLastScoIdStmt.setString(1, courseId);
            getLastScoIdStmt.setString(2, userId);

            ResultSet rs = getLastScoIdStmt.executeQuery();
            if (rs.next()) {
                String scoId = rs.getString("scoid");
                rs.close();
                return scoId;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public String getScoPath(String scoId) {
        try {
            getScoPathStmt.setString(1, scoId);

            ResultSet rs = getScoPathStmt.executeQuery();
            if (rs.next()) {
                String scoPath = rs.getString("scopath");
                rs.close();
                return scoPath;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public String getScoPoint(String scoId, String userId) {
        try {
            getScoPointStmt.setString(1, scoId);
            getScoPointStmt.setString(2, userId);

            ResultSet rs = getScoPointStmt.executeQuery();
            if (rs.next()) {
                String scoPoint = rs.getString("scopoint");
                rs.close();
                return scoPoint;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public boolean storeLastSco(String courseURL, String courseId, String userId, String scoId, String scoPath, String scoPoint) {
        System.out.println("storeLastSco " + courseURL + " " + courseId + " " + userId + " " + scoId + " " + scoPath + " " + scoPoint);
        try {

            storeLastScoStmt.setString(1, courseId);
            storeLastScoStmt.setString(2, userId);
            storeLastScoStmt.setString(3, scoId);
            storeLastScoStmt.setString(4, scoPath);
            storeLastScoStmt.setString(5, scoPoint);
            storeLastScoStmt.setString(6, courseURL);

            storeLastScoStmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public boolean deleteLastSco(String courseId, String userId) {
        try {
            deleteLastScoStmt.setString(1, courseId);
            deleteLastScoStmt.setString(2, userId);

            deleteLastScoStmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public Vector getAllPoints() {
        System.out.println("getAllPoints");
        Vector allPoints = new Vector();

        allPoints.add(new Vector<String>());//courseid
        allPoints.add(new Vector<String>());//userid
        allPoints.add(new Vector<String>());//scoid
        allPoints.add(new Vector<String>());//scopath
        allPoints.add(new Vector<String>());//scopoint
        allPoints.add(new Vector<String>());//courseURL

        try {
            ResultSet rs = getAllPointsStmt.executeQuery();

            while (rs.next()) {

                ((Vector<String>) allPoints.get(0)).add(rs.getString("courseid"));
                ((Vector<String>) allPoints.get(1)).add(rs.getString("userid"));
                ((Vector<String>) allPoints.get(2)).add(rs.getString("scoid"));
                ((Vector<String>) allPoints.get(3)).add(rs.getString("scopath"));
                ((Vector<String>) allPoints.get(4)).add(rs.getString("scopoint"));
                ((Vector<String>) allPoints.get(5)).add(rs.getString("courseURL"));
            }
            rs.close();
            return allPoints;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public boolean deleteAllPoints() {
        try {
            deleteAllPointsStmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }
}
