/**
 * SphericalLib.SqlIO
 * 
 * SphericalLib is a .NET library to deal with geometry on the unit sphere.
 * It is intented to be used for complex survey footprint descriptions in
 * standalone applications as well as in web services and perhaps Yukon.
 * 
 * Written by Tamas Budavari (budavari@pha.jhu.edu)
 * See bottom of file for revision history
 * 
 * Current revision:
 *   ID:          $Id: SqlIO.cs,v 1.1.1.1 2006/11/14 02:56:39 gupchup Exp $
 *   Revision:    $Revision: 1.1.1.1 $
 *   Date:        $Date: 2006/11/14 02:56:39 $
 */
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using Spherical;

namespace Spherical.IO
{
    [CLSCompliant(true)]
    public class SqlIO : IDisposable
    {
        private SqlConnection sqlconn;

        /// <summary>
        /// Create from SQL connection string
        /// </summary>
        /// <param name="connection"></param>
        public SqlIO(string connection)
        {
            this.sqlconn = new SqlConnection(connection);
            this.sqlconn.Open();
        }

        public SqlIO(SqlConnection conn)
        {
            this.sqlconn = conn;
        }


        /// <summary>
        /// Read a given region, load constraints from halfspace table
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Region ReadRegion(Int64 id, string dbprefix)
        {
            string qry = String.Format
                ("SELECT convexid, x, y, z, c FROM {0}HalfSpace WHERE regionid={1} ORDER BY convexid", dbprefix, id);
            SqlCommand cmd = new SqlCommand(qry, this.sqlconn);
            cmd.CommandTimeout = 600;
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
            Region reg = new Region();
            reg.Name = String.Format("SQL Region #{0}", id);
            double x, y, z, c;
            int cid, pid = -1;
            while (rdr.Read())
            {
                cid = (int)rdr.GetInt64(0);
                if (cid != pid)
                {
#if true
                    reg.Add
                        (new Convex(String.Format("{0} ConvexID {1}", reg.Name, cid), 1));
#else
                    reg.convexList.Add(new Convex());
#endif
                    pid = cid;
                }
                x = rdr.GetDouble(1);
                y = rdr.GetDouble(2);
                z = rdr.GetDouble(3);
                c = rdr.GetDouble(4);
                reg.ConvexList[reg.ConvexList.Count - 1].Add
                    (new Halfspace(x, y, z, true, c));
            }
            rdr.Close();
            rdr.Dispose();
            return reg;
        }

        /// <summary>
        /// Read a given region, load constraints from halfspace table
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Region ReadRegion(Int64 id)
        {
            return ReadRegion(id, "");
        }

        /// <summary>
        /// Read a given region (parse string instead of loading from halfspace table)
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Region ReadRegionString(Int64 id)
        {
            double area;
            string type;
            byte ismask;
            return this.ReadRegionString(id, out area, out type, out ismask);
        }

        /// <summary>
        /// Read a given region (parse string instead of loading from halfspace table)
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Region ReadRegionString(Int64 id, out double area, out string type, out byte ismask)
        {
            string qry = String.Format
                ("SELECT regionString, type, comment, ismask, area FROM Region WHERE regionid={0}", id);
            SqlCommand cmd = new SqlCommand(qry, this.sqlconn);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
            rdr.Read();
            Region reg = Region.Parse(rdr.GetString(0),true);
            type = rdr.GetString(1);
            reg.Name = rdr.GetString(2);
            ismask = rdr.GetByte(3);
            area = rdr.GetDouble(4);
            rdr.Close();
            rdr.Dispose();
            return reg;
        }

        /// <summary>
        /// Fetch region IDs from the database for a query
        /// </summary>
        /// <param name="qry"></param>
        /// <returns></returns>
        public List<long> QueryRegionId(string qry)
        {
            SqlCommand cmd = new SqlCommand(qry, this.sqlconn);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
            List<long> id = new List<long>(50000);
            while (rdr.Read()) id.Add(rdr.GetInt64(0));
            rdr.Close();
            rdr.Dispose();
            id.TrimExcess();
            return id;
        }

        /// <summary>
        /// Union of all regions with a given type
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public Region ReadRegionsOfType(string type)
        {
            string qry = String.Format
                   ("SELECT regionid FROM Region WHERE type='{0}'", type);
            List<long> regionid = this.QueryRegionId(qry);
            Region reg = new Region(type);
            foreach (long rid in regionid)
            {
                Region r = this.ReadRegion(rid);
                reg.AddRange(r.ConvexList);
            }
            return reg;
        }

        /// <summary>
        /// Write a fully simplified region into SQL tables
        /// </summary>
        public long Write(Region r)
        {
            return this.Write(r, -1, null, "", 0);
        }

