using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Data;

namespace CineBook.Data;

// Quelle: https://mysqlconnector.net/ (abgerufen: 2026-01-29)
// Zweck: MySQL Zugriff mit parameterisierten Queries (Schutz vor SQL-Injection) + Fehlerbehandlung
public class Db
{
    private readonly string _cs;
    public Db(string connectionString) => _cs = connectionString;

    // Quelle/Hilfe: https://mysqlconnector.net/transactions/ (abgerufen: 2026-03-05)
    // Zweck: Mehrere DB-Statements atomar ausführen (z.B. Buchung + Seat-Update + Counter-Update)
    public T InTransaction<T>(Func<MySqlConnection, MySqlTransaction, T> work)
    {
        using var con = new MySqlConnection(_cs);
        con.Open();
        using var tx = con.BeginTransaction();
        try
        {
            var result = work(con, tx);
            tx.Commit();
            return result;
        }
        catch
        {
            try { tx.Rollback(); } catch { /* ignore rollback errors */ }
            throw;
        }
    }

    public int Execute(MySqlConnection con, MySqlTransaction tx, string sql, Dictionary<string, object?> p)
    {
        using var cmd = new MySqlCommand(sql, con, tx);
        foreach (var kv in p)
            cmd.Parameters.AddWithValue(kv.Key, kv.Value ?? DBNull.Value);
        return cmd.ExecuteNonQuery();
    }

    public List<T> Query<T>(MySqlConnection con, MySqlTransaction tx, string sql, Dictionary<string, object?> p, Func<MySqlDataReader, T> map)
    {
        using var cmd = new MySqlCommand(sql, con, tx);
        foreach (var kv in p)
            cmd.Parameters.AddWithValue(kv.Key, kv.Value ?? DBNull.Value);
        using var r = cmd.ExecuteReader();
        var list = new List<T>();
        while (r.Read()) list.Add(map(r));
        return list;
    }

    public List<T> Query<T>(string sql, Dictionary<string, object?> p, Func<MySqlDataReader, T> map)
    {
        try
        {
            using var con = new MySqlConnection(_cs);
            con.Open();
            using var cmd = new MySqlCommand(sql, con);
            foreach (var kv in p)
                cmd.Parameters.AddWithValue(kv.Key, kv.Value ?? DBNull.Value);
            using var r = cmd.ExecuteReader();
            var list = new List<T>();
            while (r.Read()) list.Add(map(r));
            return list;
        }
        catch (MySqlException ex)
        {
            throw new Exception("DB-Fehler: " + ex.Message, ex);
        }
    }

    public List<T> Query<T>(string sql, Func<MySqlDataReader, T> map)
        => Query(sql, new Dictionary<string, object?>(), map);

    public int Execute(string sql, Dictionary<string, object?> p)
    {
        try
        {
            using var con = new MySqlConnection(_cs);
            con.Open();
            using var cmd = new MySqlCommand(sql, con);
            foreach (var kv in p)
                cmd.Parameters.AddWithValue(kv.Key, kv.Value ?? DBNull.Value);
            return cmd.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            throw new Exception("DB-Fehler: " + ex.Message, ex);
        }
    }

    public long InsertAndGetId(string sql, Dictionary<string, object?> p)
    {
        try
        {
            using var con = new MySqlConnection(_cs);
            con.Open();
            using var cmd = new MySqlCommand(sql, con);
            foreach (var kv in p)
                cmd.Parameters.AddWithValue(kv.Key, kv.Value ?? DBNull.Value);
            cmd.ExecuteNonQuery();
            return cmd.LastInsertedId;
        }
        catch (MySqlException ex)
        {
            throw new Exception("DB-Fehler: " + ex.Message, ex);
        }
    }

    public bool TestConnection()
    {
        try
        {
            using var con = new MySqlConnection(_cs);
            con.Open();
            return con.State == ConnectionState.Open;
        }
        catch { return false; }
    }
}