using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.IO;

namespace organiser
{
    public partial class Form1 : Form
    {
        bool updating = false;
        int id;
        double range = 0;
        SqlCeEngine engine;
        SqlCeConnection connect;
        SqlCeDataReader readNotes;

        public Form1()
        {
            InitializeComponent();

            if (File.Exists("data.sdf"))
            {
                engine = new SqlCeEngine("Data Source = data.sdf");
                connect = new SqlCeConnection("Data Source = data.sdf");
            }
            else
            {
                // Pokud neexistuje db, vytvořit
                engine = new SqlCeEngine("Data Source = data.sdf");
                engine.CreateDatabase();
                connect = new SqlCeConnection("Data Source = data.sdf");
                connect.Open();

                SqlCeCommand crdb = connect.CreateCommand();
                crdb.CommandText = "CREATE TABLE organiser (id INT PRIMARY KEY IDENTITY NOT NULL, name NTEXT NOT NULL, description NTEXT NULL, dt DATETIME NOT NULL, rep INT NULL, alert INT NOT NULL)";
                crdb.ExecuteNonQuery();
                connect.Close();

            }


            // Je nutné to pokaždé nastavovat na dnešní datum?
            monthCalendar1.SelectionStart = DateTime.Today;
            monthCalendar1.SelectionEnd = DateTime.Today;
            dateTimePicker1.Value = DateTime.Today;

            // Zobrazit úkoly
            updateList();
            checkToDo();
        }

        private void sqlIUD(string cmdt) // SQL Insert, Update, Delete
        {
            SqlCeCommand cmd = connect.CreateCommand();
            cmd.CommandText = cmdt;
            connect.Open();
            cmd.ExecuteNonQuery();
            connect.Close();
        }


        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void delete(object sender, EventArgs e)
        {
            Button clicked = (Button)sender; 
            string deleteNote = "DELETE FROM organiser WHERE id = '" + clicked.Name.ToString() + "'"; // Které tlačítko zmáčknuto? (name = autoincrement id)
            sqlIUD(deleteNote);
            MessageBox.Show("Deleted.");
            updateList();

        }

        private void update(object sender, EventArgs e)
        {

            Insert.Visible = false;
            Update_table.Visible = true;
            Cancel_update.Visible = true;

            Button clicked = (Button)sender;

            SqlCeCommand selectNote = connect.CreateCommand();
            selectNote.CommandText = "SELECT * FROM organiser WHERE id = '" + clicked.Name.ToString() + "'"; //Které tlačítko zmáčknuto?
            connect.Open();

            readNotes = selectNote.ExecuteReader(); 
            updating = true; //umožní editovat datum
            readNotes.Read();
            // Načíst data k upravení (do původního formuláře), znemožnit insert
            id = Convert.ToInt32(readNotes.GetInt32(0)); // uložit id z tlačítka pro pozdější použití při UPDATE
            monthCalendar1.SelectionStart = readNotes.GetDateTime(3);
            monthCalendar1.SelectionEnd = readNotes.GetDateTime(3);
            dateTimePicker1.Value = readNotes.GetDateTime(3);
            insert_name.Text = readNotes.GetString(1);
            insert_description.Text = readNotes.GetString(2);
            insert_repeat_numeric.Value = Convert.ToDecimal(readNotes.GetInt32(4));
            insert_alert_days.Value = Convert.ToDecimal(readNotes.GetInt32(5));

            connect.Close();

        }

        private void updateList() // zobrazit data pro den/týden/měsíc (+ double range - počet zobrazených dní)
        {
            DateTime oneDay = dateTimePicker1.Value.Subtract(dateTimePicker1.Value.TimeOfDay).AddHours(23.9);

            connect.Open();
            SqlCeCommand selectCount = connect.CreateCommand();
            selectCount.CommandText = "SELECT count(*) FROM organiser WHERE dt BETWEEN '" + dateTimePicker1.Value.ToString("MM.dd.yyyy HH:mm:ss") + "' AND '" + oneDay.AddDays(range).ToString("MM.dd.yyyy HH:mm:ss") + "'";
            int count = Convert.ToInt32(selectCount.ExecuteScalar()); // kvůli dynamickým prvkům, je nutné zjistit potřebnou velikost pole

            SqlCeCommand selectNotes = connect.CreateCommand();
            selectNotes.CommandText = "SELECT * FROM organiser WHERE dt BETWEEN '" + dateTimePicker1.Value.ToString("MM.dd.yyyy HH:mm:ss") + "' AND '" + oneDay.AddDays(range).ToString("MM.dd.yyyy HH:mm:ss") + "' ORDER BY dt ASC";

            readNotes = selectNotes.ExecuteReader();

            Label[] label = new Label[count];
            Button[] b = new Button[count];
            Button[] b2 = new Button[count];

            int x = 0;
            int paintY = 0;


            todo_list.Controls.Clear(); // vymazání labelů/buttonů z minulého updateListu
            while (readNotes.Read())
            {
                b[x] = new Button(); //mazací čudlík
                b[x].Size = new Size(10, 10);
                b[x].Text = "x";
                b[x].Location = new Point(0, paintY);
                b[x].FlatStyle = FlatStyle.Flat;
                b[x].Click += new EventHandler(delete);
                b[x].Name = readNotes.GetInt32(0).ToString();
                b[x].BackColor = Color.Red;

                b2[x] = new Button(); //editovací čudlík
                b2[x].Size = new Size(10, 10);
                b2[x].Text = "e";
                b2[x].Location = new Point(10, paintY);
                b2[x].FlatStyle = FlatStyle.Flat;
                b2[x].Click += new EventHandler(update);
                b2[x].Name = readNotes.GetInt32(0).ToString();
                b2[x].BackColor = Color.Green;

                label[x] = new Label(); // samotná poznámka
                label[x].Text.Trim();
                label[x].MaximumSize = new Size(344, 1000);
                label[x].MinimumSize = new Size(344, 1);
                label[x].AutoSize = true;
                label[x].Location = new Point(20, paintY);
                label[x].Text = readNotes.GetDateTime(3).ToString() + "; " + readNotes.GetString(1) + " - " + readNotes.GetString(2);

                if (x % 2 == 0) // střídání barev
                    label[x].BackColor = Color.Aqua;
                else
                    label[x].BackColor = Color.Yellow;

                todo_list.Controls.Add(label[x]);
                todo_list.Controls.Add(b[x]);
                todo_list.Controls.Add(b2[x]);

                paintY += label[x].Height; //na jakém Y začít kreslit další label?
                x++;
            }
            connect.Close();
        }

