create database xbspi;

-- create Security_User {users table}
CREATE TABLE `Security_User` (
    `Id` CHAR(36) PRIMARY KEY NOT NULL,
    `CompanyId` CHAR(36) NULL,
    `ContactId` CHAR(36) NULL,

    `Username` VARCHAR(100) NULL,
    `ProfileImageUrl` VARCHAR(250) NULL,

    `PasswordHash` VARCHAR(100) NULL,
    `PasswordSalt` VARCHAR(100) NULL,

    `RegistrationToken` VARCHAR(50) NULL,
    `PasswordResetToken` VARCHAR(50) NULL,

    `LastLoginDateUtc` DATETIME NULL,
    `LastLogoutDateUtc` DATETIME NULL,
    `LastPasswordChangeUtc` DATETIME NULL,

    `Status` INT NULL,

    `CreatedDate` DATETIME NULL,
    `CreatedByContactId` CHAR(36) NULL,

    `UpdatedDate` DATETIME NULL,
    `UpdatedByContactId` CHAR(36) NULL,

    `IsDeleted` BOOLEAN DEFAULT FALSE,
    `IsActive` BOOLEAN DEFAULT FALSE,

    `DesignationId` CHAR(36) NULL,
    `BloodGroupId` CHAR(36) NULL,
    `BatchId` CHAR(36) NULL,
    `PassingYearId` CHAR(36) NULL,
    `DepartmentId` CHAR(36) NULL
);
CREATE TABLE `Security_Contact` (
    `Id` CHAR(36) PRIMARY KEY NOT NULL,
    `CompanyId` CHAR(36) NULL,
    `FirstName` VARCHAR(100) NOT NULL,
    `LastName` VARCHAR(100) NULL,
    `DesignationId` CHAR(36) NULL,
    `Email` VARCHAR(100) NULL,
    `Phone` VARCHAR(20) NULL,
    `ProfileImageUrl` VARCHAR(200) NULL,
    `Status` INT NULL,
    `CreatedDate` DATETIME NULL,
    `CreatedByContactId` CHAR(36) NULL,
    `UpdatedDate` DATETIME NULL,
    `UpdatedByContactId` CHAR(36) NULL,
    `IsEmployee` TINYINT(1) NULL,
    `IsDeleted` TINYINT(1) DEFAULT 0,
    `IsActive` TINYINT(1) DEFAULT 0
);
CREATE TABLE `Security_Role` (
    `Id` CHAR(36) PRIMARY KEY NOT NULL,
    `Name` VARCHAR(100) NOT NULL,
    `Status` TINYINT NULL,
    `CreatedDate` DATETIME NULL,
    `CreatedByContactId` CHAR(36) NULL,
    `UpdatedDate` DATETIME NULL,
    `UpdatedByContactId` CHAR(36) NULL,
    `ObjectId` VARCHAR(40) NULL,
    `CompanyId` CHAR(36) NULL,
    `IsFixed` TINYINT(1) NULL,
    `IsDeleted` TINYINT(1) DEFAULT 0,
    `IsActive` TINYINT(1) DEFAULT 0
);
CREATE TABLE `Security_UserRole` (
    `Id` CHAR(36) PRIMARY KEY NOT NULL,
    `UserId` CHAR(36) NULL,
    `RoleId` CHAR(36) NULL,
    `UpdatedDate` DATETIME NULL,
    `UpdatedByContactId` CHAR(36) NULL,
    `CreatedDate` DATETIME NULL,
    `IsDeleted` TINYINT(1) DEFAULT 0,
    `IsActive` TINYINT(1) DEFAULT 0
);

CREATE TABLE `Department` (
    `Id` CHAR(36) PRIMARY KEY NOT NULL,
    `Name` VARCHAR(255) NOT NULL,
    `CreatedByContactId` CHAR(36) NULL,
    `UpdatedByContactId` CHAR(36) NULL,
    `ObjectId` CHAR(36) NULL,
    `CreatedDate` DATETIME NULL,
    `UpdatedDate` DATETIME NULL,
    `IsDeleted` TINYINT(1) DEFAULT 0,
    `IsActive` TINYINT(1) DEFAULT 0
);