using System;
using System.IO;
using System.Data;
using System.Collections;
using System.Text;
using System.Diagnostics;
using System.Drawing;
using System.Drawing.Imaging;

using MySql.Data.MySqlClient;

namespace WmData {
	/// <summary>
	/// WMIT Data File Support
	/// Since filechanges on the local filesystem resulted in application pool restarts,
	/// alternatives were searched for file storage. This database file support extension is 
	/// the result of a balanced decision. The system was first implemented for Oculus v2, handy combined
	/// with a practical exam for RMCR @ University of Amsterdam.
	/// Created 9 december 2007 / Willem Middelkoop
	/// </summary>
	public class WmDataFileSupport  {
		protected WmData data;
		protected WmDataBaseCredentials databaseCredentials;
		protected DataTable fileTable;
		protected MySqlDataAdapter fileTableAdapter;
		protected MySqlConnection adapterConnection;
		protected WmDataIdManager idManager;
		protected string updateBinaryDataCommandText;
		protected string readBinaryDataCommandText; 
		protected static string tableName = "wmdatafile"; 

		public WmDataFileSupport(WmData data, WmDataIdManager idManager, WmDataBaseCredentials databaseCredentials) {
			this.data = data;
			this.databaseCredentials = databaseCredentials;
			this.idManager = idManager;

			// Make sure that the SQL database has a table for this system to operate
			checkDataBase();

			// Create DataSet Table, this one is special and thus manually created.
			createFileTable();

			// Create connection object for datatable updates, used by the data adapter
			adapterConnection = new MySqlConnection(databaseCredentials.getConnectionString());

			// Create the filetable dataadapter, performed by an integrated livelink like function
			createFileTableAdapter();

			// Download all file records into the local dataset except for the files itself.
			fillFileTable();

			// Create SQL Command texts for reading and saving binary data
			updateBinaryDataCommandText = "UPDATE " + databaseCredentials.Catalog + "." + tableName + " SET binarydata=?binarydata where id=?id";
			readBinaryDataCommandText = "SELECT binarydata FROM " + databaseCredentials.Catalog + "." + tableName + " WHERE id=?id";

			// Done loading the file support extension.
		}

		public void saveBinaryData(WmDataFile dataFile, byte[] binaryData) {
			lock (dataFile) {
				MySqlConnection saveConnection = new MySqlConnection(databaseCredentials.getConnectionString());
				MySqlCommand saveCommand = new MySqlCommand(updateBinaryDataCommandText, saveConnection);

				MySqlParameter idParameter = new MySqlParameter("?id", MySqlDbType.Int32);
				idParameter.Value = dataFile.Id;
				saveCommand.Parameters.Add(idParameter);

				MySqlParameter binaryParameter = new MySqlParameter("?binarydata", MySqlDbType.LongBlob);
				binaryParameter.Value = binaryData;
				saveCommand.Parameters.Add(binaryParameter);

				try {
					saveConnection.Open();
					int numRowsAffected = saveCommand.ExecuteNonQuery();
					if (numRowsAffected == 1) {
						Trace.WriteLine("WmDataFileSupport: Successfully saved binary data to database. " );
					}
				} catch (Exception error) {
					Trace.WriteLine("WmDataFileSupport: Error while saving binary data to database. " + error.Message);
				} finally {
					saveConnection.Close();
				}
			}
		}

		public byte[] readBinaryData(WmDataFile dataFile) {
			MySqlConnection readConnection = new MySqlConnection(databaseCredentials.getConnectionString());
			MySqlCommand readCommand = new MySqlCommand(readBinaryDataCommandText, readConnection);

			MySqlParameter idParameter = new MySqlParameter("?id", MySqlDbType.Int32);
			idParameter.Value = dataFile.Id;
			readCommand.Parameters.Add(idParameter);
			
			byte[] binaryData = null;
			try {
				readConnection.Open();
				binaryData = (byte[]) readCommand.ExecuteScalar();	
			} catch (Exception error) {
				Trace.WriteLine("WmDataFileSupport: Error while reading binary data from database. " + error.Message);
			} finally {
				readConnection.Close();
			}
			return binaryData;
		}

	

