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 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');