using System;

namespace zero
{
	namespace netboot
	{

			public class SqlManager
			{
				//private Mono.Data.SqliteClient.SqliteConnection connection;
				private Mono.Data.Sqlite.SqliteConnection connection;
				public string sql_url;
				public bool debug;
				private string extractedModel;
				public SqlManager ()
				{
					debug=zero.netboot.Core.getCore().debug;
					dprint("Connecting to database...");
					sql_url="URI=file:/net/zero-net-boot/db/status.db";
					try
					{
						connection=new Mono.Data.Sqlite.SqliteConnection(sql_url);
						connection.Open();
					}
					catch
					{
						Console.WriteLine("[SqlManager] Failed to connect to sql database");
						zero.netboot.Core.getCore().close_me=true;
					}
				
					
				
				}
				
			
				
				
				public void check_version(string table)
				{
				
					Mono.Data.Sqlite.SqliteCommand command;
					Mono.Data.Sqlite.SqliteDataReader reader;
					command=connection.CreateCommand();
					command.CommandText="SELECT * FROM " + table ;
					try
					{
						reader=command.ExecuteReader();
					
						while(reader.Read())
							reader.GetInt16(4);
					
						reader.Close();
						reader=null;
						command.Dispose();
						command=null;					
					}
					catch
					{
						command=connection.CreateCommand();
						string query="ALTER TABLE isos ADD enabled INTEGER";
						Console.WriteLine("[SqlManager] Executing " + query);
						command.CommandText=query;
						reader=command.ExecuteReader();
						reader.Close();
						reader=null;
						command.Dispose();
						command=null;					
						
					}
	
				}	
			
				public void check_isos()
				{
				
					foreach(zero.netboot.IsoInfo info in zero.netboot.Core.getCore().isoList)
					{
						extractedModel = info.version.Split(' ')[0].Replace("modelo-","");
						string std_out="";
						string std_error="";
						lliurex.utils.Commands.run(zero.netboot.Core.getCore().chroot_script,"update_hacks " + info.fileName + " " + extractedModel + " " + zero.netboot.Core.getCore().confman.options["language_enabled"] + " " + zero.netboot.Core.getCore().confman.options["isos-sources-list"] + " " + zero.netboot.Core.getCore().debug + " " + zero.netboot.Core.getCore().confman.options["liveuser"] + " " + zero.netboot.Core.getCore().confman.options["livepasswd"], out std_out,out std_error); 
						
						Console.Write("   ISO " + zero.netboot.Core.getCore().lliurexlist[extractedModel] + ":  Updating hooks...    ");
						string fail_ok="[ OK ]";
						string[] list_values=std_out.Split('\n');
						foreach(string line in list_values )
						{
							if (zero.netboot.Core.getCore().debug) Console.WriteLine(line);
							if (line.Contains("="))	
							{
								string[] tmp=line.Split('=');
								switch(tmp[0]){
									case "FAIL_SQUASHFS":
										if (tmp[1] == "failed") {
											fail_ok="[ FAILED ]";
										}
										break;
									default:
										break;
								}
							}
						}
						Console.WriteLine(fail_ok);
					}
	
				}
			
				
				public void select_query(string query, out System.Collections.Generic.List<zero.netboot.IsoInfo> isolist)
				{
					System.Collections.Generic.List<zero.netboot.IsoInfo> list=new System.Collections.Generic.List<zero.netboot.IsoInfo>();
				
					Mono.Data.Sqlite.SqliteCommand command=connection.CreateCommand();
					command.CommandText=query;
					Mono.Data.Sqlite.SqliteDataReader reader=command.ExecuteReader();
					while(reader.Read())
					{
						// 0 ID, 1 NAME, 2 VERSION, 3 DATE, 4 ENABLED
					
						
						
						try
						{
							//tmp_data[reader.GetString(1)]=reader.GetString(2);
							bool enabled;
							if(reader.GetInt16(4)==0)
								enabled=false;
							else
								enabled=true;
							zero.netboot.IsoInfo iso=new zero.netboot.IsoInfo(reader.GetInt16(0),reader.GetString(1),reader.GetString(2),reader.GetString(3),enabled);
							list.Add(iso);
							zero.netboot.Core.getCore().isoList.Add(iso);
						}
						catch
						{
						
							dprint("Error processing " + reader.GetString(1) + " state");
						}
					}
				 	
					reader.Close();
					reader=null;
					command.Dispose();
					command=null;				
				
					isolist=list;
				}
			
