# drop DATABASE test3;
# CREATE DATABASE test3;
use test3;
CREATE TABLE Schools (
  name          VARCHAR(50),
  address       VARCHAR(50),
  PRIMARY KEY (name, address),
  general_info  VARCHAR(50),
  vision        VARCHAR(50),
  mission       VARCHAR(50),
  email         VARCHAR(50),
  main_language VARCHAR(50),
  phone_number  VARCHAR(50)

);


CREATE TABLE Employees
(
  employee_ID       INTEGER AUTO_INCREMENT,
  PRIMARY KEY (employee_ID),
  first_name        VARCHAR(20),
  middle_name       VARCHAR(20),
  last_name         VARCHAR(20),
  gender            VARCHAR(1),
  birth_date        DATETIME,
  e_address         VARCHAR(50),
  email             VARCHAR(50),
  username          VARCHAR(20) UNIQUE,
  username_password VARCHAR(20),
  salary            FLOAT,
  school_name       VARCHAR(50),
  school_address    VARCHAR(50),
  FOREIGN KEY (school_name, school_address) REFERENCES Schools (name, address)
    ON DELETE SET NULL

);


CREATE TABLE  Students(
 ssn INTEGER NOT NULL ,
  PRIMARY KEY (ssn),
  school_name VARCHAR(50),
  school_address varchar(50),
  user_name VARCHAR(50) unique,
   gender VARCHAR(50),
  name VARCHAR(50),
  birth_date DATETIME ,
  accepted    int(1),
  password VARCHAR(50) ,
     age int AS (YEAR('2016-1-1') - YEAR(birth_date)) ,
  grade int As ((YEAR('2016-1-1') - YEAR(birth_date))-5) ,
FOREIGN KEY (school_name,school_address) REFERENCES Schools (name ,address)
);
SELECT *
FROM School_Activities;
SELECT *
FROM Announcements;

CREATE TABLE Types (
  type        VARCHAR(50),
  school_name VARCHAR(50),
  address     VARCHAR(50),
  FOREIGN KEY (school_name, address) REFERENCES Schools (name, address)

);


CREATE TABLE Teachers (
  seniority_level    INTEGER,
  first_year_of_work DATETIME,
  experience_year    INTEGER AS (year('2016-1-1') - year(first_year_of_work)),
  teacher_id         INTEGER,

  FOREIGN KEY (teacher_id) REFERENCES Employees (employee_id)
    ON DELETE CASCADE

);

CREATE TABLE Parents (
  user_name     VARCHAR(50) NOT NULL,
  PRIMARY KEY (user_name),
  password      VARCHAR(50),
  first_name    VARCHAR(50),
  last_name     VARCHAR(50),
  home_phone    VARCHAR(50),
  email         VARCHAR(50),
  address       VARCHAR(50),
  report_number INT -- ,
  --   FOREIGN KEY (report_number)REFERENCES Reports(report_number)

);


CREATE TABLE Reports (
  report_number INT AUTO_INCREMENT,
  PRIMARY KEY (report_number),
  comment       VARCHAR(50),
  content       VARCHAR(50),
  issue_date    DATETIME,
  reply         VARCHAR(50),
  user_name     VARCHAR(50),
  student_ssn   INTEGER,
  teacher_id    INTEGER  ,
  FOREIGN KEY (user_name)REFERENCES Parents(user_name)  ,
  FOREIGN KEY (teacher_id)REFERENCES Teachers(teacher_id),
  FOREIGN KEY (student_ssn)REFERENCES Students(ssn)
);

ALTER TABLE Parents
  ADD CONSTRAINT report_number FOREIGN KEY (report_number) REFERENCES Reports (report_number);
ALTER TABLE Reports
  ADD CONSTRAINT user_name FOREIGN KEY (user_name) REFERENCES Parents (user_name);
ALTER TABLE Reports
  ADD CONSTRAINT teacher_id FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id);
ALTER TABLE Reports
  ADD CONSTRAINT student_ssn FOREIGN KEY (student_ssn) REFERENCES Students (ssn);

CREATE TABLE Parents_apply_Students_Schools (
  accept           TINYINT,
  parent_user_name VARCHAR(50),
  student_ssn      INTEGER,
  school_name      VARCHAR(20),
  PRIMARY KEY (student_ssn,school_name,parent_user_name),
  FOREIGN KEY (student_ssn) REFERENCES Students (ssn),
  FOREIGN KEY (school_name) REFERENCES Schools (name),
  FOREIGN KEY (parent_user_name) REFERENCES Parents (user_name)
);

CREATE TABLE Teachers_supervises_Teachers (

  teacher_id1 INTEGER,
  teacher_id2 INTEGER,
  FOREIGN KEY (teacher_id1) REFERENCES Teachers (teacher_id),
  FOREIGN KEY (teacher_id2) REFERENCES Teachers (teacher_id)
);

CREATE TABLE Courses(
  course_code INTEGER  NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(course_code),
  name VARCHAR(50) ,
  description VARCHAR(50) ,
 school_address VARCHAR(50),
 school_name varchar(20) ,
  FOREIGN KEY (school_name,school_address)REFERENCES Schools(name,address)
);

CREATE TABLE High_Schools (
  name    VARCHAR(50),
  address VARCHAR(50),
  FOREIGN KEY (name, address) REFERENCES Schools (name, address)
);
CREATE TABLE Mid_Schools (
  name    VARCHAR(50),
  address VARCHAR(50),
  FOREIGN KEY (name, address) REFERENCES Schools (name, address)

);
CREATE TABLE Elementary_Schools (
  name    VARCHAR(50),
  address VARCHAR(50),
  FOREIGN KEY (name, address) REFERENCES Schools (name, address)

);
CREATE TABLE Required_Supplies (
  required_supplies VARCHAR(50),
  name              VARCHAR(50),
  address           VARCHAR(50),
  FOREIGN KEY (name, address) REFERENCES Schools (name, address)

);

CREATE TABLE Adminstrators (
  adminstrator_id INTEGER AUTO_INCREMENT,
  FOREIGN KEY (adminstrator_id) REFERENCES Employees (employee_id)
    ON DELETE CASCADE

);

CREATE TABLE Announcements (

  date        DATETIME,
  title       VARCHAR(50),
  description VARCHAR(50),
  type        VARCHAR(50),
  employee_id INTEGER,
  FOREIGN KEY (employee_id) REFERENCES Adminstrators (adminstrator_id)
);


CREATE TABLE School_Activities
(
  activity_Date   DATETIME,
  location        VARCHAR(200),
  PRIMARY KEY (activity_Date, location),
  teacher_id      INT,
  adminstrator_id INT,
  name            VARCHAR(50),
  equipment       VARCHAR(50),
  type            VARCHAR(50),
  description     VARCHAR(50),
  FOREIGN KEY (adminstrator_id) REFERENCES Adminstrators (adminstrator_id),
  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
);

CREATE TABLE Assignments (

  assignment_number INTEGER AUTO_INCREMENT,
  PRIMARY KEY (assignment_number),
  posting_date      DATETIME,
  due_date          DATETIME,
  content           VARCHAR(50)

);

CREATE TABLE Activities_Involve_Students (
  student_ssn   INTEGER,
  activity_Date DATETIME,
  location      VARCHAR(200),
  PRIMARY KEY (student_ssn,activity_Date,location),
  FOREIGN KEY (student_ssn) REFERENCES Students (ssn),
  FOREIGN KEY (activity_Date, location) REFERENCES School_Activities (activity_Date, location)
);


CREATE TABLE Mobile_numbers (
  mobile           VARCHAR(50),
  parent_user_name VARCHAR(50),
  FOREIGN KEY (parent_user_name) REFERENCES Parents (user_name)
);

CREATE TABLE Levels (
  level VARCHAR(50),
  PRIMARY KEY (level)
);

set FOREIGN_KEY_CHECKS =0;
drop TABLE Levels_offer_Schools;
CREATE TABLE Levels_offer_Schools (
  level          VARCHAR(50),
  school_name    VARCHAR(50),
  school_address VARCHAR(50),
  FOREIGN KEY (level) REFERENCES Levels (level),
  FOREIGN KEY (school_name, school_address) REFERENCES Schools (name, address)

);
set FOREIGN_KEY_CHECKS =1;


CREATE TABLE Courses_offer_Levels (
  grade       INTEGER,
  level       VARCHAR(50),
  course_code INTEGER,
  PRIMARY KEY (level,course_code),
  FOREIGN KEY (level) REFERENCES Levels (level),
  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
);



CREATE TABLE Administrators_Assigns_Teachers
(
  teacher_id      INT,
  adminstrator_id INT,
  name            VARCHAR(50),
  activity_Date   DATETIME,
  location        VARCHAR(200),
  PRIMARY KEY (adminstrator_id,teacher_id,activity_Date,location),
  FOREIGN KEY (adminstrator_id) REFERENCES Adminstrators (adminstrator_id)
    ON DELETE CASCADE,
  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
    ON DELETE CASCADE,
  FOREIGN KEY (activity_Date, location) REFERENCES School_Activities (activity_Date, location)
);

CREATE TABLE Questions
(
  question_number INT PRIMARY KEY AUTO_INCREMENT,
  question        VARCHAR(50),
  course_code     INT,
  ssn             INT,
  content VARCHAR(500),
  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
    ON DELETE CASCADE,
  FOREIGN KEY (ssn) REFERENCES Students (ssn)
    ON DELETE CASCADE
);

CREATE TABLE Students_Asks_Questions
(
  ssn             INT,
  course_code     INT,
  question_number INT,
  PRIMARY KEY (course_code,ssn,question_number),
  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
    ON DELETE CASCADE,
  FOREIGN KEY (ssn) REFERENCES Students (ssn)
    ON DELETE CASCADE,
  FOREIGN KEY (question_number) REFERENCES Questions (question_number)
    ON DELETE CASCADE
);

CREATE TABLE Answers
(
  answer_number   INTEGER AUTO_INCREMENT,
  PRIMARY KEY (answer_number),
  answer          VARCHAR(20),
  teacher_id      INT,
  question_number INT,
  content VARCHAR(500),
  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
    ON DELETE CASCADE,
  FOREIGN KEY (question_number) REFERENCES Questions (question_number)
    ON DELETE CASCADE
);

CREATE TABLE Questions_isPartOf_Courses
(
  course_code     INTEGER,
  answer_number   INT,
  question_number INT,
  PRIMARY KEY (course_code,question_number,answer_number),
  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
    ON DELETE CASCADE,
  FOREIGN KEY (question_number) REFERENCES Questions (question_number)
    ON DELETE CASCADE,
  FOREIGN KEY (answer_number) REFERENCES Answers (answer_number)
    ON DELETE CASCADE
);


CREATE TABLE grade
(
  teacher_id        INT,
  assignment_number INT,
  course_code       INT,
  student_grade     INT,
  PRIMARY KEY (teacher_id,course_code,assignment_number),
  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
    ON DELETE CASCADE,
  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
    ON DELETE CASCADE,
  FOREIGN KEY (assignment_number) REFERENCES Assignments (assignment_number)
    ON DELETE CASCADE
);

