/*
 * Postarc
 *
 * Author:
 * Christian Lins <christian.lins@intevation.de>
 *
 * Copyright:
 * Copyright (C) 2012 Intevation GmbH <http://www.intevation.de/>
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ESRI.ArcGIS.Geodatabase;
using ESRI.ArcGIS.Geometry;
using Npgsql;

namespace Postarc.Catalog
{
    public class FeatureClassExporter
    {
        protected IFeatureClass featureClass;

        public FeatureClassExporter(IFeatureClass featClass)
        {
            this.featureClass = featClass;
        }

        protected string CreateTableDefinition(IFields fields, string tableName)
        {
            StringBuilder buf = new StringBuilder();
            buf.Append("CREATE TABLE ");
            buf.Append(tableName);
            buf.Append(" ( ");

            bool hasOID = false;
            for (int n = 0; n < fields.FieldCount; n++)
            {
                IField field = fields.get_Field(n);
                string fieldType = GetSQLType(field.Type);
                if (fieldType != null)
                {
                    if (field.Name.Equals(Postarc.Feature.FeatureCursor.ARCGIS_OID_COLUMN, StringComparison.InvariantCultureIgnoreCase))
                    {
                        hasOID = true;
                        buf.Append(Postarc.Feature.FeatureCursor.POSTARC_OID_COLUMN);
                    }
                    else
                    {
                        buf.Append(field.Name);
                    }
                    buf.Append(" ");
                    buf.Append(fieldType);
                    buf.Append(",");
                }

                if (!hasOID && n == fields.FieldCount - 1)
                {
                    buf.Append(Postarc.Feature.FeatureCursor.POSTARC_OID_COLUMN);
                    buf.Append(" BIGSERIAL NOT NULL,");
                }
            }

            buf.Append("PRIMARY KEY(");
            buf.Append(Postarc.Feature.FeatureCursor.POSTARC_OID_COLUMN);
            buf.Append(")");
            buf.Append(" )");
            return buf.ToString();
        }

        protected string CreateGeoColumnDef(string tableName)
        {
            StringBuilder buf = new StringBuilder();

            buf.Append("SELECT AddGeometryColumn('public', '");
            buf.Append(tableName);
            buf.Append("', '");
            buf.Append(Postarc.Feature.FeatureCursor.POSTARC_SHAPE_COLUMN);
            buf.Append("', -1, 'POINT', 2)");

            return buf.ToString();
        }

        protected string GetSQLType(esriFieldType type)
        {
            switch (type)
            {
                case esriFieldType.esriFieldTypeInteger:
                    return "INT";
        //        case esriFieldType.esriFieldTypeString:
        //            return "TEXT";
            }
            return null;
        }

        protected string CreateInsertStatement(string tableName, object[] row)
        {
            StringBuilder buf = new StringBuilder();
            buf.Append("INSERT INTO ");
            buf.Append(tableName);
            buf.Append("(");

            List<string> fields = new List<string>();
            for (int n = 0; n < featureClass.Fields.FieldCount; n++)
            {
                IField field = featureClass.Fields.get_Field(n);
                string fieldType = GetSQLType(field.Type);

                if (fieldType != null)
                {
                    fields.Add(field.Name);
                }
                else if (field.Type == esriFieldType.esriFieldTypeGeometry)
                {
                    fields.Add(Postarc.Feature.FeatureCursor.POSTARC_SHAPE_COLUMN);
                }
            }

            for (int n = 0; n < fields.Count; n++)
            {
                buf.Append(fields[n]);
                if (n < fields.Count - 1)
                    buf.Append(",");
            }
            
            buf.Append(") VALUES (");

            List<string> values = new List<string>();
            for (int n = 0; n < row.Length; n++)
            {
                esriFieldType fieldType = featureClass.Fields.get_Field(n).Type;

                if (GetSQLType(fieldType) != null || fieldType == esriFieldType.esriFieldTypeGeometry)
                {
                    values.Add("'" + row[n] + "'");
                }
            }

            for (int n = 0; n < values.Count; n++)
            {
                buf.Append(values[n]);
                if (n < values.Count - 1)
                    buf.Append(",");
            }

            buf.Append(")");
            return buf.ToString();
        }

        protected void CreateTable(PostGISConnection conn, string tableName)
        {
            NpgsqlConnection npgsqlConn = conn.Open();
            string sql;
            NpgsqlCommand cmd;

            sql = CreateTableDefinition(featureClass.Fields, tableName);    
            cmd = new NpgsqlCommand(sql, npgsqlConn);
            cmd.ExecuteNonQuery();

            sql = CreateGeoColumnDef(tableName);
            cmd = new NpgsqlCommand(sql, npgsqlConn);
            cmd.ExecuteNonQuery();
        }

        protected object[] CreateRow(IFeature feature)
        {
            object[] row = new object[feature.Fields.FieldCount];
            for (int n = 0; n < row.Length; n++)
            {
                row[n] = feature.get_Value(n);
                if (row[n] is IGeometry)
                {
                    Postarc.Feature.Point point = new Postarc.Feature.Point(row[n] as IPoint);
                    row[n] = point.GetWKT();
                }
                else if (feature.Fields.get_Field(n).Type == esriFieldType.esriFieldTypeString)
                {
                    row[n] = EscapeString(row[n] as string);
                }
            }
            return row;
        }

        protected string EscapeString(string str)
        {
            str = str.Replace("'", "\'");
            return str;
        }

        protected int WriteRow(string tableName, NpgsqlConnection conn, object[] row)
        {
            string sql = CreateInsertStatement(tableName, row);
            NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
            return cmd.ExecuteNonQuery();
        }

        public int ExportFeatureClass(IQueryFilter filter, PostGISConnection conn, string tableName)
        {
            // FIXME: Enclose in transaction
            CreateTable(conn, tableName);

            NpgsqlConnection npgsqlConn = conn.Open();
            int numExported = 0;

            IFeatureCursor cursor = this.featureClass.Search(filter, true);
            IFeature ifeature;
            while ((ifeature = cursor.NextFeature()) != null)
            {
                object[] row = CreateRow(ifeature);
                WriteRow(tableName, npgsqlConn, row);
                numExported++;
            }

            npgsqlConn.Close();
            return numExported;
        }
    }
}