		public void addWmDataFile(WmDataFile dataFile) {
			lock (fileTable) {
				DataRow row = fileTable.NewRow();
				dataFile.Id = idManager.getNewId(tableName); // get a new id from the Data id Manager
				updateWmDataFileRowFromObject(row, dataFile);
				fileTable.Rows.Add(row);
				updateFileTable();
				row.AcceptChanges();
			}
		}
		public WmDataFile getWmDataFile(int dataFileId) {
			WmDataFile[] dataFileArray = getWmDataFiles("id=" + dataFileId, WmDataFileSortBy.ID, true);
			if (dataFileArray.Length == 1) {
				return dataFileArray[0];
			}
			return null;
		}
		public WmDataFile[] getWmDataFiles(string select, WmDataFileSortBy sortBy, bool sortAsc) {
			ArrayList list = new ArrayList();
			lock (fileTable) {
				foreach (DataRow row in fileTable.Select(select)) {
					if (row["objectinstance"] is DBNull) {
						row["objectinstance"] = getWmDataFileObjectFromDataRow(row);
					}
					list.Add(row["objectinstance"]);
				}
				if (list.Count > 0) {
					WmDataFile.SortBy = sortBy;
					list.Sort();
					if (!sortAsc) {
						list.Reverse();
					}
				}
			}
			return (WmDataFile[])list.ToArray(typeof(WmDataFile));
		}
		public void updateWmDataFile(WmDataFile dataFile) {
			lock (fileTable) {
				DataRow row = getDataRow("id=" + dataFile.Id);
				if (row != null) {
					updateWmDataFileRowFromObject(row, dataFile);
					updateFileTable();
					row.AcceptChanges();
				}
			}
		}
		public void deleteWmDataFile(WmDataFile dataFile) {
            if (dataFile != null) {
                lock (fileTable) {
                    DataRow row = getDataRow("id=" + dataFile.Id);
                    if (row != null) {
                        row.Delete();
                        updateFileTable();
                        fileTable.AcceptChanges();
                    }
                }
            }
		}
		protected WmDataFile getWmDataFileObjectFromDataRow(DataRow row) {
			WmDataFile dataFile = new WmDataFile(this);
			if (!(row["id"] is DBNull)) { dataFile.Id = (int)row["id"];	}
			if (!(row["created"] is DBNull)) { dataFile.Created = (DateTime)row["created"]; }
			if (!(row["lastaccessed"] is DBNull)) { dataFile.LastAccessed = (DateTime)row["lastaccessed"]; }
			if (!(row["lastupdated"] is DBNull)) { dataFile.LastUpdated = (DateTime)row["lastupdated"]; }
			if (!(row["filename"] is DBNull)) { dataFile.Filename = (string)row["filename"]; }
			if (!(row["fileextension"] is DBNull)) { dataFile.FileExtension = (string)row["fileextension"]; }
			if (!(row["contentlength"] is DBNull)) { dataFile.ContentLength = (int)row["contentlength"]; }
			if (!(row["contenttype"] is DBNull)) { dataFile.ContentType = (string)row["contenttype"]; }
			return dataFile;
		}
		protected void updateWmDataFileRowFromObject(DataRow row, WmDataFile objectInstance) {
			row.BeginEdit();
			row["id"] = objectInstance.Id;
			row["created"] = objectInstance.Created;
			row["lastaccessed"] = objectInstance.LastAccessed;
			row["lastupdated"] = objectInstance.LastUpdated;
			row["filename"] = objectInstance.Filename;
			row["fileextension"] = objectInstance.FileExtension;
			row["contentlength"] = objectInstance.ContentLength;
			row["contenttype"] = objectInstance.ContentType;
			row.EndEdit();
		}
		protected DataRow getDataRow(string select) {
			DataRow[] rowArray = fileTable.Select(select);
			if (rowArray.Length > 0) {
				return rowArray[0];
			}
			return null;
		}

		public WmDataFile getNewWmDataFile() {
			WmDataFile newFile = new WmDataFile(this);
			newFile.ContentLength = 0;
			newFile.ContentType = "";
			newFile.Created = DateTime.Now;
			newFile.FileExtension = "";
			newFile.Filename = "untitled";
			newFile.LastUpdated = DateTime.Now;
			newFile.LastAccessed = DateTime.Now;
			return newFile;
		}

		public WmDataFile getNewWmDataFileFromPath(string path) {
			WmDataFile newFile = getNewWmDataFile();
			newFile.Filename = Path.GetFileName(path);
			newFile.FileExtension = Path.GetExtension(path);

			FileInfo fileInfo = new FileInfo(path);
			newFile.ContentLength = Convert.ToInt32(fileInfo.Length);

			FileStream stream = File.OpenRead(path);
			byte[] binaryData = new byte[stream.Length];
			stream.Read(binaryData, 0, binaryData.Length);
			stream.Close();

			// Since we've checked if the file is accesable, it is now save to add it to database, and then, just after that, write the binary (buffered) data
			addWmDataFile(newFile);
			newFile.BinaryData = binaryData;
			return newFile;
		}