CREATE TABLE Courses_has_prerequisite_Courses (
  course_code1 INTEGER,
  course_code2 INTEGER,
  PRIMARY KEY (course_code1,course_code2),
  FOREIGN KEY (course_code1) REFERENCES Courses (course_code),
  FOREIGN KEY (course_code2) REFERENCES Courses (course_code)

);

CREATE TABLE Assignments_postedBy_Teachers (
  teacher_id        INT,
  course_code       INT,
  assignment_number INTEGER,
  PRIMARY KEY (teacher_id,course_code,assignment_number),
  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
    ON DELETE CASCADE,
  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
    ON DELETE CASCADE,
  FOREIGN KEY (assignment_number) REFERENCES Assignments (assignment_number)
);

CREATE TABLE Parents_reply_Reports (
  reply      VARCHAR(50),
  user_name  VARCHAR(50),
  teacher_id INTEGER,
  PRIMARY KEY (user_name,teacher_id),
  FOREIGN KEY (user_name) REFERENCES Parents (user_name),
  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)

);


CREATE TABLE Parents_rateAndreview_Teachers (
  rating     INT ,
  rate       INTEGER,
  teacher_id INT,
  parent     VARCHAR(50),
  PRIMARY KEY (teacher_id,parent),
  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id),
  FOREIGN KEY (parent) REFERENCES Parents (user_name)
);


CREATE TABLE Parents_rateAndreview_Schools (
  rate            INT ,
  review          VARCHAR(50),
  parent_username VARCHAR(50),
  school_name     VARCHAR(50),
  school_address  VARCHAR(50),
  PRIMARY KEY (parent_username,school_name,school_address),
  FOREIGN KEY (parent_username) REFERENCES Parents (user_name),
  FOREIGN KEY (school_name, school_address) REFERENCES Schools (name, address)
);

CREATE TABLE Clubs (
  club_name      VARCHAR(50),
  PRIMARY KEY (club_name),
  purpose        VARCHAR(50),
  school_address VARCHAR(200),
  school_name    VARCHAR(50),
  FOREIGN KEY (school_name, school_address) REFERENCES Schools (name, address)
    ON DELETE SET NULL
);

CREATE TABLE Clubs_joinBy_Students (
  ssn       INT,
  club_name VARCHAR(50),
  PRIMARY KEY (ssn,club_name),
  FOREIGN KEY (ssn) REFERENCES Students (ssn),
  FOREIGN KEY (club_name) REFERENCES Clubs (club_name)
);

CREATE TABLE Students_taughtBy_Teachers (
  ssn         INT DEFAULT 1111,

  course_code INTEGER,
  teacher_id  INT,
  PRIMARY KEY (ssn,course_code),
  FOREIGN KEY (ssn) REFERENCES Students (ssn)
    ON DELETE CASCADE,
  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)
    ON DELETE CASCADE,
  FOREIGN KEY (course_code) REFERENCES Courses (course_code)
    ON DELETE CASCADE
);

CREATE TABLE Students_Enrolled_Schools (
  ssn            INTEGER,
  school_name    VARCHAR(50),
  school_address VARCHAR(50),
  PRIMARY KEY (ssn,school_address,school_name),
  FOREIGN KEY (ssn) REFERENCES Students (ssn),
  FOREIGN KEY (school_name, school_address) REFERENCES Schools (name, address)

);


CREATE TABLE Assignments_gradedBy_Teachers_Students (
  grade             INTEGER,
  assignment_number INTEGER,
  student_ssn       INTEGER,
  teacher_id        INTEGER,
  PRIMARY KEY (student_ssn,assignment_number),
  FOREIGN KEY (student_ssn) REFERENCES Students (ssn),
  FOREIGN KEY (assignment_number) REFERENCES Assignments (assignment_number),
  FOREIGN KEY (teacher_id) REFERENCES Teachers (teacher_id)

);

CREATE TABLE Assignments_solve_Students(
  solution VARCHAR(50),
grade int ,
  student_ssn INTEGER  ,
  assignment_number INTEGER default 0 ,
  PRIMARY KEY (student_ssn,assignment_number),
  FOREIGN KEY (student_ssn)REFERENCES Students(SSN),
  FOREIGN KEY (assignment_number) REFERENCES Assignments(assignment_number)
);


-- and done
create Table Assignments_posteBy_Teachers(
  teacher_id int,
  course_code int ,
  assignment_number int
   ,PRIMARY KEY (assignment_number,course_code),
  FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id) on DELETE CASCADE,
  FOREIGN key (course_code)REFERENCES Courses (course_code) on DELETE  CASCADE,
  FOREIGN key (assignment_number) REFERENCES Assignments(assignment_number)
);
-- start of system user proc
# the following 3 procedured to the following tasks respectivly name,address,type
DELIMITER //
CREATE PROCEDURE searchForSchoolName(name VARCHAR(50))
  BEGIN
    IF name IS NULL
    THEN
      SELECT 'We do not accept null values';
    ELSE
      SELECT *
      FROM Schools
      WHERE Schools.name = name;
    END IF;
  END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE searchForSchoolAddress(address VARCHAR(50))
  BEGIN
    IF address IS NULL
    THEN
      SELECT 'We do not accept null values';
    ELSE
      SELECT *
      FROM Schools
      WHERE Schools.address = address;
    END IF;
  END //
DELIMITER ;


DELIMITER //
CREATE PROCEDURE searchForSchoolType(typee VARCHAR(50))
  BEGIN
    IF typee IS NULL
    THEN
      SELECT 'We do not accept null values';
    ELSE
      SELECT S.*
      FROM Schools S inner JOIN Types T on t.school_name = S.name and T.address = S.address
      WHERE T.type = typee;
    END IF;
  END //
DELIMITER ;



drop PROCEDURE viewSchoolsByLevel;
DELIMITER //
CREATE PROCEDURE viewSchoolsByLevel()
  BEGIN
    SELECT
      S.name,
      S.address,
      los.level
    FROM Schools S inner join Levels_offer_Schools los on los.school_name = S.name and los.school_address = S.address
    GROUP BY los.level and S.name and S.address;
  END //

call viewSchoolsByLevel();



-- 3
# View the information of a certain school along with the reviews written about it and teachers
# teaching in this school.
DELIMITER //
CREATE PROCEDURE viewSchoolAndRating(sName VARCHAR(50), sAddress VARCHAR(50))
  BEGIN
    SELECT
      S.*,
      rating.rate,
      rating.review,
      rating.parent_username,
      E.first_name
    FROM Schools S INNER JOIN Parents_rateAndreview_Schools rating
        ON S.address = rating.school_address AND S.name = rating.school_name
      INNER JOIN Employees E ON E.school_address = S.address AND E.school_name = S.name
      INNER JOIN Teachers T ON T.teacher_id = E.employee_ID
    where E.first_name is not null  and E.school_name = sName and E.school_address = sAddress;
  END //

-- 1
# View all unverified teachers (those with username and password as null)
DELIMITER //
CREATE PROCEDURE View_Unverified_Teachers(myName VARCHAR(50), myPassword VARCHAR(50))
  BEGIN

    DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);

    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    SELECT E.*
    FROM Teachers T INNER JOIN Employees E ON E.employee_ID = T.teacher_id
      INNER JOIN Schools S2 ON E.school_name = S2.name AND E.school_address = S2.address
    WHERE
      S2.address = mySchoolAddress AND S2.name = mySchoolName AND E.username IS NULL AND E.username_password IS NULL;

  END //
DELIMITER ;
SELECT S.name

    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
    WHERE E.username = 'abeer.elsayed' and E.username_password = 'blabizo';
SELECT *
FROM Employees;


# Verify or assign usernames and passwords to all teacher who applied to the admin's but do not have usernames and passwords yet
# A salary for this teacher is also calculated

DELIMITER //
CREATE PROCEDURE Verify_Teacher(myName VARCHAR(50), myPass VARCHAR(50), tID INT, newName VARCHAR(50), newPass VARCHAR(50))
  BEGIN
    DECLARE myID int;
    SELECT E2.employee_ID into myID
    FROM Employees E2
    WHERE E2.username = myName and E2.username_password = myPass;

    UPDATE Employees E
    SET E.username = newName, E.username_password = newPass
    WHERE E.employee_ID = tID;
    CALL addSalary(myID, tID);


  END //

DELIMITER ;


# a helper procedure used to calculate the salary of a teacher
DELIMITER //
CREATE PROCEDURE addSalary(myID INT, tID INT)
  BEGIN
    DECLARE x INT;
    SELECT T.experience_year
    INTO x
    FROM Teachers T
    WHERE tID = T.teacher_id;

    UPDATE Employees E
    SET E.salary = 500 * x
    WHERE E.employee_ID = tID;

  END;

DELIMITER ;


-- 2
# DROP PROCEDURE View_Unverified_Students;
#  views all students who applied to the admin's school but do not have a username and a password yet
DELIMITER //
CREATE PROCEDURE View_Unverified_Students(myName VARCHAR(50) , myPass VARCHAR(50))
  BEGIN
    DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);

    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
    WHERE E.username = myName and E.username_password = myPass;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
    WHERE E.username = myName and E.username_password = myPass;

    SELECT S.*
    FROM Students S INNER JOIN Schools school ON S.school_name = school.name AND S.school_address = school.address
    WHERE
      school.address = mySchoolAddress AND school.name = mySchoolName AND S.user_name IS NULL AND S.password IS NULL;

  END //
use test3;
INSERT INTO Students (ssn, school_name, school_address)
VALUES (32352854,'El rowad','8 Mostafa El Nahhas');
SELECT *
FROM Schools;
SELECT *
FROM Employees;
SELECT *
FROM Students;

DELIMITER //
SELECT * FROM Students WHERE school_name = 'saintfatima' and '66 Ismail Al Kabbani' and accepted is null;
UPDATE Students set accepted = 1 where ssn = 1222;
DELIMITER //
# drop PROCEDURE Verify_Student;
# verify or assign a user name or password to non verified students
CREATE PROCEDURE Verify_Student( sID INT, user VARCHAR(50), pass VARCHAR(50))
  BEGIN

    UPDATE Students S
    SET S.user_name = user, S.password = pass
    WHERE S.ssn = sID;

  END //
DELIMITER ;
use test3;
SELECT *
FROM Employees;
SELECT *
FROM Adminstrators;