        private void checkToDo()
        {
            // stejný princip jako kód výše, přidána podmínka pokud (dnešní den + Alert dní)>= den poznámky; zobraz upozornění

            DateTime date = DateTime.Today;
            connect.Open();
            SqlCeCommand selectCount = connect.CreateCommand();
            selectCount.CommandText = "SELECT count(*) FROM organiser WHERE dt >= '" + date.ToString("MM.dd.yyyy HH:mm:ss") + "'";
            int count = Convert.ToInt32(selectCount.ExecuteScalar());

            SqlCeCommand selectNotes = connect.CreateCommand();
            selectNotes.CommandText = "SELECT * FROM organiser WHERE dt >= '" + date.ToString("MM.dd.yyyy HH:mm:ss") + "' ORDER BY dt ASC";

            readNotes = selectNotes.ExecuteReader();

            Label[] label2 = new Label[count];

            int x = 0;
            int paintY = 0;

            alert_panel.Controls.Clear();
            while (readNotes.Read())
            {
                if (date.AddDays(Convert.ToDouble(readNotes.GetInt32(5))) >= Convert.ToDateTime(readNotes.GetDateTime(3)))
                {

                    label2[x] = new Label();
                    label2[x].Text.Trim();
                    label2[x].MaximumSize = new Size(660, 1000);
                    label2[x].MinimumSize = new Size(660, 1);
                    label2[x].AutoSize = true;
                    label2[x].Location = new Point(0, paintY);
                    label2[x].Text = readNotes.GetDateTime(3).ToString() +"; " + readNotes.GetString(1) + " - " + readNotes.GetString(2);


                    if (x % 2 == 0)
                        label2[x].BackColor = Color.Aqua;
                    else
                        label2[x].BackColor = Color.Yellow;

                    alert_panel.Controls.Add(label2[x]);


                    paintY += label2[x].Height;
                    x++;
                }
            }
            connect.Close();
            
        }

        private void monthCalendar1_DateChanged(object sender, DateRangeEventArgs e)
        {
            dateTimePicker1.Value = monthCalendar1.SelectionStart;

            // nutný check kvůli editaci data
            if (updating == false)
            {
                updateList();
            }
        }

        private void Insert_Click(object sender, EventArgs e) // klasický insert
        {
            for (int x = 0; x < Convert.ToInt32(insert_repeat_numeric.Value); x++)
            {
                string insertNote = "INSERT INTO organiser (name, description, dt, rep, alert) VALUES ('" + insert_name.Text + "', '" + insert_description.Text + "', '" + dateTimePicker1.Value.AddDays(Convert.ToDouble(x)).ToString("MM.dd.yyyy HH:mm:ss") + "', '" + Convert.ToInt32(insert_repeat_numeric.Value) + "', '" + Convert.ToInt32(insert_alert_days.Value) + "')";
                sqlIUD(insertNote);
                updateList();
            }
            MessageBox.Show("Done.");
            updateList(); //netuším proč to jednou nebere
        }

        private void Cancel_update_Click(object sender, EventArgs e) // zrušení editace
        {
            Insert.Visible = true;
            Update_table.Visible = false;
            Cancel_update.Visible = false;
            updating = false;
        }

        private void Update_table_Click(object sender, EventArgs e) // dokončení editace
        {
            string updateNote = "UPDATE organiser SET  name = '" + insert_name.Text + "', description = '" + insert_description.Text + "', dt = '" + dateTimePicker1.Value.ToString("MM.dd.yyyy HH:mm:ss") + "', alert = '" + Convert.ToInt32(insert_alert_days.Value) + "' WHERE id = '" + id.ToString() + "'";
            sqlIUD(updateNote);
            MessageBox.Show("Updated.");
            updating = false;
            updateList();
        }

        private void timer1_Tick(object sender, EventArgs e) // check na (dnešní den + Alert dní)>= den poznámky; zobraz upozornění jednou za 5s
        {
            checkToDo();
        }

        private void day1_Click(object sender, EventArgs e) // upravit počet zobrazených dní
        {
            range = 0;
            updateList();
        }

        private void day7_Click(object sender, EventArgs e)
        {
            range = 7;
            updateList();
        }

        private void day30_Click(object sender, EventArgs e)
        {
            range = 30; 
            updateList();
        }




    }
}