using framework_business.handlers;
using Google.Apis.Sheets.v4;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace framework_business.classes
{
    public class xGoogleSheetData
    {
        static SpreadsheetsResource.ValuesResource _googleSheetValues;
        static GoogleSheetsHelper googleSheetsHelper;

        public static DataTable ReadGoogleSheetData(string applicationName,string spreadSheetId,string sheetName,string startColumn,string endColumn,int headingIndex)
        {
            googleSheetsHelper = new GoogleSheetsHelper(applicationName);
            var range = $"{sheetName}!{startColumn}:{endColumn}";
            _googleSheetValues = googleSheetsHelper.Service.Spreadsheets.Values;
            var request = _googleSheetValues.Get(spreadSheetId, range);
            var response = request.Execute();
            return IListToDatatable(response.Values.ToList(), headingIndex);
        }

        /// <summary>
        /// Converts ilist object to datatabable based on index
        /// </summary>
        /// <param name="values"></param>
        /// <param name="headingIndex">Starts with 0 index</param>
        /// <returns></returns>
        public static DataTable IListToDatatable(IList<IList<object>> values,int headingIndex)
        {
            DataTable dtData = new DataTable();
            try
            {
                for (int i = 0; i < values[headingIndex].Count; i++)
                {
                    if (values[headingIndex][i] == null || string.IsNullOrEmpty(values[headingIndex][i].ToString())) continue;
                    dtData.Columns.Add(values[headingIndex][i].ToString());
                }

                DataRow drNew;
                foreach (var value in values)
                {
                    if (values.IndexOf(value) <= headingIndex || value?.Count == 0) continue;
                    drNew = dtData.NewRow();
                    for (int i = 0; i < dtData.Columns.Count; i++)
                    {
                        if (value.ElementAtOrDefault(i) == null) continue;
                        drNew[i] = Convert.ToString(value[i]);
                    }
                    dtData.Rows.Add(drNew);
                }
            }
            catch (Exception ex)
            {
                //throw;
            }
            
            return dtData;
        }

        public static DataTable ToDataTable<T>(List<T> items)
        {
            DataTable dataTable = new DataTable(typeof(T).Name);
            //Get all the properties
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                //Setting column names as Property names
                dataTable.Columns.Add(prop.Name);
            }
            foreach (T item in items)
            {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {
                    //inserting property values to datatable rows
                    values[i] = Props[i].GetValue(item, null);
                }
                dataTable.Rows.Add(values);
            }
            //put a breakpoint here and check datatable
            return dataTable;
        }
    }
}