using CineBook.Models;
using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Security.Cryptography;
using System.Text;

namespace CineBook.Data;

// Quelle: Eigene Implementierung basierend auf Unterrichtsmaterial + MySqlConnector Dokumentation
public class Repository
{
    private readonly Db _db;
    public Repository(Db db) => _db = db;

    // ==================== USERS ====================
    public User? GetUserByCredentials(string username, string password)
    {
        var hash = HashPassword(password);
        var users = _db.Query(
            "SELECT * FROM Users WHERE Username = @u AND PasswordHash = @p LIMIT 1",
            new Dictionary<string, object?> { ["@u"] = username, ["@p"] = hash },
            r => new User
            {
                UserID = r.GetInt32("UserID"),
                Username = r.GetString("Username"),
                PasswordHash = r.GetString("PasswordHash"),
                Role = r.GetString("Role"),
                Email = r.IsDBNull(r.GetOrdinal("Email")) ? "" : r.GetString("Email"),
                CreatedAt = r.GetDateTime("CreatedAt")
            });
        return users.Count > 0 ? users[0] : null;
    }

    public List<User> GetAllUsers() => _db.Query(
        "SELECT * FROM Users ORDER BY Username",
        r => new User
        {
            UserID = r.GetInt32("UserID"),
            Username = r.GetString("Username"),
            PasswordHash = r.GetString("PasswordHash"),
            Role = r.GetString("Role"),
            Email = r.IsDBNull(r.GetOrdinal("Email")) ? "" : r.GetString("Email"),
            CreatedAt = r.GetDateTime("CreatedAt")
        });

    public bool CreateUser(string username, string password, string role, string email)
    {
        if (string.IsNullOrWhiteSpace(username) || username.Length < 3) return false;
        if (string.IsNullOrWhiteSpace(password) || password.Length < 6) return false;
        try
        {
            _db.Execute(
                "INSERT INTO Users (Username, PasswordHash, Role, Email) VALUES (@u, @p, @r, @e)",
                new Dictionary<string, object?> { ["@u"] = username, ["@p"] = HashPassword(password), ["@r"] = role, ["@e"] = email });
            return true;
        }
        catch { return false; }
    }

    public bool UpdateUser(int id, string username, string role, string email)
    {
        try
        {
            _db.Execute("UPDATE Users SET Username=@u, Role=@r, Email=@e WHERE UserID=@id",
                new Dictionary<string, object?> { ["@u"] = username, ["@r"] = role, ["@e"] = email, ["@id"] = id });
            return true;
        }
        catch { return false; }
    }

    public bool DeleteUser(int id)
    {
        try { _db.Execute("DELETE FROM Users WHERE UserID=@id", new Dictionary<string, object?> { ["@id"] = id }); return true; }
        catch { return false; }
    }

    // ==================== MOVIES ====================
    public List<Movie> GetMovies(bool onlyActive = false)
    {
        var sql = onlyActive
            ? "SELECT * FROM Movies WHERE IsActive=1 ORDER BY Title"
            : "SELECT * FROM Movies ORDER BY Title";
        return _db.Query(sql, r => MapMovie(r));
    }

    public List<Movie> SearchMovies(string term) => _db.Query(
        "SELECT * FROM Movies WHERE Title LIKE @t OR Genre LIKE @t ORDER BY Title",
        new Dictionary<string, object?> { ["@t"] = $"%{term}%" },
        r => MapMovie(r));

    public bool CreateMovie(Movie m)
    {
        if (string.IsNullOrWhiteSpace(m.Title)) return false;
        try
        {
            _db.Execute(
                "INSERT INTO Movies (Title, Genre, DurationMinutes, Description, Rating, IsActive) VALUES (@t,@g,@d,@desc,@r,1)",
                new Dictionary<string, object?> { ["@t"] = m.Title, ["@g"] = m.Genre, ["@d"] = m.DurationMinutes, ["@desc"] = m.Description, ["@r"] = m.Rating });
            return true;
        }
        catch { return false; }
    }

    public bool UpdateMovie(Movie m)
    {
        try
        {
            _db.Execute("UPDATE Movies SET Title=@t, Genre=@g, DurationMinutes=@d, Description=@desc, Rating=@r, IsActive=@a WHERE MovieID=@id",
                new Dictionary<string, object?> { ["@t"] = m.Title, ["@g"] = m.Genre, ["@d"] = m.DurationMinutes, ["@desc"] = m.Description, ["@r"] = m.Rating, ["@a"] = m.IsActive ? 1 : 0, ["@id"] = m.MovieID });
            return true;
        }
        catch { return false; }
    }

    public bool DeleteMovie(int id)
    {
        try { _db.Execute("DELETE FROM Movies WHERE MovieID=@id", new Dictionary<string, object?> { ["@id"] = id }); return true; }
        catch { return false; }
    }

