IF EXISTS(SELECT name FROM sysobjects WHERE name = N'settings' AND xtype='U') DROP TABLE [settings];
CREATE TABLE [settings] (

 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

 [settingname] VARCHAR(100),

 [settingvalue] VARCHAR(100),

 [createdat] DATETIME,

 [updatedat] DATETIME,

 [deletedat] DATETIME

);
INSERT INTO [settings] ( [SETTINGNAME],[SETTINGVALUE] ) VALUES ( 'installed',0 );
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'companies' AND xtype='U') DROP TABLE [companies];
CREATE TABLE [companies] (

 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

 [companyname] VARCHAR(200) NOT NULL,

 [address] VARCHAR(1000),

 [phone] VARCHAR(50),

 [fax] VARCHAR(50),

 [createdat] DATETIME,

 [updatedat] DATETIME,

 [deletedat] DATETIME

);
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'organizations' AND xtype='U') DROP TABLE [organizations];
CREATE TABLE [organizations] (

 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

 [organizationname] VARCHAR(255) NOT NULL,

 [companyid] INT DEFAULT NULL,

 [createdat] DATETIME,

 [updatedat] DATETIME,

 [deletedat] DATETIME,

 CONSTRAINT [FK_organizations_companies] FOREIGN KEY ([companyid]) REFERENCES companies([id])

);
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'grades' AND xtype='U') DROP TABLE [grades];
CREATE TABLE [grades] (

 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

 [gradename] VARCHAR(255) NOT NULL,

 [companyid] INT DEFAULT NULL,

 [createdat] DATETIME,

 [updatedat] DATETIME,

 [deletedat] DATETIME,

 CONSTRAINT [FK_grades_companies] FOREIGN KEY ([companyid]) REFERENCES companies([id])

);
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'users' AND xtype='U') DROP TABLE [users];
CREATE TABLE [users] (

 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

 [username] VARCHAR(50) NOT NULL,

 [userpassword] VARCHAR(128) NOT NULL,

 [isadmin] VARCHAR(1) NOT NULL,

 [userstatus] VARCHAR(1) NOT NULL,

 [crosscompany] VARCHAR(1) NOT NULL,

 [lastlogin] DATETIME,

 [createdat] DATETIME,

 [updatedat] DATETIME,

 [deletedat] DATETIME

);
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'userroles' AND xtype='U') DROP TABLE [userroles];
CREATE TABLE [userroles] (

 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

 [userid] INT DEFAULT NULL,

 [role] VARCHAR(200) NOT NULL,

 [createdat] DATETIME,

 [updatedat] DATETIME,

 [deletedat] DATETIME,

 CONSTRAINT [FK_userroles_users] FOREIGN KEY ([userid]) REFERENCES users([id])

);
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'people' AND xtype='U') DROP TABLE [people];
CREATE TABLE [people] (

 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

 [firstname] VARCHAR(50) NOT NULL,

 [middlename] VARCHAR(50),

 [lastname] VARCHAR(50),

 [idcard] VARCHAR(50),

 [birthplace] VARCHAR(100),

 [birthdate] DATETIME,

 [maritalstatus] VARCHAR(20),

 [userid] INT DEFAULT NULL,

 [createdat] DATETIME,

 [updatedat] DATETIME,

 [deletedat] DATETIME,

 CONSTRAINT [FK_people_users] FOREIGN KEY ([userid]) REFERENCES users([id])

);
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'employees' AND xtype='U') DROP TABLE [employees];
CREATE TABLE [employees] (

 [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

 [startdate] DATETIME NOT NULL,

 [enddate] DATETIME,

 [personid] INT DEFAULT NULL,

 [companyid] INT DEFAULT NULL,

 [organizationid] INT DEFAULT NULL,

 [gradeid] INT DEFAULT NULL,

 [createdat] DATETIME,

 [updatedat] DATETIME,

 [deletedat] DATETIME,

 CONSTRAINT [FK_employees_people] FOREIGN KEY ([personid]) REFERENCES people([id]),

 CONSTRAINT [FK_employees_companies] FOREIGN KEY ([companyid]) REFERENCES companies([id]),

 CONSTRAINT [FK_employees_organizations] FOREIGN KEY ([organizationid]) REFERENCES organizations([id]),

 CONSTRAINT [FK_employees_grades] FOREIGN KEY ([gradeid]) REFERENCES grades([id])

);