SQLQuery1.sql

-- Create Database
CREATE DATABASE SchoolManagementSyDB;
GO

USE SchoolManagementSyDB;
GO

-- Create Staff Table
CREATE TABLE Staff (
    Id INT PRIMARY KEY IDENTITY(1,1),
    FormattedId VARCHAR(50) UNIQUE NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    Gender CHAR(1) NOT NULL CHECK (Gender IN ('M', 'F')),
    DateOfBirth DATE NOT NULL,
    PlaceOfBirth NVARCHAR(100) NOT NULL,
    Department NVARCHAR(100) NOT NULL,
    Username NVARCHAR(50) UNIQUE NOT NULL,
    Password NVARCHAR(100) NOT NULL,
    CreatedDate DATETIME DEFAULT GETDATE()
);
GO

-- Create Student Table
CREATE TABLE Students (
    Id INT PRIMARY KEY IDENTITY(1,1),
    FormattedId VARCHAR(50) UNIQUE NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    Gender CHAR(1) NOT NULL CHECK (Gender IN ('M', 'F')),
    DateOfBirth DATE NOT NULL,
    PlaceOfBirth NVARCHAR(100) NOT NULL,
    Major NVARCHAR(100) NOT NULL,
    Username NVARCHAR(50) UNIQUE NOT NULL,
    Password NVARCHAR(100) NOT NULL,
    CreatedDate DATETIME DEFAULT GETDATE()
);
GO

-- Insert Sample Data
INSERT INTO Staff (FormattedId, Name, Gender, DateOfBirth, PlaceOfBirth, Department, Username, Password) VALUES 
('0000001.STA', 'Vorn Kanha', 'F', '2005-09-09', 'Kandal', 'Computer Science', 'kanha.vorn.staff', 'kanha123'),
('0000002.STA', 'Uy Sotheary', 'F', '2005-09-10', 'Phnom Penh', 'Fintech', 'theary.uy.staff', 'theary456'),
('0000003.STA', 'Yem Panhapich', 'F', '2005-10-13', 'Kompot', 'IT', 'panhapich.yem.staff', 'pich789');

INSERT INTO Students (FormattedId, Name, Gender, DateOfBirth, PlaceOfBirth, Major, Username, Password) VALUES 
('0000001.STU', 'Oun Sophanha', 'M', '2006-01-30', 'Phnom Penh', 'Computer Science', 'sophanha.oun.student', 'phanha123'),
('0000002.STU', 'Bob Milliams', 'M', '2004-07-22', 'Siem Reap', 'Computer Science', 'sopanha.ukk.student', 'panha456'),
('0000003.STU', 'Un Vongvechika', 'F', '2005-11-16', 'Kompot', 'Computer Science', 'vongvechika.un.student', 'vechika789');
GO

-- Create Stored Procedures
-- Staff Procedures
CREATE PROCEDURE sp_GetAllStaff
AS
BEGIN
    SELECT FormattedId, Name, Gender, DateOfBirth, PlaceOfBirth, Department, Username
    FROM Staff
    ORDER BY FormattedId;
END
GO

CREATE PROCEDURE sp_GetStaffByPassword
    @Password NVARCHAR(100)
AS
BEGIN
    SELECT FormattedId, Name, Gender, DateOfBirth, PlaceOfBirth, Department, Username, Password
    FROM Staff
    WHERE Password = @Password;
END
GO

CREATE PROCEDURE sp_AddStaff
    @FormattedId VARCHAR(50),
    @Name NVARCHAR(100),
    @Gender CHAR(1),
    @DateOfBirth DATE,
    @PlaceOfBirth NVARCHAR(100),
    @Department NVARCHAR(100),
    @Username NVARCHAR(50),
    @Password NVARCHAR(100)
AS
BEGIN
    INSERT INTO Staff (FormattedId, Name, Gender, DateOfBirth, PlaceOfBirth, Department, Username, Password)
    VALUES (@FormattedId, @Name, @Gender, @DateOfBirth, @PlaceOfBirth, @Department, @Username, @Password);
    
    SELECT SCOPE_IDENTITY() AS NewId;
END
GO

CREATE PROCEDURE sp_UpdateStaff
    @FormattedId VARCHAR(50),
    @Name NVARCHAR(100),
    @Gender CHAR(1),
    @DateOfBirth DATE,
    @PlaceOfBirth NVARCHAR(100),
    @Department NVARCHAR(100),
    @Username NVARCHAR(50)
