﻿using System;
using System.Data;
using System.IO;
using System.Linq;
using Kurswahl.DataModel;
using NUnit.Framework;
using ServiceStack.Common.Utils;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Sqlite;

namespace Kurswahl.Test
{
    [TestFixture]
    public class Database
    {
        private const string DefFirstName = "Max";
        private const string DefLastName = "Müller";
        private const string DefName = DefLastName + ", " + DefFirstName;

        public Database()
        {
            OrmLiteConfig.DialectProvider = SqliteOrmLiteDialectProvider.Instance;
        }

        private void CreateTables(bool overwrite, IDbConnection db)
        {
            db.CreateTables(overwrite, typeof(Kurs), typeof(Klasse), typeof(KursWahl), typeof(Schueler));
        }

        [Test]
        public void CreateTables()
        {
            using (IDbConnection db = ":memory:".OpenDbConnection())
            {
                const bool overwrite = false;
                CreateTables(overwrite, db);

                Assert.That(db.TableExists("Kurse"));
                Assert.That(db.TableExists("Klassen"));
                Assert.That(db.TableExists("Schueler"));
                Assert.That(db.TableExists("KursXSchueler"));
            }
        }

        [Test]
        public void TestData()
        {
            using (IDbConnection db = ":memory:".OpenDbConnection())
            {
                const bool overwrite = false;
                CreateTables(overwrite, db);

                db.Insert(
                    new Klasse("G10a"),
                    new Klasse("R7a"));

                db.Insert(new Schueler()
                    {
                        Vorname = DefFirstName,
                        Nachname = DefLastName,
                        KlassenId = db.Select<Klasse>(k => k.Stufe == 7).First().Id
                    });

                for (int i = 0; i < 25; i++)
                {
                    db.Insert(new Schueler()
                        {
                            Vorname = DefFirstName + i,
                            Nachname = DefLastName + i,
                            KlassenId = db.Select<Klasse>(k => k.Schulrichtung == Schulrichtung.Gymnasium).First().Id
                        });
                }

                var klasse = db.Select<Klasse>(k => k.Schulrichtung == Schulrichtung.Gymnasium);
                Assert.That(klasse.Count == 1);
                Assert.That(klasse[0].Bezeichnung == "G10a");

                var sch = db.Select<Schueler>(k => k.Nachname == DefLastName);
                Assert.That(sch.Count == 1);
                Schueler s = sch[0];
                Assert.That(s.Nachname == DefLastName);
                Assert.That(s.Vorname == DefFirstName);
                Assert.That(s.Name == DefName);
                Assert.That(db.Select<Klasse>(x => x.Id == s.KlassenId).First().Bezeichnung == "R7a");

                sch = db.Select<Schueler>(k => k.Name == DefName);
                Assert.That(sch.Count == 1);

                var exp = db.Select<Schueler>(
                    "SELECT * from schueler where KlassenId = (SELECT Id from Klassen where Bezeichnung = 'G10a')");

                Assert.That(exp.Count == 25);
                //Assert.That(db.Select<Klasse>(x => x.Schulrichtung == Schulrichtung.Gymnasium).Count == 1);

                //for (int i = 0; i < 20; i++)
                //{
                //    db.Insert(new Schueler());
                //}
            }
        }
    }
}