				public zero.netboot.IsoInfo get_last_iso_entry()
				{
					Mono.Data.Sqlite.SqliteCommand command=connection.CreateCommand();
					command.CommandText="SELECT * FROM isos";
					Mono.Data.Sqlite.SqliteDataReader reader=command.ExecuteReader();
					int id=0;
					string file="";
					string version="";
					string date="";
					bool enabled=false;
					
					while(reader.Read())
					{
						// 0 ID, 1 NAME, 2 VERSION, 4 ENABLED
						int tmp=reader.GetInt16(0);
						if(tmp>id)
						{
							id=tmp;
							file=reader.GetString(1);
							version=reader.GetString(2);
							date=reader.GetString(3);
							if(reader.GetInt16(4)==0)
								enabled=false;
							else
								enabled=true;
							
						
						}
					}
				

				
					

				
				
					zero.netboot.IsoInfo info=new zero.netboot.IsoInfo(id,file,version,date,enabled);
					return info;
				}
			
			
				public void enable_iso(int iso_id, bool enabled)
				{
				
					string query_template="UPDATE isos SET enabled=%ENABLED% WHERE id='%ISO%'";
					string query=query_template.Replace("%ISO%",Convert.ToString(iso_id));
					if(enabled)
						query=query.Replace("%ENABLED%","1");
					else
						query=query.Replace("%ENABLED%","0");

					Mono.Data.Sqlite.SqliteCommand command = connection.CreateCommand();
					command.CommandText=query;
					command.ExecuteNonQuery();
					command.Dispose();
					command=null;				
				
				
				}			
			
				public void update_iso_version(int iso_id, string version)
				{
				
					string query_template="UPDATE isos SET version='%VERSION%' WHERE id='%ISO%'";
					string query=query_template.Replace("%ISO%",Convert.ToString(iso_id));
					query=query.Replace("%VERSION%",version);
					dprint("Executing " + query);
					Mono.Data.Sqlite.SqliteCommand command = connection.CreateCommand();
					command.CommandText=query;
					command.ExecuteNonQuery();
					command.Dispose();
					command=null;				
				
				
				}
			
				public void insert_iso(string name, string version, int enable)
				{
					string query_template="INSERT INTO isos VALUES(NULL,'%NAME%','%VERSION%','%DATE%'," + enable + ")";
					string query=query_template.Replace("%NAME%",name);
					query=query.Replace("%VERSION%",version);
					query=query.Replace("%DATE%",DateTime.Now.ToShortDateString());
					
					
					try
					{
						dprint("Executing " + query);
						Mono.Data.Sqlite.SqliteCommand command = connection.CreateCommand();
						command.CommandText=query;
						command.ExecuteNonQuery();
						command.Dispose();
						command=null;
						zero.netboot.Core.getCore().isomanwidget.isos.AppendValues(version);
					}
					catch
					{
					
						zero.netboot.Core.getCore().wardialog=new zero.netboot.WarningDialog();
						zero.netboot.Core.getCore().wardialog.msg="Error inserting into Database. Are you running Zero-Netboot with administration privileges?";
					
					}
					
					
					
				}
			
				/*		
				public void insert_tcos()
				{
					
					int iso_id=get_last_download_id();
				
					string query_template="INSERT INTO tcos VALUES(NULL,0,%ISO%)";
					string query=query_template.Replace("%ISO%",Convert.ToString(iso_id));
									
					try
					{
						dprint("Executing " + query);
						Mono.Data.Sqlite.SqliteCommand command = connection.CreateCommand();
						command.CommandText=query;
						command.ExecuteNonQuery();
						command.Dispose();
						command=null;
					}
					catch
					{
					
						zero.netboot.Core.getCore().wardialog=new zero.netboot.WarningDialog();
						zero.netboot.Core.getCore().wardialog.msg="Error inserting into Database. Are you running Zero-Netboot with administration privileges?";
					
					}
				}
				*/
			
