namespace SkiResort.Models { public class Destination { public int Id { get; set; } public string Name { get; set; } public string? Country { get; set; } } } namespace SkiResort.Models { public class Resort { public int Id { get; set; } public string Name { get; set; } public int DestinationId { get; set; } } } namespace SkiResort.Models { public class Lift { public int Id { get; set; } public string Name { get; set; } public string LiftType { get; set; } public int VerticalDrop { get; set; } public int ResortId { get; set; } } } namespace SkiResort.Models { public class Season { public int Id { get; set; } public string Name { get; set; } public DateTime StartDate { get; set; } public DateTime EndDate { get; set; } } } namespace SkiResort.Models { public class Skier { public int Id { get; set; } public string Firstname { get; set; } public string Lastname { get; set; } public string Email { get; set; } public string Username { get; set; } public string ImageUrl { get; set; } } } namespace SkiResort.Models { public class SkiPass { public int Id { get; set; } public int CardNumber { get; set; } public DateTime ValidUntil { get; set; } public int SkierId { get; set; } } } namespace SkiResort.Models { public class SkiRun { public int Id { get; set; } public TimeSpan Timestamp { get; set; } public int LiftId { get; set; } public int SkiPassId { get; set; } public int SeasonId { get; set; } } } using Npgsql; using SkiResort.Models; using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Threading.Tasks; namespace SkiResort.Repository { public class SkiResortRepository { private readonly string _connectionString; public SkiResortRepository() { _connectionString = ConfigurationManager.AppSettings["ConnectionString"]; } public async Task AddSkierAsync(Skier skier) { using (var conn = new NpgsqlConnection(_connectionString)) { await conn.OpenAsync(); using (var cmd = new NpgsqlCommand( "INSERT INTO skiers (firstname, lastname, email, username, imageurl) VALUES (@firstname, @lastname, @email, @username, @imageurl) RETURNING id", conn)) { cmd.Parameters.AddWithValue("firstname", skier.Firstname); cmd.Parameters.AddWithValue("lastname", skier.Lastname); cmd.Parameters.AddWithValue("email", skier.Email); cmd.Parameters.AddWithValue("username", skier.Username); cmd.Parameters.AddWithValue("imageurl", skier.ImageUrl ?? (object)DBNull.Value); skier.Id = (int)await cmd.ExecuteScalarAsync(); } } } public async Task> SearchSkiersAsync(string firstName, string lastName) { var skiers = new List(); using (var conn = new NpgsqlConnection(_connectionString)) { await conn.OpenAsync(); using (var cmd = new NpgsqlCommand( "SELECT * FROM skiers WHERE firstname LIKE @firstname AND lastname LIKE @lastname", conn)) { cmd.Parameters.AddWithValue("firstname", $"%{firstName}%"); cmd.Parameters.AddWithValue("lastname", $"%{lastName}%"); using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { skiers.Add(new Skier { Id = reader.GetInt32(0), Firstname = reader.GetString(1), Lastname = reader.GetString(2), Email = reader.GetString(3), Username = reader.GetString(4), ImageUrl = reader.IsDBNull(5) ? null : reader.GetString(5) }); } } } } return skiers; } public async Task GetSkierInfoAsync(int skierId, int seasonId) { var info = new SkierInfo(); using (var conn = new NpgsqlConnection(_connectionString)) { await conn.OpenAsync(); // Get basic skier info using (var cmd = new NpgsqlCommand("SELECT * FROM skiers WHERE id = @id", conn)) { cmd.Parameters.AddWithValue("id", skierId); using (var reader = await cmd.ExecuteReaderAsync()) { if (await reader.ReadAsync()) { info.Skier = new Skier { Id = reader.GetInt32(0), Firstname = reader.GetString(1), Lastname = reader.GetString(2), Email = reader.GetString(3), Username = reader.GetString(4), ImageUrl = reader.IsDBNull(5) ? null : reader.GetString(5) }; } } } // Get ski pass info using (var cmd = new NpgsqlCommand( "SELECT * FROM skipasses WHERE skier_id = @skierId ORDER BY validuntil DESC LIMIT 1", conn)) { cmd.Parameters.AddWithValue("skierId", skierId); using (var reader = await cmd.ExecuteReaderAsync()) { if (await reader.ReadAsync()) { info.SkiPass = new SkiPass { Id = reader.GetInt32(0), CardNumber = reader.GetInt32(1), ValidUntil = reader.GetDateTime(2), SkierId = reader.GetInt32(3) }; } } } // Get ski days count using (var cmd = new NpgsqlCommand( "SELECT COUNT(DISTINCT DATE(timestamp)) FROM skiruns WHERE skier_id = @skierId AND season_id = @seasonId", conn)) { cmd.Parameters.AddWithValue("skierId", skierId); cmd.Parameters.AddWithValue("seasonId", seasonId); info.SkiDays = Convert.ToInt32(await cmd.ExecuteScalarAsync()); } // Get countries count using (var cmd = new NpgsqlCommand( @"SELECT COUNT(DISTINCT d.country) FROM skiruns sr JOIN lifts l ON sr.lift_id = l.id JOIN resorts r ON l.resort_id = r.id JOIN destinations d ON r.destination_id = d.id WHERE sr.skier_id = @skierId", conn)) { cmd.Parameters.AddWithValue("skierId", skierId); info.CountriesVisited = Convert.ToInt32(await cmd.ExecuteScalarAsync()); } } return info; } public async Task BuySkiPassAsync(int skierId, int destinationId, DateTime validUntil) { using (var conn = new NpgsqlConnection(_connectionString)) { await conn.OpenAsync(); using (var cmd = new NpgsqlCommand( "INSERT INTO skipasses (cardnumber, validuntil, skier_id) VALUES (@cardnumber, @validuntil, @skierId)", conn)) { var rnd = new Random(); cmd.Parameters.AddWithValue("cardnumber", rnd.Next(100000, 999999)); cmd.Parameters.AddWithValue("validuntil", validUntil); cmd.Parameters.AddWithValue("skierId", skierId); await cmd.ExecuteNonQueryAsync(); } } } public async Task RegisterSkiRunAsync(int liftId, int skiPassId, int seasonId) { using (var conn = new NpgsqlConnection(_connectionString)) { await conn.OpenAsync(); // Check if pass is valid using (var cmd = new NpgsqlCommand( @"SELECT 1 FROM skipasses sp JOIN skiers s ON sp.skier_id = s.id JOIN lifts l ON l.resort_id IN ( SELECT resort_id FROM destinations WHERE id = @destinationId ) WHERE sp.id = @skiPassId AND sp.validuntil >= CURRENT_DATE AND l.id = @liftId", conn)) { cmd.Parameters.AddWithValue("skiPassId", skiPassId); cmd.Parameters.AddWithValue("liftId", liftId); var isValid = await cmd.ExecuteScalarAsync(); if (isValid == null) { return false; } } // Register the run using (var cmd = new NpgsqlCommand( "INSERT INTO skiruns (timestamp, lift_id, skipass_id, season_id) VALUES (NOW(), @liftId, @skiPassId, @seasonId)", conn)) { cmd.Parameters.AddWithValue("liftId", liftId); cmd.Parameters.AddWithValue("skiPassId", skiPassId); cmd.Parameters.AddWithValue("seasonId", seasonId); await cmd.ExecuteNonQueryAsync(); } return true; } } public async Task DeleteSkierAsync(int skierId) { using (var conn = new NpgsqlConnection(_connectionString)) { await conn.OpenAsync(); // Check if skier has runs using (var cmd = new NpgsqlCommand("SELECT COUNT(*) FROM skiruns WHERE skier_id = @skierId", conn)) { cmd.Parameters.AddWithValue("skierId", skierId); var runCount = Convert.ToInt32(await cmd.ExecuteScalarAsync()); if (runCount > 0) { return false; } } // Delete ski passes using (var cmd = new NpgsqlCommand("DELETE FROM skipasses WHERE skier_id = @skierId", conn)) { cmd.Parameters.AddWithValue("skierId", skierId); await cmd.ExecuteNonQueryAsync(); } // Delete skier using (var cmd = new NpgsqlCommand("DELETE FROM skiers WHERE id = @skierId", conn)) { cmd.Parameters.AddWithValue("skierId", skierId); await cmd.ExecuteNonQueryAsync(); } return true; } } public async Task DeleteSkierWithRunsAsync(int skierId) { using (var conn = new NpgsqlConnection(_connectionString)) { await conn.OpenAsync(); // Delete ski runs using (var cmd = new NpgsqlCommand("DELETE FROM skiruns WHERE skier_id = @skierId", conn)) { cmd.Parameters.AddWithValue("skierId", skierId); await cmd.ExecuteNonQueryAsync(); } // Delete ski passes using (var cmd = new NpgsqlCommand("DELETE FROM skipasses WHERE skier_id = @skierId", conn)) { cmd.Parameters.AddWithValue("skierId", skierId); await cmd.ExecuteNonQueryAsync(); } // Delete skier using (var cmd = new NpgsqlCommand("DELETE FROM skiers WHERE id = @skierId", conn)) { cmd.Parameters.AddWithValue("skierId", skierId); await cmd.ExecuteNonQueryAsync(); } } } public async Task SimulateSeasonRunsAsync(int seasonId, List skierIds) { using (var conn = new NpgsqlConnection(_connectionString)) { await conn.OpenAsync(); // Get all lifts var lifts = new List(); using (var cmd = new NpgsqlCommand("SELECT * FROM lifts", conn)) using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { lifts.Add(new Lift { Id = reader.GetInt32(0), Name = reader.GetString(1), LiftType = reader.GetString(2), VerticalDrop = reader.GetInt32(3), ResortId = reader.GetInt32(4) }); } } // Simulate runs for each skier foreach (var skierId in skierIds) { // Get ski pass int? skiPassId = null; using (var cmd = new NpgsqlCommand( "SELECT id FROM skipasses WHERE skier_id = @skierId AND validuntil >= CURRENT_DATE LIMIT 1", conn)) { cmd.Parameters.AddWithValue("skierId", skierId); skiPassId = (int?)await cmd.ExecuteScalarAsync(); } if (skiPassId.HasValue) { // Simulate 30-50 runs per season var rnd = new Random(); var runCount = rnd.Next(30, 51); for (int i = 0; i < runCount; i++) { var lift = lifts[rnd.Next(lifts.Count)]; using (var cmd = new NpgsqlCommand( "INSERT INTO skiruns (timestamp, lift_id, skipass_id, season_id) VALUES (NOW() - @daysAgo, @liftId, @skiPassId, @seasonId)", conn)) { cmd.Parameters.AddWithValue("daysAgo", TimeSpan.FromDays(rnd.Next(0, 90))); cmd.Parameters.AddWithValue("liftId", lift.Id); cmd.Parameters.AddWithValue("skiPassId", skiPassId.Value); cmd.Parameters.AddWithValue("seasonId", seasonId); await cmd.ExecuteNonQueryAsync(); } } } } } } public async Task SimulateDayRunsAsync(int seasonId, List skierIds) { using (var conn = new NpgsqlConnection(_connectionString)) { await conn.OpenAsync(); // Get all lifts var lifts = new List(); using (var cmd = new NpgsqlCommand("SELECT * FROM lifts", conn)) using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { lifts.Add(new Lift { Id = reader.GetInt32(0), Name = reader.GetString(1), LiftType = reader.GetString(2), VerticalDrop = reader.GetInt32(3), ResortId = reader.GetInt32(4) }); } } // Simulate runs for each skier foreach (var skierId in skierIds) { // Get ski pass int? skiPassId = null; using (var cmd = new NpgsqlCommand( "SELECT id FROM skipasses WHERE skier_id = @skierId AND validuntil >= CURRENT_DATE LIMIT 1", conn)) { cmd.Parameters.AddWithValue("skierId", skierId); skiPassId = (int?)await cmd.ExecuteScalarAsync(); } if (skiPassId.HasValue) { // Simulate 5-15 runs per day var rnd = new Random(); var runCount = rnd.Next(5, 16); for (int i = 0; i < runCount; i++) { var lift = lifts[rnd.Next(lifts.Count)]; using (var cmd = new NpgsqlCommand( "INSERT INTO skiruns (timestamp, lift_id, skipass_id, season_id) VALUES (NOW(), @liftId, @skiPassId, @seasonId)", conn)) { cmd.Parameters.AddWithValue("liftId", lift.Id); cmd.Parameters.AddWithValue("skiPassId", skiPassId.Value); cmd.Parameters.AddWithValue("seasonId", seasonId); await cmd.ExecuteNonQueryAsync(); } } } } } } } public class SkierInfo { public Skier Skier { get; set; } public SkiPass SkiPass { get; set; } public int SkiDays { get; set; } public int CountriesVisited { get; set; } } }