/* Begin Application Settings */
CREATE TABLE IF NOT EXISTS ApplicationSetting
(
  ApplicationSettingId INTEGER CONSTRAINT PK_ApplicationSetting PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
  Name TEXT NOT NULL,
  Value TEXT NOT NULL,
  CreatedId TEXT NOT NULL,
  CreatedDate TEXT NOT NULL DEFAULT (datetime('now')),
  LastModifiedId TEXT NOT NULL,
  LastModifiedDate TEXT NOT NULL DEFAULT (datetime('now')) 
);

  CREATE UNIQUE INDEX IF NOT EXISTS IX_ApplicationSetting ON ApplicationSetting (Name);
/* End Application Settings */

/* Begin Profile */
CREATE TABLE IF NOT EXISTS Profile
(
  ProfileId INTEGER CONSTRAINT PK_Profile PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
  Name TEXT NOT NULL,
  CreatedId TEXT NOT NULL,
  CreatedDate TEXT NOT NULL DEFAULT (datetime('now')),
  LastModifiedId TEXT NOT NULL,
  LastModifiedDate TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE UNIQUE INDEX IF NOT EXISTS IX_Profile ON Profile (Name);

/* End Profile */

/* Begin Route FTP Location*/
CREATE TABLE IF NOT EXISTS RouteFtpLocation
(
  RouteFtpLocationId INTEGER CONSTRAINT PK_RouteFtpLocation PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
  ProfileId INTEGER NOT NULL,
  RouteName TEXT NOT NULL,
  FtpHost TEXT NOT NULL,
  FtpUser TEXT NOT NULL,
  FtpPassword TEXT NOT NULL,
  CreatedId TEXT NOT NULL,
  CreatedDate TEXT NOT NULL DEFAULT (datetime('now')),
  LastModifiedId TEXT NOT NULL,
  LastModifiedDate TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY(ProfileId) REFERENCES Profile(ProfileId)
);

/* End Route FTP Location File */

/* Begin Route File */
CREATE TABLE IF NOT EXISTS RouteFtpLocationFile
(
  RouteFtpLocationFileId INTEGER CONSTRAINT PK_RouteFtpLocationFile PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
  RouteFtpLocationId INTEGER NOT NULL,
  RemoteFilePath TEXT NOT NULL,
  LocalFilePath TEXT NOT NULL,
  CreatedId TEXT NOT NULL,
  CreatedDate TEXT NOT NULL DEFAULT (datetime('now')),
  LastModifiedId TEXT NOT NULL,
  LastModifiedDate TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY(RouteFtpLocationId) REFERENCES RouteFtpLocation(RouteFtpLocationId)

);

/* End Route FTP Location File */
.quit