// Base payment — common properties
public abstract class Payment
{
    public int      Id          { get; set; }
    public decimal  Amount      { get; set; }
    public DateTime ProcessedAt { get; set; }
    public string   Status      { get; set; } = "";
}

public class CardPayment : Payment
{
    public string Last4Digits  { get; set; } = "";
    public string CardNetwork  { get; set; } = "";   // Visa, Mastercard
}

public class BankTransferPayment : Payment
{
    public string AccountNumber { get; set; } = "";
    public string RoutingNumber { get; set; } = "";
}

public class CryptoPayment : Payment
{
    public string WalletAddress { get; set; } = "";
    public string Currency      { get; set; } = "";   // BTC, ETH
}

// DbContext — TPH is the default, no extra configuration needed
public class AppDbContext(DbContextOptions opts) : DbContext(opts)
{
    public DbSet<Payment> Payments => Set<Payment>();

    protected override void OnModelCreating(ModelBuilder model)
    {
        model.Entity<Payment>().HasDiscriminator<string>("PaymentType")
            .HasValue<CardPayment>("Card")
            .HasValue<BankTransferPayment>("BankTransfer")
            .HasValue<CryptoPayment>("Crypto");

        // Subtype-specific columns must be nullable (other subtypes leave them NULL)
        model.Entity<CardPayment>().Property(p => p.Last4Digits).IsRequired(false);
        model.Entity<BankTransferPayment>().Property(p => p.AccountNumber).IsRequired(false);
        model.Entity<CryptoPayment>().Property(p => p.WalletAddress).IsRequired(false);
    }
}

-- Generated schema: one table, all columns nullable except base columns
CREATE TABLE Payments (
    Id              INT NOT NULL PRIMARY KEY,
    Amount          DECIMAL NOT NULL,
    ProcessedAt     DATETIME NOT NULL,
    Status          VARCHAR(50) NOT NULL,
    PaymentType     VARCHAR(50) NOT NULL,   -- discriminator
    Last4Digits     VARCHAR(4),             -- CardPayment only
    CardNetwork     VARCHAR(20),            -- CardPayment only
    AccountNumber   VARCHAR(50),            -- BankTransferPayment only
    RoutingNumber   VARCHAR(20),            -- BankTransferPayment only
    WalletAddress   VARCHAR(100),           -- CryptoPayment only
    Currency        VARCHAR(10)             -- CryptoPayment only
);

// Query — EF Core adds WHERE PaymentType='Card' automatically
var cards = await context.Payments.OfType<CardPayment>().ToListAsync();

// Generated SQL:
// SELECT * FROM Payments WHERE PaymentType = 'Card'

TPH Pros:
  - Single table — fast, no JOINs
  - Simple migrations (one table to manage)
  - Efficient polymorphic queries (OfType adds one WHERE clause)

TPH Cons:
  - Subtype columns are nullable — cannot enforce NOT NULL at DB level
  - Table becomes wide as hierarchy grows (many NULL columns per row)
  - Hard to add DB-level constraints per subtype

Best for: small to medium hierarchies (3-5 subtypes) where query speed matters

protected override void OnModelCreating(ModelBuilder model)
{
    model.Entity<Payment>().ToTable("Payments");
    model.Entity<CardPayment>().ToTable("CardPayments");
    model.Entity<BankTransferPayment>().ToTable("BankTransferPayments");
    model.Entity<CryptoPayment>().ToTable("CryptoPayments");
}

-- Generated schema: shared base table + subtype tables
CREATE TABLE Payments (
    Id          INT NOT NULL PRIMARY KEY,
    Amount      DECIMAL NOT NULL,
    ProcessedAt DATETIME NOT NULL,
    Status      VARCHAR(50) NOT NULL
);

CREATE TABLE CardPayments (
    Id          INT NOT NULL PRIMARY KEY REFERENCES Payments(Id),
    Last4Digits VARCHAR(4) NOT NULL,      -- NOT NULL here is valid
    CardNetwork VARCHAR(20) NOT NULL
);

CREATE TABLE BankTransferPayments (
    Id            INT NOT NULL PRIMARY KEY REFERENCES Payments(Id),
    AccountNumber VARCHAR(50) NOT NULL,
    RoutingNumber VARCHAR(20) NOT NULL
);

// Polymorphic query — EF Core generates a LEFT JOIN for each subtype
var payments = await context.Payments.ToListAsync();

