package com.psol.resourceful;

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.mail.*;
import org.w3c.dom.*;
import org.apache.soap.*;
import org.apache.soap.rpc.*;

public class BookingService
{
    protected Connection makeConnection()
        throws SQLException
    {
        try {
            Class.forName("org.hsqldb.jdbcDriver");
            Connection connection =
               DriverManager.getConnection(
                                    "jdbc:hsqldb:db/resourceful",
                                    "sa",
                                    null);
            connection.setAutoCommit(false);
            return connection;
        }
        catch(ClassNotFoundException e) {
            e.printStackTrace();
            return null;
        }
    }

    public Resource[] getAllResources()
        throws SQLException
    {
        Vector vector = new Vector();
        Connection connection = makeConnection();
        try {
            Statement stmt = connection.createStatement();
            try {
                ResultSet rs = stmt.executeQuery("select name," +
                    "description from resource");
                while(rs.next()) {
                    Resource resource = new Resource();
                    resource.setName(rs.getString(1));
                    resource.setDescription(rs.getString(2));
                    vector.addElement(resource);
                }
            }
            finally {
                stmt.close();
            }
        }
        finally {
            connection.close();
        }
        Resource[] resourceList = new Resource[vector.size()];
        vector.copyInto(resourceList);
        return resourceList;
    }

    public Resource[] getFreeResourcesOn(java.util.Date start,
                                         java.util.Date end)
        throws SQLException
    {
        if(start.compareTo(end) > 0)
            throw new IllegalArgumentException(
                           "start date must be before end date");
        Vector vector = new Vector();
        Connection connection = makeConnection();
        try {
            PreparedStatement stmt =
                connection.prepareStatement("select distinct " +
                    "name,description from resource where " +
                    "id not in (select resourceid from booking " +
                    "where not(end < ?) and not(start > ?))");
           try {
               stmt.setTimestamp(1,new Timestamp(start.getTime()));
               stmt.setTimestamp(2,new Timestamp(end.getTime()));
               ResultSet rs = stmt.executeQuery();
               while(rs.next()) {
                   Resource resource = new Resource();
                   resource.setName(rs.getString(1));
                   resource.setDescription(rs.getString(2));
                   vector.addElement(resource);
               }
           }
           finally {
               stmt.close();
           }
        }
        finally {
           connection.close();
        }
        Resource[] resourceList = new Resource[vector.size()];
        vector.copyInto(resourceList);
        return resourceList;
    }

    protected void doBookResource(Connection connection,
                                  String resource,
                                  java.util.Date start,
                                  java.util.Date end,
                                  String email)
        throws SQLException
    {
        if(start.compareTo(end) > 0)
            throw new IllegalArgumentException(
                           "start date must be before end date");
        Timestamp tstart = new Timestamp(start.getTime()),
                  tend = new Timestamp(end.getTime());
        PreparedStatement qstmt =
           connection.prepareStatement("select count(*) " +
                "from resource where name like ? and id not in" +
                "(select resourceid from booking where not(end < ?) " +
                "and not(start > ?))");
        try {
            qstmt.setString(1,resource);
            qstmt.setTimestamp(2,tstart);
            qstmt.setTimestamp(3,tend);
            ResultSet rs = qstmt.executeQuery();
            if(!rs.next() || rs.getInt(1) < 1)
            {
               System.out.println(rs.getInt(1));
               throw new IllegalArgumentException(
                               "resource is already booked");
            }
        }
        finally {
            qstmt.close();
        }
        PreparedStatement ustmt =
            connection.prepareStatement("insert into " +
                "booking (resourceid, start, end, email) " +
                "values((select id from resource where " +
                "name like ?),?,?,?)");
        try {
            ustmt.setString(1,resource);
            ustmt.setTimestamp(2,tstart);
            ustmt.setTimestamp(3,tend);
            ustmt.setString(4,email);
            ustmt.executeUpdate();
        }
        finally {
            ustmt.close();
        }
    }

    public void bookResource(String resource,
                             java.util.Date start,
                             java.util.Date end,
                             String email)
        throws SQLException
    {
        Connection connection = makeConnection();
        try {
            doBookResource(connection,resource,start,end,email);
            connection.commit();
        }
        catch(SQLException e) {
            connection.rollback();
            throw e;
        }
        finally {
           connection.close();
        }
    }
}
