SQL

CREATE TABLE Patient (
    Patient_ID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DateOfBirth DATE CHECK (DateOfBirth < GETDATE()),
    Gender VARCHAR(10) CHECK (Gender IN ('Male', 'Female', 'Other')),
    ContactNumber VARCHAR(15) UNIQUE NOT NULL,
    Email VARCHAR(100) UNIQUE CHECK (Email LIKE '%_@__%.__%'),
    Address VARCHAR(200) NOT NULL,
    BloodType VARCHAR(5) CHECK (BloodType IN ('A+', 'A-', 'B+', 'B-', 'O+', 'O-', 'AB+', 'AB-')),
    EmergencyContactName VARCHAR(100) NOT NULL,
    EmergencyContactNumber VARCHAR(15) NOT NULL,
    CreatedDate DATETIME DEFAULT GETDATE(),
    UpdatedDate DATETIME DEFAULT GETDATE()
);

CREATE TABLE Doctor (
    Doctor_ID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Specialization VARCHAR(100) NOT NULL,
    Department VARCHAR(100) NOT NULL,
    ContactNumber VARCHAR(15) UNIQUE NOT NULL,
    Email VARCHAR(100) UNIQUE CHECK (Email LIKE '%_@__%.__%'),
    Qualification VARCHAR(200) NOT NULL,
    JoiningDate DATE CHECK (JoiningDate <= GETDATE()),
    STATUS VARCHAR(20) DEFAULT 'Active' CHECK (STATUS IN ('Active', 'Inactive', 'On Leave')),
    CreatedDate DATETIME DEFAULT GETDATE(),
    UpdatedDate DATETIME DEFAULT GETDATE()
);

CREATE TABLE Appointment (
    Appointment_ID INT PRIMARY KEY IDENTITY(1,1),
    Patient_ID INT NOT NULL,
    Doctor_ID INT NOT NULL,
    AppointmentDate DATE CHECK (AppointmentDate >= GETDATE()),
    AppointmentTime TIME NOT NULL,
    Purpose VARCHAR(200) NOT NULL,
    STATUS VARCHAR(20) CHECK (STATUS IN ('Scheduled', 'Completed', 'Cancelled', 'Pending')),
    CreatedDate DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
);


CREATE TABLE Medical_Record (
    Record_ID INT PRIMARY KEY,
    Patient_ID INT,
    Doctor_ID INT,
    DiagnosisDate DATE,
    Diagnosis VARCHAR(500),
    Prescription TEXT,
    Treatment VARCHAR(500),
    Notes TEXT,
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
);

CREATE TABLE Ward (
    Ward_ID INT PRIMARY KEY,
    WardName VARCHAR(50),
    Capacity INT,
    CurrentOccupancy INT,
    WardType VARCHAR(50)
);

CREATE TABLE Bed (
    Bed_ID INT PRIMARY KEY,
    Ward_ID INT,
    STATUS VARCHAR(20),
    BedType VARCHAR(50),
    FOREIGN KEY (Ward_ID) REFERENCES Ward(Ward_ID)
);

CREATE TABLE Admission (
    Admission_ID INT PRIMARY KEY,
    Patient_ID INT,
    Bed_ID INT,
    AdmissionDate DATE,
    DischargeDate DATE,
    AdmissionReason VARCHAR(500),
    STATUS VARCHAR(20),
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID),
    FOREIGN KEY (Bed_ID) REFERENCES Bed(Bed_ID)
);

CREATE TABLE Invoice (
    Invoice_ID INT PRIMARY KEY,
    Patient_ID INT,
    TotalAmount DECIMAL(10,2),
    InvoiceDate DATE,
    PaymentStatus VARCHAR(20),
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID)
);

CREATE TABLE Invoice_Detail (
    InvoiceDetail_ID INT PRIMARY KEY,
    Invoice_ID INT,
    ServiceDescription VARCHAR(200),
    ServiceCost DECIMAL(10,2),
    FOREIGN KEY (Invoice_ID) REFERENCES Invoice(Invoice_ID)
);

CREATE TABLE Staff (
    Staff_ID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(100),
    POSITION VARCHAR(100),
    ContactNumber VARCHAR(15),
    Email VARCHAR(100),
    HireDate DATE,
    Salary DECIMAL(10,2)
);


