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.IO;

namespace FbConsole {
    public partial class Form1 : Form {
        Dbs _dbs;
        DataTable _dtDataBase;
        DataTable _dtTablenames;
        bool _bStart;

        public Form1() {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e) {
            _dbs = new Dbs();
            setGUI();
        }

        private void setGUI() {
            tableLayoutPanel1.Dock = DockStyle.Fill;
            splitContainer3.Dock = DockStyle.Fill;
            ESQL.Dock = DockStyle.Fill;
            splitContainer1.Dock = DockStyle.Fill;
            splitContainer2.Dock = DockStyle.Fill;
            listBox1.Dock = DockStyle.Fill;
            listBox2.Dock = DockStyle.Fill;
            splitContainer1.SplitterDistance = 200;
            Grid1.Dock = DockStyle.Fill;

            Grid1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
            Grid1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;
            Grid1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;


            Grid1.AutoSize = true;
            //Grid1.AllowUserToAddRows = false;
            //Grid1.AllowUserToDeleteRows = false;
            Grid1.MultiSelect = false;
            splitContainer3.SplitterDistance = 190;
        } // setGUI

        private void MnOpenDatabase_Click(object sender, EventArgs e) {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Filter = "Datenbankdateien (*.fdb,gdb)|*.fdb;*.gdb|Alle Dateien (*.*)|*.*";
            openFileDialog1.FilterIndex = 1;// zählt von 1 !
            openFileDialog1.DefaultExt = ".fdb";
            openFileDialog1.InitialDirectory = Environment.CurrentDirectory;
            openFileDialog1.Multiselect = false;
            openFileDialog1.RestoreDirectory = true;
            if (openFileDialog1.ShowDialog() == DialogResult.OK) {
                if (BnStart.Enabled) _dbs.Close();
                string filename = openFileDialog1.FileName.ToString();
                _dbs.setDbs(filename);

                setEnabled(false);
                setListBoxSize();
            }
        }

        private void setEnabled(bool leer) {
            BnStart.Enabled = true;
            MnRunSQL.Enabled = true;
            MnExportErgebnis.Enabled = true;
            MnSaveErgebnisseToFile.Enabled = true;
            _bStart = true;
            listBox2.Items.Clear();
            if (!leer) setTables();
        }

        private void mnNewDatabase_Click(object sender, EventArgs e) {
            string filename = "";
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.Filter = "Datenbankdateien (*.fdb,gdb)|*.fdb;*.gdb|Alle Dateien (*.*)|*.*";
            saveFileDialog1.FilterIndex = 1;// zählt von 1 !
            saveFileDialog1.DefaultExt = ".fdb";
            saveFileDialog1.InitialDirectory = Environment.CurrentDirectory;
            saveFileDialog1.RestoreDirectory = true;
            if (saveFileDialog1.ShowDialog() == DialogResult.OK) {
                filename = saveFileDialog1.FileName.ToString();
                _dbs.createDatabase(filename);
                _dbs.setDbs(filename);
                listBox1.Items.Clear();
                listBox2.Items.Clear();
                setEnabled(true);
            }           
        }
        private void setListBoxSize() {
        }

