using System;
using System.Collections.Generic;
using System.IO;
using Microsoft.Data.Sqlite;

namespace BoxPacker3D
{
    /// <summary>
    /// Persistent template of an item that can be re-used across packing sessions.
    /// Stored in an on-disk SQLite database.
    /// </summary>
    public class ItemTemplate
    {
        public int    Id { get; set; }
        public string Name { get; set; } = "";
        public double Width  { get; set; }
        public double Height { get; set; }
        public double Depth  { get; set; }

        public override string ToString() =>
            $"{Name}  ({Width}×{Height}×{Depth} mm)";
    }

    /// <summary>
    /// Simple SQLite-backed repository for <see cref="ItemTemplate"/>s.
    /// Database schema is created on first use.
    /// </summary>
    public class ItemDatabase
    {
        private readonly string _connectionString;

        public ItemDatabase(string dbPath)
        {
            // Ensure directory exists
            var dir = Path.GetDirectoryName(dbPath);
            if (!string.IsNullOrEmpty(dir) && !Directory.Exists(dir))
                Directory.CreateDirectory(dir);

            _connectionString = new SqliteConnectionStringBuilder
            {
                DataSource = dbPath,
                Mode = SqliteOpenMode.ReadWriteCreate
            }.ToString();

            EnsureSchema();
        }

        private void EnsureSchema()
        {
            using var conn = Open();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = @"
                CREATE TABLE IF NOT EXISTS items (
                    Id     INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name   TEXT NOT NULL UNIQUE COLLATE NOCASE,
                    Width  REAL NOT NULL,
                    Height REAL NOT NULL,
                    Depth  REAL NOT NULL
                );";
            cmd.ExecuteNonQuery();
        }

        private SqliteConnection Open()
        {
            var conn = new SqliteConnection(_connectionString);
            conn.Open();
            return conn;
        }

        /// <summary>Returns all templates ordered alphabetically by name.</summary>
        public List<ItemTemplate> GetAll()
        {
            var list = new List<ItemTemplate>();
            using var conn = Open();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT Id, Name, Width, Height, Depth FROM items ORDER BY Name COLLATE NOCASE;";
            using var rdr = cmd.ExecuteReader();
            while (rdr.Read())
                list.Add(new ItemTemplate
                {
                    Id     = rdr.GetInt32(0),
                    Name   = rdr.GetString(1),
                    Width  = rdr.GetDouble(2),
                    Height = rdr.GetDouble(3),
                    Depth  = rdr.GetDouble(4)
                });
            return list;
        }

        /// <summary>Returns the template with the given name or null if none exists.</summary>
        public ItemTemplate? FindByName(string name)
        {
            using var conn = Open();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT Id, Name, Width, Height, Depth FROM items WHERE Name = $n COLLATE NOCASE LIMIT 1;";
            cmd.Parameters.AddWithValue("$n", name);
            using var rdr = cmd.ExecuteReader();
            if (rdr.Read())
                return new ItemTemplate
                {
                    Id     = rdr.GetInt32(0),
                    Name   = rdr.GetString(1),
                    Width  = rdr.GetDouble(2),
                    Height = rdr.GetDouble(3),
                    Depth  = rdr.GetDouble(4)
                };
            return null;
        }

        /// <summary>Inserts or replaces a template (upsert by Name).</summary>
        public void Upsert(ItemTemplate item)
        {
            using var conn = Open();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = @"
                INSERT INTO items (Name, Width, Height, Depth)
                VALUES ($n, $w, $h, $d)
                ON CONFLICT(Name) DO UPDATE SET
                    Width = excluded.Width,
                    Height = excluded.Height,
                    Depth = excluded.Depth;";
            cmd.Parameters.AddWithValue("$n", item.Name);
            cmd.Parameters.AddWithValue("$w", item.Width);
            cmd.Parameters.AddWithValue("$h", item.Height);
            cmd.Parameters.AddWithValue("$d", item.Depth);
            cmd.ExecuteNonQuery();
        }

        public void DeleteByName(string name)
        {
            using var conn = Open();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = "DELETE FROM items WHERE Name = $n COLLATE NOCASE;";
            cmd.Parameters.AddWithValue("$n", name);
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// Checks if a template with the same name or exact dimensions already exists.
        /// Returns the existing template if found, null otherwise.
        /// </summary>
        public ItemTemplate? FindDuplicate(ItemTemplate item)
        {
            using var conn = Open();
            using var cmd = conn.CreateCommand();
            
            // Check for same name (case-insensitive)
            cmd.CommandText = "SELECT Id, Name, Width, Height, Depth FROM items WHERE Name = $n COLLATE NOCASE LIMIT 1;";
            cmd.Parameters.AddWithValue("$n", item.Name);
            using (var rdr = cmd.ExecuteReader())
            {
                if (rdr.Read())
                {
                    return new ItemTemplate
                    {
                        Id = rdr.GetInt32(0),
                        Name = rdr.GetString(1),
                        Width = rdr.GetDouble(2),
                        Height = rdr.GetDouble(3),
                        Depth = rdr.GetDouble(4)
                    };
                }
            }
            
            // Check for same dimensions (exact match)
            cmd.CommandText = @"
                SELECT Id, Name, Width, Height, Depth FROM items 
                WHERE Width = $w AND Height = $h AND Depth = $d 
                LIMIT 1;";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("$w", item.Width);
            cmd.Parameters.AddWithValue("$h", item.Height);
            cmd.Parameters.AddWithValue("$d", item.Depth);
            using (var rdr = cmd.ExecuteReader())
            {
                if (rdr.Read())
                {
                    return new ItemTemplate
                    {
                        Id = rdr.GetInt32(0),
                        Name = rdr.GetString(1),
                        Width = rdr.GetDouble(2),
                        Height = rdr.GetDouble(3),
                        Depth = rdr.GetDouble(4)
                    };
                }
            }
            
            return null;
        }
    }
}