using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SQLite;

namespace Test7
{
    class Databaze
    {
        private void ExecuteNonQuery(string sql, SQLiteConnection connection)
        {
            using (SQLiteCommand command = new SQLiteCommand(sql, connection))
            {
                command.ExecuteNonQuery();
            }
        }

        public void Vytvor()
        {
            string databazeFileName = "mojedb.sqlite";
            string connectionString = $"Data Source={databazeFileName};Version=3;";

            if (!System.IO.File.Exists(databazeFileName))
            {
                SQLiteConnection.CreateFile(databazeFileName);


                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();


                    string sql = "CREATE TABLE kategorie (kategorieID INTEGER PRIMARY KEY, nazev VARCHAR(256))";
                    ExecuteNonQuery(sql, connection);

                    sql = "CREATE TABLE produkt (productID INTEGER PRIMARY KEY, cena REAL, nazev VARCHAR(256), popis VARCHAR(1024), kategorieID INTEGER REFERENCES kategorie(kategorieID))";
                    ExecuteNonQuery(sql, connection);

                    sql = "CREATE TABLE zakaznik (zakaznikID INTEGER PRIMARY KEY, jmeno VARCHAR(256), adresa VARCHAR(1024))";
                    ExecuteNonQuery(sql, connection);

                    sql = "CREATE TABLE stav (stavID INTEGER PRIMARY KEY, nazev VARCHAR(256))";
                    ExecuteNonQuery(sql, connection);

                    sql = "CREATE TABLE objednavka (objednavkaID INTEGER PRIMARY KEY, zakaznikID INTEGER REFERENCES zakaznik(zakaznikID), cena REAL, stavID INTEGER REFERENCES stav(stavID))";
                    ExecuteNonQuery(sql, connection);

                    sql = "CREATE TABLE polozka (polozkaID INTEGER PRIMARY KEY, objednavkaID INTEGER REFERENCES objednavka(objednavkaID), produktID INTEGER REFERENCES produkt(produktID), mnozstvi INTEGER, cena REAL)";
                    ExecuteNonQuery(sql, connection);

                    sql = "INSERT INTO stav(stavID,nazev) VALUES(1,'kosik'),(2,'potvrzena'),(3,'odeslana'),(4,'zrusena')";
                    ExecuteNonQuery(sql, connection);

                    connection.Close();
                }
            }
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Databaze databaze = new Databaze();
            databaze.Vytvor();

        }
    }
}