        public DataTable getTables() {
            string[] filter = new string[4];
            filter[0] = "dbo"; //  null;
            filter[1] = null;
            filter[2] = null;
            filter[3] = null;
            DataTable dt;
            dt = _dbs.GetSchema("Tables", filter);
            return dt;
        }
        private void setTables() {
            // _dtDataBase = _Fbcnn.GetSchema("Tables", new string[] { null, null, null, "TABLE" });

            _dtDataBase = getTables();
            listBox1.Items.Clear();
            for (int row = 0; row < _dtDataBase.Rows.Count; row++) {
                string sTabletyp = (string)_dtDataBase.Rows[row][3];
                if (sTabletyp.Equals("TABLE"))
                    listBox1.Items.Add(_dtDataBase.Rows[row][2].ToString());
            }
            _bStart = false;
            if (listBox1.Items.Count>0) listBox1.SelectedIndex = 0;
            //listBox1_SelectedIndexChanged(null, null);
        }
        private void listBox1_SelectedIndexChanged(object sender, EventArgs e) {
            int i = listBox1.SelectedIndex;
            if ((!_bStart) && (i >= 0)) {
                string sTable = (string)listBox1.SelectedItem;
                _dtTablenames = getDataTyps(sTable);  // getDataTyps(string sTable)
                listBox2.Items.Clear();
                for (int row = 0; row < _dtTablenames.Rows.Count; row++) {
                    string sAttribname = (string)_dtTablenames.Rows[row][3];
                    string sFormat = (string)_dtTablenames.Rows[row][4];
                    listBox2.Items.Add(sAttribname + " (" + sFormat + ")");
                }
            }

        }  // listBox1_SelectedIndexChanged

        private void listBox1_DoubleClick(object sender, EventArgs e) {
            int i = listBox1.SelectedIndex;
            if ((!_bStart) && (i >= 0)) {
                string sTable = (string)listBox1.SelectedItem;
                Clipboard.SetText(" " + sTable);
                ESQL.Paste();
            }
        }  // listBox1_DoubleClick



        private void MnOpenSQLFile_Click(object sender, EventArgs e) {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Filter = "Textdateien (*.txt)|*.txt|Alle Dateien (*.*)|*.*";
            openFileDialog1.FilterIndex = 3;// zählt von 1 !
            openFileDialog1.DefaultExt = ".txt";
            openFileDialog1.InitialDirectory = Environment.CurrentDirectory;
            openFileDialog1.Multiselect = false;
            openFileDialog1.RestoreDirectory = true;
            if (openFileDialog1.ShowDialog() == DialogResult.OK) {
                string sFilename = openFileDialog1.FileName.ToString();
                loadFile(sFilename);
            }
        }  // MnOpenSQLFile_Click

        private void loadFile(string sFilename) {
            string sStr;
            FileStream oFile = new FileStream(sFilename, FileMode.Open);
            StreamReader inStream = new StreamReader(oFile);
            sStr = inStream.ReadToEnd();
            ESQL.Text = sStr;
            inStream.Close();
            oFile.Close();
        }  // loadFile

        private string getFilename() {
            string sFilename = "";
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.Filter = "Textdateien (*.txt)|*.txt|Alle Dateien (*.*)|*.*";
            saveFileDialog1.FilterIndex = 1;// zählt von 1 !
            saveFileDialog1.DefaultExt = ".txt";
            saveFileDialog1.InitialDirectory = Environment.CurrentDirectory;
            saveFileDialog1.RestoreDirectory = true;
            if (saveFileDialog1.ShowDialog() == DialogResult.OK) {
                sFilename = saveFileDialog1.FileName.ToString();

            }
            return sFilename;
        }  // getFilename

        private void MnSaveSQL_Befehl_Click(object sender, EventArgs e) {
            string sFilename = getFilename();
            if (!sFilename.Equals("")) saveFile(sFilename);
        } // MnSaveSQL_Befehl_Click

        private void saveFile(string sFilename) {
            FileStream oFile = new FileStream(sFilename, FileMode.Create);
            StreamWriter oStream = new StreamWriter(oFile);
            oStream.WriteLine(ESQL.Text);
            oStream.Close();
            oFile.Close();
        }  // SaveFile


        private void MnClose_Click(object sender, EventArgs e) {
            Close();
        }