		public WmDataFile getNewWmDataFileFromImageAsJpg(Image image, string fileName, long jpgCompression) {
			WmDataFile newFile = getNewWmDataFile();
			newFile.Filename = fileName;
			newFile.FileExtension = Path.GetExtension(fileName);
			newFile.ContentType = "image/jpeg";
		
			EncoderParameters encoderParameters = new EncoderParameters(1);
			encoderParameters.Param[0] = new EncoderParameter( System.Drawing.Imaging.Encoder.Quality, jpgCompression );
			ImageCodecInfo codecInfo = WmDataTools.GetEncoderInfo("image/jpeg");
			
			MemoryStream stream = new MemoryStream();
			image.Save(stream, codecInfo,encoderParameters);
			byte[] binaryData = stream.ToArray();
			stream.Close();
			
			newFile.ContentLength = binaryData.Length;

			addWmDataFile(newFile);
			newFile.BinaryData = binaryData;
			return newFile;
		}

		public WmDataFile getNewWmDataFileFromImageAsPng(Image image, string fileName) {
			WmDataFile newFile = getNewWmDataFile();
			newFile.Filename = fileName;
			newFile.FileExtension = Path.GetExtension(fileName);
			newFile.ContentType = "image/png";

			MemoryStream stream = new MemoryStream();
			image.Save(stream, ImageFormat.Png);
			byte[] binaryData = stream.ToArray();
			stream.Close();

			newFile.ContentLength = binaryData.Length;

			addWmDataFile(newFile);
			newFile.BinaryData = binaryData;
			return newFile;
		}
		

		protected void createFileTableAdapter() {
			// Used to generate the SQL Command more effeciently:
			ArrayList columnNames = new ArrayList();
			ArrayList columnDbTypes = new ArrayList();

			columnNames.Add("id");
			columnDbTypes.Add(MySqlDbType.Int32);
			
			columnNames.Add("created");
			columnDbTypes.Add(MySqlDbType.DateTime);

			columnNames.Add("lastaccessed");
			columnDbTypes.Add(MySqlDbType.DateTime);

			columnNames.Add("lastupdated");
			columnDbTypes.Add(MySqlDbType.DateTime);
			
			columnNames.Add("filename");
			columnDbTypes.Add(MySqlDbType.VarChar);

			columnNames.Add("fileextension");
			columnDbTypes.Add(MySqlDbType.VarChar);

			columnNames.Add("contentlength");
			columnDbTypes.Add(MySqlDbType.Int32);

			columnNames.Add("contenttype");
			columnDbTypes.Add(MySqlDbType.VarChar);

			#region Select Command
			string selectSql = "SELECT #PROPERTIES# FROM " + databaseCredentials.Catalog + "." + tableName;
			string propTmp = "";
			for (int i = 0; i < columnNames.Count; i++) {
				propTmp += " " + tableName + "." + columnNames[i] + ",";
			}
			propTmp = propTmp.Substring(0, propTmp.Length - 1);// Remove the last (obsolete) comma
			selectSql = selectSql.Replace("#PROPERTIES#", propTmp);
			#endregion

			fileTableAdapter = new MySqlDataAdapter(selectSql, adapterConnection);
			fileTableAdapter.RowUpdated += new MySqlRowUpdatedEventHandler(fileTableAdapter_RowUpdated);
			#region Update Command
			MySqlCommand updateCommand = new MySqlCommand();
			updateCommand.Connection = adapterConnection;
			string updatePropTmp = "";
			string updateSql = "UPDATE " + databaseCredentials.Catalog + "." + tableName + " SET #PROPERTIES# WHERE id=?id";
			for (int i = 0; i < columnNames.Count; i++) {
				if (!columnNames[i].Equals("id")) {
					updatePropTmp += tableName + "." + columnNames[i] + "=?" + columnNames[i] + ",";
					updateCommand.Parameters.Add("?" + ((string)columnNames[i]), (MySqlDbType) columnDbTypes[i], 0, (string) columnNames[i]);
				}
			}
			updateCommand.Parameters.Add("?id", MySqlDbType.Int32, 0, "id"); // manually add the id parameter to the command
			updatePropTmp = updatePropTmp.Substring(0, updatePropTmp.Length - 1);
			updateSql = updateSql.Replace("#PROPERTIES#", updatePropTmp);
			updateCommand.CommandText = updateSql;
			fileTableAdapter.UpdateCommand = updateCommand;
			#endregion

			#region Insert Command
			MySqlCommand insertCommand = new MySqlCommand();
			insertCommand.Connection = adapterConnection;
			string insertSql = "INSERT INTO " + databaseCredentials.Catalog + "." + tableName + "  (#COLUMNS#) VALUES (#VALUES#)";
			string insertColumnsTmp = "";
			string insertValuesTmp = "";

			for (int i = 0; i < columnNames.Count; i++) {
				insertColumnsTmp += tableName + "." + columnNames[i] + ",";
				insertValuesTmp += "?" + columnNames[i] + ",";
				insertCommand.Parameters.Add("?" + ((string)columnNames[i]), (MySqlDbType) columnDbTypes[i], 0, (string) columnNames[i]);
			}
			insertColumnsTmp = insertColumnsTmp.Substring(0, insertColumnsTmp.Length - 1); // remove last obsolete ,
			insertValuesTmp = insertValuesTmp.Substring(0, insertValuesTmp.Length - 1); // remove last obsolete ,
			insertSql = insertSql.Replace("#COLUMNS#", insertColumnsTmp); // set columnTmp on its position
			insertSql = insertSql.Replace("#VALUES#", insertValuesTmp); // set valueTmp on its position
			insertCommand.CommandText = insertSql;
			fileTableAdapter.InsertCommand = insertCommand;
			#endregion

			#region Delete Command
			string deleteSql = "DELETE FROM " + databaseCredentials.Catalog + "." + tableName + " WHERE id=?id";
			MySqlCommand deleteCommand = new MySqlCommand(deleteSql, adapterConnection);
			deleteCommand.Parameters.Add("?id", MySqlDbType.Int32, 0, "id");
			fileTableAdapter.DeleteCommand = deleteCommand;
			#endregion

		}