AS
BEGIN
    UPDATE Staff
    SET Name = @Name,
        Gender = @Gender,
        DateOfBirth = @DateOfBirth,
        PlaceOfBirth = @PlaceOfBirth,
        Department = @Department,
        Username = @Username
    WHERE FormattedId = @FormattedId;
END
GO

CREATE PROCEDURE sp_ResetStaffPassword
    @FormattedId VARCHAR(50),
    @NewPassword NVARCHAR(100)
AS
BEGIN
    UPDATE Staff
    SET Password = @NewPassword
    WHERE FormattedId = @FormattedId;
END
GO

CREATE PROCEDURE sp_DeleteStaff
    @FormattedId VARCHAR(50)
AS
BEGIN
    DELETE FROM Staff
    WHERE FormattedId = @FormattedId;
END
GO

CREATE PROCEDURE sp_GetStaffByUsername
    @Username NVARCHAR(50)
AS
BEGIN
    SELECT FormattedId, Name, Gender, DateOfBirth, PlaceOfBirth, Department, Username, Password
    FROM Staff
    WHERE Username = @Username;
END
GO

-- Student Procedures
CREATE PROCEDURE sp_GetAllStudents
AS
BEGIN
    SELECT FormattedId, Name, Gender, DateOfBirth, PlaceOfBirth, Major, Username
    FROM Students
    ORDER BY FormattedId;
END
GO

CREATE PROCEDURE sp_AddStudent
    @FormattedId VARCHAR(50),
    @Name NVARCHAR(100),
    @Gender CHAR(1),
    @DateOfBirth DATE,
    @PlaceOfBirth NVARCHAR(100),
    @Major NVARCHAR(100),
    @Username NVARCHAR(50),
    @Password NVARCHAR(100)
AS
BEGIN
    INSERT INTO Students (FormattedId, Name, Gender, DateOfBirth, PlaceOfBirth, Major, Username, Password)
    VALUES (@FormattedId, @Name, @Gender, @DateOfBirth, @PlaceOfBirth, @Major, @Username, @Password);
    
    SELECT SCOPE_IDENTITY() AS NewId;
END
GO

CREATE PROCEDURE sp_UpdateStudent
    @FormattedId VARCHAR(50),
    @Name NVARCHAR(100),
    @Gender CHAR(1),
    @DateOfBirth DATE,
    @PlaceOfBirth NVARCHAR(100),
    @Major NVARCHAR(100),
    @Username NVARCHAR(50)
AS
BEGIN
    UPDATE Students
    SET Name = @Name,
        Gender = @Gender,
        DateOfBirth = @DateOfBirth,
        PlaceOfBirth = @PlaceOfBirth,
        Major = @Major,
        Username = @Username
    WHERE FormattedId = @FormattedId;
END
GO

CREATE PROCEDURE sp_ResetStudentPassword
    @FormattedId VARCHAR(50),
    @NewPassword NVARCHAR(100)
AS
BEGIN
    UPDATE Students
    SET Password = @NewPassword
    WHERE FormattedId = @FormattedId;
END
GO

CREATE PROCEDURE sp_DeleteStudent
    @FormattedId VARCHAR(50)
AS
BEGIN
    DELETE FROM Students
    WHERE FormattedId = @FormattedId;
END
GO

CREATE PROCEDURE sp_GetStudentByUsername
    @Username NVARCHAR(50)
AS
BEGIN
    SELECT FormattedId, Name, Gender, DateOfBirth, PlaceOfBirth, Major, Username, Password
    FROM Students
    WHERE Username = @Username;
END
GO

-- Get next ID procedures
CREATE PROCEDURE sp_GetNextStaffId
AS
BEGIN
    SELECT ISNULL(MAX(CAST(LEFT(FormattedId, CHARINDEX('.', FormattedId) - 1) AS INT)), 0) + 1 AS NextId
    FROM Staff;
END
GO

CREATE PROCEDURE sp_GetNextStudentId
AS
BEGIN
    SELECT ISNULL(MAX(CAST(LEFT(FormattedId, CHARINDEX('.', FormattedId) - 1) AS INT)), 0) + 1 AS NextId
    FROM Students;
END
GO