using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;

/// <summary>
/// Handles connection with stuffbyd_ass3 database.
/// </summary>
public class DBconnection
{
    MySqlConnection mysqlconnection;

    public DBconnection() {
        mysqlconnection = new MySqlConnection("Server=stuffbydavid.com;Database=stuffbyd_ass3;Uid=stuffbyd_ass3;Pwd=BMU?R?Ar+WQv");
    }

    public bool UserExists(string user, string pass) {
        bool foundValue = false;

        try {
            mysqlconnection.Open();
            MySqlCommand cmd = mysqlconnection.CreateCommand();
            cmd.CommandText = "select * from users where username=@user and password=@pass;";
            cmd.Parameters.AddWithValue("@user", user);
            cmd.Parameters.AddWithValue("@pass", pass);
            MySqlDataReader read = cmd.ExecuteReader();
            foundValue = read.Read();
            read.Close();
        } catch (Exception e) {
            // Errrorrr
        } finally {
            mysqlconnection.Close();
        }
        return (foundValue);
    }

    public void UploadNews(string user, string title, string body, string file, string category, string keywords) {
        try {
            mysqlconnection.Open();
            MySqlCommand cmd = mysqlconnection.CreateCommand();
            cmd.CommandText = "insert into news(username,dateadded,title,body,filename,category,keywords) values(@user,NOW(),@title,@body,@file,@category,@keywords)";
            cmd.Parameters.AddWithValue("@user", user);
            cmd.Parameters.AddWithValue("@title", title);
            cmd.Parameters.AddWithValue("@body", body);
            cmd.Parameters.AddWithValue("@file", file);
            cmd.Parameters.AddWithValue("@category", category);
            cmd.Parameters.AddWithValue("@keywords", keywords);
            cmd.ExecuteNonQuery();
        } catch (Exception e) {
            // ERRORorro
        } finally {
            mysqlconnection.Close();
        }
    }

    public List<string> FindNews(string searchWord) {
        List<string> found = new List<string>();

        try {
            mysqlconnection.Open();
            MySqlCommand cmd = mysqlconnection.CreateCommand();
            cmd.CommandText = "select title from news where title like @search or body like @search or category like @search or keywords like @search";
            cmd.Parameters.AddWithValue("@search", "%" + searchWord + "%");

            MySqlDataReader read = cmd.ExecuteReader();
            while (read.Read())
                found.Add(read.GetString(0));
            read.Close();
        } catch (Exception e) {
            // ERrororor
        } finally {
            mysqlconnection.Close();
        }
        return found;
    }
}