DB

[serviceproviderpk]

CREATE TABLE DoctorSchedules ( ScheduleID INT PRIMARY KEY IDENTITY(1,1), DoctorID INT FOREIGN KEY REFERENCES Doctors(DoctorID), DayOfWeek NVARCHAR(10), -- e.g., Monday, Tuesday StartTime TIME, EndTime TIME, IsAvailable BIT DEFAULT 1 );

[serviceproviderpk]

CREATE TABLE Appointments ( AppointmentID INT PRIMARY KEY IDENTITY(1,1), PatientID INT FOREIGN KEY REFERENCES Patients(PatientID), DoctorID INT FOREIGN KEY REFERENCES Doctors(DoctorID), AppointmentDate DATETIME, Status NVARCHAR(50), -- e.g., Scheduled, Completed, Cancelled Notes NVARCHAR(MAX), CreatedDate DATETIME DEFAULT GETDATE() );

[serviceproviderpk]

CREATE TABLE Billing ( BillingID INT PRIMARY KEY IDENTITY(1,1), AppointmentID INT FOREIGN KEY REFERENCES Appointments(AppointmentID), PatientID INT FOREIGN KEY REFERENCES Patients(PatientID), TotalAmount DECIMAL(18, 2), AmountPaid DECIMAL(18, 2), PaymentMethod NVARCHAR(50), -- e.g., Cash, Credit Card, Insurance BillingDate DATETIME DEFAULT GETDATE(), IsPaid BIT DEFAULT 0 );

[serviceproviderpk]

CREATE TABLE PatientVisits ( VisitID INT PRIMARY KEY IDENTITY(1,1), AppointmentID INT FOREIGN KEY REFERENCES Appointments(AppointmentID), PatientID INT FOREIGN KEY REFERENCES Patients(PatientID), DoctorID INT FOREIGN KEY REFERENCES Doctors(DoctorID), VisitDate DATETIME DEFAULT GETDATE(), Diagnosis NVARCHAR(MAX), Treatment NVARCHAR(MAX), FollowUpDate DATETIME, Notes NVARCHAR(MAX) );

[serviceproviderpk]

CREATE TABLE QueueTickets ( TicketID INT PRIMARY KEY IDENTITY(1,1), PatientID INT FOREIGN KEY REFERENCES Patients(PatientID), Status NVARCHAR(50), -- e.g., Waiting, InProgress, Completed PriorityLevel NVARCHAR(50), -- e.g., Normal, Urgent CreatedDate DATETIME DEFAULT GETDATE() );

[serviceproviderpk]

CREATE TABLE Prescriptions ( PrescriptionID INT PRIMARY KEY IDENTITY(1,1), PatientID INT FOREIGN KEY REFERENCES Patients(PatientID), DoctorID INT FOREIGN KEY REFERENCES Doctors(DoctorID), VisitID INT FOREIGN KEY REFERENCES PatientVisits(VisitID), PrescriptionDate DATETIME DEFAULT GETDATE(), Medication NVARCHAR(MAX), Dosage NVARCHAR(100), Frequency NVARCHAR(100), Notes NVARCHAR(MAX) );

[serviceproviderpk]

CREATE VIEW vw_DoctorPerformance AS SELECT d.DoctorID, d.FirstName, d.LastName, COUNT(v.VisitID) AS TotalVisits, AVG(DATEDIFF(MINUTE, a.AppointmentDate, v.VisitDate)) AS AvgWaitTime FROM Doctors d JOIN PatientVisits v ON d.DoctorID = v.DoctorID JOIN Appointments a ON v.AppointmentID = a.AppointmentID GROUP BY d.DoctorID, d.FirstName, d.LastName;

[serviceproviderpk]

CREATE TABLE SupportTickets ( TicketID INT PRIMARY KEY IDENTITY(1,1), PatientID INT FOREIGN KEY REFERENCES Patients(PatientID), IssueDescription NVARCHAR(MAX), Status NVARCHAR(50), -- e.g., Open, InProgress, Resolved CreatedDate DATETIME DEFAULT GETDATE(), ResolvedDate DATETIME );