WinExe net7.0-windows enable true enable PreserveNewest PreserveNewest { "ConnectionStrings": { "DefaultConnection": "Host=localhost;Port=5432;Database=skiresort;Username=postgres;Password=password" }, "AppSettings": { "CurrentSeasonId": 1, "DefaultLiftId": 1 } } BEGIN; CREATE TABLE IF NOT EXISTS public.destination ( id serial NOT NULL, name character varying COLLATE pg_catalog."default" NOT NULL, country character varying COLLATE pg_catalog."default", CONSTRAINT destination_pkey PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS public.lift ( id serial NOT NULL, name character varying COLLATE pg_catalog."default" NOT NULL, lift_type character varying COLLATE pg_catalog."default" NOT NULL, vertical_drop integer NOT NULL, resort_id integer NOT NULL, CONSTRAINT lift_pkey PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS public.resort ( id serial NOT NULL, name character varying COLLATE pg_catalog."default" NOT NULL, destination_id integer NOT NULL, CONSTRAINT resort_pkey PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS public.season ( id serial NOT NULL, name character varying COLLATE pg_catalog."default" NOT NULL, start_date date NOT NULL, end_date date NOT NULL, CONSTRAINT season_pkey PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS public.ski_pass ( id serial NOT NULL, card_number integer NOT NULL, valid_until date NOT NULL, skier_id integer NOT NULL, CONSTRAINT ski_pass_pkey PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS public.ski_run ( id serial NOT NULL, "timestamp" timestamp without time zone NOT NULL, -- Changed to timestamp for easier date tracking lift_id integer NOT NULL, ski_pass_id integer NOT NULL, season_id integer NOT NULL, CONSTRAINT ski_run_pkey PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS public.skier ( id serial NOT NULL, firstname character varying COLLATE pg_catalog."default" NOT NULL, lastname character varying COLLATE pg_catalog."default" NOT NULL, "email " character varying COLLATE pg_catalog."default" NOT NULL, -- Keep original name with space username character varying COLLATE pg_catalog."default" NOT NULL, image_url text COLLATE pg_catalog."default" NOT NULL, CONSTRAINT skier_pkey PRIMARY KEY (id) ); -- Add dummy data for testing if tables are empty INSERT INTO public.season (id, name, start_date, end_date) SELECT 1, '2023-2024', '2023-11-01', '2024-04-30' WHERE NOT EXISTS (SELECT 1 FROM public.season WHERE id = 1); INSERT INTO public.destination (id, name, country) SELECT 1, 'Alps', 'France' WHERE NOT EXISTS (SELECT 1 FROM public.destination WHERE id = 1); INSERT INTO public.resort (id, name, destination_id) SELECT 1, 'Chamonix', 1 WHERE NOT EXISTS (SELECT 1 FROM public.resort WHERE id = 1); INSERT INTO public.lift (id, name, lift_type, vertical_drop, resort_id) SELECT 1, 'Aiguille du Midi', 'Cable Car', 2800, 1 WHERE NOT EXISTS (SELECT 1 FROM public.lift WHERE id = 1); -- Foreign Keys (ensure they are added correctly) DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'lift_resort_id_fkey') THEN ALTER TABLE public.lift ADD CONSTRAINT lift_resort_id_fkey FOREIGN KEY (resort_id) REFERENCES public.resort (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION; END IF; END; $$; DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'resort_destination_id_fkey') THEN ALTER TABLE public.resort ADD CONSTRAINT resort_destination_id_fkey FOREIGN KEY (destination_id) REFERENCES public.destination (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION; END IF; END; $$; DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'ski_pass_skier_id_fkey') THEN ALTER TABLE public.ski_pass ADD CONSTRAINT ski_pass_skier_id_fkey FOREIGN KEY (skier_id) REFERENCES public.skier (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE; -- Cascade delete passes if skier is deleted END IF; END; $$; DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'ski_run_lift_id_fkey') THEN ALTER TABLE public.ski_run ADD CONSTRAINT ski_run_lift_id_fkey FOREIGN KEY (lift_id) REFERENCES public.lift (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION; END IF; END; $$; DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'ski_run_season_id_fkey') THEN ALTER TABLE public.ski_run ADD CONSTRAINT ski_run_season_id_fkey FOREIGN KEY (season_id) REFERENCES public.season (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION; END IF; END; $$; DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'ski_run_ski_pass_id_fkey') THEN ALTER TABLE public.ski_run ADD CONSTRAINT ski_run_ski_pass_id_fkey FOREIGN KEY (ski_pass_id) REFERENCES public.ski_pass (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE; -- Cascade delete runs if pass is deleted END IF; END; $$; END; namespace SkiResortApp.Models { public class Skier { public int Id { get; set; } public string Firstname { get; set; } = string.Empty; public string Lastname { get; set; } = string.Empty; public string Email { get; set; } = string.Empty; // Mapped to "email " column public string Username { get; set; } = string.Empty; public string ImageUrl { get; set; } = string.Empty; public override string ToString() { return $"{Firstname} {Lastname} (ID: {Id})"; } } } using System; namespace SkiResortApp.Models { public class SkiPass { public int Id { get; set; } public int CardNumber { get; set; } public DateTime ValidUntil { get; set; } public int SkierId { get; set; } } } using System; namespace SkiResortApp.Models { public class SkiRun { public int Id { get; set; } public DateTime Timestamp { get; set; } // Changed to DateTime public int LiftId { get; set; } public int SkiPassId { get; set; } public int SeasonId { get; set; } } } using System; using System.Collections.Generic; using System.IO; using System.Text.Json; using System.Threading.Tasks; using Npgsql; using SkiResortApp.Models; namespace SkiResortApp.Repositories { public class SkiRepository : IDisposable { private readonly string _connectionString; private readonly int _currentSeasonId; private readonly int _defaultLiftId; private readonly Random _random = new Random(); public SkiRepository() { try { var configJson = File.ReadAllText("appsettings.json"); using var jsonDoc = JsonDocument.Parse(configJson); _connectionString = jsonDoc.RootElement .GetProperty("ConnectionStrings") .GetProperty("DefaultConnection") .GetString() ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found."); _currentSeasonId = jsonDoc.RootElement .GetProperty("AppSettings") .GetProperty("CurrentSeasonId") .GetInt32(); _defaultLiftId = jsonDoc.RootElement .GetProperty("AppSettings") .GetProperty("DefaultLiftId") .GetInt32(); } catch (Exception ex) { // Handle exceptions during configuration reading (e.g., file not found, JSON parsing error) throw new InvalidOperationException($"Failed to initialize SkiRepository from appsettings.json: {ex.Message}", ex); } } private async Task GetOpenConnectionAsync() { var connection = new NpgsqlConnection(_connectionString); await connection.OpenAsync(); return connection; } public async Task RegisterSkierAsync(Skier skier) { await using var conn = await GetOpenConnectionAsync(); // Note the quotes around "email " because of the space in the column name var sql = @"INSERT INTO public.skier(firstname, lastname, ""email "", username, image_url) VALUES (@fn, @ln, @em, @un, @img) RETURNING id;"; await using var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.AddWithValue("fn", skier.Firstname); cmd.Parameters.AddWithValue("ln", skier.Lastname); cmd.Parameters.AddWithValue("em", skier.Email); cmd.Parameters.AddWithValue("un", skier.Username); cmd.Parameters.AddWithValue("img", skier.ImageUrl); var skierId = (int?)await cmd.ExecuteScalarAsync(); return skierId ?? throw new InvalidOperationException("Failed to retrieve ID after inserting skier."); } public async Task> SearchSkiersAsync(string? firstname, string? lastname) { var skiers = new List(); await using var conn = await GetOpenConnectionAsync(); var sqlParts = new List(); var parameters = new List(); if (!string.IsNullOrWhiteSpace(firstname)) { sqlParts.Add("firstname ILIKE @fn"); parameters.Add(new NpgsqlParameter("fn", $"%{firstname}%")); } if (!string.IsNullOrWhiteSpace(lastname)) { sqlParts.Add("lastname ILIKE @ln"); parameters.Add(new NpgsqlParameter("ln", $"%{lastname}%")); } if (sqlParts.Count == 0) // If no criteria, return empty list or maybe all? Let's return empty. { return skiers; } var whereClause = string.Join(" AND ", sqlParts); // Note the quotes around "email " var sql = $"SELECT id, firstname, lastname, \"email \", username, image_url FROM public.skier WHERE {whereClause} ORDER BY lastname, firstname;"; await using var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.AddRange(parameters.ToArray()); await using var reader = await cmd.ExecuteReaderAsync(); while (await reader.ReadAsync()) { skiers.Add(new Skier { Id = reader.GetInt32(reader.GetOrdinal("id")), Firstname = reader.GetString(reader.GetOrdinal("firstname")), Lastname = reader.GetString(reader.GetOrdinal("lastname")), Email = reader.GetString(reader.GetOrdinal("email ")), // Use ordinal for the column with space Username = reader.GetString(reader.GetOrdinal("username")), ImageUrl = reader.GetString(reader.GetOrdinal("image_url")) }); } return skiers; } public async Task GetLatestSkiPassAsync(int skierId) { await using var conn = await GetOpenConnectionAsync(); var sql = @"SELECT id, card_number, valid_until, skier_id FROM public.ski_pass WHERE skier_id = @sid ORDER BY valid_until DESC LIMIT 1;"; await using var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.AddWithValue("sid", skierId); await using var reader = await cmd.ExecuteReaderAsync(); if (await reader.ReadAsync()) { return new SkiPass { Id = reader.GetInt32(0), CardNumber = reader.GetInt32(1), ValidUntil = reader.GetDateTime(2), SkierId = reader.GetInt32(3) }; } return null; } public async Task GetSkiDaysThisSeasonAsync(int skierId) { await using var conn = await GetOpenConnectionAsync(); // Counts distinct days within the current season var sql = @"SELECT COUNT(DISTINCT DATE(sr.timestamp)) FROM public.ski_run sr JOIN public.ski_pass sp ON sr.ski_pass_id = sp.id WHERE sp.skier_id = @sid AND sr.season_id = @seasonId;"; await using var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.AddWithValue("sid", skierId); cmd.Parameters.AddWithValue("seasonId", _currentSeasonId); var result = await cmd.ExecuteScalarAsync(); return Convert.ToInt32(result ?? 0); } public async Task GetCountriesVisitedAsync(int skierId) { await using var conn = await GetOpenConnectionAsync(); var sql = @"SELECT COUNT(DISTINCT d.country) FROM public.ski_run sr JOIN public.ski_pass sp ON sr.ski_pass_id = sp.id JOIN public.lift l ON sr.lift_id = l.id JOIN public.resort r ON l.resort_id = r.id JOIN public.destination d ON r.destination_id = d.id WHERE sp.skier_id = @sid AND d.country IS NOT NULL;"; // Ensure country is not null await using var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.AddWithValue("sid", skierId); var result = await cmd.ExecuteScalarAsync(); return Convert.ToInt32(result ?? 0); } public async Task BuySkiPassAsync(int skierId, DateTime validUntil) { await using var conn = await GetOpenConnectionAsync(); var cardNumber = _random.Next(100000, 999999); // Generate random card number var sql = @"INSERT INTO public.ski_pass(card_number, valid_until, skier_id) VALUES (@cn, @vu, @sid);"; await using var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.AddWithValue("cn", cardNumber); cmd.Parameters.AddWithValue("vu", validUntil.Date); // Store only the date part cmd.Parameters.AddWithValue("sid", skierId); await cmd.ExecuteNonQueryAsync(); } public async Task<(bool Success, string Message)> RegisterSkiRunAsync(int skierId, int liftId) { var skiPass = await GetLatestSkiPassAsync(skierId); if (skiPass == null) { return (false, "Skier does not have a ski pass."); } if (skiPass.ValidUntil.Date < DateTime.Now.Date) { return (false, $"Ski pass expired on {skiPass.ValidUntil.ToShortDateString()}."); } // Optional: Check if lift belongs to a valid resort/destination if needed // For now, we assume the liftId is valid for the context await using var conn = await GetOpenConnectionAsync(); var sql = @"INSERT INTO public.ski_run(""timestamp"", lift_id, ski_pass_id, season_id) VALUES (@ts, @lid, @spid, @sid);"; await using var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.AddWithValue("ts", DateTime.Now); // Use current timestamp cmd.Parameters.AddWithValue("lid", liftId); cmd.Parameters.AddWithValue("spid", skiPass.Id); cmd.Parameters.AddWithValue("sid", _currentSeasonId); await cmd.ExecuteNonQueryAsync(); return (true, "Ski run registered successfully."); } public async Task GetSkiRunCountAsync(int skierId) { await using var conn = await GetOpenConnectionAsync(); var sql = @"SELECT COUNT(sr.id) FROM public.ski_run sr JOIN public.ski_pass sp ON sr.ski_pass_id = sp.id WHERE sp.skier_id = @sid;"; await using var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.AddWithValue("sid", skierId); var result = await cmd.ExecuteScalarAsync(); return Convert.ToInt32(result ?? 0); } public async Task DeleteSkiRunsForSkierAsync(int skierId) { await using var conn = await GetOpenConnectionAsync(); // Deleting runs based on skier_id through the ski_pass table var sql = @"DELETE FROM public.ski_run WHERE ski_pass_id IN (SELECT id FROM public.ski_pass WHERE skier_id = @sid);"; await using var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.AddWithValue("sid", skierId); await cmd.ExecuteNonQueryAsync(); } public async Task DeleteSkierAndPassesAsync(int skierId) { // Assumes runs are already deleted or cascade delete is set up correctly // for ski_pass -> ski_run and skier -> ski_pass await using var conn = await GetOpenConnectionAsync(); // Delete associated ski passes first (if cascade delete isn't reliable or not set) var deletePassSql = "DELETE FROM public.ski_pass WHERE skier_id = @sid;"; await using (var passCmd = new NpgsqlCommand(deletePassSql, conn)) { passCmd.Parameters.AddWithValue("sid", skierId); await passCmd.ExecuteNonQueryAsync(); } // Then delete the skier var deleteSkierSql = "DELETE FROM public.skier WHERE id = @sid;"; await using (var skierCmd = new NpgsqlCommand(deleteSkierSql, conn)) { skierCmd.Parameters.AddWithValue("sid", skierId); await skierCmd.ExecuteNonQueryAsync(); } } public async Task SimulateRunsAsync(List skierIds, int minRuns, int maxRuns) { if (!skierIds.Any()) return; var tasks = new List(); foreach (var skierId in skierIds) { tasks.Add(Task.Run(async () => // Run simulation for each skier concurrently { int runs = _random.Next(minRuns, maxRuns + 1); for (int i = 0; i < runs; i++) { // Add a small random delay to simulate time passing await Task.Delay(_random.Next(10, 100)); await RegisterSkiRunAsync(skierId, _defaultLiftId); } })); } await Task.WhenAll(tasks); } public async Task SimulateSeasonRunsAsync(List skierIds) { // Simulate a larger number of runs for a season await SimulateRunsAsync(skierIds, 15, 50); } public async Task SimulateDayRunsAsync(List skierIds) { // Simulate a smaller number of runs for a day await SimulateRunsAsync(skierIds, 2, 12); } public void Dispose() { // NpgsqlConnection is disposed via 'await using', nothing else needed here for now. GC.SuppressFinalize(this); } } }