// Generated SQL:
// SELECT p.*, cp.Last4Digits, btp.AccountNumber, crp.WalletAddress
// FROM Payments p
// LEFT JOIN CardPayments cp ON p.Id = cp.Id
// LEFT JOIN BankTransferPayments btp ON p.Id = btp.Id
// LEFT JOIN CryptoPayments crp ON p.Id = crp.Id
// → expensive with many subtypes

TPT Pros:
  - Subtype columns can be NOT NULL (better integrity)
  - Normalised — no wasted NULL columns
  - Each table is clean and focused

TPT Cons:
  - Polymorphic queries require JOINs across all tables — slow with many subtypes
  - Insert/update touches multiple tables — more round trips
  - Complex migrations for hierarchy changes

Best for: small hierarchies where DB normalisation and constraints matter more than query speed

protected override void OnModelCreating(ModelBuilder model)
{
    model.Entity<Payment>().UseTpcMappingStrategy();
    // Each subtype table contains all columns (base + subtype)
    model.Entity<CardPayment>().ToTable("CardPayments");
    model.Entity<BankTransferPayment>().ToTable("BankTransferPayments");
    model.Entity<CryptoPayment>().ToTable("CryptoPayments");

    // TPC requires a sequence for IDs (not IDENTITY per table)
    model.Entity<Payment>().Property(p => p.Id).UseHiLo("payment_sequence");
}

-- Each table is fully self-contained
CREATE TABLE CardPayments (
    Id          INT NOT NULL PRIMARY KEY,
    Amount      DECIMAL NOT NULL,         -- base columns duplicated
    ProcessedAt DATETIME NOT NULL,
    Status      VARCHAR(50) NOT NULL,
    Last4Digits VARCHAR(4) NOT NULL,      -- subtype columns
    CardNetwork VARCHAR(20) NOT NULL
);

CREATE TABLE BankTransferPayments (
    Id            INT NOT NULL PRIMARY KEY,
    Amount        DECIMAL NOT NULL,
    ProcessedAt   DATETIME NOT NULL,
    Status        VARCHAR(50) NOT NULL,
    AccountNumber VARCHAR(50) NOT NULL,
    RoutingNumber VARCHAR(20) NOT NULL
);

// Query specific type — single table, no JOIN, fast
var cards = await context.Set<CardPayment>().ToListAsync();

// Polymorphic query — EF Core generates UNION ALL
var all = await context.Payments.ToListAsync();

// Generated SQL:
// SELECT Id, Amount, ..., Last4Digits, NULL AS AccountNumber, ... FROM CardPayments
// UNION ALL
// SELECT Id, Amount, ..., NULL AS Last4Digits, AccountNumber, ... FROM BankTransferPayments
// UNION ALL
// SELECT Id, Amount, ..., NULL AS Last4Digits, NULL AS AccountNumber, ... FROM CryptoPayments

TPC Pros:
  - Concrete type queries are fastest — single table, no JOINs, no NULLs
  - All columns can be NOT NULL
  - Tables are clean — no NULL pollution from siblings

TPC Cons:
  - Polymorphic queries use UNION ALL — poor performance with many rows
  - ID generation is tricky — cannot use IDENTITY (values overlap across tables); use Hi-Lo or UUID
  - Schema changes to base class require altering all tables

Best for: hierarchies where you query subtypes individually (rarely polymorphic),
          or where each subtype has very different query patterns

                   TPH              TPT              TPC
Tables             1                1 + N            N
Polymorphic query  Fast (1 WHERE)   Slow (N JOINs)   Medium (UNION ALL)
Subtype query      Fast             Fast             Fastest
NULL columns       Many             None             None
DB constraints     Weak (nullable)  Strong           Strong
Migration ease     Easy             Medium           Medium
ID generation      IDENTITY         IDENTITY         Sequence / UUID
Best for           Default choice   Normalised data  Isolated subtypes

// Owned entity — part of the owner, not a separate entity in the hierarchy
public class Order
{
    public int     Id      { get; set; }
    public Address Address { get; set; } = null!;   // owned
}

[Owned]
public class Address
{
    public string Street  { get; set; } = "";
    public string City    { get; set; } = "";
    public string Country { get; set; } = "";
}

// EF Core maps Address columns INTO the Orders table
// No separate Addresses table, no FK, no ID
// SELECT Orders.Id, Orders.Address_Street, Orders.Address_City FROM Orders