using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Web;

using System.Configuration;
using System.Data;
using System.Xml;
using System.Xml.Linq;
using System.Net;
using System.IO;
using ApnTools;
using System.Data.Sql;

//
// Sample code for loading permit data into a point layer using the ParcelStream API. 
//
// This code uses the following external classes:
//      DataReader: The purpose of this class is to hide the form of the incoming permit data.  It may be in a csv file or 
//                  a database, or it may even come from a webservice api to a permit application.
//      ApnResolver: This contains the logging of resolving an APN to a parcel or property record.
//
// This code assumes that a layer called LG8DEMO.JWILSON/PERMITS has been created already with fields that correspond with the
// DataTable created below.
//
// TODO: Make the layer name a constant declared near the top of the program.
// TODO: Make the Book/Page length of the APN a constant that is passed to ApnResolver.
//
namespace PermitSample
{
    class Program
    {
        // GetXML makes the http request in sQueryString and returns the response.
        private static XmlDocument GetXML(string sQueryString)
        {
            HttpWebRequest oRequest = (HttpWebRequest)WebRequest.Create(sQueryString);
            HttpWebResponse oResponse = (HttpWebResponse)oRequest.GetResponse();
            XmlDocument oResponseXML = new XmlDocument();
            oResponseXML.Load(oResponse.GetResponseStream());
            return (oResponseXML);
        }

        // ParseDate takes a string and turns it into a DateTime if possible, otherwise it returns DBNull.
        private static object ParseDate(string s)
        {
            DateTime rslt;
            DateTime.TryParse(s, out rslt);
            if (rslt.Ticks == 0) return (object)DBNull.Value;
            return rslt;
        }

        // BuildGeo takes a result returned from the LookupApn function and formats the data into a string that is parsed
        // in WriteRecords to create the GeoLink to the geographic info.
        private static string BuildGeo(ApnResult rslt) 
        {
            if (rslt.Match_Type == MatchType.Parcel)
                return @"*SS.Prop.ParcelDetail/ParcelDetail|LOCATION_ID|" + rslt.Location_Id;
            else if (rslt.Match_Type == MatchType.Property)
                return @"*SS.Prop.PropertyDetail/PropertyDetail|TAX_ID|" + rslt.Tax_Id;
            else if (rslt.Match_Type == MatchType.MultiParcel)
                return "+" + "POINT(" + rslt.Longitude + " " + rslt.Latitude + ")";
            else
                return null;
        }

        // BuildMatchedTo tranlates the MatchType result to a string for the ApnMatchedTo database field.
        private static string BuildMatchedTo(ApnResult rslt)
        {
            if (rslt.Match_Type == MatchType.Parcel)
                return "PARCEL";
            else if (rslt.Match_Type == MatchType.Property)
                return "PROPERTY";
            else if (rslt.Match_Type == MatchType.MultiParcel)
                return "MULTI";
            else if (rslt.Match_Type == MatchType.None)
                return "NONE";
            else
                return "ERROR";
        }

        // BuildMatchedMulti creates the value for the ApnMatchedMulti database field based on the ApnResult
        private static string BuildMatchedMulti(ApnResult rslt)
        {
            if (rslt.Match_Type == MatchType.MultiParcel)
                return rslt.Location_Id;
            else
                return null;
        }