        /// <summary>
        /// Write a fully simplified region into SQL tables
        /// </summary>
        /// <param name="r"></param>
        /// <param name="id"></param>
        /// <param name="comment"></param>
        /// <param name="type"></param>
        /// <param name="ismask"></param>
        /// <returns></returns>
        public long Write(Region r, long id, string comment, string type, short ismask)
        {
            long regionId;
            using (SqlTransaction transaction = this.sqlconn.BeginTransaction())
            {
                #region Insert single row to table Region and get the regionID...

                if (comment == null)
                {
                    if (r.Name != null) comment = r.Name;
                    else comment = "";
                }

                Console.Error.WriteLine("Comment: [{0}]", comment);

                using (SqlCommand insertRegion = new SqlCommand("", this.sqlconn))
                {
                    insertRegion.CommandText = @"INSERT Region ([id],[type],[comment],[ismask],[area],[regionString],[sql],[xml])
                       VALUES (@id, @type, @comment, @ismask, @area, @regionString, @sql, @xml)";
                    insertRegion.Transaction = transaction;
                    insertRegion.Parameters.Add(new SqlParameter("@id", id));
                    insertRegion.Parameters.Add(new SqlParameter("@type", type));
                    insertRegion.Parameters.Add(new SqlParameter("@comment", comment));
                    insertRegion.Parameters.Add(new SqlParameter("@ismask", ismask));
                    insertRegion.Parameters.Add(new SqlParameter("@area", r.Area));
                    insertRegion.Parameters.Add(new SqlParameter("@regionString", r.ToString()));
                    insertRegion.Parameters.Add(new SqlParameter("@sql", ""));
                    insertRegion.Parameters.Add(new SqlParameter("@xml", ""));
                    int onerow = insertRegion.ExecuteNonQuery();
                    if (onerow != 1) throw new ApplicationException
                        ("SqlIO.Write(Region,...): AFFECTED ROWS NOT ONE IN INSERT REGION");
                }

                using (SqlCommand selectID = new SqlCommand("select @@IDENTITY", this.sqlconn))
                {
                    selectID.Transaction = transaction;
                    object obj = selectID.ExecuteScalar();
                    Console.Error.WriteLine("Type: {0}", obj.GetType());
                    regionId = (long)(decimal)obj;
                    Console.Error.WriteLine("Region ID: {0}", regionId);
                }

                #endregion
                #region Insert halfspaces to table Halfspace...

                using (SqlCommand insertHalfspace = new SqlCommand("", this.sqlconn))
                {
                    insertHalfspace.CommandText = @"INSERT Halfspace ([regionId],[convexId],[x],[y],[z],[c])
                                                    VALUES (@regionID, @convexID, @x,@y,@z,@c)";
                    insertHalfspace.Transaction = transaction;
                    insertHalfspace.Parameters.Add("@regionId", SqlDbType.BigInt);
                    insertHalfspace.Parameters.Add("@convexId", SqlDbType.BigInt);
                    insertHalfspace.Parameters.Add("@x", SqlDbType.Float);
                    insertHalfspace.Parameters.Add("@y", SqlDbType.Float);
                    insertHalfspace.Parameters.Add("@z", SqlDbType.Float);
                    insertHalfspace.Parameters.Add("@c", SqlDbType.Float);
                    // fill them up
                    insertHalfspace.Parameters["@regionId"].Value = regionId;
                    for (int convexId = 0; convexId < r.ConvexList.Count; convexId++)
                    {
                        insertHalfspace.Parameters["@convexId"].Value = convexId;
                        foreach (Halfspace h in r.ConvexList[convexId].HalfspaceList)
                        {
                            insertHalfspace.Parameters["@x"].Value = h.Vector.X;
                            insertHalfspace.Parameters["@Y"].Value = h.Vector.Y;
                            insertHalfspace.Parameters["@z"].Value = h.Vector.Z;
                            insertHalfspace.Parameters["@c"].Value = h.Cos0;
                            int onerow = insertHalfspace.ExecuteNonQuery();
                            if (onerow != 1) throw new ApplicationException
                                ("SqlIO.Write(Region,...): AFFECTED ROWS NOT ONE IN INSERT HALFSPACE");
                        }
                    }
                }

                #endregion
                #region Insert patches to RegionPatch...

                using (SqlCommand insertPatch = new SqlCommand("", this.sqlconn))
                {
                    insertPatch.CommandText = @"INSERT RegionPatch ([regionId],[convexId],[patch],[type],[radius],[ra],[dec],[x],[y],[z],[c],[htmId],[convexString])
                                                VALUES (@regionID, @convexID, @patch, @type, @radius, @ra, @dec, @x,@y,@z,@c, @htmId, @convexString)";
                    insertPatch.Transaction = transaction;
                    insertPatch.Parameters.Add("@regionId", SqlDbType.BigInt);
                    insertPatch.Parameters.Add("@convexId", SqlDbType.BigInt);
                    insertPatch.Parameters.Add("@patch", SqlDbType.Int);
                    insertPatch.Parameters.Add("@type", SqlDbType.VarChar);
                    insertPatch.Parameters.Add("@radius", SqlDbType.Float);
                    insertPatch.Parameters.Add("@ra", SqlDbType.Float);
                    insertPatch.Parameters.Add("@dec", SqlDbType.Float);
                    insertPatch.Parameters.Add("@x", SqlDbType.Float);
                    insertPatch.Parameters.Add("@y", SqlDbType.Float);
                    insertPatch.Parameters.Add("@z", SqlDbType.Float);
                    insertPatch.Parameters.Add("@c", SqlDbType.Float);
                    insertPatch.Parameters.Add("@htmId", SqlDbType.BigInt);
                    insertPatch.Parameters.Add("@convexString", SqlDbType.VarChar);

                    // fill them up
                    insertPatch.Parameters["@regionId"].Value = regionId;
                    insertPatch.Parameters["@htmId"].Value = -1;
                    insertPatch.Parameters["@type"].Value = type;
                    for (int convexId = 0; convexId < r.ConvexList.Count; convexId++)
                    {
                        Convex convex = r.ConvexList[convexId];
                        insertPatch.Parameters["@convexId"].Value = convexId;
                        insertPatch.Parameters["@convexString"].Value = convex.ToString();

                        for (int patchId = 0; patchId < convex.PatchList.Count; patchId++)
                        {
                            Patch patch = convex.PatchList[patchId];
                            insertPatch.Parameters["@patch"].Value = patchId;
                            insertPatch.Parameters["@radius"].Value = patch.Mec.RadiusInRadian * Const.Radian2Arcmin;
                            insertPatch.Parameters["@ra"].Value = patch.Mec.Vector.RA;
                            insertPatch.Parameters["@dec"].Value = patch.Mec.Vector.Dec;
                            insertPatch.Parameters["@x"].Value = patch.Mec.Vector.X;
                            insertPatch.Parameters["@Y"].Value = patch.Mec.Vector.Y;
                            insertPatch.Parameters["@z"].Value = patch.Mec.Vector.Z;
                            insertPatch.Parameters["@c"].Value = patch.Mec.Cos0;
                            int onerow = insertPatch.ExecuteNonQuery();
                            if (onerow != 1) throw new ApplicationException
                                ("SqlIO.Write(Region,...): AFFECTED ROWS NOT ONE IN INSERT PATCH");
                        }
                    }
                }

                #endregion
                #region Insert arcs to RegionArcs...

                using (SqlCommand insertArc = new SqlCommand("", this.sqlconn))
                {
                    insertArc.CommandText = @"INSERT RegionArcs ([regionId],[convexId],[halfspaceId],[patch],[state],[draw],[ra1],[dec1],[ra2],[dec2],[x],[y],[z],[c],[length])
                                                         VALUES (@regionID, @convexID, @halfspaceID, @patch, @state, @draw, @ra1, @dec1, @ra2, @dec2, @x,@y,@z,@c, @length)";
                    insertArc.Transaction = transaction;
                    insertArc.Parameters.Add("@regionId", SqlDbType.BigInt);
                    insertArc.Parameters.Add("@convexId", SqlDbType.BigInt);
                    insertArc.Parameters.Add("@halfspaceId", SqlDbType.BigInt);
                    insertArc.Parameters.Add("@patch", SqlDbType.Int);
                    insertArc.Parameters.Add("@state", SqlDbType.VarChar);
                    insertArc.Parameters.Add("@draw", SqlDbType.Float);
                    insertArc.Parameters.Add("@ra1", SqlDbType.Float);
                    insertArc.Parameters.Add("@dec1", SqlDbType.Float);
                    insertArc.Parameters.Add("@ra2", SqlDbType.Float);
                    insertArc.Parameters.Add("@dec2", SqlDbType.Float);
                    insertArc.Parameters.Add("@x", SqlDbType.Float);
                    insertArc.Parameters.Add("@y", SqlDbType.Float);
                    insertArc.Parameters.Add("@z", SqlDbType.Float);
                    insertArc.Parameters.Add("@c", SqlDbType.Float);
                    insertArc.Parameters.Add("@length", SqlDbType.Float);
                    //insertArc.Parameters.Add("@arcId", SqlDbType.BigInt);

                    // fill them up
                    insertArc.Parameters["@regionId"].Value = regionId;
                    insertArc.Parameters["@halfspaceId"].Value = -1;
                    insertArc.Parameters["@draw"].Value = 1;
                    for (int convexId = 0; convexId < r.ConvexList.Count; convexId++)
                    {
                        Convex convex = r.ConvexList[convexId];
                        insertArc.Parameters["@convexId"].Value = convexId;

                        for (int patchId = 0; patchId < convex.PatchList.Count; patchId++)
                        {
                            Patch patch = convex.PatchList[patchId];
                            insertArc.Parameters["@patch"].Value = patchId;
                            foreach (Arc arc in patch.ArcList)
                            {
                                insertArc.Parameters["@ra1"].Value = arc.Point1.RA;
                                insertArc.Parameters["@dec1"].Value = arc.Point1.Dec;
                                insertArc.Parameters["@ra2"].Value = arc.Point2.RA;
                                insertArc.Parameters["@dec2"].Value = arc.Point2.Dec;
                                insertArc.Parameters["@x"].Value = arc.Circle.Vector.X;
                                insertArc.Parameters["@Y"].Value = arc.Circle.Vector.Y;
                                insertArc.Parameters["@z"].Value = arc.Circle.Vector.Z;
                                insertArc.Parameters["@c"].Value = arc.Circle.Cos0;
                                insertArc.Parameters["@length"].Value = arc.Length;
                                // try to check if a hole
                                if (arc.Circle.Cos0 < 0 && arc.Point1 == arc.Point2)
                                    insertArc.Parameters["@state"].Value = 1; // 'hole'
                                else
                                    insertArc.Parameters["@state"].Value = 2; // 'root'
                                int onerow = insertArc.ExecuteNonQuery();
                                if (onerow != 1) throw new ApplicationException
                                    ("SqlIO.Write(Region,...): AFFECTED ROWS NOT ONE IN INSERT ARC");
                            }
#if false
                            // dump bounding circle as well?
                            if (false)
                            {
                                double ra, dec;
                                Cartesian.Cartesian2Radec(patch.Mec.PointWest(), out ra, out dec);
                                insertArc.Parameters["arcId"].Value = patch.ArcList.Count;
                                insertArc.Parameters["@ra1"].Value = ra;
                                insertArc.Parameters["@dec1"].Value = dec;
                                insertArc.Parameters["@ra2"].Value = ra;
                                insertArc.Parameters["@dec2"].Value = dec;
                                insertArc.Parameters["@x"].Value = patch.Mec.Vector.X;
                                insertArc.Parameters["@Y"].Value = patch.Mec.Vector.Y;
                                insertArc.Parameters["@z"].Value = patch.Mec.Vector.Z;
                                insertArc.Parameters["@c"].Value = patch.Mec.Cos0;
                                insertArc.Parameters["@length"].Value = 2 * Math.PI * Math.Sin(patch.Mec.RadiusInRadian * Const.Radian2Arcmin);
                                insertArc.Parameters["@state"].Value = 3; // 'bounding circle'
                                int onerow = insertArc.ExecuteNonQuery();
                                if (onerow != 1) throw new ApplicationException
                                    ("SqlIO.Write(Region,...): AFFECTED ROWS NOT ONE IN INSERT ARC");
                            }
#endif
                        }
                    }
                }

                #endregion

                transaction.Commit();
            }
            return id;
        }

