Table Business {
  BusinessID bigint [primary key, increment]
  RegisteredName nvarchar(25)
  Industry tinyint
  TradingName nvarchar(25)
  VATNumber bigint [null]
  StreetAddress nvarchar(70) [null]
  PostalSameAsStreet bit [null]
  PostalAddress nvarchar(70) [null]
  LandLine int [null]
  Email nvarchar(40) [null]
  Website nvarchar(40) [null]
  Facebook nvarchar(50) [null]
  Twitter nvarchar(50) [null]
  TradingHours nvarchar(128) [null]
  PayGateID bigint
  PayGatePassword nvarchar(15) [null]
  Bank tinyint
  BranchNumber int
  AccountNumber bigint
  AccountType tinyint
  GPSLocation decimal(9, 6) [null]
  LocationLastUpdated datetime [null]
  AverageRating tinyint [null]

  indexes {
    (BusinessID) [pk]
    (Industry, TradingName) [unique]
    (RegisteredName) [unique]
  }
}

Table Good {
  GoodID bigint [primary key, increment]
  Barcode bigint [null]
  ManufacturerID bigint [null]
  Name nvarchar(40)
  Description nvarchar(70) [null]
  IsPrivate bit
  Image varbinary(max) [null]

  indexes {
    (GoodID) [pk]
  }
}

Table OfferType {
  OfferTypeID tinyint [primary key, increment]
  TypeName nvarchar(40)
  Description nvarchar(128) [null]

  indexes {
    (OfferTypeID) [pk]
  }
}

Table Offer {
  OfferID bigint [increment, pk]
  OfferTypeID tinyint [not null]
  CreatedByUserID bigint [not null]
  BasePrice money [null]
  OfferDtTmStart datetime
  OfferDtTmEnd datetime
    
  indexes {
    (OfferID) [pk]
  }
}

Table GatheredBasket {
  GatheredBasketID bigint [increment, not null]
  BundleID bigint [not null]
  BundleMultiplier int [not null]
  
  indexes {
    (GatheredBasketID, BundleID) [pk]
  }
}

Table EffectiveBasket {
  EffectiveBasketID bigint [increment, not null]
  GatheredBasketID bigint [not null]
  EffectiveDiscount money
  AuthorisedByUserID bigint
  
  indexes {
    (EffectiveBasketID, GatheredBasketID) [pk]
  }
}

Table AdditionalDiscount {
  GatheredBasketID bigint [pk]
  AuthorisedByUserID bigint [not null]
  Discount money [not null]
  DiscountReason text [not null]

  indexes {
    (GatheredBasketID)
  }
}

Table Purchasable {
  PurchasableID bigint
  SaleableID bigint [not null]
  OfferID bigint [not null]
  
  indexes {
    (PurchasableID, SaleableID, OfferID) [pk]
  }
}

Table PurchasableSpecial {
  PurchasableSpecialID bigint
  PurchasableID bigint [not null]
  SpecialID bigint [null]
  
  indexes {
    (PurchasableSpecialID, PurchasableID, SpecialID) [unique]
  }
}

Table Special {
  SpecialID bigint [increment, pk]
  CreatedByUserID bigint [not null]
  SpecialName varchar(20)
  SpecialDescription varchar(80)
  SpecialDtTmStart datetime
  SpecialDtTmEnd datetime
  Discount money
  IsActive bit [default: 1]

  indexes {
    (SpecialID)
  }
}

Table Bundle {
  BundleID bigint [not null]
  PurchasableSpecialID bigint [not null]
  
  indexes {
      (BundleID, PurchasableSpecialID) [unique]
  }
}

Table Catalog {
  CatalogID bigint [primary key, increment]
  BusinessID bigint [not null]
  CreatorUserEmail nvarchar(256) [not null]
  Name nvarchar(70) [not null]
  Description nvarchar(128) [null]
  LastUpdated smalldatetime
  
  indexes {
    (CatalogID) [pk]
  }
}

Table CatalogPurchasable {
  CatalogID bigint [not null]
  PurchasableSpecialID bigint [not null]
  
  indexes {
    (CatalogID, PurchasableSpecialID) [pk]
  }
}

Table Saleable {
  SaleableID bigint [pk, increment]
  ServiceID bigint [null, unique]
  ProductID bigint [null, unique]
  IsService bit
}

Table Comment {
  CommentID bigint [primary key, increment]
  UserEmail nvarchar(256)
  SaleableID bigint [not null]
  Comment nvarchar(80) [null]
  Rating tinyint [null]
  ResponseToCommentID bigint [null]
  
  indexes {
    (CommentID) [pk]
  }
}

Table Sale {
  SaleID bigint [pk, increment]
  UserID bigint
  DontPoolWithStrangers bit [not null]
  DtTmRequestedCheckout datetime
  ItemCount int
  IsAmtManual bit
  TotalAmount money
  DtTmCompleted datetime [null]
  
  indexes {
    (SaleID) [pk]
  }
}

Table Status {
  StatusID int [pk]
  StatusName varchar [not null]
  StatusDescription varchar [not null]
}

Table SaleStatus {
  SaleID bigint
  ChangedByUserID bigint [not null]
  DtTmStatusChanged timestamp [pk]
  NewStatusID tinyint
  ChangeReason varchar(255)
  
  indexes {
    (SaleID, ChangedByUserID, DtTmStatusChanged, NewStatusID) [pk]
  }
}

