/**
 * ForumConnectionHandlerComm.java  1.1  5.1.97 Michael Shoffner
 *
 * Copyright (c) 1997 Michael Shoffner. All Rights Reserved.
 *
 * Permission to use, copy, modify, and distribute this software
 * for non-commercial purposes and without fee is hereby granted
 * provided that this copyright notice appears in all copies.
 *
 * http://prominence.com/                  shoffner@prominence.com
 */                   

import java.net.*;
import java.util.*;
import java.io.*;
import java.sql.*;

class ForumConnectionHandlerComm implements ForumCommInterface {
  
  // 1.0 Forum client sender/message delimiter for backwards compatibility
  static final String DELIMITER = "\u0000";

  // Connect Software's <www.connectsw.com> FastForward driver used here...
  static final String DRIVER = "connect.microsoft.MicrosoftDriver";
  static final String URL = "jdbc:ff-microsoft:/host.yourdomain.com:1433";
  static final String LOGIN = "forum";
  static final String PASSWD = "yourpassword";
  static final String DB_TABLE = "Messages";

  static {
    try {
      // try to load the ODBC driver
      Class.forName (DRIVER);
      // enable debugging by uncommenting:
      // DriverManager.setLogStream (System.out);    
    } catch (ClassNotFoundException ex) {
      ex.printStackTrace ();
    }
  }

  Connection con;
  Statement stmt;
  ResultSet rs;
  
  public ForumConnectionHandlerComm () throws SQLException {
    // single-threaded: one instance is created for each 
    // ForumConnectionHandler. only one method is executed per instance 
    con = DriverManager.getConnection (URL, LOGIN, PASSWD);
    stmt = con.createStatement ();
    // connection and statement metadata could be checked here
  }

  // 1.0 API
  public Hashtable loadAllThreads () throws SQLException {
    Hashtable threads = new Hashtable ();
    String q = "SELECT Thread FROM " + DB_TABLE;
    rs = stmt.executeQuery (q);
    // load threads from 1-column ResultSet
    while (rs.next ()) {
      String nextThread = sqlDecode (rs.getString (1));
      if (!threads.containsKey (nextThread))
	threads.put (nextThread, new Vector ());
    }
    // clean up
    rs.close ();
    stmt.close ();
    con.close ();
    return threads;
  }

  public Vector loadThreadArticles (String t) throws SQLException {
    Vector arts = new Vector ();
    String q = "SELECT Sender, Article FROM " + DB_TABLE + " WHERE Thread LIKE '" + sqlEncode (t) + "'";
    rs = stmt.executeQuery (q);
    while (rs.next ())
      arts.addElement (sqlDecode (rs.getString (1)) + DELIMITER + sqlDecode (rs.getString (2)));
    rs.close ();
    stmt.close ();
    con.close ();
    return arts;
  }

  public boolean postArticle (String art, String t) throws SQLException {
    String s = art.substring (0, art.indexOf (DELIMITER));
    String a = art.substring (art.indexOf (DELIMITER) + 1);
    String q = "INSERT INTO " + DB_TABLE + " (Sender, Article, Thread) " + " VALUES ('" + sqlEncode (s) + "', '" + sqlEncode (a) + "', '" + sqlEncode (t) + "')";
    stmt.executeUpdate (q);
    stmt.close ();
    con.close ();
    return true;
  }

  // 1.1 API additions
  public Vector loadThreadSubjects (String t) throws SQLException {
    Vector subs = new Vector ();
    String q = "SELECT Subject FROM " + DB_TABLE + " WHERE Thread LIKE '" + sqlEncode (t) + "'";
    rs = stmt.executeQuery (q);
    // Note that the DB can return nulls for Subjects, so ignore them
    while (rs.next ()) {
      String next = (String) rs.getString (1);
      if (next != null)
	subs.addElement (sqlDecode (next));
    }
    rs.close ();
    stmt.close ();
    con.close ();
    return subs;
  }

  public Vector loadSubjectArticles (String t, String s) throws SQLException {
    Vector subArts = new Vector ();
    String q = "SELECT Article FROM " + DB_TABLE + " WHERE Thread LIKE '" + sqlEncode (t) + "' AND Subject LIKE " + "'" + sqlEncode (s) + "'";
    rs = stmt.executeQuery (q);
    while (rs.next ())
      subArts.addElement (sqlDecode (rs.getString (1)));
    rs.close ();
    stmt.close ();
    con.close ();
    return subArts;
  }
  
  public Vector loadUserThreadArticles (String t, String u) throws SQLException {
    Vector threadArts = new Vector ();
    String q = "SELECT Article FROM " + DB_TABLE + " WHERE Thread LIKE '" + sqlEncode (t) + "' AND Sender LIKE " + "'" + sqlEncode (u) + "'";
    rs = stmt.executeQuery (q);
    while (rs.next ())
      threadArts.addElement (sqlDecode (rs.getString (1)));
    rs.close ();
    stmt.close ();
    con.close ();
    return threadArts;
  }

  String sqlEncode (String txt) {
    // escape character is ^
    StringBuffer e = new StringBuffer ();
    for (int i = 0; i < txt.length (); i ++) {
      char c = txt.charAt (i);
      if (c < 16)
	e.append ("^0" + Integer.toString (c, 16));
      else if ((c < 32) || (c > 127) || ("%_^'\"".indexOf (c) >= 0))
	e.append ("^" + Integer.toString (c, 16));
      else 
	e.append (c);
    }
    return e.toString ();
  }

  String sqlDecode (String txt) {
    // escape character is ^
    StringBuffer d = new StringBuffer ();
    for (int i = 0; i < txt.length (); i ++) {
      char c = txt.charAt (i);
      if (c == '^') {
	String hex = txt.substring (i + 1, i + 3);
	char dec = (char) Integer.parseInt (hex, 16);
	d.append (dec);
	i += 2;
      }
      else
	d.append (c); 
    }
    return d.toString ();
  }
}