-- 3
#Add other admins to the school I am working in. An admin has first name, middle name, last name,
# birthdate, address, email, username, password, and gender. Note that the salary of the admin
# depends on the type of the school
DELIMITER //
CREATE PROCEDURE addAdmin(newusername          VARCHAR(20),
                          newusername_password VARCHAR(20),
                          newfirst_name        VARCHAR(20),
                          newmiddle_name       VARCHAR(20),
                          newlast_name         VARCHAR(20),
                          newgender            VARCHAR(1),
                          newemail             VARCHAR(50),
                          newaddress           VARCHAR(50),
                          newbirth_date        DATETIME, adminName VARCHAR(50),adminPassword VARCHAR(50),schoolType VARCHAR(50))
  BEGIN
    DECLARE schoolAddress VARCHAR(50);
    DECLARE schoolName VARCHAR(50);
    DECLARE id INT DEFAULT 0;

    SELECT S.name
    INTO schoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
    WHERE E.username = adminName and E.username_password = adminPassword;


    SELECT S.address
    INTO schoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
    WHERE E.username = adminName and E.username_password = adminPassword;


    INSERT INTO Employees (username, username_password, first_name, middle_name, last_name, gender, email, e_address, birth_date, school_name, school_address)
    VALUES
      (newusername, newusername_password, newfirst_name, newmiddle_name, newlast_name, newgender, newemail, newaddress,
                    newbirth_date, schoolName, schoolAddress);

    SELECT E.employee_ID
    INTO id
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
    WHERE E.username = newusername;

    INSERT INTO Adminstrators (adminstrator_id)
      VALUE (id);

    IF (schoolType='international') then
      UPDATE Employees E2
        SET E2.salary = 5000
      WHERE E2.employee_ID = id;
    ELSE
        UPDATE Employees E2
        SET E2.salary = 3000
      WHERE E2.employee_ID = id;
end IF ;
  END //
DELIMITER ;


-- 4
# DROP PROCEDURE View_Employees;
# View employees in the same school as the adminsystem.
DELIMITER //
CREATE PROCEDURE View_Employees(myName VARCHAR(50), myPass VARCHAR(50))
  BEGIN

    DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);
    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
    WHERE E.username = myName and E.username_password = myPass;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
    WHERE E.username = myName and E.username_password = myPass;

    SELECT E.*
    FROM Employees E
    WHERE E.school_name = mySchoolName AND E.school_address = mySchoolAddress;
  END //
DELIMITER ;




# Delete certain employee from the admin's system
DELIMITER //
CREATE PROCEDURE deleteEmployeesFromSystem(id INT)
  BEGIN
    DELETE FROM Employees
    WHERE employee_ID = id;


  END //
DELIMITER ;


-- 5
# drop PROCEDURE updateGeneralInfo;
# update admin's school general info
DELIMITER //
CREATE PROCEDURE updateGeneralInfo(info VARCHAR(200), myName  VARCHAR(50), myPassword VARCHAR(50))
  BEGIN
 DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);

    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    UPDATE Schools S
    SET S.general_info = info
    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;

  END //
DELIMITER ;
# call updateGeneralInfo()

# update admin's school vision
DELIMITER //
# drop PROCEDURE updateVision;
CREATE PROCEDURE updateVision(vis VARCHAR(200),myName  VARCHAR(50), myPassword VARCHAR(50))
  BEGIN
DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);

    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    UPDATE Schools S
    SET S.vision = vis
    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;
  END //
DELIMITER ;

DELIMITER //
# drop PROCEDURE updateAddress;
# update admin's school address
CREATE PROCEDURE updateAddress(naddress VARCHAR(50),myName  VARCHAR(50), myPassword VARCHAR(50))
  BEGIN
 DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);

    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    UPDATE Schools S
    SET S.address = naddress
    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;

  END //
DELIMITER ;



DELIMITER //
drop PROCEDURE updateMission;
# update admin's school mission
CREATE PROCEDURE updateMission(Mis VARCHAR(200), myName  VARCHAR(50), myPassword VARCHAR(50))
  BEGIN
    DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);

    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    UPDATE Schools S
    SET S.mission = Mis
    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;

  END //


DELIMITER ;


DELIMITER //
# update admin's school email
CREATE PROCEDURE updateEmail(mail VARCHAR(50), myName VARCHAR(50),myPassword VARCHAR(50))
  BEGIN
   DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);

    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    UPDATE Schools S
    SET S.email = mail
    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;

  END //
DELIMITER ;



# update admin's school mainLanguage
DELIMITER //
CREATE PROCEDURE updateMainLangauge(lang VARCHAR(50), myName VARCHAR(50) ,myPassword VARCHAR(50) )
  BEGIN
       DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);

    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    UPDATE Schools S
    SET S.main_language = lang
    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;

  END //
# drop PROCEDURE updateMainLangauge;
DELIMITER ;
SELECT *
FROM Schools;

# update admin's school number
DELIMITER //
# drop PROCEDURE updatePhoneNumber;
CREATE PROCEDURE updatePhoneNumber(num INT, myName VARCHAR(50), myPassword VARCHAR(50))
  BEGIN
      DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);

    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON a.adminstrator_id = E.employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = employee_ID
    WHERE E.username = myName and E.username_password = myPassword;

    UPDATE Schools S
    SET S.phone_number = num
    WHERE S.name = mySchoolName AND S.address = mySchoolAddress;
  END //


# 6 Post announcements with the following information: date, title, description and type (events, news,
# trips ...etc) to the admin's school
DELIMITER //
CREATE PROCEDURE createAnnouncement(newdata        DATETIME, newtitle VARCHAR(50), newdescription VARCHAR(50),
                                    newemployee_id INT, newtype VARCHAR(50))
  BEGIN
    INSERT INTO Announcements (date, title, description, employee_id, type)
    VALUES (newdata, newtitle, newdescription, newemployee_id, newtype);
  END//
DELIMITER ;
SELECT *
FROM Announcements;


-- 7
# Create activities and assign every activity to a certain teacher. An activity has its own date, location
# in school, type, equipment(if any), and description of the admin's school
DELIMITER //
CREATE PROCEDURE createActivity(date               DATETIME, newlocation VARCHAR(50),
                                newadminstrator_id INT,
                                newname            VARCHAR(50),
                                newequipment       VARCHAR(50),
                                newtype            VARCHAR(50),
                                newdescription     VARCHAR(50))
  BEGIN
    INSERT INTO School_Activities (activity_Date, location, adminstrator_id, name, equipment, type, description)
    VALUES (date, newlocation, newadminstrator_id, newname, newequipment, newtype, newdescription);

  END//
SELECT *
FROM School_Activities;


-- 8
-- note:also changes assigned teacher
# Change the teacher assigned to an activity . can also be used to assign a teacher to an assignment
DELIMITER //
CREATE PROCEDURE assignTeacherToActivity(date DATETIME, newlocation VARCHAR(50), newteacher_id INT, adminstrator_id INT)
  BEGIN
    UPDATE School_Activities S
    SET S.teacher_id = newteacher_id
    WHERE S.activity_Date = date AND S.location = newlocation;

  END //
DELIMITER ;


-- 9
-- TODO: the course thingy
#  Assign teachers to courses that are taught in my school based on the levels it offers
DELIMITER //
# assigns a teacher to a course
CREATE PROCEDURE Assign_Teacher_to_Course(myID int , tID INT, cc int)
  BEGIN
   INSERT INTO Students_taughtBy_Teachers( course_code, teacher_id)
     VALUES (cc,tID);

  END //
DELIMITER ;


SELECT *
FROM Students_taughtBy_Teachers;
-- 10
#  assigns a teacher (teacher 1) to supervise another teacher( teacher 2)
DELIMITER //
-- 1 supervises 2
CREATE PROCEDURE assignTeacherToSuperViseTeacher(id1 INT, id2 INT, adminID INT)
  BEGIN
    INSERT INTO Teachers_supervises_Teachers (teacher_id1, teacher_id2)
    VALUES (id1, id2);
  END;
DELIMITER ;



-- 11 Accept or reject the application submitted by parents to their children. in the admin's school
# DROP PROCEDURE viewPendingStudents;
DELIMITER //
CREATE PROCEDURE viewPendingStudents(myID INT)
  BEGIN
    DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);

    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
    WHERE E.employee_ID = myID;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
    WHERE E.employee_ID = myID;

    SELECT S.*
    FROM Students S
    WHERE S.school_address = mySchoolAddress AND S.school_name = mySchoolName AND S.accepted IS NULL;
  END;
DELIMITER ;


DELIMITER //

CREATE PROCEDURE acceptOrRejectStudents(myID INT, sID INT, decission INT(1))
  BEGIN
    DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);

    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
    WHERE E.employee_ID = myID;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Adminstrators A ON A.adminstrator_id = E.employee_ID
    WHERE E.employee_ID = myID;

    UPDATE Students S
    SET S.accepted = decission
    WHERE S.school_address = mySchoolAddress AND S.school_name = mySchoolName AND S.accepted IS NULL AND sID = S.ssn;

  END //

-- 9 view the questions asked by the students for each course I teach.
DELIMITER //

CREATE PROCEDURE Teacher_View_Questions(myID INT)
  BEGIN
    DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);

    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Teachers T ON T.teacher_id = E.employee_ID
    WHERE E.employee_ID = myID;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
      INNER JOIN Teachers T ON T.teacher_id = E.employee_ID
    WHERE E.employee_ID = myID;

    SELECT Q.*
    FROM Questions Q INNER JOIN Courses C ON Q.course_code = C.course_code
      INNER JOIN Students S ON S.ssn = Q.ssn
      INNER JOIN Students_taughtBy_Teachers stt ON stt.ssn = S.ssn AND stt.course_code = C.course_code
    WHERE stt.teacher_id = myID;
  END //
DELIMITER ;

#Answer the questions asked by the students for each course I teach.
-- 9
DELIMITER //
CREATE PROCEDURE Teacher_Answers_Questions(myID INT, qnum INT, cont VARCHAR(500))
  BEGIN
    INSERT INTO Answers (teacher_id, question_number, content)
    VALUES (myID, qnum, cont);
  END //
DELIMITER ;



-- TODO: test above  proc
-- 10
-- TODO:verify 10
# View a list of students that i teach categorized by the grade and ordered by their name (first name
# and last name).

DELIMITER //
CREATE PROCEDURE View_Students(myID INT)
  BEGIN
    SELECT
      S.name,
      g.student_grade
    FROM Students_taughtBy_Teachers stt INNER JOIN Students S ON stt.ssn = S.ssn
      INNER JOIN grade g ON g.teacher_id = stt.teacher_id
    WHERE stt.teacher_id = myID
    ORDER BY S.name;
  END //
DELIMITER ;


# View a list of students that did not join any activity.
DELIMITER //
CREATE PROCEDURE Students_Without_Activity(myId INT)
  BEGIN
    DECLARE mySchoolName VARCHAR(50);
    DECLARE mySchoolAddress VARCHAR(50);

    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
    WHERE E.employee_ID = myID;

    SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Employees E ON E.school_name = S.name AND E.school_address = S.address
    WHERE E.employee_ID = myID;


    SELECT S.*
    FROM Students S, Activities_Involve_Students ais
    WHERE S.school_name = mySchoolName AND S.school_address = mySchoolAddress AND S.ssn NOT IN (SELECT ais.student_ssn
                                                                                                FROM
                                                                                                  Activities_Involve_Students ais);


  END //
