-- 1. Table Creation
-- Table creation for Institution
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)
);

-- Table creation for Student
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)
);

-- Table creation for Instructor
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)
);

-- Table creation for Course
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)
);

-- Table creation for Enrollment
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)
);

-- Table creation for Payment
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)
);

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


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


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

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

-- select *from Course;

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

-- select *from Enrollment;

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