        /// <summary>
        /// Close SQL connection
        /// </summary>
        public void Close()
        {
            this.sqlconn.Close();
        }

        /// <summary>
        /// Dispose connection - close it beforehand if not closed
        /// </summary>
        public void Dispose()
        {
            if (this.sqlconn.State != ConnectionState.Closed)
                this.sqlconn.Close();
            this.sqlconn.Dispose();
        }

        /// <summary>
        /// Revision from CVS
        /// </summary>
        public static string Revision
        {
            get { return "$Revision: 1.1.1.1 $"; }
        }

        #region IDisposable Members

        void IDisposable.Dispose()
        {
            this.Dispose();
        }

        #endregion
    }
}

#region Revision History
/* Revision History

        $Log: SqlIO.cs,v $
        Revision 1.1.1.1  2006/11/14 02:56:39  gupchup
        no message

        Revision 1.6  2006/05/17 13:43:41  budavari
        SQL writer finished

        Revision 1.5  2006/05/09 21:02:12  budavari
        Adding SQL writer for Region

        Revision 1.4  2006/04/24 15:21:45  budavari
        ReadRegion w/ custom db prefix added

        Revision 1.3  2006/04/19 16:52:13  budavari
        EVerything is now CLS compliant

        Revision 1.2  2006/03/10 15:45:49  budavari
        Added back SqlIO into project

        Revision 1.1  2006/02/28 16:36:59  budavari
        Module has IO related classes

        Revision 1.1  2006/01/24 15:56:05  budavari
        Re-structured directory layout to accommodate more projects

        Revision 1.3  2005/12/20 02:25:41  budavari
        Normalize vectors even if read from Halfspace table

        Revision 1.2  2005/12/16 19:48:57  budavari
        Fixed a bug ReadRegion() - convexid's are random Halfspace table

        Revision 1.1  2005/12/14 22:36:09  budavari
        SQL Input working

*/
#endregion