-- drop database "73705_project"


INSERT INTO Patient (FirstName, LastName, DateOfBirth, Gender, ContactNumber, Email, Address, BloodType, EmergencyContactName, EmergencyContactNumber)
VALUES
('John', 'Doe', '1980-05-15', 'Male', '555-1234', 'john.doe@email.com', '123 Main St, New York', 'A+', 'Jane Doe', '555-5678'),
('Emily', 'Smith', '1995-12-20', 'Female', '555-9876', 'emily.smith@email.com', '456 Oak Ave, Chicago', 'B-', 'Michael Smith', '555-4321'),
('David', 'Johnson', '1975-07-10', 'Male', '555-2468', 'david.johnson@email.com', '789 Pine Rd, Los Angeles', 'O+', 'Sarah Johnson', '555-1357')
('Alice', 'Williams', '1985-03-22', 'Female', '555-1111', 'alice.williams@email.com', '321 Maple St, Boston', 'A-', 'Bob Williams', '555-2222'),
('Bob', 'Miller', '1990-07-30', 'Male', '555-3333', 'bob.miller@email.com', '654 Elm St, Seattle', 'B+', 'Alice Miller', '555-4444'),
('Charlie', 'Davis', '1982-11-11', 'Male', '555-5555', 'charlie.davis@email.com', '987 Birch St, Miami', 'O-', 'Diana Davis', '555-6666'),
('Diana', 'Garcia', '1978-02-28', 'Female', '555-7777', 'diana.garcia@email.com', '123 Cedar St, Denver', 'AB+', 'Evan Garcia', '555-8888'),
('Evan', 'Martinez', '1992-09-15', 'Male', '555-9999', 'evan.martinez@email.com', '456 Spruce St, Austin', 'A+', 'Fiona Martinez', '555-0000'),
('Fiona', 'Hernandez', '1987-06-05', 'Female', '555-1212', 'fiona.hernandez@email.com', '789 Pine St, San Francisco', 'B-', 'George Hernandez', '555-3434');



INSERT INTO Doctor (FirstName, LastName, Specialization, Department, ContactNumber, Email, Qualification, JoiningDate)
VALUES
('Michael', 'Brown', 'Cardiology', 'Heart Center', '555-7890', 'michael.brown@hospital.com', 'MD, Cardiology', '2010-03-15'),
('Sarah', 'Wilson', 'Pediatrics', 'Children''s Ward', '555-6543', 'sarah.wilson@hospital.com', 'MD, Pediatrics', '2015-06-20'),
('Robert', 'Taylor', 'Orthopedics', 'Bone & Joint Center', '555-9012', 'robert.taylor@hospital.com', 'MD, Orthopedic Surgery', '2008-11-10');

INSERT INTO Appointment (Patient_ID, Doctor_ID, AppointmentDate, AppointmentTime, Purpose, STATUS)
VALUES
(1, 1, '2024-02-15', '10:00:00', 'Cardiac Check-up', 'Scheduled'),
(2, 2, '2024-02-16', '14:30:00', 'Child Vaccination', 'Pending'),
(3, 3, '2024-02-17', '11:15:00', 'Knee Pain Consultation', 'Scheduled');

INSERT INTO Medical_Record (Record_ID, Patient_ID, Doctor_ID, DiagnosisDate, Diagnosis, Prescription, Treatment, Notes)
VALUES
(1, 1, 1, '2024-02-15', 'Mild Hypertension', 'Lisinopril 10mg', 'Blood Pressure Monitoring', 'Lifestyle changes recommended'),
(2, 2, 2, '2024-02-16', 'Routine Vaccination', 'MMR Vaccine', 'Immunization', 'Child is healthy'),
(3, 3, 3, '2024-02-17', 'Knee Ligament Strain', 'Ibuprofen 400mg', 'Physical Therapy', '6 weeks rehabilitation');

INSERT INTO Ward (Ward_ID, WardName, Capacity, CurrentOccupancy, WardType)
VALUES
(1, 'Cardiac Ward', 20, 15, 'Intensive Care'),
(2, 'Pediatric Ward', 15, 10, 'General'),
(3, 'Orthopedic Ward', 25, 18, 'Surgical');

