namespace MTG_CollectionVerwaltung.Data
{
    using Microsoft.EntityFrameworkCore;
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;

    namespace MTGCollection
    {
        public class MTGContext : DbContext
        {
            public MTGContext(DbContextOptions<MTGContext> options)
                : base(options)
            {
                var conn = Database.GetDbConnection();
                conn.Open();

                // SQLite Funktion registrieren
                var sqliteConn = (Microsoft.Data.Sqlite.SqliteConnection)conn;
                sqliteConn.CreateFunction<string, string, int, int>("levenshtein", LevenshteinMinWordDistanceByLength);
            }

            public DbSet<Card> Cards { get; set; }
            public DbSet<CardPrinting> CardPrintings { get; set; }
            public DbSet<CardType> CardTypes { get; set; }
            public DbSet<MTGType> Types { get; set; }
            public DbSet<Set> Sets { get; set; }
            public DbSet<CardCollection> CardCollections { get; set; }
            public DbSet<Supertype> Supertypes { get; set; }
            public DbSet<CardPrintingFts> CardPrintingsFts { get; set; }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                // CardPrinting: zusammengesetzter Schlüssel
                modelBuilder.Entity<CardPrinting>()
                    .HasIndex(cp => new { cp.CardId, cp.Lang })
                    .IsUnique(); // falls du weiterhin Eindeutigkeit auf CardId+Lang willst

                modelBuilder.Entity<CardPrinting>()
                    .HasOne(cp => cp.Card)
                    .WithMany(c => c.Printings)
                    .HasForeignKey(cp => cp.CardId);

                // CardType: zusammengesetzter Schlüssel
                modelBuilder.Entity<CardType>()
                    .HasKey(ct => new { ct.CardId, ct.TypeId });

                modelBuilder.Entity<CardType>()
                    .HasOne(ct => ct.Card)
                    .WithMany(c => c.CardTypes)
                    .HasForeignKey(ct => ct.CardId);

                modelBuilder.Entity<CardType>()
                    .HasOne(ct => ct.Type)
                    .WithMany()
                    .HasForeignKey(ct => ct.TypeId);

                // CardCollection: zusammengesetzter Schlüssel
                modelBuilder.Entity<CardCollection>()
                    .HasKey(cc => new { cc.CardId, cc.UserId });

                modelBuilder.Entity<CardCollection>()
                    .HasOne(cc => cc.Card)
                    .WithMany(c => c.Collections)
                    .HasForeignKey(cc => cc.CardId);

                // Card -> Set
                modelBuilder.Entity<Card>()
                    .HasOne(c => c.Set)
                    .WithMany(s => s.Cards)
                    .HasForeignKey(c => c.SetCode)
                    .HasPrincipalKey(s => s.SetCode);

                // Card -> Supertype
                modelBuilder.Entity<Card>()
                    .HasOne(c => c.SuperType)
                    .WithMany()
                    .HasForeignKey(c => c.SuperTypeId);

                // Indizes
                modelBuilder.Entity<Card>()
                    .HasIndex(c => c.CardName);

                modelBuilder.Entity<CardCollection>()
                    .HasIndex(cc => cc.UserId);

                modelBuilder.Entity<Card>()
                    .HasIndex(c => new { c.CollectorNumber, c.SetCode });

                modelBuilder.Entity<CardPrinting>()
                    .HasIndex(c => c.Name);

                modelBuilder.Entity<CardPrinting>()
                    .HasIndex(c => c.OracleText);

                // Default Values
                modelBuilder.Entity<Card>()
                    .Property(c => c.Foil)
                    .HasDefaultValue(false);

                modelBuilder.Entity<Card>()
                    .Property(c => c.NonFoil)
                    .HasDefaultValue(false);

                //Remove CaseSensitivity
                modelBuilder.Entity<CardPrinting>()
                    .Property(c => c.Name)
                    .UseCollation("NOCASE");

                modelBuilder.Entity<CardPrinting>()
                    .Property(c => c.OracleText)
                    .UseCollation("NOCASE");

                modelBuilder.Entity<MTGType>()
                    .Property(c => c.TypeEn)
                    .UseCollation("NOCASE");

                modelBuilder.Entity<MTGType>()
                    .Property(c => c.TypeDe)
                    .UseCollation("NOCASE");

                modelBuilder.Entity<Supertype>()
                    .Property(c => c.TypeEn)
                    .UseCollation("NOCASE");

                modelBuilder.Entity<Supertype>()
                    .Property(c => c.TypeDe)
                    .UseCollation("NOCASE");

                modelBuilder.Entity<CardPrintingFts>(entity =>
                {
                    entity.HasNoKey();      // Keyless, weil FTS rowid nicht als PK nutzbar
                    entity.ToView(null);    // keine Migration/View erzeugen
                    entity.Property(e => e.Name);
                    entity.Property(e => e.OracleText);
                    entity.Property(e => e.FlavorText);
                });
            }

            public static int LevenshteinMinWordDistanceByLength(string s, string t, int tolerance = 2)
            {
                if (string.IsNullOrEmpty(s)) return t?.Length ?? 0;
                if (string.IsNullOrEmpty(t)) return s.Length;

                if (s.Contains(t)) return 0;

                var wordsS = s.Split(new[] { ' ', '-', '_', '`', '\'', ',' }, StringSplitOptions.RemoveEmptyEntries);
                var wordsT = t.Split(new[] { ' ', '-', '_', '`', '\'', ',' }, StringSplitOptions.RemoveEmptyEntries);

                int minDistance = int.MaxValue;

                foreach (var ws in wordsS)
                {
                    // nur Wörter in sinnvollem Längenbereich vergleichen
                    if (Math.Abs(ws.Length - t.Length) <= tolerance)
                    {
                        foreach (var wt in wordsT)
                        {
                            if (Math.Abs(ws.Length - wt.Length) <= tolerance)
                            {
                                int dist = Levenshtein(ws, wt);
                                if (dist < minDistance)
                                    minDistance = dist;
                            }
                        }
                    }
                }

                return minDistance;
            }