    private Movie MapMovie(MySqlDataReader r) => new Movie
    {
        MovieID = r.GetInt32("MovieID"),
        Title = r.GetString("Title"),
        Genre = r.IsDBNull(r.GetOrdinal("Genre")) ? "" : r.GetString("Genre"),
        DurationMinutes = r.GetInt32("DurationMinutes"),
        Description = r.IsDBNull(r.GetOrdinal("Description")) ? "" : r.GetString("Description"),
        Rating = r.IsDBNull(r.GetOrdinal("Rating")) ? 0 : r.GetDecimal("Rating"),
        IsActive = r.GetBoolean("IsActive"),
        CreatedAt = r.GetDateTime("CreatedAt")
    };

    // ==================== SCREENINGS ====================
    public List<Screening> GetScreenings() => _db.Query(
        @"SELECT s.*, m.Title as MovieTitle FROM Screenings s
          JOIN Movies m ON s.MovieID=m.MovieID
          ORDER BY s.ScreeningDate, s.StartTime",
        r => MapScreening(r));

    public List<Screening> GetScreeningsByMovie(int movieId) => _db.Query(
        @"SELECT s.*, m.Title as MovieTitle FROM Screenings s
          JOIN Movies m ON s.MovieID=m.MovieID
          WHERE s.MovieID=@id ORDER BY s.ScreeningDate, s.StartTime",
        new Dictionary<string, object?> { ["@id"] = movieId },
        r => MapScreening(r));

    public bool CreateScreening(Screening s)
    {
        if (s.MovieID <= 0) return false;
        if (s.ScreeningDate == default) return false;
        if (s.TotalSeats <= 0) return false;
        if (s.PricePerSeat <= 0) return false;

        try
        {
            // Quelle/Hilfe: https://mysqlconnector.net/transactions/ (abgerufen: 2026-03-05)
            // Zweck: Screening + Seats atomar anlegen, damit Ticketbuchung immer Seat-Daten vorfindet.
            return _db.InTransaction((con, tx) =>
            {
                // Screening anlegen
                using var cmd = new MySqlCommand(
                    "INSERT INTO Screenings (MovieID, ScreeningDate, StartTime, Hall, TotalSeats, AvailableSeats, PricePerSeat) " +
                    "VALUES (@m,@d,@t,@h,@ts,@as,@p);",
                    con, tx);
                cmd.Parameters.AddWithValue("@m", s.MovieID);
                cmd.Parameters.AddWithValue("@d", s.ScreeningDate.Date);
                cmd.Parameters.AddWithValue("@t", s.StartTime.ToString(@"hh\:mm\:ss"));
                cmd.Parameters.AddWithValue("@h", s.Hall);
                cmd.Parameters.AddWithValue("@ts", s.TotalSeats);
                cmd.Parameters.AddWithValue("@as", s.TotalSeats);
                cmd.Parameters.AddWithValue("@p", s.PricePerSeat);
                cmd.ExecuteNonQuery();
                var screeningId = (int)cmd.LastInsertedId;

                // Seats generieren (einfaches Kino-Layout: Reihen A.., Sitznummer 1..10)
                GenerateSeats(con, tx, screeningId, s.TotalSeats);
                return true;
            });
        }
        catch { return false; }
    }

    public bool UpdateScreening(Screening s)
    {
        if (s.ScreeningID <= 0) return false;
        if (s.MovieID <= 0) return false;
        if (s.ScreeningDate == default) return false;
        if (s.PricePerSeat <= 0) return false;

        try
        {
            // AvailableSeats wird NICHT blind überschrieben, damit bestehende Buchungen nicht zerstört werden.
            _db.Execute(
                "UPDATE Screenings SET MovieID=@m, ScreeningDate=@d, StartTime=@t, Hall=@h, PricePerSeat=@p WHERE ScreeningID=@id",
                new Dictionary<string, object?>
                {
                    ["@m"] = s.MovieID,
                    ["@d"] = s.ScreeningDate.Date,
                    ["@t"] = s.StartTime.ToString(@"hh\:mm\:ss"),
                    ["@h"] = s.Hall,
                    ["@p"] = s.PricePerSeat,
                    ["@id"] = s.ScreeningID
                });
            return true;
        }
        catch { return false; }
    }

    public bool DeleteScreening(int id)
    {
        try { _db.Execute("DELETE FROM Screenings WHERE ScreeningID=@id", new Dictionary<string, object?> { ["@id"] = id }); return true; }
        catch { return false; }
    }