INSERT INTO Bed (Bed_ID, Ward_ID, STATUS, BedType)
VALUES
(1, 1, 'Occupied', 'ICU'),
(2, 1, 'Available', 'Standard'),
(3, 2, 'Occupied', 'Pediatric'),
(4, 3, 'Available', 'Orthopedic');

INSERT INTO Admission (Admission_ID, Patient_ID, Bed_ID, AdmissionDate, DischargeDate, AdmissionReason, STATUS)
VALUES
(1, 1, 1, '2024-02-15', NULL, 'Cardiac Observation', 'Active'),
(2, 2, 3, '2024-02-16', NULL, 'Routine Monitoring', 'Active');

INSERT INTO Invoice (Invoice_ID, Patient_ID, TotalAmount, InvoiceDate, PaymentStatus)
VALUES
(1, 1, 1500.00, '2024-02-20', 'Pending'),
(2, 2, 750.00, '2024-02-21', 'Paid');

INSERT INTO Invoice_Detail (InvoiceDetail_ID, Invoice_ID, ServiceDescription, ServiceCost)
VALUES
(1, 1, 'Cardiac Consultation', 500.00),
(2, 1, 'ECG Test', 250.00),
(3, 1, 'Medication', 750.00),
(4, 2, 'Pediatric Consultation', 250.00),
(5, 2, 'Vaccination', 500.00);

INSERT INTO Staff (Staff_ID, FirstName, LastName, Department, POSITION, ContactNumber, Email, HireDate, Salary)
VALUES
(1, 'Lisa', 'Martinez', 'Nursing', 'Head Nurse', '555-3456', 'lisa.martinez@hospital.com', '2012-05-01', 65000.00),
(2, 'James', 'Anderson', 'Administration', 'Hospital Manager', '555-7890', 'james.anderson@hospital.com', '2010-01-15', 85000.00);


CREATE PROCEDURE AddPatient
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @DateOfBirth DATE,
    @Gender VARCHAR(10),
    @ContactNumber VARCHAR(15),
    @Email VARCHAR(100),
    @Address VARCHAR(200),
    @BloodType VARCHAR(5),
    @EmergencyContactName VARCHAR(100),
    @EmergencyContactNumber VARCHAR(15)
AS
BEGIN
    INSERT INTO Patient (FirstName, LastName, DateOfBirth, Gender, ContactNumber, Email, Address, BloodType, EmergencyContactName, EmergencyContactNumber)
    VALUES (@FirstName, @LastName, @DateOfBirth, @Gender, @ContactNumber, @Email, @Address, @BloodType, @EmergencyContactName, @EmergencyContactNumber);
END;


CREATE PROCEDURE UpdatePatient
    @Patient_ID INT,
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @DateOfBirth DATE,
    @Gender VARCHAR(10),
    @ContactNumber VARCHAR(15),
    @Email VARCHAR(100),
    @Address VARCHAR(200),
    @BloodType VARCHAR(5),
    @EmergencyContactName VARCHAR(100),
    @EmergencyContactNumber VARCHAR(15)
AS
BEGIN
    UPDATE Patient
    SET FirstName = @FirstName,
        LastName = @LastName,
        DateOfBirth = @DateOfBirth,
        Gender = @Gender,
        ContactNumber = @ContactNumber,
        Email = @Email,
        Address = @Address,
        BloodType = @BloodType,
        EmergencyContactName = @EmergencyContactName,
        EmergencyContactNumber = @EmergencyContactNumber,
        UpdatedDate = GETDATE()
    WHERE Patient_ID = @Patient_ID;
END;



CREATE PROCEDURE DeletePatient
    @Patient_ID INT
AS
BEGIN
    DELETE FROM Patient WHERE Patient_ID = @Patient_ID;
END;




CREATE TRIGGER trg_UpdatePatient
ON Patient
AFTER UPDATE
AS
BEGIN
    UPDATE Patient
    SET UpdatedDate = GETDATE()
    WHERE Patient_ID IN (SELECT DISTINCT Patient_ID FROM Inserted);
END;