		protected void fileTableAdapter_RowUpdated(object sender, MySqlRowUpdatedEventArgs e) {
			if (e.RecordsAffected == 0) {
				Trace.WriteLine("WmDataFileSupport: Excecuted SqlCommand, but no records affected: " + e.Command.CommandText);
				foreach (MySqlParameter param in e.Command.Parameters) {
					Trace.WriteLine(param.ParameterName + " / " + param.DbType + " / " + param.Size + " / " + param.SourceColumn + " / " + param.Value);
				}
			}
		}

		protected void fillFileTable() {
			lock (fileTable) {
				try {
					adapterConnection.Open();
					fileTableAdapter.Fill(data.WmDataSet, tableName);
				} catch (Exception error) {
					Trace.WriteLine("WmDataFileSupport: Error while attempting to fill datatable " + tableName + ": " + error.Message);
				} finally {
					adapterConnection.Close();
				}
			}
		}
		protected void updateFileTable() {
			lock (fileTable) {
				try {
					adapterConnection.Open();
					fileTableAdapter.Update(data.WmDataSet, tableName);
				} catch (Exception error) {
					Trace.WriteLine("WmDataFileSupport: Error while attempting to update datatable " + tableName + ": " + error.Message);
				} finally {
					adapterConnection.Close();
				}
			}
		}
		
		protected void createFileTable() {
			fileTable = new DataTable(tableName);
			fileTable.Columns.Add(new DataColumn("id", typeof(int)));
			fileTable.Columns.Add(new DataColumn("created", typeof(DateTime)));
			fileTable.Columns.Add(new DataColumn("lastaccessed", typeof(DateTime)));
			fileTable.Columns.Add(new DataColumn("lastupdated", typeof(DateTime)));
			fileTable.Columns.Add(new DataColumn("filename", typeof(string)));
			fileTable.Columns.Add(new DataColumn("fileextension", typeof(string)));
			fileTable.Columns.Add(new DataColumn("contentlength", typeof(int)));
			fileTable.Columns.Add(new DataColumn("contenttype", typeof(string)));
			fileTable.Columns.Add(new DataColumn("objectinstance", typeof(object)));

			DataColumn[] fileTableKey = new DataColumn[] { fileTable.Columns["id"] };
			fileTable.PrimaryKey = fileTableKey;

			data.WmDataSet.Tables.Add(fileTable);
		}


		protected void checkDataBase() {
			if (!WmDataBaseCreator.isTableExistent(tableName,databaseCredentials)) {
				string createFileTableSql = "CREATE TABLE `"+databaseCredentials.Catalog+"`.`"+tableName+"` (`id` INTEGER NOT NULL,`created` DATETIME, `lastaccessed` DATETIME, `lastupdated` DATETIME,  `filename` VARCHAR(1000), `contentlength` INTEGER, `contenttype` VARCHAR(100), `fileextension` VARCHAR(20), `binarydata` LONGBLOB,  PRIMARY KEY (`id`))ENGINE = InnoDB;";
				MySqlConnection connection = new MySqlConnection(databaseCredentials.getConnectionString());
				MySqlCommand tableCommand = new MySqlCommand(createFileTableSql, connection);
				try {
					connection.Open();
					tableCommand.ExecuteNonQuery();
					Trace.WriteLine("WmDataFileSupport: Created file support table");
				} catch (Exception error) {
					throw new Exception("WmDataFileSupport: Something went wrong while creating table for file support. " + error.Message);
				} finally {
					tableCommand.Connection.Close();
					connection.Close();
				}
			}// else: database is already there, no problem then, perhaps implement version check of table for future useage.
		}

		public static string TableName{
			get { return tableName; }
		}

	}
}