				public void delete_iso(int id)
				{
				
					string query="DELETE FROM isos WHERE id = %ID%";
				
					query=query.Replace("%ID%",Convert.ToString(id));
					dprint("Executing " + query);
				
					Mono.Data.Sqlite.SqliteCommand command = connection.CreateCommand();
					command.CommandText=query;
					command.ExecuteNonQuery();
					command.Dispose();
					command=null;					
					
					
					
				
				
				}
			
			
				public void check_integrity()
				{

					System.Collections.Generic.List<int> list=new System.Collections.Generic.List<int>();
				
					Mono.Data.Sqlite.SqliteCommand command=connection.CreateCommand();
					command.CommandText="SELECT * FROM isos";
					Mono.Data.Sqlite.SqliteDataReader reader=command.ExecuteReader();
					while(reader.Read())
					{
						// 0 ID, 1 NAME, 2 VERSION, 3 DATE, 4 ENABLED
					
						
						
						try
						{
							//tmp_data[reader.GetString(1)]=reader.GetString(2);
							bool enabled;
							if(reader.GetInt16(4)==0)
								enabled=false;
							else
								enabled=true;
							zero.netboot.IsoInfo iso=new zero.netboot.IsoInfo(reader.GetInt16(0),reader.GetString(1),reader.GetString(2),reader.GetString(3),enabled);
							if(iso.fileName.Length < 1 || iso.version.Length < 1 || iso.version.Contains("modelo-")==false)
								list.Add(iso.id);
							
						}
						catch
						{
						
							
						}
					}
				 	
					reader.Close();
					reader=null;
					command.Dispose();
					command=null;				
								
					foreach(int id in list)
					{
						try
						{
							delete_iso(id);
						}
						catch
						{
						
						}
					}
				
				
				}
				
				public void create_database()
				{
				    string query="CREATE TABLE isos(id INTEGER PRIMARY KEY, name VARCHAR(100),version VARCHAR(200), added DATE, enabled INTEGER)";
					dprint("Executing " + query);
				
					Mono.Data.Sqlite.SqliteCommand command = connection.CreateCommand();
					command.CommandText=query;
					command.ExecuteNonQuery();
					command.Dispose();
					command=null;				
				}
			
				public void create_tcos_database()
				{
					string query="CREATE TABLE tcos(id INTEGER PRIMARY KEY, enable INTEGER, iso INTEGER, FOREIGN KEY(iso) REFERENCES isos(id))";
					dprint("Executing " + query);
				
					Mono.Data.Sqlite.SqliteCommand command = connection.CreateCommand();
					command.CommandText=query;
					command.ExecuteNonQuery();
					command.Dispose();
					command=null;
					
					
				}
			
				public void create_download_database()
				{
					string query="CREATE TABLE downloads(id INTEGER PRIMARY KEY, name VARCHAR(200), url VARCHAR(200), progress INTEGER, added DATE, finished DATE)";
					dprint("Executing " + query);
				
					Mono.Data.Sqlite.SqliteCommand command = connection.CreateCommand();
					command.CommandText=query;
					command.ExecuteNonQuery();
					command.Dispose();
					command=null;
					
					
				}			
			
			
				public void insert_download(zero.netboot.DownloadInfo info)
				{
					string query_template="INSERT INTO downloads VALUES(NULL,'%NAME%','%URL%',0,DATETIME('NOW'),NULL)";
					string query=query_template.Replace("%NAME%",info.local_file);
					query=query.Replace("%URL%",info.file_url);
					//zero.netboot.Core.getCore().isomanwidget.isos.AppendValues(version);
					//zero.netboot.Core.getCore().
					Mono.Data.Sqlite.SqliteCommand command = connection.CreateCommand();
					command.CommandText=query;
					command.ExecuteNonQuery();
					command.Dispose();
					command=null;				
				
				}
			
