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

namespace SP_VIP.Data
{
    public class ProjectService
    {
        private static readonly string[] Semesters = new[]
        {
            "Spring 2019", "Fall 2019", "Spring 2018", "Fall 2018", "Spring 2020", "Fall 2020"
        };

        public const int maxStudents = 2;

        public string thisTitle = "N/A";

        public string Members = "John Doe, Jane Doe";

        public Task<Project[]> GetProjectAsync()
        {
            var rng = new Random();
            return Task.FromResult(Enumerable.Range(1, 10).Select(index => new Project
            {
                title = "Test Project " + rng.Next(1, 100).ToString(),
                description = "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.",
                semester = Semesters[rng.Next(Semesters.Length)],
                reqSkills = "Test Skill 1, Test Skill 2",
                members = Members
            }).ToArray());
        }

        public string testFunct()
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = "fa19-seniorproject.database.windows.net"; 
            builder.UserID = "cis4911";            
            builder.Password = "FA19-SeniorProject";     
            builder.InitialCatalog = "SPWA_DB";

            using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
            {
                Console.WriteLine("\nQuery data example:");
                Console.WriteLine("=========================================\n");

                connection.Open();       
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT TOP (1000) * FROM [dbo].[Projects]");
                String sql = sb.ToString();

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine("{0} {1} {2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
                            thisTitle = reader.GetString(1);
                        }
                    }
                }                    
            }
            return thisTitle;
        }

        public void testSendProject()
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = "fa19-seniorproject.database.windows.net"; 
            builder.UserID = "cis4911";            
            builder.Password = "FA19-SeniorProject";     
            builder.InitialCatalog = "SPWA_DB";

            using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
            {
                Console.WriteLine("\nQuery data example:");
                Console.WriteLine("=========================================\n");

                connection.Open();       
                StringBuilder sb = new StringBuilder();
                sb.Append("INSERT INTO Projects ([id], [ownerId], [title], [semester], [details], [requiredSkills], [maxStudents], [isActive], [isApproved], [member]) VALUES ( '20a', '1b', 'Test Project 1', 'Fall 2019', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.', 'Test Skill 1, Test Skill 2', '2', 1, 1,'John Doe, Jane Doe')");
                String sql = sb.ToString();

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                        }
                    }
                }                    
            }
        }

        //--------
        public List<Project> GetProjectListings()
        {
            List<Project> listings = new List<Project>();

            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = "fa19-seniorproject.database.windows.net"; 
            builder.UserID = "cis4911";            
            builder.Password = "FA19-SeniorProject";     
            builder.InitialCatalog = "SPWA_DB";

            using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
            {
                Console.WriteLine("\nQuery data example:");
                Console.WriteLine("=========================================\n");

                connection.Open();       
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT TOP (1000) * FROM [dbo].[Projects]");
                String sql = sb.ToString();

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine("{0} {1} {2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
                            
                            listings.Add(new Project {
                                title = reader.GetString(1),
                                description = reader.GetString(2),
                                semester = reader.GetString(7),
                                reqSkills = reader.GetString(4),
                                members = reader.GetString(6)
                            });
                        }
                    }
                }                    
            }
            return listings;
        }





        //--------
    }
}
