//	Spludlow Software
//	Copyright © Samuel P. Ludlow 2020 All Rights Reserved
//	Distributed under the terms of the GNU General Public License version 3
//	Distributed WITHOUT ANY WARRANTY; without implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE
//	https://www.spludlow.co.uk/LICENCE.TXT
//	The Spludlow logo is a registered trademark of Samuel P. Ludlow and may not be used without permission
//	v1.14

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

using System.Web;
using System.Data;

namespace Spludlow.MetSat
{
	/// <summary>
	/// Used by the Spludlow MetSat Intranet
	/// </summary>
	public class View
	{
		public static Spludlow.Data.IDAL _Database = Spludlow.Data.DAL.Create("@MetSat");

		public static string _StoreDirectory = Spludlow.Config.Get("MetSat.Store");

		public static Spludlow.Drawing.ThumbNails _ThumbNails = new Drawing.ThumbNails("MetSat.Thumbs");

		/// <summary>
		/// Put supported products first
		/// </summary>
		public static Schema.DataProductsDataTable Products()
		{
			Schema.DataProductsDataTable table = new Schema.DataProductsDataTable();

			Spludlow.Data.IDAL dal = Spludlow.Data.DAL.Create("@MetSat");

			dal.Fill(table, "select * from DataProducts WHERE (Name LIKE '%*') ORDER BY Name");
			dal.Fill(table, "select * from DataProducts WHERE (Name NOT LIKE '%*') ORDER BY Name");

			return table;
		}

		public static DataSet Query(string dataProductId, DateTime startDate, DateTime beforeEndDate)
		{
			DataSet dataSet = new DataSet();

			Schema.DataItemsDataTable itemsTable = new Schema.DataItemsDataTable();
			dataSet.Tables.Add(itemsTable);

			Schema.DataItemFilesDataTable filesTable = new Schema.DataItemFilesDataTable();
			dataSet.Tables.Add(filesTable);
			
			string commandText;

			commandText = "SELECT * FROM DataItems WHERE ((DataProductId = @DataProductId) AND (CaptureTime >= @StartDate) AND (CaptureTime < @BeforeEndDate)) ORDER BY CaptureTime, DataProductId";
			_Database.Fill(itemsTable, commandText, dataProductId, startDate, beforeEndDate);

			commandText = "SELECT DataItemFiles.* FROM DataItems INNER JOIN DataItemFiles ON DataItems.DataItemId = DataItemFiles.DataItemId " +
							"WHERE ((DataProductId = @DataProductId) AND (CaptureTime >= @StartDate) AND (CaptureTime < @BeforeEndDate)) ORDER BY DataItemFiles.DataItemId, DataItemFiles.DataItemFileId";
			_Database.Fill(filesTable, commandText, dataProductId, startDate, beforeEndDate);

			filesTable.Columns.Add("File", typeof(string));
			filesTable.Columns.Add("Thumb", typeof(string));
			filesTable.Columns.Add("FileDirect", typeof(string));

			foreach (Schema.DataItemFilesRow fileRow in filesTable.Rows)
			{
				string storeFilename = Spludlow.Io.FileStore.FilePath(fileRow.DataItemFileId, _StoreDirectory, fileRow.StoreExtention, false);

				string webFilenameDirect = "Generic.aspx?FILE=" + HttpUtility.UrlEncode(storeFilename) + "&MIME=" + HttpUtility.UrlEncode(fileRow.Format);

				string webFilename = webFilenameDirect + "&FILENAME=" + HttpUtility.UrlEncode(fileRow.Filename);

				string thumbWeb = _ThumbNails.WebAndFilePath(storeFilename, false)[0];

				fileRow["File"] = webFilename;
				fileRow["Thumb"] = thumbWeb;
				fileRow["FileDirect"] = webFilenameDirect;
			}

			return dataSet;			
		}
	}
}