Training Power App Project

-- TFUA_Employee_List
CREATE TABLE TFUA_Employee (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(255),
    Position VARCHAR(255),
    Department VARCHAR(255),
    Email VARCHAR(255),
    Shift VARCHAR(50),
    HireDate DATE,
    Status VARCHAR(50) -- Active, Inactive, Transferred, etc.
);

-- TFUA_TrainingRoles_List
CREATE TABLE TFUA_TrainingRoles (
    RoleID INT PRIMARY KEY,
    RoleName VARCHAR(255) -- Trainer, Manager, Mentor, etc.
);

-- TFUA_UserRoles (Linking users to their roles)
CREATE TABLE TFUA_UserRoles (
    UserRoleID INT PRIMARY KEY,
    UserID INT,
    RoleID INT,
    FOREIGN KEY (UserID) REFERENCES TFUA_Employee(EmployeeID),
    FOREIGN KEY (RoleID) REFERENCES TFUA_TrainingRoles(RoleID)
);

-- TFUA_TrainingSchedule_List
CREATE TABLE TFUA_TrainingSchedule (
    ScheduleID INT PRIMARY KEY,
    EmployeeID INT,
    TrainingStartTime DATETIME,
    TrainingEndTime DATETIME,
    FOREIGN KEY (EmployeeID) REFERENCES TFUA_Employee(EmployeeID)
);

-- TFUA_TrainingFeedback_List
CREATE TABLE TFUA_TrainingFeedback (
    FeedbackID INT PRIMARY KEY,
    ScheduleID INT,
    FeedbackText TEXT,
    Grade INT,
    FOREIGN KEY (ScheduleID) REFERENCES TFUA_TrainingSchedule(ScheduleID)
);

-- TFUA_InactiveEmployees_List
CREATE TABLE TFUA_InactiveEmployees (
    InactiveEmployeeID INT PRIMARY KEY,
    EmployeeID INT,
    InactiveDate DATE,
    Reason VARCHAR(255), -- Qualified, Transferred, Terminated, etc.
    FOREIGN KEY (EmployeeID) REFERENCES TFUA_Employee(EmployeeID)
);

-- TFUA_GlobalSchedule (For managing global events like holidays)
CREATE TABLE TFUA_GlobalSchedule (
    EventID INT PRIMARY KEY,
    EventName VARCHAR(255),
    StartDate DATE,
    EndDate DATE
);

-- TFUA_ManagerOverrides (For managers to override schedules or assign backups)
CREATE TABLE TFUA_ManagerOverrides (
    OverrideID INT PRIMARY KEY,
    ScheduleID INT,
    ManagerID INT,
    BackupTrainerID INT, -- ID of the mentor or trainer taking over
    OverrideReason VARCHAR(255),
    FOREIGN KEY (ScheduleID) REFERENCES TFUA_TrainingSchedule(ScheduleID),
    FOREIGN KEY (ManagerID) REFERENCES TFUA_Employee(EmployeeID),
    FOREIGN KEY (BackupTrainerID) REFERENCES TFUA_Employee(EmployeeID)
);