CREATE TABLE Institution (
    InstitutionID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Address VARCHAR(255),
    Phone VARCHAR(20),
    Email VARCHAR(100),
    Website VARCHAR(100),
    Type VARCHAR(50),
    Accreditation VARCHAR(100)
);

CREATE TABLE Student (
    StudentID INT AUTO_INCREMENT PRIMARY KEY,
    First_Name VARCHAR(50) NOT NULL,
    Last_Name VARCHAR(50) NOT NULL,
    Date_of_Birth DATE,
    Address VARCHAR(255),
    Phone VARCHAR(20),
    Email VARCHAR(100),
    Program_of_Study VARCHAR(100)
);


CREATE TABLE Instructor (
    InstructorID INT AUTO_INCREMENT PRIMARY KEY,
    First_Name VARCHAR(50) NOT NULL,
    Last_Name VARCHAR(50) NOT NULL,
    Date_of_Birth DATE,
    Address VARCHAR(255),
    Phone VARCHAR(20),
    Email VARCHAR(100),
    Expertise VARCHAR(100)
);

CREATE TABLE Course (
    CourseID INT AUTO_INCREMENT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Description TEXT,
    Start_Date DATE,
    End_Date DATE,
    InstructorID INT,
    InstitutionID INT,
    Credits INT,
    FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID),
    FOREIGN KEY (InstitutionID) REFERENCES Institution(InstitutionID)
);

