DROP TABLE IF EXISTS [OrderLines];
DROP TABLE IF EXISTS [Orders];
DROP TABLE IF EXISTS [People];
DROP TABLE IF EXISTS [SpecificOrderLines];
DROP TABLE IF EXISTS [SpecificOrders];
DROP TABLE IF EXISTS [SpecificPeople];
DROP TABLE IF EXISTS [TransactionLogs];
DROP TABLE IF EXISTS [Note];

CREATE TABLE [People] (
	[Id] TEXT NOT NULL PRIMARY KEY,
	[FullName] TEXT,
	[Age] INTEGER NOT NULL,
	[Height] INTEGER NOT NULL,
	[Dob] INTEGER NULL
);

CREATE TABLE [Orders] (
	[Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	[PersonId] TEXT NOT NULL,
	[PoNumber] TEXT NOT NULL,
	[OrderStatus] INTEGER NOT NULL,
	[CreatedOn] INTEGER NOT NULL,
	[CreatedBy] TEXT NOT NULL,
	CONSTRAINT [FK_Orders_People] FOREIGN KEY ([PersonId]) REFERENCES [People]([Id])
);

CREATE TABLE [OrderLines] (
	[Id] INTEGER PRIMARY KEY NOT NULL,
	[OrderId] INT NOT NULL,
	[Qty] SMALLINT NOT NULL,
	[Status] SMALLINT NOT NULL,
	[SellPrice] NUMERIC(10, 4) NOT NULL,
	CONSTRAINT [FK_OrderLines_Orders] FOREIGN KEY ([OrderId]) REFERENCES [Orders]([Id])
);

CREATE TABLE [SpecificPeople] (
	[Id] TEXT NOT NULL PRIMARY KEY,
	[FullName] TEXT,
	[Age] INTEGER NOT NULL,
	[Height] INTEGER NOT NULL,
	[Dob] INTEGER NULL
);

CREATE TABLE [SpecificOrders] (
	[Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	[PersonId] TEXT NOT NULL,
	[PoNumber] TEXT NOT NULL,
	[OrderStatus] INTEGER NOT NULL,
	[CreatedOn] INTEGER NOT NULL,
	[CreatedBy] TEXT NOT NULL,
	CONSTRAINT [FK_SpecificOrders_SpecificPeople] FOREIGN KEY ([PersonId]) REFERENCES [SpecificPeople]([Id])
);

CREATE TABLE [SpecificOrderLines] (
	[Id] INTEGER PRIMARY KEY NOT NULL,
	[OrderId] INT NOT NULL,
	[Qty] SMALLINT NOT NULL,
	[Status] SMALLINT NOT NULL,
	[SellPrice] NUMERIC(10, 4) NOT NULL,
	CONSTRAINT [FK_SpecificOrderLines_SpecificOrders] FOREIGN KEY ([OrderId]) REFERENCES [SpecificOrders]([Id])
);

CREATE TABLE [TransactionLogs] (
	[Description] TEXT NOT NULL,
	[CreatedOn] INTEGER NOT NULL
);

CREATE TABLE [Note] (
	[Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	[Text] TEXT NOT NULL,
	[CreatedOn] INTEGER NOT NULL
);

-- Investigation Tables

DROP TABLE IF EXISTS [BugInvestigation_10R9LZYK];

CREATE TABLE [BugInvestigation_10R9LZYK] (
	[Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	[TestColumn1] BLOG(32)
);