/*
 * NIST HL7 Web Service
 * JdbcRepositoryDao.java Feb 12, 2008
 *
 * This code was produced by the National Institute of Standards and
 * Technology (NIST). See the "nist.disclaimer" file given in the distribution
 * for information on the use and redistribution of this software.
 */

package gov.nist.hl7.ws;

import gov.nist.hl7.core.MalformedProfileException;
import gov.nist.hl7.core.profile.Profile;
import gov.nist.hl7.core.validation.message.TableProfileDocument;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.xmlbeans.XmlException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;

/**
 * Implementation of the interface (JDBC version)
 *
 * @author Roch Bertucat (NIST)
 *
 */
public class JdbcRepositoryDao {

    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcInsert insertHandle;
    private JdbcTemplate jdbcTemplate;
    private static String prefixOID = "1.2.814.99999";

    /**
     * Update the OID of the inserted element
     * @param type the type of the element (profile, resource or handle)
     * @return the generated OID
     * @throws SQLException
     */
    private String updateOID(String type) {

        if (prefixOID == null) {
            throw new NullPointerException("The OID prefix is null.");
        }

        String oid, mid = "0", sql = "", lastIndex = "";
        if (type == "profile" || type == "resource") {
            if (type == "profile") {
                mid = "1.";
            } else if (type == "resource") {
                mid = "2.";
            }
            sql = "SELECT max(file_id) from files";

        } else if (type == "handle") {
            mid = "3.";
            sql = "SELECT max(handle_id) from handles";
        }

        // get last id
        ParameterizedRowMapper<String> mapper = new ParameterizedRowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getString(1);
            }
        };
        lastIndex = this.simpleJdbcTemplate.queryForObject(sql, mapper);
        oid = prefixOID + "." + mid + lastIndex;

        // update OID
        if (type == "profile" || type == "resource") {
            sql = "update files set OID = ? where file_id = ?";
        } else if (type == "handle") {
            sql = "update handles set handle_OID = ? where handle_id = ?";
        }
        this.simpleJdbcTemplate.update(
                sql, new Object[] {new String(oid), new String(lastIndex)});

        return oid;
    }

    /**
     * List all the handles in the repository (ie: link resources-profile)
     * @return the list
     */
    public String [] listHandles() {

        String sql = "select distinct handle_OID from handles";
        ParameterizedRowMapper<String> mapper = new ParameterizedRowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getString(1);
            }
        };
        List<String> list = new ArrayList<String>();
        try {
            list = this.simpleJdbcTemplate.query(sql, mapper);
        } catch (EmptyResultDataAccessException e) {
            ;
        }
        return (String []) list.toArray(new String [list.size()]);

    }

    /**
     * List the files of a certain type present in the repository
     * @param type profile or resource
     * @return the list
     */
    public String [] listFiles(String type) {
        String sql = "select OID from files where type = ?";
        ParameterizedRowMapper<String> mapper = new ParameterizedRowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getString(1);
            }
        };
        List<String> list = new ArrayList<String>();
        try {
            list = this.simpleJdbcTemplate.query(sql, mapper, type);
        } catch (EmptyResultDataAccessException e) {
            ;
        }
        return (String []) list.toArray(new String [list.size()]);
    }

    /**
     * Bind another resource to an existing handle
     * @param resourceOID
     * @param handleOID
     * @return
     * @throws SQLException
     */
    public boolean bindAnotherResource(String resourceOID, String handleOID) throws SQLException {

        // check if handle OID exists in the base first
        String sql = "select distinct profile_OID from handles where handle_OID = ?";
        ParameterizedRowMapper<String> mapper = new ParameterizedRowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getString(1);
            }
        };
        String profileOID = null;
        try {
            profileOID = simpleJdbcTemplate.queryForObject(sql, mapper, handleOID);
        } catch (EmptyResultDataAccessException e) {
            throw new SQLException("The handleOID doesn't exist: use bind first.");
        }

        // check if resource OID is already set in the base
        sql = "select handle_OID from handles where handle_OID = ? and resource_OID = ?";
        try {
            simpleJdbcTemplate.queryForObject(sql, mapper, handleOID, resourceOID);
            throw new SQLException("This binding already exists.");
        } catch (EmptyResultDataAccessException e) {
            ;
        }

        // get the profileOID associated with this handle
        return bind(profileOID, resourceOID, handleOID);
    }

    /**
     * Bind a resource to the profile, the OID will be generated
     * @param profileOID the profile OID
     * @param resourceOID the resource OID
     * @return the generated OID for the handle
     * @throws SQLException
     */
    public String bind(String profileOID, String resourceOID) throws SQLException {
        String oid = "";
        // insert empty OID
        if (bind(profileOID, resourceOID, "")) {
            // update OID
            oid = updateOID("handle");
        }
        return oid;
    }

    /**
     * Bind a resource to a profile
     * @param profileOID the profile OID
     * @param resourceOID the resource OID
     * @param handleOID the handle OID
     * @return true if the bind has been created, false otherwise
     * @throws SQLException
     */
    public boolean bind(String profileOID, String resourceOID, String handleOID) throws SQLException {

        // check if OID exists in the base first
        String sql = "select file_name from files where oid = ? and type = 'profile'";
        ParameterizedRowMapper<String> mapper = new ParameterizedRowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getString(1);
            }
        };

        try {
            simpleJdbcTemplate.queryForObject(sql, mapper, profileOID);
        } catch (EmptyResultDataAccessException e) {
            throw new SQLException("No associated Profile with the OID: " + profileOID + ".");
        }

        sql = "select file_name from files where oid = ? and type = 'resource'";
        try {
            simpleJdbcTemplate.queryForObject(sql, mapper, resourceOID);
        } catch (EmptyResultDataAccessException e) {
            throw new SQLException("No associated Resource with the OID: " + resourceOID + ".");
        }

        // check if the bind exists already
        sql = "select handle_OID from handles where profile_OID = ? and resource_OID = ?";

        try {
            String handle = simpleJdbcTemplate.queryForObject(sql, mapper, profileOID, resourceOID);
            throw new SQLException("A handle (" + handle
                    + ") already exists for this profile and this resource. Use bindAnotherResource.");
        } catch (EmptyResultDataAccessException e) {
            ;
        }

        // insert
        boolean ret = false;
        // create the sql command
        Map<String, Object> parameters = new HashMap<String, Object>(2);
        parameters.put("handle_OID", handleOID);
        parameters.put("profile_OID", profileOID);
        parameters.put("resource_OID", resourceOID);
        if (this.insertHandle.execute(parameters) == 1) {
            ret = true;
        }
        return ret;
    }

    /**
     * Get the bindings (handles list)
     * @param handleOID a handle OID
     * @return an array of profile resource
     * @throws SQLException
     */
    public String [] getBindings(String handleOID) throws SQLException {
        ArrayList<String> al = new ArrayList<String>();

        String sql = "select profile_OID, resource_OID from handles where handle_OID = ?";
        ParameterizedRowMapper<String[]> mapper = new ParameterizedRowMapper<String[]>() {
            public String[] mapRow(ResultSet rs, int rowNum) throws SQLException {
                String[] list = {"", ""};
                list[0] = rs.getString(1);
                list[1] = rs.getString(2);
                return list;
            }
        };

        List<String[]> list = new ArrayList<String[]>();
        try {
            list = this.simpleJdbcTemplate.query(sql, mapper, handleOID);
        } catch (EmptyResultDataAccessException e) {
            ;
        }

        // select
        int indx = 0;
        Iterator<String[]> it = list.iterator();
        while (it.hasNext()) {
            String[] curList = it.next();
            if (indx == 0) {
                al.add(curList[0]);
            }
            al.add(curList[1]);
            indx++;
        }
        return (String []) al.toArray(new String [al.size()]);
    }

    /**
     * Load the given profile
     * @param p the profile
     * @param profileName a user defined Name
     * @return the generated OID
     * @throws SQLException
     * @throws IOException
     */
    public String loadProfile(String profileName, Profile p) {
        String oid = "";
        // insert empty OID
        if (loadProfile(profileName, p, "")) {
            // update OID
            oid = updateOID("profile");
        }
        return oid;
    }

    /**
     * Load the given profile
     * @param p the profile
     * @return the generated OID
     * @throws SQLException
     * @throws IOException
     */
    public String loadProfile(Profile p) {
        String oid = "";
        // insert empty OID
        if (loadProfile(p, "")) {
            // update OID
            oid = updateOID("profile");
        }
        return oid;
    }

    /**
     * Load the current profile associated to the OID in the repository
     * @param p the profile
     * @param OID the OID
     * @return true if it worked, false otherwise
     * @throws SQLException
     * @throws IOException
     */
    public boolean loadProfile(Profile p, String oid) {
        // build the file name
        String profileName = p.getFilename();
        if (profileName == "") {
            SimpleDateFormat sdf = new SimpleDateFormat("yyMMddHHmmssZ");
            profileName = "profile-" + sdf.format(new Date());
            profileName += ".xml";
        } else {
            profileName = profileName.substring(profileName.lastIndexOf("\\") + 1);
        }
        return loadProfile(profileName, p, oid);
    }

    public boolean loadProfile(String profileName, Profile p, String oid) {
        return loadFile(profileName, oid, /*escapeCharacters(*/p.getDocument().toString()/*)*/, "profile");
    }

    /**
     * Load the resource in the repository
     * @param resource the resource (tables)
     * @return the generated OID
     * @throws SQLException
     * @throws IOException
     */
    public String loadResource(TableProfileDocument resource) {
        String oid = "";
        if (loadResource(resource, "")) {
            // update OID
            oid = updateOID("resource");
        }
        return oid;
    }

    /**
     * Load the resource associated to the OID in the repository
     * @param resource the resource
     * @param OID the OID
     * @return true it ok, false otherwise
     * @throws SQLException
     * @throws IOException
     */
    public boolean loadResource(TableProfileDocument resource, String oid) {
        // build the file name
        SimpleDateFormat sdf = new SimpleDateFormat("yyMMddHHmmssZ");
        String profileName = "resource-" + sdf.format(new Date());
        profileName += ".xml";
        return loadFile(profileName, oid, /*escapeCharacters(*/resource.toString()/*)*/, "resource");
    }

    /**
     * Load the file resource or profile into the repository
     * @param name the name of the file
     * @param oid the oid of the file
     * @param file the file content
     * @param type the type of the file
     * @return true if the insertion was made, false otherwise
     */
    private boolean loadFile(final String name, final String oid, final String file,
            final String type) {
        boolean ret = false;
        // create the sql command
        final InputStream blobIs = new ByteArrayInputStream(file.getBytes());
        DefaultLobHandler lobhandler = new DefaultLobHandler();
        Object obj = this.jdbcTemplate.execute(
                "INSERT INTO files (file_name, OID, type, file) VALUES (?, ?, ?, ?)",
                new AbstractLobCreatingPreparedStatementCallback(lobhandler) {
                    protected void setValues(PreparedStatement ps, LobCreator lobCreator)
                    throws SQLException {
                        ps.setString(1, name);
                        ps.setString(2, oid);
                        ps.setString(3, type);
                        lobCreator.setBlobAsBinaryStream(ps, 4, blobIs, (int) file.getBytes().length);
                    }
                }
        );

        if (obj.equals(new Integer(1))) {
            ret = true;
        }
        return ret;
    }

    /**
     * Delete the profile from the repository
     * @param oid the oid of the file
     * @return the profile
     * @throws SQLException
     * @throws MalformedProfileException
     */
    public Profile removeFile(String oid) throws SQLException, MalformedProfileException {
        // delete in the DB
        Profile p = getProfile(oid);
        if (p != null) {
            this.simpleJdbcTemplate.update(
                    "delete from files where OID = ?",
                    new Object[] {new String(oid)});
            // should return 1
        }
        return p;
    }

    /**
     * Get the profile associated with the OID
     * @param OID the OID
     * @return the profile
     * @throws SQLException
     * @throws MalformedProfileException
     */
    public Profile getProfile(String oid) throws SQLException, MalformedProfileException {
        String file = getFile(oid, "profile");
        return new Profile(oid, file);
    }

    /**
     * Get the resource associated with the OID
     * @param OID the OID
     * @return the resource
     * @throws SQLException
     * @throws XmlException
     */
    public TableProfileDocument getResource(String oid) throws SQLException, XmlException {
        String file = getFile(oid, "resource");
        return TableProfileDocument.Factory.parse(file);
    }

    /**
     * Get the filename associated with the OID in the repository
     * @param OID the OID
     * @param type the type of the file
     * @return the name of the file
     * @throws SQLException
     */
    public String getFile(String oid, String type) throws SQLException {
        String sql = "select file from files where oid = ? and type = ?";
        ParameterizedRowMapper<String> mapper = new ParameterizedRowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                byte[] blobBytes = new DefaultLobHandler().getBlobAsBytes(rs, "file");
                return new String(blobBytes);
            }
        };
        String file = "";
        try {
            file = this.simpleJdbcTemplate.queryForObject(sql, mapper, oid, type);
        } catch (EmptyResultDataAccessException e) {
            throw new SQLException("No associated " + type + " with the OID: " + oid + ".");
        }
        return file;
    }

    /**
     * Set the datasource to use within the class
     * @param dataSource the datasource given through Spring
     */
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.insertHandle =  new SimpleJdbcInsert(dataSource)
        .withTableName("handles")
        .usingGeneratedKeyColumns("handle_id");
    }
}