    private Screening MapScreening(MySqlDataReader r) => new Screening
    {
        ScreeningID = r.GetInt32("ScreeningID"),
        MovieID = r.GetInt32("MovieID"),
        MovieTitle = r.IsDBNull(r.GetOrdinal("MovieTitle")) ? "" : r.GetString("MovieTitle"),
        ScreeningDate = r.GetDateTime("ScreeningDate"),
        StartTime = r.GetTimeSpan("StartTime"),
        Hall = r.GetString("Hall"),
        TotalSeats = r.GetInt32("TotalSeats"),
        AvailableSeats = r.GetInt32("AvailableSeats"),
        PricePerSeat = r.GetDecimal("PricePerSeat")
    };

    // ==================== SEATS ====================
    public List<Seat> GetSeatsByScreening(int screeningId) => _db.Query(
        "SELECT * FROM Seats WHERE ScreeningID=@id ORDER BY Row, SeatNumber",
        new Dictionary<string, object?> { ["@id"] = screeningId },
        r => new Seat
        {
            SeatID = r.GetInt32("SeatID"),
            ScreeningID = r.GetInt32("ScreeningID"),
            Row = r.GetString("Row"),
            SeatNumber = r.GetInt32("SeatNumber"),
            IsBooked = r.GetBoolean("IsBooked"),
            Category = r.GetString("Category")
        });

    public bool EnsureSeatsForScreening(int screeningId, int totalSeats)
    {
        if (screeningId <= 0 || totalSeats <= 0) return false;
        try
        {
            return _db.InTransaction((con, tx) =>
            {
                var existing = _db.Query(con, tx,
                    "SELECT COUNT(*) FROM Seats WHERE ScreeningID=@id",
                    new Dictionary<string, object?> { ["@id"] = screeningId },
                    r => r.GetInt32(0));
                if (existing.Count > 0 && existing[0] > 0) return true;
                GenerateSeats(con, tx, screeningId, totalSeats);
                return true;
            });
        }
        catch { return false; }
    }

    // ==================== BOOKINGS ====================
    public List<Booking> GetAllBookings() => _db.Query(
        @"SELECT b.*, u.Username, m.Title as MovieTitle, s.ScreeningDate, s.StartTime,
                 CONCAT(se.Row, se.SeatNumber) as SeatDisplay
          FROM Bookings b
          JOIN Users u ON b.UserID=u.UserID
          JOIN Screenings s ON b.ScreeningID=s.ScreeningID
          JOIN Movies m ON s.MovieID=m.MovieID
          JOIN Seats se ON b.SeatID=se.SeatID
          ORDER BY b.BookingDate DESC",
        r => MapBooking(r));

    public List<Booking> GetBookingsByUser(int userId) => _db.Query(
        @"SELECT b.*, u.Username, m.Title as MovieTitle, s.ScreeningDate, s.StartTime,
                 CONCAT(se.Row, se.SeatNumber) as SeatDisplay
          FROM Bookings b
          JOIN Users u ON b.UserID=u.UserID
          JOIN Screenings s ON b.ScreeningID=s.ScreeningID
          JOIN Movies m ON s.MovieID=m.MovieID
          JOIN Seats se ON b.SeatID=se.SeatID
          WHERE b.UserID=@uid ORDER BY b.BookingDate DESC",
        new Dictionary<string, object?> { ["@uid"] = userId },
        r => MapBooking(r));

    public bool CreateBooking(int userId, int screeningId, int seatId, decimal price)
    {
        if (userId <= 0 || screeningId <= 0 || seatId <= 0) return false;
        if (price <= 0) return false;

        try
        {
            // Quelle/Hilfe: https://mysqlconnector.net/transactions/ (abgerufen: 2026-03-05)
            // Zweck: Seat sperren + AvailableSeats updaten + Booking anlegen atomar.
            return _db.InTransaction((con, tx) =>
            {
                var code = "CB-" + DateTime.Now.ToString("yyyyMMddHHmmss");

                // Seat nur buchen, wenn er noch frei ist
                var seatUpdated = _db.Execute(con, tx,
                    "UPDATE Seats SET IsBooked=1 WHERE SeatID=@sid AND ScreeningID=@sc AND IsBooked=0",
                    new Dictionary<string, object?> { ["@sid"] = seatId, ["@sc"] = screeningId });
                if (seatUpdated != 1) return false;

                // AvailableSeats nur verringern, wenn noch > 0
                var seatsCounterUpdated = _db.Execute(con, tx,
                    "UPDATE Screenings SET AvailableSeats=AvailableSeats-1 WHERE ScreeningID=@id AND AvailableSeats > 0",
                    new Dictionary<string, object?> { ["@id"] = screeningId });
                if (seatsCounterUpdated != 1) return false;

                _db.Execute(con, tx,
                    "INSERT INTO Bookings (UserID, ScreeningID, SeatID, TotalPrice, Status, BookingCode) VALUES (@u,@s,@seat,@p,'Confirmed',@c)",
                    new Dictionary<string, object?> { ["@u"] = userId, ["@s"] = screeningId, ["@seat"] = seatId, ["@p"] = price, ["@c"] = code });
                return true;
            });
        }
        catch { return false; }
    }