Table SaleBasket {
  SaleID bigint [not null]
  EffectiveBasketID bigint [not null, unique]

  indexes {
    (SaleID, EffectiveBasketID) [pk]
  }
}

Table Product {
  GoodID bigint [not null, unique]
  BusinessID bigint [not null]
  
  indexes {
    (GoodID, BusinessID) [pk]
  }
}

Table Service {
  ServiceID bigint [not null, unique]
  BusinessID bigint [not null]
  Name nvarchar(40) [not null]
  Description nvarchar(128) [null]
  Price money [not null]

  indexes {
    (ServiceID, BusinessID, Name) [pk]
  }
}

Table Payment {
  PaymentID bigint [primary key, increment]
  SaleID bigint
  SenderEmail nvarchar(256)
  CardID bigint
  Amount money
  CurrencyCode nvarchar(5)
  StatusID tinyint
  InitiationDateTime datetime
  PayGateTransactionID int [null]
  PayGateBankAuthID nvarchar(10) [null]
  SuccesfulDateTime datetime [null]
  
  indexes {
    (PaymentID) [pk]
  }
}

Table Failed {
  FailedID bigint [primary key, increment]
  PaymentID bigint
  IsRefund bit
  FailureDateTime datetime
  PayGateTransactionID int [null]
  PayGateBankAuthID nvarchar(10) [null]
  PayGateTransactionCode tinyint [null]
  PayGateResultCode int [null]
  PayGateValidationErrorCode int [null]
  MopaErrorCode int
  
  indexes {
    (FailedID) [pk]
  }
}

Table Refund {
  RefundID bigint [primary key, increment]
  PaymentID bigint
  InitiationDateTime datetime
  PayGateTransactionID int [null]
  PayGateBankAuthID nvarchar(10) [null]
  SuccesfulDateTime datetime [null]
  Reason nvarchar(50) [null]
  
  indexes {
    (RefundID) [pk]
  }
}

Table Delivery {
  DeliveryID bigint [pk, increment]
  DtTmCreated timestamp
  DtTmDriverAccepted timestamp
  DriverAcceptedLocation gps
  DtTmDriverOnScene timestamp
  DtTmClosed timestamp

  indexes {
    (DeliveryID) [pk]
  }
}

Table DeliveryDriverLeg {
  DeliveryDriverLegID bigint [primary key, increment]
  DeliveryLegID bigint [not null]
  DeliveryID bigint [not null]
  DriverID bigint [not null]

  indexes {
    (DeliveryDriverLegID, DeliveryLegID, DriverID) [pk]
  }
}

Table DeliveryLeg{
  DeliveryLegID bigint
  SaleID int [pk, increment]
  DtTmDriverOnScene timestamp
  DtTmCollected timestamp
  DtTmArrivedAtCust timestamp
  DtTmOTPPassed timestamp
  DtTmPaid timestamp
  DtTmDisputed timestamp
  DisputedReason varchar(255)
  DtTmResolved timestamp
  Resolution varchar(255)

  indexes {
    (DeliveryLegID, SaleID) [pk]
  }
}

Table Driver {
  UserID int [pk]
  LastOnline timestamp
  GPSLocation decimal(9, 6)
  LocationLastUpdated timestamp

  indexes {
    (UserID) [pk]
  }
}

// Define the references at the top level
Ref: Saleable.ServiceID > Service.ServiceID
Ref: Saleable.ProductID > Product.GoodID
Ref: DeliveryDriverLeg.DeliveryID > Delivery.DeliveryID
Ref: Driver.UserID > DeliveryDriverLeg.DriverID
Ref: DeliveryLeg.DeliveryLegID > DeliveryDriverLeg.DeliveryLegID
Ref: SaleStatus.NewStatusID > Status.StatusID
Ref: SaleStatus.SaleID > Sale.SaleID
Ref: DeliveryLeg.SaleID > Sale.SaleID
Ref: Product.GoodID > Good.GoodID
Ref: Service.BusinessID > Business.BusinessID
Ref: Offer.OfferTypeID > OfferType.OfferTypeID
Ref: GatheredBasket.BundleID > Bundle.BundleID
Ref: Bundle.PurchasableSpecialID > PurchasableSpecial.PurchasableSpecialID
Ref: AdditionalDiscount.GatheredBasketID > GatheredBasket.GatheredBasketID
Ref: EffectiveBasket.GatheredBasketID > GatheredBasket.GatheredBasketID
Ref: PurchasableSpecial.PurchasableID > Purchasable.PurchasableID
Ref: PurchasableSpecial.SpecialID > Special.SpecialID
Ref: Purchasable.SaleableID > Saleable.SaleableID
Ref: Purchasable.OfferID > Offer.OfferID
Ref: CatalogPurchasable.PurchasableSpecialID > PurchasableSpecial.PurchasableSpecialID
Ref: CatalogPurchasable.CatalogID > Catalog.CatalogID
Ref: Catalog.BusinessID > Business.BusinessID
Ref: SaleBasket.EffectiveBasketID > EffectiveBasket.EffectiveBasketID
Ref: Comment.SaleableID > Saleable.SaleableID
Ref: SaleBasket.SaleID > Sale.SaleID
Ref: Payment.SaleID > Sale.SaleID
Ref: Failed.PaymentID > Payment.PaymentID
Ref: Refund.PaymentID > Payment.PaymentID
Ref: Product.BusinessID > Business.BusinessID