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

using ufoSQLCommandNS;

namespace test_ufoSQLCommand
{
    class Program
    {
        static void Main(string[] args)
        {
            var c = new ufoSQLCommand("", "gokhan"); // login as root user
            
            var booltype = c.execute("CREATE DATABASE mydb 1234"); // create a database named mydb with password 1234

            Console.WriteLine("return value:" + booltype); // show return value
            Console.WriteLine("message:" + c.message); // show command message

            c.close_connection(); // close the commection

            c = new ufoSQLCommand("mydb", "1234"); // login to mydb database with password 1234

            booltype = c.execute("CREATE TABLE persons(per_id int IDENTITY, per_name text, per_surname text)"); // create a table named persons

            Console.WriteLine("return value:" + booltype);
            Console.WriteLine("message:" + c.message);

            booltype = c.execute("CREATE INDEX ndx_persons_01 ON persons(per_name, per_surname)"); // create a index named ndx_persons_01 on persons table

            Console.WriteLine("return value:" + booltype);
            Console.WriteLine("message:" + c.message);

            c.execute("INSERT INTO persons(per_name, per_surname) VALUES('John', 'Korukcu')"); // add a record to persons table
            c.execute("INSERT INTO persons(per_name, per_surname) VALUES('Peter', 'Korukcu')"); // add a record to persons table
            c.execute("INSERT INTO persons(per_name, per_surname) VALUES('Smith', 'Korukcu')"); // add a record to persons table
            c.execute("INSERT INTO persons(per_name, per_surname) VALUES('Julia', 'Korukcu')"); // add a record to persons table

            booltype = c.execute("CREATE TABLE vehicles(veh_id int IDENTITY, veh_name text CI, veh_per_id int)"); // create a table named vehicles

            Console.WriteLine("return value:" + booltype);
            Console.WriteLine("message:" + c.message);

            booltype = c.execute("CREATE UNIQUE INDEX ndx_vehicles_01 ON vehicles(veh_name)"); // create a unique index named ndx_vehicles_01 on vehicles table

            Console.WriteLine("return value:" + booltype);
            Console.WriteLine("message:" + c.message);

            c.execute("INSERT INTO vehicles(veh_name, veh_per_id) VALUES('BMW', 1)"); // add a record to vehicles table
            c.execute("INSERT INTO vehicles(veh_name, veh_per_id) VALUES('Mercedes', 1)"); // add a record to vehicles table
            c.execute("INSERT INTO vehicles(veh_name, veh_per_id) VALUES('Ferrari', 2)"); // add a record to vehicles table
           
            var r = c.execute_reader("SELECT * FROM persons JOIN vehicles ON per_id = veh_per_id ORDER BY per_name DESC LIMIT 10"); // query tables
            
            Console.WriteLine("message:" + r.message);
            Console.WriteLine("numrows:" + r.num_rows());

            while (r.fetch_next_record()) { // fetch next record
                for (int i = 0; i < r.record.Count(); ++i)
                    Console.Write(r.record[i] + " | ");
                Console.WriteLine();
            }
            Console.WriteLine();

            r = c.execute_reader("SELECT * FROM persons LEFT JOIN vehicles ON per_id = veh_per_id WHEREWITHINDEX per_name = 'Smith' WHERE per_id > 1 ORDER BY per_name DESC, per_id LIMIT 0, 5"); // query tables
            
            Console.WriteLine("message:" + r.message);
            Console.WriteLine("numrows:" + r.num_rows());

            while (r.fetch_next_record()) { // fetch next record
                for (int i = 0; i < r.record.Count(); ++i)
                    Console.Write(r.record[i] + " | ");
                Console.WriteLine();
            }
            Console.WriteLine();

            r = c.execute_reader("SELECT per_name, COUNT(*) AS cnt FROM persons WHERE per_id < 100 GROUP BY per_name HAVING cnt >= 1"); // query table
            
            Console.WriteLine("message:" + r.message);
            Console.WriteLine("numrows:" + r.num_rows());

            while (r.fetch_next_record()) { // fetch next record
                for (int i = 0; i < r.record.Count(); ++i)
                    Console.Write(r.record[i] + " | ");
                Console.WriteLine();
            }
            
            Console.ReadLine();
            c.close_connection();
        }
    }
}