DELIMITER ;

# return the average rating of the teacher
DELIMITER //
CREATE PROCEDURE Rate_Teacher_Avg(OUT res FLOAT, myId INT)
  BEGIN
    DECLARE total FLOAT;
    DECLARE cnt FLOAT;

    SELECT count(prt.rating)
    INTO cnt
    FROM Teachers T INNER JOIN Parents_rateAndreview_Teachers prt ON prt.teacher_id = T.teacher_id
    WHERE T.teacher_id = myId;

    SELECT sum(prt.rating)
    INTO total
    FROM Teachers T INNER JOIN Parents_rateAndreview_Teachers prt ON prt.teacher_id = T.teacher_id
    WHERE T.teacher_id = myId;

    SET res = total / cnt;

  END;
DELIMITER ;
-- youmna
-- 1
-- used to create a new school
CREATE PROCEDURE Create_school(
  school_name   VARCHAR(50),
  address       VARCHAR(50),
  general_info  VARCHAR(50),
  vision        VARCHAR(50),
  mission       VARCHAR(50),
  email         VARCHAR(50),
  main_language VARCHAR(50),
  phone_number  VARCHAR(50)
)
  BEGIN
    INSERT INTO Schools
    VALUES (school_name, address, general_info, vision, mission, email, main_language, phone_number);
  END;
DELIMITER ;

-- CALL Create_school('Rowad2','NasrCity','schoolInfo','ourVision','ourMission','Rowad@yahoo.com','English','0123456789');

-- 2
-- used to add courses
CREATE PROCEDURE ADD_Course(

  course_code  INTEGER,
  course_name  VARCHAR(50),
  description  VARCHAR(50),
  course_level VARCHAR(50),
  grade        INTEGER
)
  BEGIN
    INSERT INTO Courses (course_code, name, description)
    VALUES (course_code, course_name, description);
    INSERT INTO Courses_offer_Levels VALUES (grade, course_code, level);
    INSERT INTO courses_has_prerequisite_courses VALUES (course_code1, course_code2);
  END;
DELIMITER ;

#  CALL ADD_Course(10,'Math3','Mathematics course for high school students', 'High',12);
#  CALL ADD_Course(11,'Math3','Mathematics course for high school students', 'High',12);


-- used to add prequiisite courses to other courses
CREATE PROCEDURE ADD_prreRequisites(course_code1 INTEGER, course_code2 INTEGER)
  BEGIN
    INSERT INTO Courses_has_prerequisite_Courses (course_code1, course_code2) VALUES (course_code1, course_code2);
  END;

DELIMITER ;


# CALL Add_Admins('Reem', 'Adel', 'Aboushawareb', 'F', '1996-5-18', 'tagamooo', 'Reem@yahoo.com', 'Reem.AbouShwareb',
#                 'balabizo', 'stFatima', 'international');

-- 4
-- used to delete schools
CREATE PROCEDURE Delete_school(school_name VARCHAR(50))
  BEGIN
    DELETE FROM Schools
    WHERE name = school_name;

  END;

-- As a parent

-- 1
-- used by parents to sign up on the site
CREATE PROCEDURE Sign_Up(f_name VARCHAR(50), l_name VARCHAR(50), email VARCHAR(50), address VARCHAR(50),
                         home_phone_number VARCHAR(50), parent_user_name VARCHAR(50), parent_password VARCHAR(50))
  BEGIN

    INSERT INTO Parents (user_name, password, first_name, last_name, home_phone, email) VALUES
      (parent_user_name, parent_password, f_name, l_name, home_phone_number, email);

  END;
CALL Sign_Up('hady','mohamed','hadyyasser','wara','011148','hadyz1100','keytroni');
CREATE PROCEDURE parents_mobileNumbers(mobileNumber VARCHAR(50))
  BEGIN
    INSERT INTO Parents  (home_phone) VALUES (mobileNumber);

  END;
DELIMITER ;

-- 2
-- used by parent to apply for their child in a school
CREATE PROCEDURE Apply_for_child(school_name VARCHAR(50), ssn INTEGER, name VARCHAR(50), birth_date DATETIME,
                                 gender  VARCHAR(1),parent_user_name VARCHAR(50))
  BEGIN
    INSERT INTO Students (ssn, user_name, gender, name, birth_date)
    VALUES (ssn, name, gender,name, birth_date);


    INSERT INTO Parents_apply_Students_Schools ( parent_user_name, student_ssn, school_name) VALUES (parent_user_name,ssn ,school_name);

  END;

# INSERT INTO Parents (user_name, password, first_name, last_name, home_phone, email, address)
# VALUES ('Mahmoud.Abdelaziz','balabizo','Mahmoud','Abdelaziz','0232424244','Mahmoud@gmail.com','NasrCity');

# CALL Apply_for_child('Elmasrya',7,'Laila','2001-2-3','F','Mahmoud.Abdelaziz');

-- 3


# CALL listOfSchools_Accepted(3);

-- 4


-- views list of schools in which student is accepted
CREATE PROCEDURE listOfSchools_Accepted(IN student_ssn INTEGER)
  BEGIN

    SELECT p.school_name
    FROM Parents_apply_Students_Schools p
    WHERE p.accept = 1 AND p.student_ssn = student_ssn
    GROUP BY p.student_ssn;

  END;
DELIMITER ;



# CALL listOfSchools_Accepted(3);

-- 5
-- used by parent to enroll their student in a shcool
CREATE PROCEDURE enroll_MyStudent(student_ssn INTEGER, school_name VARCHAR(50), school_address VARCHAR(50))
  BEGIN
    INSERT INTO Students_Enrolled_Schools (ssn, school_name, school_address) VALUES
      (student_ssn, school_name, school_address);

  END;
DELIMITER ;
# DROP PROCEDURE listOfReports;
-- views
CREATE PROCEDURE listOfReports(parent_user_name VARCHAR(50))
  BEGIN
    SELECT content
    FROM Reports  WHERE user_name =parent_user_name
    GROUP BY student_ssn;
  END;
DELIMITER ;

 -- INSERT INTO Reports( comment, content, issue_date,  user_name, student_ssn, teacher_id,report_number)
    -- VALUES ('you are Good','Wohooo','2016-11-23','Nader.Elghandoor',2,3,1);

# CALL listOfReports('Nader.Elghandoor');

-- 6
-- used to reply to reports
CREATE PROCEDURE ReplyToReports(content VARCHAR(50), user_name VARCHAR(50), teacher_id INTEGER)
  BEGIN

    INSERT INTO Parents_reply_Reports VALUES (content, user_name, teacher_id);
  END;
DELIMITER ;

-- 7
-- used by parents to view their children school list
CREATE PROCEDURE MyChildrenSchoolList(parent_user_name VARCHAR(50))
  BEGIN
    SELECT p.school_name
    FROM students s INNER JOIN Parents_apply_Students_Schools p ON parent_user_name = p.parent_user_name
    GROUP BY p.school_name;
  END;
DELIMITER ;
# CALL MyChildrenSchoolList('Nader.elghandoor');
SELECT *
FROM Parents;
-- 8
-- views announcements made int he past 10 days
CREATE PROCEDURE Announcements_inPastDays(student_ssn INTEGER)

  BEGIN
     DECLARE x_interval INT DEFAULT 0;
      set x_interval =(CURDATE()-INTERVAL 10 DAY );
    SELECT x_interval ;
    SELECT A.description
      FROM Announcements A
  WHERE A.date  >= DATE(NOW()) - INTERVAL 10 DAY AND
    exists( SELECT s.ssn
    FROM Students_Enrolled_Schools s
    WHERE  s.ssn =student_ssn);
  END;
DELIMITER ;


# INSERT INTO Announcements (date, title, description, employee_id)
# VALUES ('2016-11-22','MileStone PostPoned :) ','you all got the full mark',2);

# CALL Announcements_inPastDays;


-- 9
-- used by parent to rate their student's teacher
CREATE PROCEDURE Rate_MyChildren_Teachers2(rate INTEGER, t_id INTEGER, parent_user_name VARCHAR(50))
  BEGIN
    INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES (rate, t_id, parent_user_name);
  END;
DELIMITER ;

-- views parent children in schools
CREATE PROCEDURE getMychild_ssn(IN parent_user_name VARCHAR(50), OUT student_ssn INTEGER)
  BEGIN
    SELECT s.ssn
    FROM Parents
      INNER JOIN Students s ON parent_user_name = s.user_name
    INTO student_ssn;
  END;
DELIMITER ;

-- 11
-- deletes parent review
CREATE PROCEDURE DeleteReview(parent_user_name VARCHAR(50), parent_rate_number INTEGER)
  BEGIN

    DELETE FROM Parents_rateAndreview_Schools
    WHERE parent_username = parent_user_name AND
          rate = parent_rate_number;
  END;
DELIMITER ;

# CALL DELETEReview('Nader.elghandoor', 1);


# CALL topSchools();

-- 14
-- views inernational school with highest reputation
CREATE PROCEDURE InternationalSchool_highestReputation()
  BEGIN
    DECLARE international INT DEFAULT 0;
    DECLARE national INT DEFAULT 0;
    SELECT count(review)
    FROM Parents_rateAndreview_Schools s INNER JOIN Types t
        ON s.school_name = t.school_name AND t.type = 'national'
    INTO national;

    SELECT count(review)
    FROM Parents_rateAndreview_Schools s INNER JOIN Types t
        ON s.school_name = t.school_name AND t.type = 'internationl'
    INTO international;

    IF (national > international)
    THEN
      SELECT s.name
      FROM Schools s INNER JOIN Types t
          ON s.name = t.school_name AND t.type = 'national';
    ELSE SELECT s.name
         FROM Schools s INNER JOIN Types t
             ON s.name = t.school_name AND t.type = 'international';
    END IF;
  END;
DELIMITER ;

# CALL InternationalSchool_highestReputation();


-- helper procedures and View Procedures
CREATE PROCEDURE view_students_I_Teach(teacher_id INTEGER)
  BEGIN
    SELECT t.ssn
    FROM Students_taughtBy_Teachers t
    WHERE t.teacher_id = teacher_id;

  END;
DELIMITER ;

# CALL view_students_I_Teach(3);

-- reem
-- used by students to update their data
create PROCEDURE student_update_his_data
    (student_ssn int ,gender varchar(50),
    student_name varchar(50),
    birth_date datetime,
    password varchar(50),
    sch_name VARCHAR(50),
    sch_address VARCHAR(50))
BEGIN

    Update  Students SET Students.gender=gender,
      Students.name=student_name,
      Students.birth_date=birth_date,
      Students.password=password,
      Students.school_address=sch_address,
      Students.school_name=sch_name

    where Students.ssn = student_ssn;
 END;
