Index: .idea/sqldialects.xml =================================================================== --- .idea/sqldialects.xml (revision d1a8cb0de012b850acb4a2e12229b3ddca3a9a91) +++ .idea/sqldialects.xml (revision d715225165f5c02dac7aa685b6df02652bc35e06) @@ -2,4 +2,5 @@ + Index: music/admin.py =================================================================== --- music/admin.py (revision d1a8cb0de012b850acb4a2e12229b3ddca3a9a91) +++ music/admin.py (revision d715225165f5c02dac7aa685b6df02652bc35e06) @@ -16,2 +16,3 @@ admin.site.register(PlaylistTrack, PlaylistTrackAdmin) admin.site.register(Track) +admin.site.register(DeletedCustomerLog) Index: music/migrations/0004_deletedcustomerlog_delete_tracks_count_per_genre_and_more.py =================================================================== --- music/migrations/0004_deletedcustomerlog_delete_tracks_count_per_genre_and_more.py (revision d715225165f5c02dac7aa685b6df02652bc35e06) +++ music/migrations/0004_deletedcustomerlog_delete_tracks_count_per_genre_and_more.py (revision d715225165f5c02dac7aa685b6df02652bc35e06) @@ -0,0 +1,39 @@ +# Generated by Django 5.1.3 on 2025-05-07 07:43 + +from django.db import migrations, models + + +class Migration(migrations.Migration): + + dependencies = [ + ("music", "0003_tracks_count_per_genre"), + ] + + operations = [ + migrations.CreateModel( + name="DeletedCustomerLog", + fields=[ + ("log_id", models.AutoField(primary_key=True, serialize=False)), + ("first_name", models.CharField(max_length=40)), + ("last_name", models.CharField(max_length=20)), + ("deleted_at", models.DateTimeField()), + ("total_spent", models.DecimalField(decimal_places=2, max_digits=10)), + ("invoice_count", models.IntegerField()), + ], + options={ + "db_table": "deleted_customer_log", + "managed": False, + }, + ), + migrations.DeleteModel( + name="tracks_count_per_genre", + ), + migrations.AlterModelOptions( + name="invoice", + options={"managed": False}, + ), + migrations.AlterModelOptions( + name="invoiceline", + options={"managed": False}, + ), + ] Index: music/migrations/0005_alter_deletedcustomerlog_options_and_more.py =================================================================== --- music/migrations/0005_alter_deletedcustomerlog_options_and_more.py (revision d715225165f5c02dac7aa685b6df02652bc35e06) +++ music/migrations/0005_alter_deletedcustomerlog_options_and_more.py (revision d715225165f5c02dac7aa685b6df02652bc35e06) @@ -0,0 +1,29 @@ +# Generated by Django 5.1.3 on 2025-05-07 09:03 + +from django.db import migrations + + +class Migration(migrations.Migration): + + dependencies = [ + ("music", "0004_deletedcustomerlog_delete_tracks_count_per_genre_and_more"), + ] + + operations = [ + migrations.AlterModelOptions( + name="deletedcustomerlog", + options={}, + ), + migrations.AlterModelOptions( + name="invoice", + options={}, + ), + migrations.AlterModelOptions( + name="invoiceline", + options={}, + ), + migrations.AlterModelOptions( + name="playlisttrack", + options={}, + ), + ] Index: music/models.py =================================================================== --- music/models.py (revision d1a8cb0de012b850acb4a2e12229b3ddca3a9a91) +++ music/models.py (revision d715225165f5c02dac7aa685b6df02652bc35e06) @@ -113,5 +113,5 @@ class Invoice(models.Model): invoice_id = models.AutoField(primary_key=True) - customer = models.ForeignKey(Customer, on_delete=models.CASCADE, db_column='customer_id') + customer = models.ForeignKey(Customer, on_delete=models.SET_NULL, db_column='customer_id', null=True) invoice_date = models.DateTimeField() billing_address = models.CharField(max_length=70, blank=True, null=True) @@ -158,5 +158,4 @@ class Meta: db_table = 'playlist_track' - managed = False unique_together = (('playlist', 'track'),) @@ -170,9 +169,9 @@ last_name = models.CharField(max_length=20) deleted_at = models.DateTimeField() - + total_spent = models.DecimalField(max_digits=10, decimal_places=2) + invoice_count = models.IntegerField() class Meta: db_table = 'deleted_customer_log' - managed = False def __str__(self): - return f"{self.first_name} {self.last_name} {self.deleted_at}" + return f"{self.first_name} {self.last_name} - Deleted on {self.deleted_at} - Total Spent: ${self.total_spent} from {self.invoice_count} invoices" Index: music/triggers/customer_deletion.sql =================================================================== --- music/triggers/customer_deletion.sql (revision d1a8cb0de012b850acb4a2e12229b3ddca3a9a91) +++ music/triggers/customer_deletion.sql (revision d715225165f5c02dac7aa685b6df02652bc35e06) @@ -1,13 +1,31 @@ +DROP TRIGGER IF EXISTS trg_customer_deletion ON customer; +DROP FUNCTION IF EXISTS customer_deletion(); + CREATE OR REPLACE FUNCTION customer_deletion() RETURNS TRIGGER AS $$ +DECLARE + total_spent NUMERIC(10, 2); + invoice_count INTEGER; BEGIN - INSERT INTO deleted_customer_log (first_name, last_name, deleted_at) - VALUES (OLD.first_name, OLD.last_name, NOW()); + SELECT COALESCE(SUM(total), 0), COUNT(*) + INTO total_spent, invoice_count + FROM invoice + WHERE customer_id = OLD.customer_id; + + INSERT INTO deleted_customer_log ( + first_name, last_name, deleted_at, total_spent, invoice_count + ) + VALUES ( + OLD.first_name, OLD.last_name, NOW(), total_spent, invoice_count + ); + RETURN OLD; END; $$ LANGUAGE plpgsql; +DROP TRIGGER IF EXISTS trg_customer_deletion ON customer; + CREATE TRIGGER trg_customer_deletion -AFTER DELETE ON customer +BEFORE DELETE ON customer FOR EACH ROW EXECUTE FUNCTION customer_deletion(); Index: templates/sidebar.html =================================================================== --- templates/sidebar.html (revision d1a8cb0de012b850acb4a2e12229b3ddca3a9a91) +++ templates/sidebar.html (revision d715225165f5c02dac7aa685b6df02652bc35e06) @@ -1,7 +1,9 @@ -
+

Menu