				public void finish_download(zero.netboot.DownloadInfo info)
				{
				
					string query_template="UPDATE downloads SET finished=DATETIME('NOW') WHERE id='%ID%'";
					string query=query_template.Replace("%ID%",Convert.ToString(info.id));

					Mono.Data.Sqlite.SqliteCommand command = connection.CreateCommand();
					command.CommandText=query;
					command.ExecuteNonQuery();
					command.Dispose();
					command=null;				
				
				
				}

			
				public void pause_download(int id, int progress)
				{
				
					string query_template="UPDATE downloads SET progress=%DOWNSIZE% WHERE id='%ID%'";
					string query=query_template.Replace("%ID%",Convert.ToString(id));
					query=query.Replace("%DOWNSIZE%",Convert.ToString(progress));

					Mono.Data.Sqlite.SqliteCommand command = connection.CreateCommand();
					command.CommandText=query;
					command.ExecuteNonQuery();
					command.Dispose();
					command=null;				
				
				
				}			
			
			
				public void select_query_dl(out System.Collections.Generic.List<zero.netboot.DownloadInfo> list)
				{
				
					Mono.Data.Sqlite.SqliteCommand command=connection.CreateCommand();
					command.CommandText="SELECT * FROM downloads";
					Mono.Data.Sqlite.SqliteDataReader reader=command.ExecuteReader();
					System.Collections.Generic.List<zero.netboot.DownloadInfo> tmp_list=new System.Collections.Generic.List<zero.netboot.DownloadInfo>();
					
					while(reader.Read())
					{
					
						// ID 0 LOCAL_FILE 1 URL 2 PROGRESS 3 ADDED 4 FINISHED 5 
					
						dprint(reader.GetString(1) + "\n\t\t      [URL] " + reader.GetString(2) + "\n\t\t      [ADDED ON] " + reader.GetDateTime(4).ToShortDateString());
					
						if (!reader.IsDBNull(5))
						{
							dprint("[FINISHED] " + reader.GetDateTime(5).ToShortDateString());	
						}
						else
						{
							dprint ("Download on " + reader.GetString(1) + " is not finished yet." );	
						}
					
					
					
						zero.netboot.DownloadInfo info=new zero.netboot.DownloadInfo();
						info.id=reader.GetInt32(0);
						info.local_file=reader.GetString(1);
						info.file_url=reader.GetString(2);
						info.progress=reader.GetInt32(3);
						info.addedDate=reader.GetDateTime(4);
						if (!reader.IsDBNull(5))
						{
							info.finishedDate=reader.GetDateTime(5);
							info.completed=true;
							
						}
						else
						{
							info.completed=false;
						}
					
						tmp_list.Add(info);
							
					
						
					
					
					}
				
					list=tmp_list;
				
				}
			
				public int get_last_download_id()
				{
				
				
					Mono.Data.Sqlite.SqliteCommand command=connection.CreateCommand();
					command.CommandText="SELECT * FROM downloads";
					Mono.Data.Sqlite.SqliteDataReader reader=command.ExecuteReader();
					int id=0;
					
					
					while(reader.Read())
					{
						// 0 ID, 1 file_path, 2 url
						int tmp=reader.GetInt16(0);
						if(tmp>id)
						{
							id=tmp;
						}
					}
				
					return id;				
				
				}
				
				
				public void delete_download(int id)
				{
				
					string query="DELETE FROM downloads WHERE id = %ID%";
				
					query=query.Replace("%ID%",Convert.ToString(id));
					dprint("Executing " + query);
				
					Mono.Data.Sqlite.SqliteCommand command = connection.CreateCommand();
					command.CommandText=query;
					command.ExecuteNonQuery();
					command.Dispose();
					command=null;					
					
					
					
				
				
				}			
			
			
				public void dprint(string txt)
				{
					
					if (debug)
					{
						Console.WriteLine("[SqlManager] {0}",txt);
					}	
				}			
			
			}
	}
}