-- used by students to view courses
create procedure student_view_courses(user VARCHAR(50),pass VARCHAR(50))
  begin
      select distinct  c.name
      from  Courses c inner join Courses_offer_Levels col
      on   c.course_code=col.course_code
         where col.grade= (
           select s.grade
       from Students s
        where s.user_name=user  and s.password=pass and s.school_address=c.school_address and s.school_name=c.school_name
           );
  END;
-- use dby students to post questions
CREATE PROCEDURE student_post_question(user varchar(50),pass varchar(50),question VARCHAR(50)
  ,courseCode int)
begin
    declare student_ssn int;
  declare x int;

  select   s.ssn into student_ssn
      from Students s
       where s.user_name=user and s.password=pass ;

INSERT into Questions (course_code,ssn,content)
          values(courseCode,student_ssn,question);
  END;
-- views all questions
create procedure view_all_questions(user VARCHAR(50),pass varchar(50),courseCode VARCHAR(50))
  BEGIN
declare z VARCHAR(50);
    declare y VARCHAR(50);
    select s.school_address into y
    from Students s
    where s.user_name=user and s.password=pass;

    select s.school_name into z
    from Students s
    where s.user_name=user and s.password=pass;

   select q.content
     from Questions q inner join Courses c
   on q.course_code=c.course_code
  where c.school_name=z and c.school_address=y and c.course_code=courseCode;

  END;
-- used by students to post assignment solution
CREATE PROCEDURE  student_solve_assignment( user varchar(50),pass varchar(50),solution varchar(50),assignmentNumber  int)
   BEGIN
     declare x int;
     select s.ssn into x
       from Students s
         where s.user_name=user and s.password= pass;
 insert into Assignments_solve_Students (solution, student_ssn, assignment_number)
     values(solution,x,assignmentNumber);

END;
-- used by student to view grade
CREATE PROCEDURE student_view_grade(user varchar(50),pass varchar(50),assignment_number int,courseCode int)
 BEGIN
    declare ssn_student int;
   select s.ssn into ssn_student
   from Students  s
    where s.user_name=user and s.password=pass;

     select a.grade
       from Assignments_solve_Students a inner join Assignments_posteBy_Teachers ass
     on ass.assignment_number=a.assignment_number
   where a.student_ssn= ssn_student and ass.course_code-courseCode;

 end;
-- used by students to view announcements
create PROCEDURE student_view_announcement(user VARCHAR(50),pass int)
      BEGIN
        declare x VARCHAR(50);
        declare y VARCHAR(50);
         select s.school_name into x
           from Students s
           where s.user_name=user and s.password=pass;
         select s.school_address into y
           from Students s
           where s.user_name=user and s.password=pass;
           select a.*
          from Announcements a inner join Employees e
            on a.employee_id=e.employee_ID
        where a.date >=(a.date -10)  and e.school_name=x and e.school_address=y;
      END;
DROP  PROCEDURE view_announcement;
create PROCEDURE view_announcement(x VARCHAR(50),y VARCHAR(50))
      BEGIN
           select a.*
          from Announcements a inner join Employees e
            on a.employee_id=e.employee_ID
        where a.date >=(a.date -10)  and e.school_name=x and e.school_address=y;
      END;
CALL view_announcement('saintfatima','66 Ismail Al Kabbani');
SELECT *
FROM Employees;
-- used by student to view activity
 create procedure student_view_activities(user VARCHAR(50),pass VARCHAR(50) )
  begin
    declare x VARCHAR(50);
    declare y varchar(50);
    select s.school_name into x
      from Students s
    where s.user_name=user and s.password=pass;
    select s.school_address into y
      from Students s
    where s.user_name=user and s.password=pass;

    select a.activity_Date,a.location,a.description,a.equipment,a.name,a.type,e1.first_name,e1.middle_name,e1.last_name,t.experience_year,t.seniority_level
      from School_Activities a inner join Employees e1
    on e1.employee_ID=a.teacher_id
    inner join Employees e2
    on e2.employee_ID=a.adminstrator_id
    inner join Teachers t
          on t.teacher_id=e1.employee_ID
    where e1.school_address=y and e1.school_name=x and e2.school_address=y and e2.school_name=x;
  END;

-- used to apply for activity
create procedure students_applyForActivites(user varchar(50),pass VARCHAR(50),date DATETIME,activity_location VARCHAR(50))
  begin
    declare x int ;
    declare Y VARCHAR(50);
  select s.ssn into x
    from Students s
    where s.user_name=user and  s.password=pass;
   select s.type into y
     from School_Activities s
     where s.activity_Date=date and s.location=activity_location;
    if x not IN (
     select sa.student_ssn
       from Activities_Involve_Students sa inner join  School_Activities s
         on s.activity_Date=sa.activity_Date and s.location=sa.location
       where sa.activity_Date=date and   s.type=y)
      THEN
      insert into Activities_Involve_Students values (x,date,activity_location);

     else select
            'You Can`t apply for this activity Please choose Another one or this one in another time';
    END IF;

  END;
-- used by students to view assignments
 CREATE PROCEDURE student_view_assignments(user VARCHAR(50),pass VARCHAR(50))
  begin
  declare x int;
    declare y varchar(50);
    declare z VARCHAR(50);
      select s.grade into x
      From Students s
     where s.user_name=user and s.password=pass;

    select s.school_address into y
    from Students s
    where s.user_name=user and s.password=pass;

    select s.school_name into z
    from Students s
    where s.user_name=user and s.password=pass;

     select  a.*
      from Assignments_posteBy_Teachers t inner join Courses_offer_Levels c
        on t.course_code=c.course_code
     inner join Assignments a
    on a.assignment_number=t.assignment_number
        inner join Employees e
        on e.employee_ID=t.teacher_id
    WHERE e.school_address=y and e.school_name=z and c.grade=x;
  END;


-- used to make student join a club
CREATE PROCEDURE student_join_clubs (user varchar(50),pass varchar(50),clubName VARCHAR(50))
   BEGIN
    declare student_grade int;
    declare student_ssn int;
     declare x VARCHAR(50);
     DECLARE y VARCHAR(50);
      DECLARE z VARCHAR(50);
     select s.school_name into x
     from Students  s
     where s.user_name=user and  s.password=pass;

     select s.school_address into y
   from Students  s
    where s.user_name=user and  s.password=pass;

   select s.grade into student_grade
   from Students  s
    where s.user_name=user and  s.password=pass;


     select c.club_name into z
       from Clubs c
       where c.school_address=y and c.school_name=x and c.club_name=clubName;
    if(student_grade >=10 and z is not null)
       then
         select s.ssn into student_ssn
           from Students s
           where s.user_name=user and  s.password=pass;

     insert into Clubs_joinBy_Students(club_name,ssn)
    values(clubName,student_ssn);
      ELSE
        select 'Sorry You can`t join that club';
   END IF;

  end;



-- lets student seachf or course
 create PROCEDURE student_searchFor_courses(user VARCHAR(50),pass VARCHAR(50),course_name VARCHAR(50),code int)
   BEGIN
 if (course_name is not  null)
  then

    select c.description,c.course_code,c.name
         from Courses_offer_Levels cl inner join Students s
           on s.grade =cl.grade
         inner join Courses c
           on c.school_name=s.school_name and  c.school_address=s.school_address and c.course_code=cl.course_code
where c.name= course_name and s.user_name=user and  s.password=pass ;


elseif (code is not null)
  then

    select c.name,c.description,c.course_code
          from Students s inner join Courses_offer_Levels cl
            on cl.grade=s.grade
      inner join Courses c
            on c.school_address=s.school_address and c.school_name=s.school_name and c.course_code=cl.course_code
           where c.course_code= code and  s.user_name=user and  s.password=pass ;
   else SELECT 'You should enter course name or code';
END IF;
   END;
-- used by teacher to sign up in school
create procedure teacher_sign_up ( fname varchar(50) , mname varchar(50) , lname varchar(50), gend varchar(1) , birth_date datetime
  , mail varchar(50) , address varchar(50)   , seniority int , first_year_of_work DATETIME )
  BEGIN
    # teacher_id , seniority_level , first_year_of_work
    DECLARE x int;
    insert into  Employees (first_name,middle_name,last_name,email,gender,e_address,birth_date) values(fname,mname,lname, mail ,gend
      , address,birth_date );
    SELECT count(employee_ID) into x
      FROM Employees;


    INSERT into teachers ( teacher_id ,seniority_level, first_year_of_work ) VALUES (x , seniority , first_year_of_work );
  END;

###
# INSERT INTO Schools (name, address, general_info, vision, mission, email, main_language, phone_number) VALUES ('name', 'address', 'general_info' , 'vision', 'mission', 'email', 'main_language'
# , 'phone_number');
# INSERT INTO Employees ( username , username_password , first_name , middle_name , last_name , gender , salary , email , e_address , birth_date ,
# school_name , school_address) values ('mar' , 'bal' , 'mariem' , 'adel' , 'abou' , 'f' , 4500 , 'fjdk' , 'fdsjk' , '1989-12-12' , 'name' , 'address'  );
# CALL teacher_sign_up( 'mariem' , 'adel' , 'aboushawareb' , 'f' , '1198-02-02' , 'fdsfds' , 'fdsf' , 5 , '1998-02-05' );

# drop table employees;
# drop table teachers;
##ended testing

#
# SELECT *
# FROM Courses;
# drop PROCEDURE teachers_views_courses_taught_by_him;
# call teachers_views_courses_taught_by_him('mar1' , 'bal1' );
# SELECT *
#   from Courses;
# SELECT *
#   FROM Courses_offer_Levels;
# testing

-- used by teacher to view the courses he teaches
DELIMITER //
create procedure teachers_views_courses_taught_by_him (teacherUserName VARCHAR(50) , password varchar(50) )

BEGIN

declare t_ID INT;
SELECT  employee_ID into t_ID
from Employees
  WHERE Employees.username = teacherUserName AND Employees.username_password = password;
  SELECT DISTINCT Courses.course_code,  name   , grade , level
  from Courses
    INNER JOIN Students_taughtBy_Teachers  ON Courses.course_code = Students_taughtBy_Teachers.course_code AND
              Students_taughtBy_Teachers.teacher_id = t_ID
    INNER JOIN Courses_offer_Levels  ON Courses.course_code = Courses_offer_Levels.course_code
  ORDER BY Courses_offer_Levels.grade;
END;

DELIMITER //


##testing
# INSERT INTO Schools (name, address, general_info, vision, mission, email, main_language, phone_number) VALUES ('name1', 'address1', 'general_info' , 'vision', 'mission', 'email', 'main_language'
# , 'phone_number');
# INSERT INTO Schools (name, address, general_info, vision, mission, email, main_language, phone_number) VALUES ('name2', 'address2', 'general_info' , 'vision', 'mission', 'email', 'main_language'
# , 'phone_number');
# INSERT INTO Employees ( username , username_password , first_name , middle_name , last_name , gender , salary , email , e_address , birth_date ,
# school_name , school_address) values ('mar1' , 'bal1' , 'mariem1' , 'adel1' , 'abou1' , 'f' , 45001 , 'fjdk1' , 'fdsjk1' , '1989-12-10' , 'name1' , 'address1'  );

