/** this is test program for accesing sql database
    as for now it requires SqlServer database server running on localhost
 */

// NO-TEST

using System;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using System.Data.SqlClient;
using Nemerle.Data;
using Nemerle.Text;
using System.Windows.Forms;

// this is how compiler connects to database to validate queries occuring in program
[assembly: ConfigureConnection ("System.Data.SqlClient.SqlConnection", "Server=MORDOR\\SQLEXPRESS;Integrated Security=True")]


public class Test 
 {
 
    class OknoLogowania
    {
      dbcon :  IDbConnection;
      crypt : MD5 = MD5.Create();

      private loginTextBox : System.Windows.Forms.TextBox;
      private passwordTextBox : System.Windows.Forms.TextBox;
      private categoryBox : System.Windows.Forms.TextBox;
      private pointsBox : System.Windows.Forms.TextBox;

      public this () {
          def connectionString = 
          "Server=MORDOR\\SQLEXPRESS;Integrated Security=True";                          

          dbcon = Nemerle.Data.CreateConnection (connectionString);
          dbcon.Open();
          Console.WriteLine("connection opened...");
          
          this.loginTextBox = System.Windows.Forms.TextBox();
          this.passwordTextBox = System.Windows.Forms.TextBox();
          loginTextBox.Text = "nazgul";
          passwordTextBox.Text = "kamil";
          this.categoryBox = System.Windows.Forms.TextBox();
          this.pointsBox = System.Windows.Forms.TextBox();
          categoryBox.Text = "Quake 3";
          pointsBox.Text = "1000";
          
      }

      private ComputeMD5HashString(pass : string) : string
      {
          def hash = crypt.ComputeHash(Encoding.ASCII.GetBytes(pass));
          def builder = StringBuilder ("0x");
          foreach (num in hash) _ = builder.Append(num.ToString());
          builder.ToString();
      }

      public logowanie_Click(_ : object, _ : EventArgs) :  void
      {
         def haslo = ComputeMD5HashString (passwordTextBox.Text);
         def login = loginTextBox.Text;

         def sql = "SELECT name, id "
                   "FROM Uzytkownicy WHERE login = '" + login + 
                   "'AND password = '" + haslo + "'";
         using (dbcmd = SqlCommand(sql, dbcon :> SqlConnection))
         {
            using (reader = dbcmd.ExecuteReader())
            {
              if (reader.Read())
              {
                def name = reader.GetString(0);
                def _id = reader.GetInt32(1);
                _ = MessageBox.Show ("Witaj " + name);
              }
              else
                {
                  _ = MessageBox.Show ("Zly login lub haslo!");
                }
            }
        }
      }

      public zalogujSafe_Click(_ : object, _ : EventArgs) : void
      {
        def hash = ComputeMD5HashString (passwordTextBox.Text);
        def login = loginTextBox.Text;

        def reader = ExecuteReader("SELECT name, id " 
                      "FROM Uzytkownicy WHERE login = $login AND password = $hash", dbcon);
        if (reader.Read())
         {
              def name = reader.GetString(0);
              def _id = reader.GetInt32(1);
              _ = MessageBox.Show ("Witaj " + name);
         }
         else
         {
              _ = MessageBox.Show ("Zly login lub haslo!");
         }
        reader.Dispose ();
      }
      
      public szukaj_Click(_ : object, _ : EventArgs) :  void
      {
          def category = categoryBox.Text;
          def minimum = Int32.Parse(pointsBox.Text);

          ExecuteReaderLoop ("SELECT nick, points, description " 
                             "FROM Gamers WHERE game = $category " 
                             "AND points >= $minimum ORDER BY points DESC", dbcon,
            {
               Console.WriteLine ($"$nick ($description) with $points points");
            })
      }
    }
 

    static insert (conn : SqlConnection, x : string, y : string) : void
    {
      /// this is the usage of macro ExecuteNonQuery, which creates SqlClient parameters
      /// [@x] and [@y], set their value to corresponding variables [x] and [y],
      /// verify syntax and type correctness of query at compile-time, and generate
      /// code for runtime insertion of data to database
      
      _ = ExecuteNonQuery ("INSERT INTO Ludzie VALUES ($x, $y, 5)", conn);
    }
   
    public static Main() : void
    {
       Console.WriteLine("start...");

       def connectionString = "Server=MORDOR\\SQLEXPRESS;Integrated Security=True"; 

       def dbcon = CreateConnection (connectionString);
       dbcon.Open ();
       Console.WriteLine("connection opened...");

//       insert (dbcon, "Kocia", "Bicia");

       def myparm = "Kamil";

       /// This is standard C#-like implementation of database interoperation,
       /// without using Nemerle macros
       def sql = "SELECT (imie) AS ff, nazwisko " +
           "FROM Ludzie WHERE imie = @a";

       def dbtran = dbcon.BeginTransaction ();
       def dbcmd = SqlCommand (sql, dbcon, dbtran);
 
       _ = dbcmd.Parameters.AddWithValue("a", myparm);

       def reader = dbcmd.ExecuteReader();
       while(reader.Read()) {
         def firstname = reader.GetString (0);
         def lastname = reader.GetString (1);
         Nemerle.IO.printf ("Name: %s %s\n", firstname, lastname)            
       };
       reader.Close();
       dbtran.Rollback ();
       dbcmd.Dispose();

       /// and this is equivalent code utilizing ExecuteReaderLoop, which is
       /// a macro checking syntax and type validness of query at compile-time
       /// (by connecting to database)
       ExecuteReaderLoop ("SELECT * FROM Ludzie WHERE imie = $myparm", dbcon,
       {
         Nemerle.IO.printf ("Name: %s %s %d\n", imie, nazwisko, wiek.Value)
       });

       //_ = ExecuteNonQuery ("INSERT INTO intstr VALUES (5, 'beber')", dbcon);
       
       //// another examples using Nemerle sql macros       
       def tt = 4; def ty = "dfd4";

       /// CREATE TABLE intstr (a INT4, b VARCHAR(32));
       ExecuteReaderLoop (
          "SELECT a AS number, b, COUNT(*) AS amount FROM intstr " 
          "WHERE a = $tt or b = $ty GROUP BY a, b", dbcon, {
         Nemerle.IO.printf ("%d %s\n", number.Value, b);
         Console.WriteLine (amount)
       });

       
       def max = ExecuteScalar ("SELECT MAX(a) FROM intstr", dbcon);
       Nemerle.IO.printf ("%d\n", max);

       /// transaction mechanism used by macros' implementation prevents any
       /// data to be changed in database by compile-time queries
       /// here insertion is rolled back 
       _ = ExecuteReader ("INSERT INTO intstr VALUES (5, '7');"
                          " SELECT * FROM intstr;", dbcon);

       dbcon.Close();
       
       def x = OknoLogowania ();
       x.zalogujSafe_Click (null, null);
       x.logowanie_Click (null, null);
       x.szukaj_Click (null, null);
    }
 }

 
 public module DefineDB {
      public define () : void {
       Console.WriteLine("start...");

       def connectionString = "Server=MORDOR\\SQLEXPRESS;Integrated Security=True"; 

       def dbcon = SqlConnection (connectionString);
       dbcon.Open ();
       Console.WriteLine("connection opened...");

       def exec (q) {
         def cmd = SqlCommand (q, dbcon);
         _ = cmd.ExecuteNonQuery ();           
       }
       //exec ("DROP TABLE Ludzie;");
       exec ("CREATE TABLE Ludzie (imie VARCHAR (35), nazwisko VARCHAR (40), wiek INTEGER);");
       
       //exec ("DROP TABLE intstr;");
       exec ("CREATE TABLE intstr (a INTEGER, b VARCHAR (40));");       
       
       exec ("CREATE TABLE Uzytkownicy (id INTEGER PRIMARY KEY, name VARCHAR (35), login VARCHAR (35), password VARCHAR (40));");
       exec ("CREATE TABLE Gamers (nick VARCHAR(35), points INTEGER, description VARCHAR(80), game VARCHAR (50));");          
          
      }
 }