            public static int Levenshtein(string s, string t)
            {
                if (string.IsNullOrEmpty(s)) return t?.Length ?? 0;
                if (string.IsNullOrEmpty(t)) return s.Length;

                int[,] d = new int[s.Length + 1, t.Length + 1];

                for (int i = 0; i <= s.Length; i++) d[i, 0] = i;
                for (int j = 0; j <= t.Length; j++) d[0, j] = j;

                for (int i = 1; i <= s.Length; i++)
                {
                    for (int j = 1; j <= t.Length; j++)
                    {
                        int cost = (s[i - 1] == t[j - 1]) ? 0 : 1;
                        d[i, j] = Math.Min(
                            Math.Min(d[i - 1, j] + 1, d[i, j - 1] + 1),
                            d[i - 1, j - 1] + cost);
                    }
                }
                return d[s.Length, t.Length];
            }

            public void EnsureFtsInitialized()
            {
                using var conn = (Microsoft.Data.Sqlite.SqliteConnection)Database.GetDbConnection();
                conn.Open();

                // Prüfen, ob FTS-Tabelle schon existiert
                using var checkCmd = conn.CreateCommand();
                checkCmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='CardPrintingsFTS';";
                var exists = checkCmd.ExecuteScalar() != null;

                if (!exists)
                {
                    // FTS-Tabelle erstellen
                    using var createCmd = conn.CreateCommand();
                    createCmd.CommandText = @"
                        CREATE VIRTUAL TABLE CardPrintingsFTS
                        USING fts5(
                        Name,
                        OracleText,
                        FlavorText,
                        content='CardPrintings',
                        content_rowid='Id',
                        tokenize='trigram'
                        );
                    ";
                    createCmd.ExecuteNonQuery();

                }
            }


            public void RebuildCardPrintingsFts()
            {
                using var conn = (Microsoft.Data.Sqlite.SqliteConnection)Database.GetDbConnection();
                conn.Open();

                using var cmd = conn.CreateCommand();
                cmd.CommandText = @"INSERT INTO CardPrintingsFTS(CardPrintingsFTS) VALUES('rebuild');";
                cmd.ExecuteNonQuery();
            }

        }


    

        public class Card
        {
            [Key]
            public string Id { get; set; } = string.Empty;

            [Required, MaxLength(10)]
            public string SetCode { get; set; } = string.Empty;

            [Required, MaxLength(100)]
            public string CardName { get; set; } = string.Empty;

            public string? CollectorNumber { get; set; }
            public string? ManaCost { get; set; }
            public bool Foil { get; set; }
            public bool NonFoil { get; set; }
            public string? Rarity { get; set; }
            public string? Power { get; set; }
            public string? Toughness { get; set; }
            public int SuperTypeId { get; set; }
            public int Cmc { get; set; }
            public string? OtherFace { get; set; }

            public Set Set { get; set; }
            public Supertype SuperType { get; set; }
            public ICollection<CardPrinting> Printings { get; set; } = new List<CardPrinting>();
            public ICollection<CardType> CardTypes { get; set; } = new List<CardType>();
            public ICollection<CardCollection> Collections { get; set; } = new List<CardCollection>();
        }

        public class CardPrinting
        {
            [Key]
            public int Id { get; set; }

            [Required, MaxLength(40)]
            public string CardId { get; set; }

            [Required, MaxLength(5)]
            public string Lang { get; set; }

            [Required, MaxLength(100)]
            public string Name { get; set; }
            public string? OracleText { get; set; }
            public string? FlavorText { get; set; }
            public string? ImageUriSmall { get; set; }
            public string? ImageUriNormal { get; set; }
            public string? ImageUriLarge { get; set; }

            public Card Card { get; set; }

        }

        [Keyless]
        public class CardPrintingFts
        {
            public string Name { get; set; }
            public string? OracleText { get; set; }
            public string? FlavorText { get; set; }
        }

        public class CardType
        {
            [Required]
            public string CardId { get; set; }

            [Required]
            public int TypeId { get; set; }

            public Card Card { get; set; }
            public MTGType Type { get; set; }
        }

        public class MTGType
        {
            [Key]
            public int TypeId { get; set; }
            public string TypeEn { get; set; } = "";
            public string TypeDe { get; set; } = "";
        }

        public class Supertype
        {
            [Key]
            public int TypeId { get; set; }
            public string TypeEn { get; set; } = "";
            public string TypeDe { get; set; } = "";
        }

        public class Set
        {
            [Key, MaxLength(10)]
            public string SetCode { get; set; }

            [Required, MaxLength(50)]
            public string SetName { get; set; }

            public string SetIcon { get; set; }
            public string SetType { get; set; }
            public int CardCount { get; set; }
            public DateTime ReleaseDate { get; set; }

            public ICollection<Card> Cards { get; set; } = new List<Card>();
        }

        public class CardCollection
        {
            [Required]
            public string CardId { get; set; }

            [Required]
            public string UserId { get; set; }

            public int Count { get; set; }

            public Card Card { get; set; }
        }
    }
}