# INSERT INTO Teachers (seniority_level, first_year_of_work, teacher_id) VALUES ( 5 , '1888-12-12' , 2 );
# INSERT INTO Courses (course_code, name, description, school_address,schoole_name)
# VALUES (101 , 'course name' , 'description' , 'school address',
# 'school name');
# INSERT INTO Courses (course_code, name, description, school_address,schoole_name)
# VALUES (303 , 'course name' , 'description' , 'school address',
# 'school name');
# INSERT INTO Courses (course_code, name, description, school_address,schoole_name)
# VALUES (404 , 'course name' , 'description' , 'school address',
# 'school name');
# INSERT INTO Courses (course_code, name, description, school_address,schoole_name)
# VALUES (505 , 'course name' , 'description' , 'school address',
# 'school name');


# INSERT INTO Courses (course_code, name, description, school_address, school_name) VALUES (202 , 'course name' , 'description' , 'school address' ,'school name');
# INSERT INTO Students_taughtBy_Teachers (course_code , teacher_id ) VALUES ( 101 , 2);
# INSERT INTO Students_taughtBy_Teachers (course_code , teacher_id ) VALUES ( 202 , 2);
# INSERT INTO Students_taughtBy_Teachers (course_code , teacher_id ) VALUES ( 303 , 2);
# INSERT INTO Students_taughtBy_Teachers (course_code , teacher_id ) VALUES ( 404 , 2);
# INSERT INTO Students_taughtBy_Teachers (course_code , teacher_id ) VALUES ( 505 , 2);
# INSERT INTO Courses_offer_Levels (grade, level, course_code) VALUES (5 , 'primary' , 101);
# INSERT INTO Courses_offer_Levels (grade, level, course_code) VALUES (5 , 'highschool' , 202);
# INSERT INTO Courses_offer_Levels (grade, level, course_code) VALUES (5 , 'highschool' , 303);
# INSERT INTO Courses_offer_Levels (grade, level, course_code) VALUES (6 , 'highschool' , 505);

##testing

-- use dby teacher to write reports
create PROCEDURE teachers_writes_reports (user VARCHAR(50),pass VARCHAR(50),ssn int, teacher_comment VARCHAR(50),
  report_content VARCHAR(50) , report_issue_date DATETIME  )
BEGIN
  declare x int ;
select t.employee_ID into x
  from Employees t
  where t.username=user and t.username_password=pass;
  insert into Reports (comment, content, issue_date, student_ssn, teacher_id)
    values (teacher_comment,report_content,report_issue_date,ssn,x);
END;



##testing

#
# CALL teachers_writes_reports( 'mar1' , 'bal1' , 1, 'excellent' , 'content' , '1998-12-12'  );
# CALL teachers_writes_reports( 'mar1' , 'bal1' , 2, 'excellent' , 'content' , '1998-12-12'  );
# SELECT *
# FROM Reports;

##testing
-- shows student who is in the most number of clubs
create PROCEDURE member_in_most_clubs (username VARCHAR(50) , password VARCHAR(50))
  BEGIN
  DECLARE s_name VARCHAR(50) ;
  DECLARE  ssn_count INTEGER;


  select s.ssn,s.name,count(c.club_name)
    from Students s inner join Clubs_joinBy_Students c
      on s.ssn=c.ssn
    GROUP BY s.ssn;

END;


-- 13
-- veiws top 10 most  rated schools
CREATE PROCEDURE topSchools()
  BEGIN
    DECLARE reviews_num INT DEFAULT 0;
    DECLARE students_num INT DEFAULT 0;

    SELECT count(review)
    FROM Parents_rateAndreview_Schools
    INTO reviews_num;
    SELECT count(ssn)
    FROM Students_Enrolled_Schools
    INTO students_num;
    IF (students_num > reviews_num)
    THEN
      SELECT school_name
      FROM Students_Enrolled_Schools
      ORDER BY students_num DESC  LIMIT 10;
    ELSE

      SELECT pr.school_name
      FROM Parents_rateAndreview_Schools pr
      WHERE pr.school_name NOT IN (
        SELECT s.school_name
        FROM Parents p INNER JOIN Students s
            ON p.user_name = s.user_name
          INNER JOIN
          Students_Enrolled_Schools st ON st.ssn = s.ssn AND
                                          st.school_name = s.school_name
           LIMIT 10

      );

    END IF;
  END;
DELIMITER ;


-- starte of insertions


#Schools
insert into Schools values('saintfatima','66 Ismail Al Kabbani',
                           'avaliable at the coordinator','A vibrant community striving for excellence.
','To develop inquiring, knowledgeable',
                           'saintfatima@hotmail.com','english','0123456789');
insert into Schools values('El rowad','8 Mostafa El Nahhas',
                           'avaliable at the coordinator','A vibrant community striving for excellence.
','To develop inquiring, knowledgeable',
                           'info@elrowadcollege.net','english','0224723271');

