CREATE TABLE [DefaultKeys] (
  [KEY] CHAR NOT NULL,
  [VALUE] CHAR NOT NULL,
  CONSTRAINT [] PRIMARY KEY ([KEY]) ON CONFLICT REPLACE);


CREATE TABLE [FormDetails] (
  [DetailID] INTEGER PRIMARY KEY AUTOINCREMENT,
  [Caption] CHAR,
  [VarName] CHAR,
  [Forced] BOOLEAN,
  [TYPE] CHAR,
  [CommandOut] CHAR,
  [DEFAULT] CHAR,
  [DisplayOrder] INTEGER NOT NULL DEFAULT 999,
  [OutputOrder] INTEGER NOT NULL DEFAULT 0);


CREATE TABLE [Projects] (
  [ProjectID] INTEGER PRIMARY KEY AUTOINCREMENT,
  [Description] CHAR,
  [RecentOpen] DATETIME DEFAULT CURRENT_TIMESTAMP,
  [LastRun] DATETIME);

CREATE UNIQUE INDEX [uniqProjectNames] ON [Projects] ([Description] COLLATE NOCASE);


CREATE TABLE [ProjectDetails] (
  [ProjectID] INTEGER CONSTRAINT [fkFromProject] REFERENCES [Projects]([ProjectID]) ON DELETE CASCADE,
  [DetailID] INTEGER CONSTRAINT [fkFromDetails] REFERENCES [FormDetails]([DetailID]) ON DELETE CASCADE,
  [VALUE] CHAR,
  CONSTRAINT [] PRIMARY KEY ([ProjectID], [DetailID]) ON CONFLICT REPLACE);


CREATE TABLE [ViewedVideos] (
  [VideoID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  [ProjectID] INTEGER NOT NULL REFERENCES [Projects]([ProjectID]) ON DELETE CASCADE,
  [Filename] CHAR NOT NULL,
  [DateViewed] DATETIME,
  [Comments] TEXT);

CREATE UNIQUE INDEX [uniqDateViewed] ON [ViewedVideos] ([ProjectID], [Filename]);


CREATE VIEW [vOutputCommands] AS
SELECT [main].[Projects].[ProjectID],
  [main].[Projects].[Description],
  [main].[FormDetails].[Caption],
  [main].[FormDetails].[VarName],
  [main].[FormDetails].[TYPE],
  [main].[ProjectDetails].[VALUE],
  [main].[FormDetails].[CommandOut]
FROM [main].[FormDetails]
  INNER JOIN [main].[ProjectDetails] ON [main].[FormDetails].[DetailID] =
    [main].[ProjectDetails].[DetailID]
  INNER JOIN [main].[Projects] ON [main].[Projects].[ProjectID] =
    [main].[ProjectDetails].[ProjectID]
ORDER BY [main].[Projects].[ProjectID],
  [main].[FormDetails].[OutputOrder];


CREATE VIEW [vPathDetails] AS
SELECT [main].[Projects].[ProjectID],
  [main].[Projects].[Description],
  [main].[ProjectDetails].[VALUE]
FROM [main].[Projects]
  INNER JOIN [main].[ProjectDetails] ON [main].[Projects].[ProjectID] =
    [main].[ProjectDetails].[ProjectID]
  INNER JOIN [main].[FormDetails] ON [main].[FormDetails].[DetailID] =
    [main].[ProjectDetails].[DetailID]
WHERE [main].[FormDetails].[VarName] = 'Path'
ORDER BY Description;


CREATE VIEW [vViewedVideos] AS
SELECT [main].[Projects].[ProjectID],
  [main].[ViewedVideos].[VideoID],
  [main].[Projects].[Description] AS [Project],
  [main].[ProjectDetails].[VALUE] AS [Path],
  [main].[ViewedVideos].[Filename],
  [main].[ViewedVideos].[DateViewed]
FROM [main].[ViewedVideos]
  INNER JOIN [main].[Projects] ON [main].[Projects].[ProjectID] =
    [main].[ViewedVideos].[ProjectID]
  INNER JOIN [main].[ProjectDetails] ON [main].[Projects].[ProjectID] =
    [main].[ProjectDetails].[ProjectID]
  INNER JOIN [main].[FormDetails] ON [main].[FormDetails].[DetailID] =
    [main].[ProjectDetails].[DetailID]
WHERE [main].[FormDetails].[VarName] = 'Path'
ORDER BY
[Project],
[main].[ViewedVideos].[DateViewed] NOT NULL ASC,
[main].[ViewedVideos].[Filename];