-- Create the base table
CREATE TABLE Inventory (
    SKU VARCHAR(50) PRIMARY KEY,
    Quantity INT,
    Barcode VARCHAR(50),
    Name VARCHAR(100),
    Description VARCHAR(255),
    InventoryLocation VARCHAR(100),
    Weight DECIMAL(10, 2),
    Length DECIMAL(10, 2),
    Height DECIMAL(10, 2),
    Width DECIMAL(10, 2),
    Volume DECIMAL(10, 2),
    Price DECIMAL(10, 2),
    Note VARCHAR(255),
    IsInStock BIT
);

CREATE TABLE NonInventory (
    SKU VARCHAR(50) PRIMARY KEY,
    Quantity INT,
    Barcode VARCHAR(50),
    Name VARCHAR(100),
    Description VARCHAR(255),
    InventoryLocation VARCHAR(100),
    Weight DECIMAL(10, 2),
    Length DECIMAL(10, 2),
    Height DECIMAL(10, 2),
    Width DECIMAL(10, 2),
    Volume DECIMAL(10, 2),
    Price DECIMAL(10, 2),
    Note VARCHAR(255),
    IsInStock BIT
);

CREATE TABLE Other (
    SKU VARCHAR(50) PRIMARY KEY,
    Quantity INT,
    Barcode VARCHAR(50),
    Name VARCHAR(100),
    Description VARCHAR(255),
    InventoryLocation VARCHAR(100),
    Weight DECIMAL(10, 2),
    Length DECIMAL(10, 2),
    Height DECIMAL(10, 2),
    Width DECIMAL(10, 2),
    Volume DECIMAL(10, 2),
    Price DECIMAL(10, 2),
    Note VARCHAR(255),
    IsInStock BIT
);
-- Create the Items table using inheritance
CREATE TABLE Items (
    Inventory_SKU VARCHAR(50),
    NonInventory_SKU VARCHAR(50),
    Other_SKU VARCHAR(50),
    FOREIGN KEY (Inventory_SKU) REFERENCES Inventory(SKU),
    FOREIGN KEY (NonInventory_SKU) REFERENCES NonInventory(SKU),
    FOREIGN KEY (Other_SKU) REFERENCES Other(SKU)
);
CREATE TABLE Client (
    AccountNumber INT PRIMARY KEY,
    ClientEmail VARCHAR(100),
    RecoveryEmail VARCHAR(100),
    FirstName VARCHAR(50),
    MiddleName VARCHAR(50),
    LastName VARCHAR(50),
    CurrentAddress VARCHAR(255),
    BillingInfo VARCHAR(255)
);
CREATE TABLE Company (
    CompanyNumber INT PRIMARY KEY,
    CompID INT,
    CompanyName VARCHAR(100),
    BillingContact VARCHAR(100),
    ShippingContact VARCHAR(100),
    AccountExecutiveContact VARCHAR(100),
    StreetAddress VARCHAR(255),
    ExtraAddress VARCHAR(255),
    City VARCHAR(50),
    Country VARCHAR(50),
    State VARCHAR(50),
    Zip VARCHAR(20)
);
CREATE TABLE Ticket (
    TicketNumber INT PRIMARY KEY,
    ReportType VARCHAR(50),
    ClientName VARCHAR(100),
    ClientEmail VARCHAR(100),
    RepresentativeName VARCHAR(100),
    SupportEmail VARCHAR(100),
    ReportDate DATE,
    IsClosed BIT,
    Description VARCHAR(255)
);
CREATE TABLE CustomerInfo (
    AccountNumber INT PRIMARY KEY,
    FirstName VARCHAR(50),
    MiddleName VARCHAR(50),
    LastName VARCHAR(50),
    Zip VARCHAR(20),
    City VARCHAR(50),
    State VARCHAR(50),
    Country VARCHAR(50),
    HomePhone VARCHAR(20),
    WorkPhone VARCHAR(20),
    CellPhone VARCHAR(20),
    Email VARCHAR(100)
);
CREATE TABLE BillingInfo (
    AccountNumber INT PRIMARY KEY,
    IsSame BIT,
    FirstName VARCHAR(50),
    MiddleName VARCHAR(50),
    LastName VARCHAR(50),
    Zip VARCHAR(20),
    City VARCHAR(50),
    State VARCHAR(50),
    Country VARCHAR(50),
    HomePhone VARCHAR(20),
    WorkPhone VARCHAR(20),
    CellPhone VARCHAR(20),
    Email VARCHAR(100)
);
CREATE TABLE ReceiptOrder (
    OrderNumber INT PRIMARY KEY,
    ReceiptStatus VARCHAR(50),
    Warehouse VARCHAR(100),
    Client VARCHAR(100),
    ReceiptDate DATE
);
CREATE TABLE ReceiptDetails (
    OrderNumber INT PRIMARY KEY,
    ReceiptStatus VARCHAR(50),
    Warehouse VARCHAR(100),
    Client VARCHAR(100),
    ReceiptDate DATE,
    ReceivingDate DATE,
    ItemsDescription VARCHAR(255),
    DeliveryPhoto IMAGE, -- Note: IMAGE data type is used for images in SQL Server
    Notes VARCHAR(255),
    ReceiptTicket VARCHAR(50) -- Assuming the ticket number is a string
);
CREATE TABLE RouteSchedules (
    RouteName VARCHAR(100),
    RouteDate DATE,
    Driver VARCHAR(100),
    IsDelivered BIT,
    IsRouted BIT,
    Notes VARCHAR(255),
    IsClosed BIT,
    IsPaid BIT,
    CheckNumber VARCHAR(50),
    IsLocked BIT
);
CREATE TABLE RouteScheduleDetails (
    RouteName VARCHAR(100), -- Inherits from RouteSchedules
    RouteDate DATE, -- Inherits from RouteSchedules
    Driver VARCHAR(100), -- Inherits from RouteSchedules
    IsDelivered BIT, -- Inherits from RouteSchedules
    IsRouted BIT, -- Inherits from RouteSchedules
    Notes VARCHAR(255), -- Inherits from RouteSchedules
    IsClosed BIT, -- Inherits from RouteSchedules
    IsPaid BIT, -- Inherits from RouteSchedules
    CheckNumber VARCHAR(50), -- Inherits from RouteSchedules
    IsLocked BIT, -- Inherits from RouteSchedules
    CompID INT, -- Inherited from Company table
    RouteStops INT,
    Type VARCHAR(50),
    Consignee VARCHAR(100),
    Address VARCHAR(255),
    City VARCHAR(50),
    State VARCHAR(50),
    Zip VARCHAR(20),
    TotalPrice DECIMAL(10, 2),
    CF VARCHAR(50),
    ScheduledDate DATE,
    StartTime TIME,
    EndTime TIME,
    IsConfirmed BIT,
    IsCompleted BIT,
    IsEmailed BIT
);
CREATE TABLE Quotes (
    QuoteNumber INT PRIMARY KEY,
    QuoteReference VARCHAR(50),
    QuoteDate DATE,
    CompID INT, -- Inherited from Company table
    Products VARCHAR(MAX),
    Subtotal DECIMAL(10, 2),
    Discount DECIMAL(10, 2),
    ShippingCost DECIMAL(10, 2),
    OtherFees DECIMAL(10, 2),
    TotalPrice DECIMAL(10, 2),
    Notes VARCHAR(MAX)
);
CREATE TABLE QuoteDetails (
    QuoteNumber INT PRIMARY KEY, -- Inherits from Quotes
    QuoteReference VARCHAR(50), -- Inherits from Quotes
    QuoteDate DATE, -- Inherits from Quotes
    CompID INT, -- Inherits from Quotes
    Products VARCHAR(MAX), -- Inherits from Quotes
    Subtotal DECIMAL(10, 2), -- Inherits from Quotes
    Discount DECIMAL(10, 2), -- Inherits from Quotes
    ShippingCost DECIMAL(10, 2), -- Inherits from Quotes
    OtherFees DECIMAL(10, 2), -- Inherits from Quotes
    TotalPrice DECIMAL(10, 2), -- Inherits from Quotes
    Notes VARCHAR(MAX), -- Inherits from Quotes
    AccountNumber INT, -- Inherited from CustomerInfo table
    FirstName VARCHAR(50),
    MiddleName VARCHAR(50),
    LastName VARCHAR(50),
    Zip VARCHAR(20),
    City VARCHAR(50),
    State VARCHAR(50),
    Country VARCHAR(50),
    HomePhone VARCHAR(20),
    WorkPhone VARCHAR(20),
    CellPhone VARCHAR(20),
    Email VARCHAR(100),
    IsSame BIT, -- Inherited from BillingInfo table
    BillingFirstName VARCHAR(50),
    BillingMiddleName VARCHAR(50),
    BillingLastName VARCHAR(50),
    BillingZip VARCHAR(20),
    BillingCity VARCHAR(50),
    BillingState VARCHAR(50),
    BillingCountry VARCHAR(50),
    BillingHomePhone VARCHAR(20),
    BillingWorkPhone VARCHAR(20),
    BillingCellPhone VARCHAR(20),
    BillingEmail VARCHAR(100),
    QuoteItems VARCHAR(MAX), -- Inherited from QuoteItems table
    TotalItemValue DECIMAL(10, 2),
    CalcMethod VARCHAR(50),
    InventoryCount INT,
    TotalCF VARCHAR(50)
);
CREATE TABLE Invoice (
    InvoiceNumber INT PRIMARY KEY,
    InvoiceReference VARCHAR(50),
    InvoiceType VARCHAR(50),
    InvoiceDate DATE,
    DueDate DATE,
    CompID INT, -- Inherited from Company table
    TotalPrice DECIMAL(10, 2),
    IsPaid BIT,
    RouteName VARCHAR(100),
    RouteDate DATE,
    IsDelivered BIT
);
CREATE TABLE InvoiceDetails (
    InvoiceNumber INT PRIMARY KEY, -- Inherits from Invoice
    InvoiceReference VARCHAR(50), -- Inherits from Invoice
    InvoiceType VARCHAR(50), -- Inherits from Invoice
    InvoiceDate DATE, -- Inherits from Invoice
    DueDate DATE, -- Inherits from Invoice
    CompID INT, -- Inherits from Invoice
    TotalPrice DECIMAL(10, 2), -- Inherits from Invoice
    IsPaid BIT, -- Inherits from Invoice
    RouteName VARCHAR(100), -- Inherits from Invoice
    RouteDate DATE, -- Inherits from Invoice
    IsDelivered BIT, -- Inherits from Invoice
    Client VARCHAR(100),
    Customer VARCHAR(100),
    PickUpDate DATE,
    OrderStatus VARCHAR(50),
    CalcMethod VARCHAR(50),
    Warehouse VARCHAR(100),
    Site VARCHAR(100),
    Service VARCHAR(100),
    CustomerAddress VARCHAR(255),
    CustomerCountry VARCHAR(50),
    CustomerState VARCHAR(50),
    CustomerCity VARCHAR(50),
    CustomerZip VARCHAR(20),
    CustomerZone VARCHAR(50),
    CustomerPhone VARCHAR(20),
    CustomerSecondPhone VARCHAR(20),
    BillingInfo VARCHAR(MAX), -- Inherited from BillingInfo table
    InvoiceItems VARCHAR(MAX), -- Inherited from Items table
    InvoiceNotes VARCHAR(MAX),
    Updates VARCHAR(MAX)
);
CREATE TABLE Notes (
    InvoiceMessage VARCHAR(MAX),
    DriverNote VARCHAR(MAX),
    ScheduleDeliverNote VARCHAR(MAX),
    ManifestNote VARCHAR(MAX),
    ZipCode VARCHAR(20),
    State VARCHAR(50),
    Country VARCHAR(50)
);