#Employees
insert into Employees (username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
VALUES ('mariem.shwareb','blabizo','mariem','mohamed','shwareb','f','mariem_adel@gmail.com','18 slah salem amarat AlAbor',
                       '1980-10-30 23:59:59','saintfatima','66 Ismail Al Kabbani');

INSERT INTO Employees(first_name, middle_name, last_name, gender, birth_date, e_address, email, username, username_password, school_name, school_address)
VALUES('Farah','Emad','Ahmed','F','1968-10-30 23:59:59','NasrCity','Farah@gmail.com','Farah.Ahmed','balabizo','saintfatima','66 Ismail Al Kabbani');

insert into Employees(username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
VALUES ('Youmna.Ghandor','blabizo','Youmna','Nader','ElGhandor','f','youmna.elghandour@gmail.com','22 abbassia Street',
                       '1970-10-30 23:59:59','saintfatima','66 Ismail Al Kabbani');


insert into Employees(username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
VALUES ('abeer.elsayed','blabizo','Abeer','Mohamed','ElSayed','f','abeer_elsayed@gmail.com','22 helioblies street',
                       '1985-10-30 23:59:59','saintfatima','66 Ismail Al Kabbani');
insert into Employees (username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
VALUES ('ahmed.shwareb','blabizo','ahmed','mohamed','shwareb','m','ahmed44@gmail.com','15 slah salem amarat AlAbor',
                       '1985-10-1 23:59:59','saintfatima','66 Ismail Al Kabbani');
#
# insert into Employees (username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
# VALUES ('mariem.shwareb','blabizo','mariem','mohamed','shwareb','f','mariem_adel@gmail.com','18 slah salem amarat AlAbor',
#                        '1980-10-30 23:59:59','saintfatima','8 Mostafa El Nahhas');

insert into Employees (username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
VALUES ('hady.mohamed','blabizo','hady','mohamed','osman','m','hady_mohamed@gmail.com','10 mahmoud Tawfik nasr city',
                       '1980-10-30 23:59:59','El rowad','8 Mostafa El Nahhas');

INSERT INTO Employees(first_name, middle_name, last_name, gender, birth_date, e_address, email, username, username_password, school_name, school_address)
VALUES('Ghada','Emad','Ahmed','F','1990-10-30 23:59:59','18 ghamra street','ghada13@gmail.com','ghada.ahmed','balabizo','El rowad','8 Mostafa El Nahhas');

insert into Employees(username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
VALUES ('haithem.hany','blabizo','haithem','Nader','hany','m','hithem.nader@gmail.com','24 abbassia Street',
                       '1980-10-30 23:59:59','El rowad','8 Mostafa El Nahhas');

insert into Employees(username, username_password, first_name, middle_name, last_name, gender,  email, e_address, birth_date, school_name, school_address)
VALUES ('saleh.hossam','blabizo','Saleh','Mohamed','Hossam','f','saleh_hossam@gmail.com','25 helioblies street',
                       '1985-10-30 23:59:59','El rowad','8 Mostafa El Nahhas');

#Students

insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
    values (2222,'saintfatima', '66 Ismail Al Kabbani' , 'raouf_mohamed' , 'male' , 'raouf mohamed','2001-12-31 23:59:59' , 'blabizo' );
 insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
    values (1111,'saintfatima', '66 Ismail Al Kabbani' , 'reem.meky' , 'female' , 'reem meky','2001-12-31 23:59:59' , 'blabizo' );
  insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
    values (1222,'saintfatima', '66 Ismail Al Kabbani' , 'raghda_mohamed' , 'female' , 'raghda eslam','2002-12-31 23:59:59' , 'blabizo' );
insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
    values (12822,'saintfatima', '66 Ismail Al Kabbani' , 'rodaina_mohamed' , 'female' , 'rodaina mohamed','2002-5-31 23:59:59' , 'blabizo' );


insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
    values (3333,'El rowad', '8 Mostafa El Nahhas ' , 'mohamed.ahmed' , 'male' , 'mohamed ahmed','2001-5-31 23:59:59' , 'blabizo' );
insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
    values (3355,'El rowad', '8 Mostafa El Nahhas ' , 'hani.seif' , 'male' , 'Hani seif eldien','2001-6-25 23:59:59' , 'blabizo' );
insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
    values (3377,'El rowad', '8 Mostafa El Nahhas ' , 'mohamed.hani' , 'male' , 'mohamed Hani','2002-4-3 23:59:59' , 'blabizo' );
insert into Students (ssn ,school_name,school_address,user_name,gender,name,birth_date,password)
    values (888888,'El rowad', '8 Mostafa El Nahhas ' , null , 'd' , null,null, null );


#Clubs
insert into Clubs
values ('TCS','Teach You How To be a good Actor','66 Ismail Al Kabbani','saintfatima');
insert into Clubs
    values ('IEEE','Take You To Science Life ','66 Ismail Al Kabbani','saintfatima');
insert into Clubs
    values ('Bdaia','Charity','8 Mostafa El Nahhas','El rowad');

insert into Clubs
    values ('Eco Diving','Teach You Diving','8 Mostafa El Nahhas','El rowad');

#Teachers
insert into Teachers(seniority_level, first_year_of_work, teacher_id)
values(3,'1970-10-30 23:59:59',1);

insert into Teachers(seniority_level, first_year_of_work, teacher_id)
values(2,'1990-10-30 23:59:59',2);

insert into Teachers(seniority_level, first_year_of_work, teacher_id)
values(3,'1970-10-30 23:59:59',5);

insert into Teachers(seniority_level, first_year_of_work, teacher_id)
values(3,'1970-10-30 23:59:59',7);


insert into Teachers(seniority_level, first_year_of_work, teacher_id)
values(3,'1970-10-30 23:59:59',9);


insert into Adminstrators values (3);

insert into Adminstrators values (4);

insert into Adminstrators values (7);

insert into Adminstrators values (8);



#Parents
insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
    values('mohamed_adel','blabizo','mohamed','adel',022750654,'mohamed_adel@hotmail.com','10 elmarghani street nasr city');
insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
    values('meky_mohamed','blabizo','meky','mohamed',0227861654,'meky15@hotmail.com','25 mahmoud tawfik');
insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
    values('mohamed_eslam','blabizo','mohamed','eslam',022852654,'mohamed_eslam@hotmail.com','16 abo bakr street abbasia');
insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
    VALUES ('mohamed_ibrahim','blabizo','mohamed','ibrahim',0222654789,'mohamed52@yahoo.com','17 mohamed ali street masr algdeda');

insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
    values('ahmed_abdelrahman','blabizo','ahmed','abdelrahman',022750654,'ahmed_abdelrahman@hotmail.com','20 elmarghani street nasr city');
insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
    values('seif_eldin','blabizo','seif','eldin',0227861654,'seif14@hotmail.com','29 mahmoud tawfik');
insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
    values('hani_mohamed','blabizo','hani','mohamed',022852654,'hani5@hotmail.com','17 abo bakr street abbasia');
insert into Parents(user_name, password, first_name, last_name, home_phone, email, address)
    VALUES ('hani_amir','blabizo','hani','amir',0222654789,'hani_amir@yahoo.com','20 mohamed ali street masr algdeda');
#Reports

insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
    values('He is good but need to practice','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','mohamed_adel',2222,1);
insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
    values('He is good but need to practice','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','meky_mohamed',1111,1);

insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
    values('Your son need to work harder','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','mohamed_adel',2222,2);
insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
    values('Your son need to work harder','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','meky_mohamed',1111,2);

insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
    values('He is good but needs to practice','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','mohamed_eslam',1222,1);
insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
    values('He is good but needs to practice','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','mohamed_ibrahim',12822,1);

insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
    values('your grades is the worst','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','mohamed_eslam',1222,2);
insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
    values('Your son need to work harder','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','mohamed_ibrahim',12822,2);



insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
    values('he is fine','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','ahmed_abdelrahman',3333,9);

insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
    values('Your son need to work harder','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','seif_eldin',3355,9);
insert into Reports(comment, content, issue_date, reply, user_name, student_ssn, teacher_id)
    values('he is good','please check his grades','2016-11-30 11:59:59','ok thanks for your efforts','hani_mohamed',3377,9);



insert into Courses (name, description, school_address, school_name)
values('Math1','Math Techniques','66 Ismail Al Kabbani','saintfatima');
insert into Courses (name, description, school_address, school_name)
values('Enlish1','learn how to describe yoursef in english','66 Ismail Al Kabbani','saintfatima');
insert into Courses (name, description, school_address, school_name)
    values('Arabic1','learn how to describe yoursef in arabic','66 Ismail Al Kabbani','saintfatima');

insert into Courses  (name, description, school_address, school_name)
    values('Math2','Math Techniques','66 Ismail Al Kabbani','saintfatima');
insert into Courses  (name, description, school_address, school_name)
    values('English2','learn how to describe yoursef in english','66 Ismail Al Kabbani','saintfatima');
insert into Courses  (name, description, school_address, school_name)
    values('Arabic2','learn how to describe yoursef in arabic','66 Ismail Al Kabbani','saintfatima');



insert into Courses(name, description, school_address, school_name)
values('Math1','Math Techniques','8 Mostafa El Nahhas','El rowad');

insert into Courses(name, description, school_address, school_name)
values('Enlish','learn how to describe yoursef in english','66 Ismail Al Kabbani','saintfatima');
insert into Courses(name, description, school_address, school_name)
    values('Arabic','learn how to describe yoursef in arabic','8 Mostafa El Nahhas','El rowad');

insert into Courses  (name, description, school_address, school_name)
    values('Math2','Math Techniques','66 Ismail Al Kabbani','saintfatima');
insert into Courses  (name, description, school_address, school_name)
    values('English2','learn how to describe yoursef in english','8 Mostafa El Nahhas','El rowad');
insert into Courses  (name, description, school_address, school_name)
    values('Arabic2','learn how to describe yoursef in arabic','8 Mostafa El Nahhas','El rowad');

#Levels

insert into Levels values('1st level');
insert into Levels values('2nd level');
insert into Levels values('3rd level');

#Levels_offer_Schools
SELECT *
FROM Levels_offer_Schools;
insert into Levels_offer_Schools values ('1st level','saintfatima','66 Ismail Al Kabbani');
insert into Levels_offer_Schools values ('2nd level','saintfatima','66 Ismail Al Kabbani');
insert into Levels_offer_Schools values ('3rd level','saintfatima','66 Ismail Al Kabbani');

insert into Levels_offer_Schools values ('1st level','El rowad','8 Mostafa El Nahhas');
call viewSchoolsByLevel();

SELECT *
FROM Schools;
CALL viewSchoolsByLevel();
#Courses_offer_Levels

insert into Courses_offer_Levels
    values (9,'2nd level',1);
insert into Courses_offer_Levels
    values (9,'2nd level',2);
insert into Courses_offer_Levels
    values (9,'2nd level',3);
insert into Courses_offer_Levels
    values (10,'3rd level',4);
insert into Courses_offer_Levels
    values (10,'3rd level',5);
insert into Courses_offer_Levels
    values (10,'3rd level',6);




#School_Activities;

insert into School_Activities(activity_Date, location, teacher_id, adminstrator_id, name, equipment, type, description)
    values('2016-11-30 15:00:00','c5201',1,3,'how to draw','sketches and colors','drawing','teach you how to draw from skratch');
 insert into School_Activities(activity_Date, location, teacher_id, adminstrator_id, name, equipment, type, description)
    values('2016-11-30 15:00:00','c6201',2,4,'geometric paints','sketches and colors','drawing','teach you how to draw from geometric paints');



#Assignments
insert into Assignments (posting_date, due_date, content)
    values('2016-10-20 23:59:59','2016-10-30 23:59:59','Solve The First two page of the workbook');

insert into Assignments (posting_date, due_date, content)
    values('2016-10-20 23:59:59','2016-10-30 23:59:59','make a research about  egypt');

insert into Assignments (posting_date, due_date, content)
    values('2016-10-20 23:59:59','2016-10-30 23:59:59','solve the first three page of the workbook');



insert into Assignments (posting_date, due_date, content)
    values('2016-10-20 23:59:59','2016-10-30 23:59:59','Solve The First two page of the workbook');

insert into Assignments (posting_date, due_date, content)
    values('2016-10-20 23:59:59','2016-10-30 23:59:59','make a research about  world war 2');

insert into Assignments (posting_date, due_date, content)
    values('2016-10-20 23:59:59','2016-10-30 23:59:59','solve the first three page of the workbook');

#Assignments_posteBy_Teachers
insert into Assignments_posteBy_Teachers(teacher_id, course_code,assignment_number)
    values (1,1,1);
insert into Assignments_posteBy_Teachers(teacher_id, course_code,assignment_number)
    values (2,2,2);
insert into Assignments_posteBy_Teachers(teacher_id, course_code,assignment_number)
    values (5,3,3);
insert into Assignments_posteBy_Teachers(teacher_id, course_code,assignment_number)
    values (1,4,4);

insert into Assignments_posteBy_Teachers(teacher_id, course_code,assignment_number)
    values (2,5,5);
insert into Assignments_posteBy_Teachers(teacher_id, course_code,assignment_number)
    values (5,6,6);



#Students_taughtBy_Teachers

insert into Students_taughtBy_Teachers
    values(1111,4,1);
 insert into Students_taughtBy_Teachers
    values(2222,4,1);
insert into Students_taughtBy_Teachers
    values(1111,5,2);
insert into Students_taughtBy_Teachers
    values(2222,5,2);
insert into Students_taughtBy_Teachers
    values(1111,6,5);
insert into Students_taughtBy_Teachers
    values(2222,6,5);

insert into Students_taughtBy_Teachers
    values(1222,1,1);

insert into Students_taughtBy_Teachers
    values(1222,2,2);

insert into Students_taughtBy_Teachers
    values(1222,3,5);


insert into Students_taughtBy_Teachers
    values(3377,8,9);



insert into Students_taughtBy_Teachers
    values(3333,11,9);
insert into Students_taughtBy_Teachers
    values(3355,11,9);

#Types

INSERT INTO Types VALUES ('international', 'saintfatima', '66 Ismail Al Kabbani');
INSERT INTO Types VALUES ('national', 'El rowad', '8 Mostafa El Nahhas');



#Parents_apply_Students_Schools

INSERT INTO Parents_apply_Students_Schools (accept, parent_user_name, student_ssn, school_name)
VALUES (1, 'mohamed_eslam', 1111, 'El rowad');

INSERT INTO Parents_apply_Students_Schools (accept, parent_user_name, student_ssn, school_name)
VALUES (1, 'meky_mohamed', 2222, 'saintfatima');


INSERT INTO Parents_apply_Students_Schools (accept, parent_user_name, student_ssn, school_name)
VALUES (1, 'mohamed_adel', 1111, 'saintfatima');



# Teachers_supervises_Teachers

INSERT INTO  Teachers_supervises_Teachers(teacher_id1, teacher_id2)
values(1,2);

INSERT INTO  Teachers_supervises_Teachers(teacher_id1, teacher_id2)
values(1,5);

-- # High_Schools

INSERT INTO High_Schools(name, address)
 VALUES ('saintfatima','66 Ismail Al Kabbani');

INSERT INTO High_Schools(name, address)
 VALUES ('El rowad','8 Mostafa El Nahhas');


-- # Mid_Schools

INSERT INTO Mid_Schools(name, address)
 VALUES ('saintfatima','66 Ismail Al Kabbani');

INSERT INTO Mid_Schools(name, address)
 VALUES ('El rowad','8 Mostafa El Nahhas');

-- # Elementary_Schools

INSERT INTO Elementary_Schools(name, address)
 VALUES ('saintfatima','66 Ismail Al Kabbani');

INSERT INTO Elementary_Schools(name, address)
 VALUES ('El rowad','8 Mostafa El Nahhas');

-- #Required_Supplies

INSERT into Required_Supplies(required_supplies, name, address) VALUES
('Backpack and Lunchbag','saintfatima','66 Ismail Al Kabbani') ;

INSERT into Required_Supplies(required_supplies, name, address) VALUES
('Eraser and Pencils','El rowad','8 Mostafa El Nahhas') ;


-- # Announcements
INSERT INTO Announcements (date, title, description, employee_id)
VALUES ('2016-11-22','English H.W','The H.W is Cancelled',4);

INSERT INTO Announcements (date, title, description, employee_id)
VALUES ('2016-11-22','Math Grade','All GOT THE Full  Mark',3);

-- # Assignments_solve_Students

INSERT INTO Assignments_solve_Students (solution, student_ssn, assignment_number)
VALUES ('2*2=4', 1111, 1);


INSERT INTO Assignments_solve_Students (solution, student_ssn, assignment_number)
VALUES ('2*3=6', 1222, 1);

-- # Activities_Involve_Students

INSERT INTO  Activities_Involve_Students  (student_ssn, activity_Date, location)
VALUES (1111,'2016-11-30 15:00:00','c5201');
SELECT * from School_Activities;


INSERT INTO  Activities_Involve_Students  (student_ssn, activity_Date, location)
VALUES (2222,'2016-11-30 15:00:00','c6201');

-- #Mobile_numbers

INSERT INTO Mobile_numbers(mobile, parent_user_name) VALUES
  ('0123749886','mohamed_adel');


INSERT INTO Mobile_numbers(mobile, parent_user_name) VALUES
  ('0123749886','meky_mohamed');

-- # Parents_rateAndreview_Teachers

INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES
  (4, 2, 'meky_mohamed');

INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES
  (4, 2, 'mohamed_adel');

-- # Courses_has_prerequisite_Courses

 INSERT INTO Courses_has_prerequisite_Courses (course_code1, course_code2) VALUES (4, 1);

 INSERT INTO Courses_has_prerequisite_Courses (course_code1, course_code2) VALUES (5, 2);

 INSERT INTO Courses_has_prerequisite_Courses (course_code1, course_code2) VALUES (6, 3);

-- # Parents_rateAndreview_Teachers

INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES (5, 2, 'ahmed_abdelrahman');


INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES (3, 5, 'mohamed_eslam');

INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES (4, 1, 'seif_eldin');

INSERT INTO Parents_rateAndreview_Teachers (rate, teacher_id, parent) VALUES (2, 1, 'meky_mohamed');



-- start of calling procedures

CALL searchForSchoolName('saintfatima');
CALL searchForSchoolAddress('66 Ismail Al Kabbani');
CALL searchForSchoolType('international');
call searchForSchoolType('national');

CALL viewSchoolsByLevel();

CALL viewSchoolAndRating('saintfatima','66 Ismail Al Kabbani');

INSERT INTO Employees (school_name,school_address) VALUES ('El rowad','8 Mostafa El Nahhas');
SELECT *
FROM Teachers;

INSERT INTO Teachers(teacher_id) VALUES (25);
 CALL View_Unverified_Teachers('medo1100','keytronic');
SELECT *
FROM Adminstrators;
CALL Verify_Teacher('Youmna.Ghandor','blabizo',21,'hadyz1100','key');
# INSERT into Adminstrators(adminstrator_id);
SELECT * from Employees;
SELECT * from Students;
CALL View_Unverified_Students('ghada.ahmed','balabizo');
SELECT *
FROM Students;
SELECT *
FROM Adminstrators;
CALL Verify_Student(1111,'dodo','medo');
SELECT *
FROM Students;
CALL addAdmin('Youmna.Ghdandor','blabizo', 'hand', 'yasser', 'man', 'm', 'handman23@gmail.com', '22 faisal st', '1996-04-04',
              'Youmna.Ghandor','blabizo','national');

CALL View_Employees('Youmna.Ghandor','blabizo');
CALL deleteEmployeesFromSystem(10);

CALL updateGeneralInfo('stuf','Youmna.Ghandor','blabizo');

CALL updateVision('no','Youmna.Ghandor','blabizo');

CALL updateAddress('666 Ismail Al Kabbanii','ghada.ahmed','balabizo');
SELECT *
FROM Schools;
CALL updateEmail('keys','Youmna.Ghandor','blabizo');

CALL updateMission('mis','Youmna.Ghandor','blabizo');

CALL updateMainLangauge('oran','Youmna.Ghandor','blabizo');
SELECT *
FROM Schools;

CALL updatePhoneNumber('55666','Youmna.Ghandor','blabizo');

CALL createAnnouncement('2004-04-04','sport','try',3,'blabizo');

CALL createActivity('2004-04-04','GUC',3,'name','dd','ddd','sports');

CALL assignTeacherToActivity('2004-04-04','GUC',1,3);
# INSERT INTO Students (ssn,school_address,school_name) VALUES (1111,);
SELECT *
FROM Courses;
CALL Assign_Teacher_to_Course(3,1,1);

CALL assignTeacherToSuperViseTeacher(1,2,3);
CALL acceptOrRejectStudents(3,1111,1);
SELECT *
FROM Students;
SELECT *
FROM Questions;
INSERT INTO Questions (question, course_code, ssn, content) VALUES ('what ?',1,1111,'no?');
INSERT INTO Students_taughtBy_Teachers(ssn, course_code, teacher_id) VALUES (1111,1,9);
CALL Teacher_View_Questions(9);

CALL Teacher_Answers_Questions (9,2,'yes');
SELECT * from Students_taughtBy_Teachers;
-- insert in grades
CALL View_Students(9);
CALL Students_Without_Activity(3);
SELECT *
FROM Parents_rateAndreview_Teachers;
UPDATE Parents_rateAndreview_Teachers
SET rating = 5;
SELECT * FROM Employees;
-- reutrn float rating if appropriate value inserted
CALL Rate_Teacher_Avg(@out_value,2);
SELECT @out_value;


use test3;
SELECT *
FROM Employees;


create table Busses(
  bus_number int PRIMARY KEY AUTO_INCREMENT,
  route VARCHAR(200),
  model VARCHAR(200),
  capacity int,
  school_name VARCHAR(50),
  school_address VARCHAR(50),
  FOREIGN KEY (school_name,school_address) REFERENCES Schools(name,address) on DELETE CASCADE on UPDATE  CASCADE
);


CREATE TABLE Busses_Offeredby_Schools(
  ssn int,
  bus_number int,
  school_name VARCHAR(50),
  school_address VARCHAR(50),
  cnt int,
  PRIMARY KEY (ssn,bus_number,school_name,school_address),
  FOREIGN KEY (ssn) REFERENCES Students(ssn) on DELETE CASCADE on UPDATE CASCADE ,
  FOREIGN KEY (bus_number) REFERENCES Busses(bus_number) on DELETE CASCADE  ,
  FOREIGN KEY (school_name, school_address) REFERENCES Schools(name,address) on DELETE CASCADE on UPDATE CASCADE
);

DELIMITER //

CREATE PROCEDURE addBuss( b_route VARCHAR(50), b_model VARCHAR(50), cap int, s_name VARCHAR(50), s_address VARCHAR(50))
  BEGIN
    INSERT INTO Busses ( route, model, capacity,school_name , school_address) VALUES (b_route,b_model,cap,s_name,s_address);
  END //
  DELIMITER ;

CALL addBuss('mohandesen', 'b12', 20,'El rowad', '8 Mostafa El Nahhas');

SELECT *
FROM Schools;
SELECT * from Busses;

DELIMITER //
CREATE PROCEDURE addStudents(stu_ssn int,b_route VARCHAR(200), )
  BEGIN
   DECLARE mySchoolName VARCHAR(50);
  DECLARE mySchoolAddress VARCHAR(50);
  DECLARE available_bus_number int DEFAULT  null;
    SELECT S.name
    INTO mySchoolName
    FROM Schools S INNER JOIN Students stu on S.name = stu.school_name and S.address = stu.school_address
    WHERE stu.ssn = stu_ssn;

 SELECT S.address
    INTO mySchoolAddress
    FROM Schools S INNER JOIN Students stu on S.name = stu.school_name and S.address = stu.school_address
    WHERE stu.ssn = stu_ssn;


    SELECT B.bus_number into available_bus_number
      from Busses B inner JOIN Schools S on S.name = B.school_name and S.address = B.school_address
    WHERE S.name = mySchoolName and S.address = mySchoolAddress and B.capacity >0
    LIMIT 1;

      IF available_bus_number IS NULL
    THEN
      SELECT 'no bus available';
    ELSE
      INSERT INTO Busses (route, model, capacity, school_name, school_address) VALUES ();
    END IF;



  END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE avg_cap(OUT res FLOAT, s_name VARCHAR(50), s_address VARCHAR(50))
  BEGIN
    DECLARE total FLOAT;
    DECLARE cnt FLOAT;

    SELECT count(B.capacity)
    INTO cnt
    FROM Schools S inner JOIN Busses B on S.name = b.school_name and S.address = B.school_address
    WHERE S.name = B.school_name and S.address = s_address;

     SELECT sum(B.capacity)
    INTO cnt
    FROM Schools S inner JOIN Busses B on S.name = b.school_name and S.address = B.school_address
    WHERE S.name = B.school_name and S.address = s_address;

    SET res = total / cnt;

  END;
DELIMITER ;


use test3;

# select * from Schools S where S.name = 'saintfatima' and S.address $arr1[1];

select * from Schools S inner join Employees E on S.name = E.school_name and S.address = E.school_address inner join Teachers T on T.teacher_id = E.employee_ID where S.name = 'El rowad' and S.address = '8 Mostafa El Nahhas';

SELECT *
FROM Schools;
CALL viewSchoolAndRating('El rowad','8 Mostafa El Nahhas');
SELECT *
FROM Parents_rateAndreview_Schools;


INSERT INTO Parents_rateAndreview_Schools (rate, review, parent_username, school_name, school_address)
VALUES (5,'good','ahmed_abdelrahman','El rowad','8 Mostafa El Nahhas');
SELECT *
FROM Parents;

SELECT * FROM Students WHERE user_name = 'dodo' and password = 'medo';

SELECT * FROM Parents WHERE user_name = 'ahmed_abdelrahman' and password = 'blabizo';

SELECT *
FROM Parents;

# INSERT INTO Parents (user_name, password, first_name, last_name, home_phone, email, address) VALUES (name,pwd,first_name,last_name,tel,email,add);
use test3;
CREATE PROCEDURE createActivity2(date               DATETIME, newlocation VARCHAR(50),
                                newadminstrator_id INT,
                                newname            VARCHAR(50),
                                newequipment       VARCHAR(50),
                                newtype            VARCHAR(50),
                                newdescription     VARCHAR(50),
                                tid int)
  BEGIN
    INSERT INTO School_Activities (activity_Date, location, adminstrator_id, name, equipment, type, description,teacher_id)
    VALUES (date, newlocation, newadminstrator_id, newname, newequipment, newtype, newdescription,tid);

  END ;
DELIMITER //

CREATE PROCEDURE viewSchoolsByLevel2()
  BEGIN
    SELECT
      S.name,
      S.address,
      los.level
    FROM Schools S inner join Levels_offer_Schools los on los.school_name = S.name and los.school_address = S.address;

  END //
SELECT *
FROM Mid_Schools;
SELECT *
FROM Schools S inner join High_Schools M on S.name = M.name and S.address = M.address;

use test3;
SELECT *
FROM Employees inner join Adminstrators on employee_ID = Adminstrators.adminstrator_id;
SELECT *
FROM Employees inner join Teachers on employee_ID = teacher_id;