        static void Main(string[] args)
        {
            //
            // Set up a file to log the results of resolving the apns
            //
            StreamWriter logFS = new StreamWriter(@"\\digmap.local\Users\jwilson\My Documents\apnLog.txt");
            int exactMatch = 0;
            int partialMatch = 0;
            int noMatch = 0;

            string sCurrent = "Starting";
            // GetSIK / InitSession
            // Create Version Entry
            // build a table of transactions to create the DMP permit layer
            //      for each record of the local permit data 
            //          add a row to table using that data
            //          use ApnResolver to get a Location_Id and/or a Tax_Id to add to the row
            // for each row in table
            //      construct the XML for the transaction from table
            //      invoke transaction.aspx
            try
            {
                sCurrent = "Calling getSIK";
                // getSIK  - this does the authentication that creates a "Session Initialization Key"
                // server IP must be registered with DMP for this to work
                string sQueryString = "http://dc1.parcelstream.com/admin/getSIK.aspx?login=jwilson&account=LG8Demo";
                XmlDocument oResponseXML = GetXML(sQueryString);

                XmlNode oNode = oResponseXML.SelectSingleNode("Response/Success");
                if (oNode == null) throw (new Exception("Could not authenticate: " + oResponseXML.SelectSingleNode("Response/Error/@message").Value));
                // Get Authentication site and key
                //  Success response looks like:
                // <Response>
                //     <Success message="/dc1/_T195/3af82f3b-d8b5-4353-8fdd-edb2206ec300-100850_275307" /> 
                // </Response>
                // message format is "/<data center>/<TempFolder>/<Guid>"
                // need to separate the components to generate the input to InitSession
                string[] sAuth = oNode.Attributes["message"].Value.Split(new char[] { '/' });
                string sDC = sAuth[1];
                string sKey = sAuth[2] + "/" + sAuth[3];  //   /<TempFolder>/<Guid>
                string sFold = sAuth[2];

                // Now construct InitSession call
                // InitSession can only be called once per "Session Initialization Key"
                // the idea is only a trusted server can call getSIK but then the client app calls initSession
                // InitSession returns "SS_CANDY" that can be used as a cookie or as part of the request string
                // Request
                // http://dc1.parcelstream.com/_T195/InitSession.aspx?sik=_T195/3af82f3b-d8b5-4353-8fdd-edb2206ec300-100850_275307

                // Successful Response
                // <Response status="success">
                //   <Results>
                //     <Data>
                //       <Row Candy="a2JpPg5VY2ib42psn-ducOjNjoHNPbZxE1tCUg~~" Domains="http://dc1.parcelstream.com/" SessionId="285219433" UserId="100850_275307" Login="buyers1" Group="ALL" Account="BUYER_SS" /> 
                //     </Data>
                //   </Results>
                // </Response>

                // Error Response
                // <Response>
                //   <Error code="1" id="-1" message="Trial Key is invalid!" /> 
                // </Response>


                sCurrent = "Calling InitSession";

                sQueryString = string.Format("http://{0}.parcelstream.com/{1}/InitSession.aspx?sik={2}", sDC, sFold, sKey);
                oResponseXML = GetXML(sQueryString);

                oNode = oResponseXML.SelectSingleNode("Response/Results");
                if (oNode == null) throw (new Exception("Could not Initialize Session: " + oResponseXML.SelectSingleNode("Response/Error/@message").Value));
                XmlNode oRowNode = oNode.SelectSingleNode("//Row");
                string sCandy = oRowNode.Attributes["Candy"].Value;
                string[] sHosts = oRowNode.Attributes["Domains"].Value.Split(new char[] { ',', ';' });  // used to indicate what data centers are available for this account
                // if an account is setup to have replicated data in multiple datacenters then this could be a list of domains and the client 
                // could enhance availability and performance by testing and choosing based on latency and availability
                // this is typically not required because the dns service can automatically select best data center
                // just use the first host in the list

                // for each row in input data resolve the apn and add info to table
                String version;
                string apn;

                DataReader reader = new DataReader();
                if (reader != null)
                {

                    // Create a new version entry - required to use transaction engine
                    sQueryString = String.Format("{0}version.aspx?&action=create&recseq=0&SS_CANDY={1}&name=InitialLoad&desc=loaded by APNLoader Program from master watch list, MasterList.csv", sHosts[0], sCandy);
                    oResponseXML = GetXML(sQueryString);

                    version = ""; // versionResults.Root.Attribute("versionId").Value.ToString();
                    if (oResponseXML.SelectSingleNode("Response/@versionId") != null)
                    {
                        version = oResponseXML.SelectSingleNode("Response/@versionId").Value;
                    }
                    else
                    {
                        throw new ApplicationException("Unable to get a version id.");
                    }

                }
                else throw new ApplicationException("Could not open CSV");

                DataTable table = new DataTable();
                table.Columns.Add("_GEOID", typeof(string));
                table.Columns.Add("APN", typeof(string));
                table.Columns.Add("APNMatchTo", typeof(string));
                table.Columns.Add("APNMatchMulti", typeof(string));
                table.Columns.Add("APNMatchAccuracyFt", typeof(int));
                table.Columns.Add("PermitNum", typeof(string));
                table.Columns.Add("Issued", typeof(DateTime));
                table.Columns.Add("Expired", typeof(DateTime));
                table.Columns.Add("PermitType", typeof(string));
                table.Columns.Add("PermitStatus", typeof(string));
                table.Columns.Add("Fees", typeof(string));
                table.Columns.Add("LocationId", typeof(string));
                table.Columns.Add("TaxId", typeof(string));
                table.Columns.Add("PermitOwner1", typeof(string));
                table.Columns.Add("PermitOwner2", typeof(string));
                table.Columns.Add("PermitAddress", typeof(string));
                table.Columns.Add("PermitCityAndState", typeof(string));
                table.Columns.Add("PermitZipCode", typeof(string));

                int recseq = 1;

                ApnParams p = new ApnParams();
                p.sHosts = sHosts;
                p.sCandy = sCandy;
                p.ApnLookupLayer = "SS.Prop.APNLookup/APNLookup";
                p.ParcelLayer = "SS.Prop.ParcelDetail/ParcelDetail";
                p.PropertyLayer = "SS.Prop.PropertyDetail/PropertyDetail";
                p.fipsCodes = new string[] {"06059"};

                ApnTools.ApnResolver apnAnalyzer = new ApnTools.ApnResolver(p, logFS, false);

                //
                // Set up a dictionary collection to hold the apns we've already resolved with the result
                //
                Dictionary<string, ApnResult> apns = new Dictionary<string, ApnResult>();

                int count = 0;  // for debugging
                while (count<1000 && reader.ReadNext())
                {
                    //count++;
                    apn = reader["site_APN"];

                    sCurrent = "Looking up APN: " + apn;

                    ApnResult rslt;
                    if (!apns.ContainsKey(apn))
                    {
                        rslt = apnAnalyzer.lookupApn(apn);
                        if (rslt.Match_Type == MatchType.Parcel || rslt.Match_Type == MatchType.Property)
                            exactMatch++;
                        else if (rslt.Match_Type == MatchType.MultiParcel)
                            partialMatch++;
                        else
                            noMatch++;
                                
                        apns[apn] = rslt;
                    }
                    else
                        rslt = apns[apn];

                    sCurrent = "parsing response for APN: " + apn;
                    //
                    // If the apn could not be resolved to a location, locate the permit in a location in the hills 
                    // outside the city so it can be manually located using Edit Geometry.
                    //
                    if (rslt.Match_Type == MatchType.None)
                    {
                        table.Rows.Add(
                        BuildGeo(new ApnResult(33.5355, -117.65599, 0, "", "")), // _GEOID
                        apn,                              // APN
                        "NONE",                           // APNMatchedTo
                        null,                             // APNMatchMulti
                        null,                             // APNMatchAccuracyFt   // not implemented
                        reader["permit_no"],              // PermitNum
                        ParseDate(reader["issued"]),      // Issued
                        ParseDate(reader["expired"]),     // Expired
                        reader["permittype"],             // PermitType
                        reader["status"],                 // PermitStatus
                        reader["fees_charged"],           // Fees
                        null,                             // LocationId
                        null,                             // TaxId
                        null,                             // PermitOwner1
                        null,                             // PermitOwner2
                        null,                             // PermitAddress
                        null,                             // PermitCityAndState
                        null                              // PermitZipCode
                        );
                    }
                    else
                    {
                        string locId = null;
                        if (rslt.Match_Type == MatchType.Parcel)
                            locId = rslt.Location_Id;

                        table.Rows.Add(
                        BuildGeo(rslt),                             // _GEOID specifies source for copy geometry action
                        rslt.Trimmed_Apn,                           // APN
                        BuildMatchedTo(rslt),                       // APNMatchedTo
                        BuildMatchedMulti(rslt),                    // APNMatchMulti
                        rslt.Accuracy,                              // APNMatchAccuracyFt   // Not Implemented
                        reader["permit_no"],                        // PermitNum
                        ParseDate(reader["issued"]),                // Issued
                        ParseDate(reader["expired"]),               // Expired
                        reader["permittype"],                       // PermitType
                        reader["status"],                           // PermitStatus
                        reader["fees_charged"],                     // Fees
                        locId,                                      // LocationId
                        rslt.Tax_Id,                                // TaxId
                        null,                                       // PermitOwner1
                        null,                                       // PermitOwner2
                        null,                                       // PermitAddress
                        null,                                       // PermitCityAndState
                        null                                        // PermitZipCode
                        );
                    }
                    if (table.Rows.Count == 100)
                    {
                        sCurrent = "Writing DataTable";
                        writeRecords(@"LG8DEMO.JWILSON/PERMITS", table, version, sHosts[0], ref recseq, sCandy);// passed by ref or value?
                        Console.WriteLine("Written up to recseq:" + recseq);
                    }

                }
                if (table.Rows.Count > 0) writeRecords(@"LG8DEMO.JWILSON/PERMITS", table, version, sHosts[0], ref recseq, sCandy);
                sQueryString = String.Format("{0}version.aspx?&action=publish&versionId={2}&SS_CANDY={1}", sHosts[0], sCandy, version);
                oResponseXML = GetXML(sQueryString);
                logFS.WriteLine("Exact matches {0}, Partial Matches {1}, No Matches {2}", exactMatch, partialMatch, noMatch);
                logFS.Close();

            }
            catch (Exception ex)
            {
                // TODO: if version created try to destroy the version
                logFS.WriteLine("APNs found {0}, APNs not found {1}", exactMatch, noMatch);
                logFS.Close();
                Console.WriteLine(string.Format("While: {0}, Message: {1}", sCurrent, ex.Message));
            }
        }
        public static void writeRecords(
            String layer,    // resource name you are writing to eg. LG8Sprint1.gwade/WatchList
            DataTable table, // data you want to write – column names and type same as destination table
            String version,  // versionId that you get back from version.aspx
            String sHost,    // URL to use for transactions – returned by InitSession
            ref int recseq,  // input and output – initially set to start reseq and incremented here
            String sAuth)    // SS_CANDY returned by InitSession
        {
            //<Request versionId="7545463_1">
            // <Insert Resource="LG8Sprint1.gwade/WatchList">
            //  <!-- Row Action Style -->
            //  <Row _RECSEQ="1" GEOMETRY="{R1.GEOMETRY}" SOURCE_ADDR="22 MAIN ST" SOURCE_CITY="CHARLOTTE" >
            //    <GetByKey Name="R1" DataSource="SS.Prop.ParcelDetail/ParcelDetail" KeyFields="_DMP_ID" KeyValues="4325325_3526" Fields="GEOMETRY" Host="dc1.parcelstream.com"  />
            //  </Row>
            // 
            // </Insert>

            /// </Request>
            /// 
            string sCurrent;
            XElement Dom = new XElement("Request");
            Dom.Add(new XAttribute("versionId", version));
            XElement iDom = new XElement("Insert");
            iDom.Add(new XAttribute("resource", layer));
            Dom.Add(iDom);
            XElement Geolink;
            XElement Row;

            foreach (DataRow r in table.Rows)
            {
                //Create Seed Row record
                Row = new XElement("Row");
                Row.Add(new XAttribute("_RECSEQ", recseq));
                iDom.Add(Row);
                // for each attribute set XML attribute, if GeoID set GetByKey
                sCurrent = "Constructing Transaction XML for record " + recseq;
                foreach (DataColumn fld in table.Columns)
                {
                    if (fld.ColumnName == @"_GEOID")
                    {   /// add Geolink - this allows the geometry to be copied to this record from another source
                        /// must be column called _GEOID in the data table with value in the format <layername>|<key>|<keyvalue>
                        /// for example SS.Prop.ParcelDetail/ParcelDetail|_DMP_ID|3531_1204472243 will copy the parcel geometry to the new record
                        string s = r[fld.Ordinal].ToString();
                        if (!String.IsNullOrEmpty(s))
                        {
                            if (s[0].Equals('*'))
                            {
                                Geolink = new XElement("GetByKey");
                                Geolink.Add(new XAttribute("Name", "R1"));
                                String[] src = s.Split('|');
                                if (src.Length != 3)
                                {
                                    throw new ApplicationException(String.Format("Invalid geolink {0} at record '{1}'", s, recseq));
                                }
                                Geolink.Add(new XAttribute("Datasource", src[0].Substring(1))); // skip the "*" beginning the string
                                Geolink.Add(new XAttribute("KeyFields", src[1]));
                                Geolink.Add(new XAttribute("KeyValues", src[2]));
                                Geolink.Add(new XAttribute("Fields", "GEOMETRY"));
                                Geolink.Add(new XAttribute("Host", "dc1.parcelstream.com"));
                                Geolink.Add(new XAttribute("ReturnGeoType", "3"));
                                Row.Add(Geolink);
                                Row.Add(new XAttribute("GEOMETRY", "{R1.GEOMETRY}"));
                            }
                            else if (s[0].Equals('+'))
                            {
                                Row.Add(new XAttribute("GEOMETRY", s.Substring(1))); // skip the "+" beginning the string
                            }
                            else
                                throw new ApplicationException(String.Format("Invalid GEOID {0} at record '{1}'", s, recseq));
                        }
                    }
                    else
                    {
                        Row.Add(new XAttribute(fld.ColumnName, r[fld.Ordinal].ToString()));
                    }

                }
                recseq++;

            }
 
            sCurrent = "Posting transaction batch for up to record number " + recseq;
            HttpWebRequest Req = (HttpWebRequest)WebRequest.Create(sHost + "Transaction.aspx");

            Req.Method = "POST";
            String postData = String.Format("SS_CANDY={0}&xml={1}&_dmp_debug=true", sAuth, HttpUtility.UrlEncode(Dom.ToString()));

            byte[] byteArray = Encoding.UTF8.GetBytes(postData);
            Req.ContentType = "application/x-www-form-urlencoded";
            Req.ContentLength = byteArray.Length;

            Stream dataStream = Req.GetRequestStream();
            dataStream.Write(byteArray, 0, byteArray.Length);
            dataStream.Close();
            WebResponse oResponse = Req.GetResponse();
            XmlDocument oResponseXML = new XmlDocument();
            oResponseXML.Load(oResponse.GetResponseStream());
            // report load results here
            XmlNode oNode = oResponseXML.SelectSingleNode("Response/Insert");
            if (oNode == null)
            {
                throw new ApplicationException(sCurrent + ":" + oResponseXML.SelectSingleNode("Response/Error/@message").Value);
            }
            oResponse.Close();
            table.Clear();  // empty the table
        }
    }
}