CREATE TABLE Enrollment (
    EnrollmentID INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    Enrollment_Date DATE,
    Grade VARCHAR(10),
    Status VARCHAR(20),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

CREATE TABLE Payment (
    PaymentID INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT,
    Amount DECIMAL(10, 2) NOT NULL,
    Payment_Date DATE,
    Payment_Method VARCHAR(50),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

INSERT INTO Institution (InstitutionID, Name, Address, Phone, Email, Website, Type, Accreditation)
VALUES
    (1, 'University of Exampleville', '123 Main St, Exampleville', '555-123-4567', 'info@exampleville.edu', 'www.exampleville.edu', 'University', 'Accredited'),
    (2, 'Community College of Imaginaria', '456 Elm St, Imaginaria', '555-987-6543', 'info@imaginaria.edu', 'www.imaginaria.edu', 'Community College', 'Accredited'),
    (3, 'Tech Institute of Techland', '789 Oak St, Techland', '555-567-8901', 'info@techlandtech.edu', 'www.techlandtech.edu', 'Technical Institute', 'Accredited'),
    (4, 'Art School of Creativia', '101 Artist Ave, Creativia', '555-234-5678', 'info@creativiaartschool.edu', 'www.creativiaartschool.edu', 'Art School', 'Not Accredited'),
    (5, 'Medical University of Healthville', '321 Hospital Rd, Healthville', '555-765-4321', 'info@healthvillemedu.edu', 'www.healthvillemedu.edu', 'Medical University', 'Accredited');

INSERT INTO Student (StudentID, First_Name, Last_Name, Date_of_Birth, Address, Phone, Email, Program_of_Study) VALUES
    (1, 'John', 'Doe', '1998-05-15', '789 Elm St, Exampleville', '555-111-2222', 'john.doe@exampleville.edu', 'Computer Science'),
    (2, 'Jane', 'Smith', '1999-03-20', '101 College Ave, Imaginaria', '555-333-4444', 'jane.smith@imaginaria.edu', 'Business Administration'),
    (3, 'Michael', 'Brown', ' 1997-07-10', '567 Tech Blvd, Techland', '555-555-6666', 'michael.brown@techlandtech.edu', 'Engineering'),
    (4, 'Emily', 'Johnson', '2000-01-25', '123 Artist St, Creativia', '555-777-8888', 'emily.johnson@creativiaartschool.edu', 'Fine Arts'),
    (5, 'Sarah', 'Wilson', '1996-11-30', '345 Hospital Dr, Healthville', '555-999-0000', 'sarah.wilson@healthvillemedu.edu', 'Medicine');

INSERT INTO Instructor (InstructorID, First_Name, Last_Name, Date_of_Birth, Address, Phone, Email, Expertise) VALUES
    (1, 'Professor', 'Smith', '1980-03-10', '101 Faculty Rd, Exampleville', '555-123-4567', 'prof.smith@exampleville.edu', 'Computer Science'),
    (2, 'Dr. Mary', 'Johnson', '1975-06-20', '123 Faculty Ave, Imaginaria', '555-987-6543', 'mary.johnson@imaginaria.edu', 'Business Administration'),
    (3, 'Professor', 'Jones', '1978-09-05', '789 Faculty St, Techland', '555-567-8901', 'prof.jones@techlandtech.eduEngineering', 'Engineering'),
    (4, 'Professor', 'Davis', '1985-12-15', '234 Faculty Blvd, Creativia', '555-234-5678', 'prof.davis@creativiaartschool.edu', 'Fine Arts'),
    (5, 'Dr. James', 'White', '1982-04-25', '567 Faculty Dr, Healthville', '555-765-4321', 'james.white@healthvillemedu.edu', 'Medicine');

INSERT INTO Course (CourseID, Title, Description, Start_Date, End_Date, InstructorID, InstitutionID, Credits) VALUES
    (1, 'Introduction to Programming', 'Fundamentals of programming in various languages.', '2023-01-15', '2023-05-15', 1, 1, 3),
    (2, 'Business Ethics', 'Ethical considerations in business management.', '2023-02-10', '2023-06-10', 2, 2, 4),
    (3, 'Mechanical Engineering Basics', 'Introductory course on mechanical engineering.', '2023-03-20', '2023-07-20', 3, 3, 4),
    (4, 'Oil Painting Techniques', 'Exploring traditional oil painting methods.', '2023-02-01', '2023-06-01', 4, 4, 2),
    (5, 'Anatomy and Physiology', 'Study of the human body and its functions.', '2023-01-30', '2023-05-30', 5, 5, 3);

INSERT INTO Enrollment (EnrollmentID, StudentID, CourseID, Enrollment_Date, Grade, Status) VALUES
    (1, 1, 1, '2023-01-20', 'A', 'Enrolled'),    
    (2, 2, 2, '2023-02-15', 'B', 'Enrolled'),
    (3, 3, 3, '2023-03-25', 'A-', 'NotEnrolled'),
    (4, 4, 4, '2023-02-05', 'B+', 'Enrolled'),
    (5, 5, 5, '2023-01-10', 'A', 'Enrolled');

INSERT INTO Payment (PaymentID, StudentID, Amount, Payment_Date, Payment_Method) VALUES
    (1, 1, 1500.00, '2023-01-25', 'Credit Card'),
    (2, 2, 2000.00, '2023-02-20', 'PayPal'),
    (3, 3, 1800.00, '2023-03-30', 'Bank Transfer'),
    (4, 4, 2200.00, '2023-02-10', 'Credit Card'),
    (5, 5, 1600.00, '2023-01-15', 'Check');

-- SELECT Course.Title, Instructor.First_Name, Instructor.Last_Name
-- FROM Course
-- JOIN Instructor ON Instructor.InstructorID=Course.InstructorID

-- SELECT Course.Title, Enrollment.Grade
-- FROM Course
-- JOIN Enrollment ON Enrollment.CourseID=Course.CourseID
-- JOIN Student ON Enrollment.StudentID=Student.StudentID
-- WHERE Student.First_Name='John' AND Student.Last_Name='Doe'

-- SELECT SUM(Payment.Amount) AS Total_Paid
-- FROM Payment
-- JOIN Student ON Student.StudentID=Payment.StudentID
-- WHERE Student.First_Name='Jane' AND Student.Last_Name='Smith'

-- SELECT Course.Title, Institution.Name
-- FROM Course
-- JOIN Institution ON Institution.InstitutionID=Course.InstitutionID
-- WHERE Institution.Name='University of Exampleville'

-- SELECT Student.First_Name, Student.Last_Name, Enrollment.Status
-- FROM Student
-- JOIN Enrollment ON Enrollment.StudentID=Student.StudentID
-- JOIN Course ON Enrollment.CourseID=Course.CourseID
-- WHERE Course.Title='Mechanical Engineering Basics'

-- SELECT Course.Title, COUNT(*)Enrollment_Count
-- FROM Course
-- JOIN Enrollment ON Enrollment.CourseID=Course.CourseID
-- GROUP BY Course.Title

-- SELECT Institution.Name, SUM(Payment.Amount) AS Total_Revenue
-- FROM Institution
-- JOIN Course ON Institution.InstitutionID=Course.InstitutionID
-- JOIN Enrollment ON Enrollment.CourseID=Course.CourseID
-- JOIN Student ON Enrollment.StudentID=Student.StudentID
-- JOIN Payment ON Payment.StudentID=Student.StudentID
-- GROUP By Institution.Name

-- SELECT Course.Title, AVG(YEAR(CURRENT_DATE())-YEAR(Student.Date_of_Birth)) AS Average_Age
-- FROM Course
-- JOIN Enrollment ON Course.CourseID=Enrollment.CourseID
-- JOIN Student ON Student.StudentID=Enrollment.StudentID
-- WHERE Course.CourseID=2

SELECT Student.StudentID, Student.First_Name, Student.Last_Name, SUM(Payment.Amount) AS Total_Paid
FROM Student
JOIN Payment ON Student.StudentID=Payment.StudentID
GROUP BY Student.StudentID 
ORDER BY Total_Paid DESC LIMIT 1