        private void MnRunSQL_Click(object sender, EventArgs e) {
            DateTime Time1, Time2;
            string sSQL = ESQL.Text.Trim();
            if (sSQL.Equals("")) return;

            // loop ueber alle SQL-Anweisungen
            String[] sql_commands = sSQL.Split( new Char[] { ';'} );
            Time1 = DateTime.Now;
            foreach (String sql in sql_commands)  {
                if (!sql.Trim().Equals("")) {
                    DataTable dt1 = _dbs.getDBSData(sql);
                    LAnzahl.Text = "Anzahl: " + dt1.Rows.Count.ToString() + " ";
                    Grid1.Visible = false;

                    Grid1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
                    Grid1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;
                    Grid1.DataSource = dt1;

                    Grid1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader;
                    Grid1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;
                }
            }
            Time2 = DateTime.Now;
            Grid1.Visible = true;
            Grid1.AllowUserToAddRows = false;
            Grid1.AllowUserToDeleteRows = false;
            Grid1.AllowUserToOrderColumns = true;

            TimeSpan duration1 = Time2 - Time1;
            Text = "Zeit: " + duration1;
            if (sSQL.ToLower().IndexOf("create") >= 0 || sSQL.ToLower().IndexOf("alter") >= 0 ||
                sSQL.ToLower().IndexOf("update") >= 0 || sSQL.ToLower().IndexOf("delete") >= 0) {
                listBox2.Items.Clear();
                setTables();
            }
        }  // MnRunSQL_Click


        private void MnExportErgebnis_Click(object sender, EventArgs e) {
            Clipboard.SetText(getResults());
        }  // MnExportErgebnis_Click

        private String getResults() {
            string sExport = "";
            string sStr;

            // Ablauf mit Zeilen und Spalten, geht von unten nach oben
            for (int col = 0; col < Grid1.ColumnCount; col++) {
                sStr = Grid1.Columns[col].HeaderText;
                if (col == 0)
                    sExport = sStr;
                else
                    sExport = sExport + "\t" + sStr;
            }
            for (int row = 0; row < Grid1.RowCount; row++) {
                for (int col = 0; col < Grid1.ColumnCount; col++) {
                    if (Grid1.Rows[row].Cells[col].Value != null) {
                        sStr = Grid1.Rows[row].Cells[col].Value.ToString();
                        if (col == 0)
                            sExport = sExport + "\r\n" + sStr;
                        else
                            sExport = sExport + "\t" + sStr;

                    }
                }
            }
            return sExport;
        }  // getResults

        private void MnSaveErgebnisseToFile_Click(object sender, EventArgs e) {
            string sFilename = getFilename();
            if (!sFilename.Equals("")) saveResults2File(sFilename);
        }  // MnSaveErgebnisseToFile_Click

        private void saveResults2File(string sFilename) {
            String sErg = getResults();
            FileStream oFile = new FileStream(sFilename, FileMode.Create);
            StreamWriter oStream = new StreamWriter(oFile);
            oStream.WriteLine(sErg);
            oStream.Close();
            oFile.Close();
        }  // saveResults2File

       

        public DataTable getDataTyps(string sTable) {
            string[] filter = new string[4];  // Schränkt das Ergebnis ein
            filter[0] = null; // Katalog  TABLE_CATALOG     z. B. dbo
            filter[1] = null; // Besitzer  TABLE_SCHEMA
            filter[2] = sTable;  // Tabellenname
            filter[3] = null;  // TABLE_TYPE  (System oder 
            DataTable dt;
            dt = new DataTable("RELATIONS");
            // dt = _cnn.GetSchema("Columns");  // liefert alle Spalten
            dt = _dbs.GetSchema("Columns", filter);
            return dt;
        }

        private void mnUndo_Click(object sender, EventArgs e) {
            ESQL.Undo();
        }

        private void mnRedo_Click(object sender, EventArgs e) {
            //ESQL.Red
        }

        private void mnCut_Click(object sender, EventArgs e) {
            ESQL.Cut();
        }

        private void mnCopy_Click(object sender, EventArgs e) {
            ESQL.Copy();
        }

        private void mnPaste_Click(object sender, EventArgs e) {
            ESQL.Paste();
        }

        private void mnSelectAll_Click(object sender, EventArgs e) {
            ESQL.SelectAll();
        }

        private void mnSamples_Click(object sender, EventArgs e) {
            FSamples dialog = new FSamples();
            dialog.Show();
        }

        


    }
}
