
package org.mbari.vdm;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.exolab.castor.jdo.Database;
import org.exolab.castor.jdo.PersistenceException;
import org.exolab.castor.mapping.MappingException;
import org.mbari.jdo.castor.JdoDatabaseFactory;
import org.mbari.seepsearch.grid.GridException;

public class VarsQueryer {

    File gridStorageDir = new File("c:/workspace/vdm/resources/grids");
    File varsQueryDir = new File("c:/workspace/vdm/resources/varsQueries");

    public File queryVars(String[] queryItems, String gridFileName, UiFeedback ui, String earliestDate)
        throws PersistenceException, SQLException, IOException, GridException {
        // TODO think more about this error handling logic... should I be
        // throwing an exception?
        if (queryItems.length < 1) {
            Exception e = new IllegalArgumentException("No query items provided");
            ui.reportError("No query items provided", e);
            return null;
        }
        if (gridFileName == null || gridFileName.length() == 0) {
            Exception e = new IllegalArgumentException("No filename provided");
            ui.reportError("No Filename provided", e);
            return null;
        }
        Database db;
        try {
            db = JdoDatabaseFactory.getDatabase("vars");
            ui.reportStatus("Established database connection");
        } catch (MappingException e) {
            e.printStackTrace();
            ui.reportError("Failed to connect to VARS database.", e);
            return null;
        }

        db.begin();
        Connection connection = db.getJdbcConnection();
        Statement statement = connection.createStatement();
        String query = makeSQLQuery(makeCommaSeparatedString(queryItems), earliestDate);
        System.out.println("using query:\n" + query);
        ResultSet resultSet = statement.executeQuery(query);
        ui.reportStatus("Parsing query results...");
        VarsLine[] lines = buildVarsLineArray(resultSet);
        ui.reportStatus(lines.length + " results parsed");
        resultSet.close();
        statement.close();
        db.commit();
        db.close();
        ui.reportStatus("saving output to disk (c:/workspace/vdm/resources/grids/" + gridFileName
            + ")");
        File csvFile = new File("c:/workspace/vdm/resources/grids/" + gridFileName);
        BufferedWriter writer = new BufferedWriter(new FileWriter(csvFile));
        CsvHelper.writeLines(writer, lines);
        return csvFile;
    }

    private VarsLine[] buildVarsLineArray(ResultSet resultSet) throws SQLException {
        List lines = new ArrayList();
        int numColumns = resultSet.getMetaData().getColumnCount();
        StringBuffer buffer = new StringBuffer();
        while (resultSet.next()) {
            buffer.delete(0, buffer.length());
            for (int i = 1; i <= numColumns; i++) {
                buffer.append(resultSet.getString(i) + ", ");
            }
            buffer.delete(buffer.length() - 2, buffer.length());
            lines.add(new VarsLine(buffer.toString().split(", ")));
        }
        return (VarsLine[]) lines.toArray(new VarsLine[0]);
    }

    private String makeSQLQuery(String conceptList, String earliestDate) {
        return "SELECT distinct Latitude, Longitude, Depth, RecordedDate, "
            + "TapeTimeCode, ObservationID_FK, ConceptName, VideoArchiveName, Image"
            + " FROM Annotations WHERE RecordedDate > '"+ earliestDate +"' AND ( ( ConceptName IN (" + conceptList + ")  OR ToConcept IN ("
            + conceptList + ") ))";
    }

    // not just an ordinary csv, this also adds single quotes around each value
    private String makeCommaSeparatedString(String[] queryItems) {
        StringBuffer buffer = new StringBuffer();
        for (int i = 0; i < queryItems.length; i++) {
            buffer.append("'" + queryItems[i] + "'");
            if (i < (queryItems.length - 1)) {
                buffer.append(", ");
            }
        }
        return buffer.toString();
    }

}
