using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;
using System.Globalization;

// For the SQL Server integration
using Microsoft.SqlServer.Server;

// Other things we need for WebRequest
using System.Net;
using System.Text;
using System.IO;
using System.Linq;
using Dapper;
using Newtonsoft.Json.Linq;



namespace SqlWebRequest
{
    public partial class Functions
    {
        // Function to return a web URL as a string value.
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
        public static void GET(SqlString uri, SqlString username, SqlString passwd, String Table_Name, String Connection_String, Boolean Create_Table)
        {
            // The SqlPipe is how we send data back to the caller
            SqlPipe pipe = SqlContext.Pipe;
            String document;

            // Set up the request, including authentication
            WebRequest req = WebRequest.Create(Convert.ToString(uri));
            if (Convert.ToString(username) != null & Convert.ToString(username) != "")
            {
                req.Credentials = new NetworkCredential(
                    Convert.ToString(username),
                    Convert.ToString(passwd));
            }
            ((HttpWebRequest)req).UserAgent = "CLR web client on SQL Server";

            // Fire off the request and retrieve the response.
            // We'll put the response in the string variable "document".
            WebResponse resp = req.GetResponse();
            req.Timeout = 50000;
            Stream dataStream = resp.GetResponseStream();
            StreamReader rdr = new StreamReader(dataStream);
            document = (String)rdr.ReadToEnd();

            // Close up everything...
            rdr.Close();
            dataStream.Close();
            resp.Close();

            // .. and return the output to the caller.
            // return (document);
            Parsing(document, Table_Name, Connection_String, Create_Table);
        }


        private static void Parsing(String Json_String, String Table_Name, String Connection_String, Boolean Create_Table)
        {
            var json = JObject.Parse(Json_String);

            var tableName = Table_Name;
            var colums = json["response"]["result"]["column_order"];
            var rows = json["response"]["result"]["rows"];
            var connectionString = Connection_String; //@"Data Source=192.168.1.52;Initial Catalog=TestDb;User ID=sa; Password=zxc12EE";

            using (IDbConnection db = new SqlConnection(connectionString))
            {
                if(Create_Table == true)
                {
                    db.Execute($@"
                    create table {tableName}
                    (
                        {string.Join("," + Environment.NewLine, colums.Select(colum => $"[{colum}] NVARCHAR(MAX)"))}
                    )
                  ", commandTimeout: 600);

                    db.Execute($@"
                    {string.Join(Environment.NewLine,
                       rows.Select(
                       row => $"INSERT INTO {tableName} VALUES ({string.Join(", ", row.Select(r => $"N'{r}'"))});"))}
                 ", commandTimeout: 600);

                }

                db.Execute($@"
                    {string.Join(Environment.NewLine,
                    rows.Select(
                        row => $"INSERT INTO {tableName} VALUES ({string.Join(", ", row.Select(r => $"N'{r}'"))});"))}
                ", commandTimeout: 600);

            }
        }
    }
}