/*
 * Copyright 2013 Yuichiro Moriguchi
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package net.morilib.sh.builtin;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import java.util.regex.Pattern;

import net.morilib.sh.ShEnvironment;
import net.morilib.sh.ShFileSystem;
import net.morilib.sh.ShProcess;
import net.morilib.unix.misc.OptionIterator;

public class ShSqlc implements ShProcess {

	private static final Pattern SEL =
		Pattern.compile("SELECT", Pattern.CASE_INSENSITIVE);

	private static final String LC = 
		"/net/morilib/sh/builtin/dbdrivers.properties";
	private static Properties dname;

	static {
		InputStream ins = null;

		dname = new Properties();
		try {
			ins = ShSqlc.class.getResourceAsStream(LC);
			dname.load(ins);
		} catch(IOException e) {
			throw new RuntimeException(e);
		} finally {
			if(ins != null) {
				try {
					ins.close();
				} catch (IOException e) {
					throw new RuntimeException(e);
				}
			}
		}
	}

	private void _bat(PrintStream out, Connection con, boolean verbose,
			String sql, String... args) throws SQLException {
		PreparedStatement stmt = null;
		ResultSetMetaData rm = null;
		String s = sql.trim();
		ResultSet rs = null;
		String d = "";
		int k = 0;

		try {
			stmt = con.prepareStatement(s);
			for(int i = 0; i < args.length; i++) {
				stmt.setString(i + 1, args[i]);
			}

			if(SEL.matcher(s).lookingAt()) {
				// select
				rs = stmt.executeQuery();
				rm = rs.getMetaData();
				for(; rs.next(); k++) {
					d  = "";
					for(int i = 1; i <= rm.getColumnCount(); i++) {
						out.print(d);  d = ",";
						out.print('"');
						out.print(rs.getString(i).replaceAll(
								"\"", "\"\""));
						out.print('"');
					}
					out.println();
				}
				if(verbose)  out.format("%d columns selected\n", k);
			} else {
				// update
				k = stmt.executeUpdate();
				if(verbose)  out.format("%d columns processed\n", k);
			}
		} finally {
			if(rs   != null)  rs.close();
			if(stmt != null)  stmt.close();
		}
	}

	private void _interactive(InputStream ins, PrintStream out,
			Connection con, boolean verbose,
			String... args) throws SQLException, IOException {
		StringBuffer b = new StringBuffer();
		BufferedReader rd;
		String s, d = "";

		rd = new BufferedReader(new InputStreamReader(ins));
		out.print("q> ");
		while((s = rd.readLine()) != null) {
			if(b.length() == 0 && s.equalsIgnoreCase("exit")) {
				return;
			} else if(s.endsWith(";")) {
				s = s.substring(0, s.length() - 1);
				b.append(d).append(s);
				_bat(out, con, verbose, b.toString(), args);
				b = new StringBuffer();
			} else {
				b.append(d).append(s);
				d = "\n";
			}
			out.print("q> ");
		}
	}

	public int main(ShEnvironment env, ShFileSystem fs, InputStream in,
			PrintStream out, PrintStream err,
			String... args) throws IOException {
		String uri = null, user = null, pwd = null, db = null, sql;
		List<String> l = new ArrayList<String>();
		String[] a = new String[args.length - 1];
		boolean verbose = false;
		Connection con = null;
		Iterator<String> t;
		OptionIterator o;

		System.arraycopy(args, 1, a, 0, a.length);
		o = new OptionIterator("vi:u:p:d:", a);
		while(o.hasNext()) {
			switch(o.nextChar()) {
			case 'v':  verbose = true;  break;
			case 'd':  db = o.getArgument();  break;
			case 'i':  uri = o.getArgument();     break;
			case 'u':  user = o.getArgument();    break;
			case 'p':  pwd = o.getArgument();  break;
			default:
				err.print("sqlc: unrecognized option: ");
				err.println((char)o.getErrorOption());
				return 2;
			}
		}

		try {
			if(db   == null)  db   = env.find("DBMS");
			if(uri  == null)  uri  = env.find("DB_URI");
			if(user == null)  user = env.find("DB_USER");
			if(pwd  == null)  pwd  = env.find("DB_PASSWORD");

			Class.forName(dname.getProperty(db));
			if(user == null || pwd == null) {
				con = DriverManager.getConnection(uri);
			} else {
				con = DriverManager.getConnection(uri, user, pwd);
			}

			t = o.filenameIterator();
			if(!t.hasNext()) {
				_interactive(in, out, con, true);
			} else if((sql = t.next()).equals("-")) {
				while(t.hasNext())  l.add(t.next());
				_interactive(in, out,
						con, verbose, l.toArray(new String[0]));
			} else {
				while(t.hasNext())  l.add(t.next());
				_bat(out, con, verbose, sql, l.toArray(new String[0]));
			}
		} catch(SQLException e) {
			err.println("sqlc: SQL error");
			e.printStackTrace(err);
			return 2;
		} catch(ClassNotFoundException e) {
			err.println("sqlc: unsupported database");
			return 2;
		} finally {
			if(con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					err.println("sqlc: SQL error");
					e.printStackTrace(err);
					return 2;
				}
			}
		}
		return 0;
	}

}