    public bool CancelBooking(int bookingId, int seatId, int screeningId)
    {
        if (bookingId <= 0 || seatId <= 0 || screeningId <= 0) return false;
        try
        {
            return _db.InTransaction((con, tx) =>
            {
                var b = _db.Execute(con, tx,
                    "UPDATE Bookings SET Status='Cancelled' WHERE BookingID=@id AND Status='Confirmed'",
                    new Dictionary<string, object?> { ["@id"] = bookingId });
                if (b != 1) return false;

                _db.Execute(con, tx,
                    "UPDATE Seats SET IsBooked=0 WHERE SeatID=@sid AND ScreeningID=@sc",
                    new Dictionary<string, object?> { ["@sid"] = seatId, ["@sc"] = screeningId });

                _db.Execute(con, tx,
                    "UPDATE Screenings SET AvailableSeats=AvailableSeats+1 WHERE ScreeningID=@id",
                    new Dictionary<string, object?> { ["@id"] = screeningId });
                return true;
            });
        }
        catch { return false; }
    }

    private static void GenerateSeats(MySqlConnection con, MySqlTransaction tx, int screeningId, int totalSeats)
    {
        // Quelle/Hilfe: Eigene Implementierung (kein Unterrichtsthema), Layout-Logik dokumentiert.
        // Layout:
        // - Standardmäßig 10 Sitze pro Reihe.
        // - Reihen A, B, C, ...
        // - VIP: Reihen C und D (falls vorhanden)
        var seatsPerRow = 10;
        var rowsNeeded = (int)Math.Ceiling(totalSeats / (double)seatsPerRow);
        var created = 0;
        for (var i = 0; i < rowsNeeded; i++)
        {
            var rowChar = (char)('A' + i);
            for (var seat = 1; seat <= seatsPerRow && created < totalSeats; seat++)
            {
                var category = (rowChar == 'C' || rowChar == 'D') ? "VIP" : "Standard";
                using var cmd = new MySqlCommand(
                    "INSERT INTO Seats (ScreeningID, Row, SeatNumber, IsBooked, Category) VALUES (@sid,@r,@n,0,@c)",
                    con, tx);
                cmd.Parameters.AddWithValue("@sid", screeningId);
                cmd.Parameters.AddWithValue("@r", rowChar.ToString());
                cmd.Parameters.AddWithValue("@n", seat);
                cmd.Parameters.AddWithValue("@c", category);
                cmd.ExecuteNonQuery();
                created++;
            }
        }
    }

    private Booking MapBooking(MySqlDataReader r) => new Booking
    {
        BookingID = r.GetInt32("BookingID"),
        UserID = r.GetInt32("UserID"),
        Username = r.GetString("Username"),
        ScreeningID = r.GetInt32("ScreeningID"),
        MovieTitle = r.GetString("MovieTitle"),
        ScreeningDate = r.GetDateTime("ScreeningDate"),
        StartTime = r.GetTimeSpan("StartTime"),
        SeatID = r.GetInt32("SeatID"),
        SeatDisplay = r.GetString("SeatDisplay"),
        BookingDate = r.GetDateTime("BookingDate"),
        TotalPrice = r.GetDecimal("TotalPrice"),
        Status = r.GetString("Status"),
        BookingCode = r.IsDBNull(r.GetOrdinal("BookingCode")) ? "" : r.GetString("BookingCode")
    };

    // ==================== STATS ====================
    public (int movies, int screenings, int bookings, int users) GetStats()
    {
        var m = _db.Query("SELECT COUNT(*) FROM Movies WHERE IsActive=1", r => r.GetInt32(0));
        var s = _db.Query("SELECT COUNT(*) FROM Screenings WHERE ScreeningDate >= CURDATE()", r => r.GetInt32(0));
        var b = _db.Query("SELECT COUNT(*) FROM Bookings WHERE Status='Confirmed'", r => r.GetInt32(0));
        var u = _db.Query("SELECT COUNT(*) FROM Users", r => r.GetInt32(0));
        return (m[0], s[0], b[0], u[0]);
    }

    public static string HashPassword(string password)
    {
        // Quelle: https://learn.microsoft.com/en-us/dotnet/api/system.security.cryptography.sha256 (abgerufen: 2026-02-01)
        using var sha = SHA256.Create();
        var bytes = sha.ComputeHash(Encoding.UTF8.GetBytes(password));
        return Convert.ToHexString(bytes).ToLower();
    }
}