--LESSA PARENT MAT3AMALSH

CREATE DATABASE dec9b
GO
USE dec9b
GO
CREATE FUNCTION isElementary(@x VARCHAR(50)) RETURNS BIT
AS BEGIN
    DECLARE @r BIT
    SELECT @r = is_elementary FROM Schools WHERE @x = email
    RETURN @r
END
GO

CREATE FUNCTION hasChildInSchool(@parentEmail VARCHAR(50), @schoolEmail VARCHAR(50)) RETURNS BIT
AS BEGIN
    DECLARE @r2 BIT
    IF EXISTS
    (
        SELECT *
        FROM Enrolled_Students es
        WHERE es.school_email = @schoolEmail AND es.parent_email = @parentEmail
    )
        SET @r2 = '1'
    ELSE
        SET @r2 = '0'
    RETURN @r2
END
GO

CREATE FUNCTION teacherTeachesStudent (@parentEmail VARCHAR(50), @teacherEmail VARCHAR(50)) RETURNS BIT
AS BEGIN
    DECLARE @r3 BIT
    IF EXISTS
    (
        SELECT *
        FROM Enrolled_Students es, Schools s, Employees e
        WHERE @parentEmail = es.parent_email
        AND es.school_email = s.email
        AND s.email = e.school_email
        AND e.email = @teacherEmail
        AND e.is_teacher = '1'
    )
    SET @r3 = '1'
    ELSE
    SET @r3 = '0'
    RETURN @r3
END
GO
------------------------------------------------------------
CREATE TABLE Schools
(
    email VARCHAR(50) PRIMARY KEY,
    vision VARCHAR(300),
    main_language VARCHAR (30) ,
    mission VARCHAR(300),
    is_national BIT DEFAULT '0',
    is_international BIT DEFAULT '0',
    phone_number VARCHAR(20) UNIQUE,
    home_page VARCHAR(100),
    fees FLOAT ,
    school_address VARCHAR(100) ,
    general_information VARCHAR(300),
    name VARCHAR (50),
    is_elementary BIT NOT NULL DEFAULT '0',
    is_middle BIT NOT NULL DEFAULT '0',
    is_high BIT NOT NULL DEFAULT '0',
    CONSTRAINT school_type CHECK (is_national<>0 OR is_international<>0),
    CONSTRAINT school_grade CHECK (is_elementary<>0 OR is_middle<>0 OR is_high<>0)
);
CREATE TABLE Elementary_Supplies
(
  supply VARCHAR(50),
  email VARCHAR(50),
  PRIMARY KEY(supply,email),
  FOREIGN KEY (email) REFERENCES Schools,
  CHECK (dbo.isElementary(email) = '1')
);
CREATE TABLE Parents
(
    email VARCHAR(50) PRIMARY KEY,
    parent_address VARCHAR(100),
    home_phone VARCHAR(20),
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    parent_user_name VARCHAR(25) UNIQUE,
    parent_password VARCHAR(25)
);
CREATE TABLE Parents_Mobiles
(
    mobile VARCHAR(20) PRIMARY KEY,
    email VARCHAR(50),
    FOREIGN KEY (email) REFERENCES Parents
);
CREATE TABLE Parents_Write_Review_Schools
(
    parent_email VARCHAR(50),
    school_email VARCHAR(50),
    reviews VARCHAR(200),
    PRIMARY KEY(parent_email, school_email),
    FOREIGN KEY(parent_email) REFERENCES Parents(email),
    FOREIGN KEY(school_email) REFERENCES Schools(email),
    CHECK(dbo.hasChildInSchool(parent_email, school_email) = '1')
);
/*
a0 2
b0 5
c1 1
d1 3
e1 10
f1 11
*/
CREATE TABLE Applicants
(
    ssn INT PRIMARY KEY,
    name VARCHAR(50),
    gender BIT,
    birth_date DATE,
    age AS (YEAR(CURRENT_TIMESTAMP) - YEAR(birth_date)),
    parent_email VARCHAR(50),
    FOREIGN KEY (parent_email) REFERENCES Parents
);

CREATE TABLE Applicants_Applies_Schools
(
    school_email VARCHAR(50),
    child_ssn INT,
    accepted BIT DEFAULT '0',
    parent_email VARCHAR(50),
    PRIMARY KEY(child_ssn, school_email),
    FOREIGN KEY(child_ssn) REFERENCES Applicants,
    FOREIGN KEY(parent_email) REFERENCES Parents,
    FOREIGN KEY(school_email) REFERENCES Schools
);
CREATE TABLE Enrolled_Students
(
    ssn INT PRIMARY KEY,
    student_level INT,
    name VARCHAR(50),
    gender BIT,
    birth_date DATE,
    age AS (YEAR(CURRENT_TIMESTAMP) - YEAR(birth_date)),
    student_password VARCHAR(20),
    student_user_name VARCHAR(20) UNIQUE,
    school_email VARCHAR(50),
    parent_email VARCHAR(50),
    FOREIGN KEY (school_email) REFERENCES Schools,
    FOREIGN KEY (parent_email) REFERENCES Parents
);
CREATE TABLE Courses
(
    code VARCHAR(10) PRIMARY KEY,
    name VARCHAR(50),
    level_of_school INT,
    course_description VARCHAR(100)
);
CREATE TABLE Courses_Prequisites
(
    course_code_1 VARCHAR(10),
    course_code_2 VARCHAR(10),
    PRIMARY KEY (course_code_1,course_code_2),
    FOREIGN KEY (course_code_1) REFERENCES Courses,
    FOREIGN KEY (course_code_2) REFERENCES Courses
);



CREATE TABLE Courses_Offered_By_Schools
(
    course_code VARCHAR(10),
    school_email VARCHAR(50),
    PRIMARY KEY (course_code, school_email),
    FOREIGN KEY (course_code) REFERENCES Courses,
    FOREIGN KEY (school_email) REFERENCES Schools
);



CREATE TABLE Employees
(
    email VARCHAR(50) PRIMARY KEY,
    birth_date DATE,
    employee_user_name VARCHAR(25) UNIQUE,
    employee_password VARCHAR(25),
    first_name VARCHAR(25),
    middle_name VARCHAR(25),
    last_name VARCHAR(25),
    gender BIT,
    employee_address VARCHAR(100),
    salary FLOAT,
    enrollment_year DATE,
    years_of_experience AS (YEAR(CURRENT_TIMESTAMP) - YEAR(enrollment_year)),
    is_administrator BIT NOT NULL DEFAULT '0',
    is_teacher BIT NOT NULL DEFAULT '0',
    is_supervisor BIT NOT NULL DEFAULT '0',
    is_verified BIT NOT NULL DEFAULT '0',
    school_email VARCHAR(50),
    supervisor_email VARCHAR(50),
    FOREIGN KEY (school_email) REFERENCES Schools,
    FOREIGN KEY (supervisor_email) REFERENCES Employees
);
CREATE TABLE Activities
(
    activity_date DATE,
    activity_location VARCHAR(30),
    activity_type VARCHAR(20),
    activity_description VARCHAR(300),
    administrator_email VARCHAR(50) NOT NULL,
    teacher_email VARCHAR(50),
    school_email VARCHAR(50) NOT NULL,
    PRIMARY KEY (activity_date, activity_location),
    FOREIGN KEY (administrator_email) REFERENCES Employees,
    FOREIGN KEY (teacher_email) REFERENCES Employees,
    FOREIGN KEY (school_email) REFERENCES Schools
);
CREATE TABLE Equipment
(
    activity_date DATE,
    activity_location VARCHAR(30),
    equipment VARCHAR(50),
    PRIMARY KEY (activity_date, activity_location, equipment),
    FOREIGN KEY (activity_date, activity_location) REFERENCES Activities
);
CREATE TABLE Activities_Applied_By_Enrolled_Students
(
    activity_date DATE,
    activity_location VARCHAR(30),
    ssn INT,
    PRIMARY KEY (ssn, activity_date, activity_location),
    FOREIGN KEY (ssn) REFERENCES Enrolled_Students,
    FOREIGN KEY (activity_date, activity_location) REFERENCES Activities
);
CREATE TABLE Clubs
(
    name VARCHAR(50) PRIMARY KEY,
    purpose VARCHAR(50)
);
CREATE TABLE Clubs_Joined_By_Enrolled_Student
(
    club_name VARCHAR(50),
    enrolled_ssn INT,
    PRIMARY KEY (club_name, enrolled_ssn),
    FOREIGN KEY (club_name) REFERENCES Clubs,
    FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students
);
CREATE TABLE Clubs_Offered_By_Schools --eh offers da :P
(
    name VARCHAR(50),
    school_email VARCHAR(50),
    PRIMARY KEY(name,school_email),
    FOREIGN KEY (name) REFERENCES Clubs,
    FOREIGN KEY (school_email) REFERENCES Schools
);
CREATE TABLE Questions
(
    question_number INT IDENTITY,
    course_code VARCHAR(10) NOT NULL,
    question_itself VARCHAR(500), --3ash X')
    question_answer VARCHAR(500),
    askers_ssn INT,
    PRIMARY KEY (question_number,course_code),
    FOREIGN KEY (course_code) REFERENCES Courses,
    FOREIGN KEY (askers_ssn) REFERENCES Enrolled_Students
);
CREATE TABLE Enrolled_Students_View_Questions
(
    ssn INT,
    question_number INT,
    course_code VARCHAR(10),
    PRIMARY KEY (ssn, question_number, course_code),
    FOREIGN KEY (ssn) REFERENCES Enrolled_Students,
    FOREIGN KEY (question_number,course_code) REFERENCES Questions
);
CREATE TABLE Teachers_Response_To_Questions
(
    question_number INT,
    course_code VARCHAR(10),
    teacher_email VARCHAR(50),
    PRIMARY KEY (question_number, course_code, teacher_email),
    FOREIGN KEY (question_number,course_code) REFERENCES Questions,
    FOREIGN KEY (teacher_email) REFERENCES Employees
);
CREATE TABLE Assignments
(
    assignment_number INT,
    course_code VARCHAR(10),
    teacher_email VARCHAR(50),
    posting_date DATE,
    due_date DATE,
    content VARCHAR(500),
    PRIMARY KEY (assignment_number, course_code, teacher_email),
    FOREIGN KEY (course_code) REFERENCES Courses,
    FOREIGN KEY (teacher_email) REFERENCES Employees
);
CREATE TABLE Assignments_Solved_By_Students
(
    ssn INT,
    assignment_number INT,
    teacher_email VARCHAR(50),
    course_code VARCHAR(10),
    grade FLOAT,
    solution VARCHAR(3000),
    PRIMARY KEY (ssn, assignment_number, teacher_email, course_code),
    FOREIGN KEY (ssn) REFERENCES Enrolled_Students,
    FOREIGN KEY (assignment_number, course_code, teacher_email) REFERENCES Assignments
);



CREATE TABLE View_And_Grade_Assignments
(
    viewer_email VARCHAR(50), --teacher_email ya3ny
    assignment_number INT,
    course_code VARCHAR(10),
    creator_email VARCHAR(50),
    PRIMARY KEY(viewer_email, assignment_number, course_code, creator_email),
    FOREIGN KEY (assignment_number, course_code, creator_email) REFERENCES Assignments,
    FOREIGN KEY (viewer_email) REFERENCES Employees
);
CREATE TABLE Attends
(
    enrolled_ssn INT,
    course_code VARCHAR(10),
    grade FLOAT,
    PRIMARY KEY (enrolled_ssn, course_code),
    FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students,
    FOREIGN KEY (course_code) REFERENCES Courses
);

CREATE TABLE Teaches
(
    teacher_email VARCHAR(50),
    course_code VARCHAR(10),
    PRIMARY KEY (course_code, teacher_email),
    FOREIGN KEY (teacher_email) REFERENCES Employees,
    FOREIGN KEY (course_code) REFERENCES Courses
);
CREATE TABLE Announcements
(
    administrator_email VARCHAR(50),
    announcement_date DATE,
    announcement_time TIME,
    announcement_type VARCHAR(20),
    title VARCHAR(25),
    announcement_description VARCHAR(300),
    school_email VARCHAR(50) NOT NULL,
    PRIMARY KEY (administrator_email, announcement_date, announcement_time),
    FOREIGN KEY (administrator_email) REFERENCES Employees,
    FOREIGN KEY (school_email) REFERENCES Schools
);
CREATE TABLE Announcements_Visible_To_Parents
(
    administrator_email VARCHAR(50),
    announcement_date DATE,
    announcement_time TIME,
    parent_email VARCHAR(50),
    PRIMARY KEY (administrator_email,announcement_date,announcement_time,parent_email),
    FOREIGN KEY (parent_email) REFERENCES Parents,
    FOREIGN KEY (administrator_email, announcement_date, announcement_time) REFERENCES Announcements
);
CREATE TABLE Announcements_Visible_To_Enrolled_Students
(
    administrator_email VARCHAR(50),
    announcement_date DATE,
    announcement_time TIME,
    enrolled_ssn INT,
    PRIMARY KEY (administrator_email,announcement_date,announcement_time),
    FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students,
    FOREIGN KEY (administrator_email, announcement_date, announcement_time) REFERENCES Announcements
);
CREATE TABLE Parents_Ratings_To_Teachers
(
    employee_email VARCHAR(50),
    parent_email VARCHAR(50),
    rating DECIMAL(2,1), --eh da? X) leh decimal tab? mesh int? madam zero to five
    CONSTRAINT chk_Rating CHECK (rating >= 0 AND rating <= 5),
    PRIMARY KEY (employee_email, parent_email),
    FOREIGN KEY (employee_email) REFERENCES Employees,
    FOREIGN KEY (parent_email) REFERENCES Parents,
    CHECK(dbo.teacherTeachesStudent(parent_email, employee_email) = '1')
);

--SELECT * FROM Monthly_Reports_Replies

CREATE TABLE Monthly_Reports
(
    enrolled_ssn INT,
    issue_date DATE,
    comment VARCHAR(200),
    writer VARCHAR(50) NOT NULL,
    PRIMARY KEY (enrolled_ssn, issue_date),
    FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students,
    FOREIGN KEY (writer) REFERENCES Employees
);

CREATE TABLE Monthly_Reports_Replies
(
    enrolled_ssn INT,
    issue_date DATE,
    parent_email VARCHAR (50),
    reply VARCHAR(200),
    PRIMARY KEY (enrolled_ssn, issue_date, parent_email),
    FOREIGN KEY (enrolled_ssn, issue_date) REFERENCES Monthly_Reports,
    FOREIGN KEY (parent_email) REFERENCES Parents
);
GO
-----------------------------------------------------------------------------------------
--sysadmin
CREATE PROC CreateSchool
@school_name VARCHAR(50),
@address VARCHAR(100),
@phone_number VARCHAR(20),
@email VARCHAR(50),
@general_information VARCHAR(300),
@vision VARCHAR(300),
@mission VARCHAR(300),
@main_language VARCHAR (30),
@TYPE BIT,
@is_elementary BIT,
@is_middle BIT,
@is_high BIT,
@fees FLOAT
AS
    DECLARE @isNational bit SET @isNational = 0;
    DECLARE @isInternational bit SET @isInternational = 0;
    IF @TYPE = 1
        SET @isInternational = 1;
    ELSE
        SET @isNational = 1
    IF @school_name IS NULL OR
    @address IS NULL OR
    @phone_number IS NULL OR
    @TYPE IS NULL OR
    @fees IS NULL
        print 'One of the inputs is null'
    ELSE
        INSERT INTO Schools(email , vision , main_language , mission , is_national , is_international , phone_number , fees , school_address , general_information , name,is_elementary,is_middle,is_high)
        VALUES(@email, @vision, @main_language, @mission, @isNational, @isInternational, @phone_number, @fees, @address, @general_information, @school_name,@is_elementary,@is_middle,@is_high)
GO

--di 7aga fadla m'el functions?
CREATE TYPE Course_List
AS TABLE
(
    course_code VARCHAR(10)
);
GO

--azonn me7tageen separate proc t'insert a separate prequisite
CREATE PROC AddCourse
@course_code VARCHAR(10),
@course_name VARCHAR(50),
--@course_level_elementary BIT,
--@course_level_middle BIT,
--@course_level_high BIT,
@school_level INT,
@description VARCHAR(100),
@prequisite_courses AS Course_List READONLY
AS
    IF @course_code IS NULL OR
    @course_name IS NULL OR
--  @course_level_elementary is NULL or
--  @course_level_middle is NULL or
--  @course_level_high is NULL or
    @school_level IS NULL
        print 'One of the inputs is null'
    ELSE
        INSERT INTO Courses(code, level_of_school, course_description, name)
        VALUES(@course_code, @school_level, @description, @course_name)

    DECLARE @code VARCHAR(10)
    DECLARE MY_CURSOR CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
    SELECT DISTINCT course_code
    FROM @prequisite_courses
    OPEN MY_CURSOR
    FETCH NEXT FROM MY_CURSOR INTO @code
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF EXISTS (SELECT code FROM Courses WHERE code = @code)
            INSERT INTO Courses_Prequisites(course_code_1, course_code_2)
            VALUES(@course_code, @code)
        ELSE
            print 'Prequisite course does not exist'
        FETCH NEXT FROM MY_CURSOR INTO @code
    END
    CLOSE MY_CURSOR
    DEALLOCATE MY_CURSOR
GO

CREATE PROC AddAdmin2
@first_name VARCHAR(25),
@middle_name VARCHAR(25),
@last_name VARCHAR(25),
@birthdate DATE,
@address VARCHAR(100),
@email VARCHAR(50),
@username VARCHAR(25),
@password VARCHAR(25),
@gender BIT
AS
    IF @first_name IS NULL OR
    @middle_name IS NULL OR
    @last_name IS NULL OR
    @birthdate IS NULL OR
    @address IS NULL OR
    @email IS NULL OR
    @username IS NULL OR
    @password IS NULL OR
    @gender IS NULL
        print 'One of the inputs is null'
    ELSE
        DECLARE @DATE AS DATE SET @DATE = CAST(GETDATE() AS DATE);
        INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,gender,employee_address,enrollment_year,birth_date,is_administrator)
        VALUES(@email, @username, @password, @first_name, @middle_name, @last_name, @gender, @address, @DATE, @birthdate, 1)
GO

CREATE PROC AssignAdminToSchool
@admin_email VARCHAR(50),
@school_email VARCHAR(50)
AS
    DECLARE @school_type BIT
    IF @admin_email IS NULL
    OR @school_email IS NULL
        print 'One of the inputs is null'
    ELSE
        IF EXISTS(
            SELECT *
            FROM Schools s
            WHERE s.email = @school_email)
        AND EXISTS(
            SELECT *
            FROM Employees
            WHERE email = @admin_email)
        BEGIN
            DECLARE @salary INT --???
            SELECT @school_type = s.is_international
            FROM Schools s
            WHERE s.email = @school_email
            IF @school_type = 1 --international
                UPDATE Employees
                SET school_email = @school_email, salary = 5000
                WHERE email = @admin_email;
            ELSE --national
                UPDATE Employees
                SET school_email = @school_email, salary = 3000
                WHERE email = @admin_email;
        END
        ELSE
            print 'School or Admin Does Not Exist'
GO

CREATE PROC DeleteSchool
@school_email VARCHAR(50)
AS
    IF
        @school_email IS NULL
        print 'Input is null'
    ELSE
        DELETE FROM Schools
        WHERE email = @school_email;
        UPDATE Employees
        SET employee_user_name = NULL, employee_password = NULL
        WHERE school_email = @school_email
GO
----------------------------------------------------------

-- Parent Procedures 1
CREATE PROC InsertParents
@email VARCHAR(50) ,
@parent_address VARCHAR(100),
@home_phone VARCHAR(20),
@first_name VARCHAR(25),
@last_name VARCHAR(25),
@parent_user_name VARCHAR(25),
@parent_password VARCHAR(25)
AS
    IF @email IS NULL OR
    @parent_address IS NULL OR
    @home_phone IS NULL OR
    @first_name IS NULL OR
    @last_name IS NULL OR
    @parent_user_name IS NULL OR
    @parent_password IS NULL
        PRINT 'One of the inputs is null'
    ELSE
        INSERT INTO Parents(email, parent_address, home_phone, first_name, last_name, parent_user_name, parent_password )
        VALUES(@email, @parent_address, @home_phone, @first_name, @last_name, @parent_user_name, @parent_password)
GO
--2
CREATE PROC Parents_Apply_Schools
@ssn INT ,
@name VARCHAR(50) ,
@gender BIT,
@birth_date DATE,
@parent_email VARCHAR(50),
@school_email_in VARCHAR(50)
AS
    IF
    @ssn IS NULL OR
    @name IS NULL OR
    @gender IS NULL OR
    @birth_date IS NULL OR
    @parent_email IS NULL
        PRINT 'One of the inputs is null'
    ELSE
        INSERT INTO Applicants (ssn,name,gender,birth_date,parent_email)
        VALUES(@ssn,@name,@gender,@birth_date,@parent_email)
        INSERT INTO Applicants_Applies_Schools
        (school_email , child_ssn , parent_email)
        VALUES (@school_email_in , @ssn , @parent_email)
GO

--3
CREATE PROC Schools_Accepted_My_Children
@ssn_in INT
AS
    SELECT s.*
    FROM Schools s
    INNER JOIN Applicants_Applies_Schools a ON a.school_email = s.email
    WHERE @ssn_in = a.child_ssn AND a.Accepted = 'True'
GO
--4
CREATE PROC ChooseSchool --noexec
@parent_email VARCHAR(50),
@child_ssn INT,
@school_email VARCHAR(50)
AS
    IF  @parent_email IS NULL
    OR @child_ssn IS NULL
    OR @school_email IS NULL
        print 'Null Input'
    ELSE
    BEGIN
        --Declare @table Table
        DECLARE @name VARCHAR(50)
        DECLARE @gender BIT
        DECLARE @birth_date DATE
        SELECT @name = e.name, @gender = e.gender, @birth_date = e.birth_date
        FROM Applicants e INNER JOIN Applicants_Applies_Schools a ON a.child_ssn = e.ssn
        WHERE e.parent_email = @parent_email AND a.accepted = 1
        INSERT INTO Enrolled_Students
        (ssn , name , gender , birth_date , school_email , parent_email)
        VALUES(@child_ssn, @name, @gender, @birth_date, @school_email, @parent_email)
        DELETE FROM Applicants
        WHERE ssn = @child_ssn
    END
GO
--5
CREATE PROC View_Reports
@parent_email VARCHAR(50)
AS
    SELECT r.*
    FROM Monthly_Reports r
    INNER JOIN Enrolled_Students e ON r.enrolled_ssn = e.ssn
    WHERE e.parent_email = @parent_email
GO
--6
CREATE PROC Reply
@parent_email VARCHAR(50),
@reply VARCHAR(200),
@issue_date DATE,
@enrolled_ssn INT
AS
    INSERT INTO Monthly_Reports_Replies (enrolled_ssn ,issue_date,parent_email,reply)
    VALUES(@enrolled_ssn,@issue_date,@parent_email,@reply)
GO
--7
CREATE PROC List_of_Children_Schools
@parent_email VARCHAR(50)
AS
SELECT e.name + ' in ' + s.name AS 'List of my children schools'
FROM Enrolled_Students e
        INNER JOIN Schools s ON e.school_email = s.email
WHERE e.parent_email = @parent_email
ORDER BY s.name
GO
--8
CREATE PROC Posted_Announcments
@parent_email VARCHAR(50),
@Enrolled_ssn INT
AS
DECLARE @p10days TIMESTAMP = DAY(CURRENT_TIMESTAMP)
SELECT a.announcement_description
FROM Announcements_Visible_To_Parents ap
        INNER JOIN Parents p ON p.email = ap.parent_email
        INNER JOIN Announcements a ON a.administrator_email = ap.administrator_email
        INNER JOIN Announcements_Visible_To_Enrolled_Students ae ON ae.administrator_email = ap.administrator_email
WHERE ap.parent_email = @parent_email AND ae.enrolled_ssn = @Enrolled_ssn AND (DAY(ap.announcement_date) + 10) > @p10days
GO
--9
CREATE PROC Rate_Teachers
@parent_email VARCHAR(50),
@teacher_email VARCHAR(50),
@rating DECIMAL(2,1)
AS
IF  @parent_email IS NULL OR
    @teacher_email IS NULL OR
    @rating IS NULL
    print 'NULL INPUT'
ELSE
IF @parent_email IN
    (
        SELECT parent_email
        FROM Enrolled_Students e
            INNER JOIN Attends a ON a.enrolled_ssn = e.ssn
            INNER JOIN Teaches T ON T.course_code = a.course_code
        WHERE e.parent_email = @parent_email
    )
    INSERT INTO Parents_Ratings_To_Teachers(employee_email, parent_email, rating)
    VALUES(@teacher_email, @parent_email, @rating)
GO
--10
CREATE PROC Write_Reviews
@parent_email VARCHAR(50),
@school_email VARCHAR(50),
@reviews VARCHAR(200)
AS
    IF @parent_email IS NULL OR @school_email IS NULL OR @reviews IS NULL
        PRINT 'Incomplete input!'
    ELSE IF NOT EXISTS(
    SELECT e.parent_email
    FROM Enrolled_Students e
    WHERE e.parent_email = @parent_email AND e.school_email = @school_email)
        PRINT 'You must have a student enrolled in this school'
    ELSE
        INSERT INTO Parents_Write_Review_Schools
        (parent_email , reviews , school_email)
        VALUES (@parent_email , @reviews ,@school_email)
GO

--EXEC Write_Reviews "shady.azoz@MSA.edu.eg" , "Shwyfat@edu.eg" , "eww"
--EXEC Write_Reviews "shady.azoz@MSA.edu.eg" , "ST.Fatima@edu.eg" , "fffff"

--11
CREATE PROC Delete_review
@parent_email VARCHAR(50),
@school_email VARCHAR(50)
AS
    DELETE FROM Parents_Write_Review_Schools
    WHERE parent_email = @parent_email
    AND @school_email = school_email
GO

--12
CREATE PROC Sum_rating
@teacher_fname VARCHAR(25),
@teacher_lname VARCHAR(25),
@teacher_rate FLOAT OUTPUT
AS
DECLARE @total INT
DECLARE @COUNT INT
SELECT @Total = SUM(pt.rating), @COUNT = COUNT(pt.rating)
FROM Parents_Ratings_To_Teachers pt
        INNER JOIN Employees e ON e.email = pt.employee_email
        WHERE e.first_name = @teacher_fname AND e.last_name = @teacher_lname
SET @teacher_rate = @total/@COUNT
PRINT @teacher_rate
GO
--13
CREATE PROC TOP_10_Schools
@parent_email VARCHAR(50)
AS
SELECT s.name, COUNT(ps.reviews), COUNT(e.ssn)
FROM Schools s
        INNER JOIN Parents_Write_Review_Schools ps ON ps.school_email = s.email
        INNER JOIN Enrolled_Students e ON e.school_email = s.email
        WHERE ps.parent_email <> @parent_email
GROUP BY s.name
GO
--14
CREATE PROC Highest_International_School
@schoolname VARCHAR(50) OUTPUT,
@COUNT INT OUTPUT
AS
SELECT @schoolname = s.name, @COUNT = COUNT(ps.reviews)
FROM Schools s
        INNER JOIN Parents_Write_Review_Schools ps ON ps.school_email = s.email
GROUP BY s.name
HAVING COUNT(ps.reviews) = MAX(ps.reviews)
GO
---------------------------------------------------------------------------------
-- System User
--1
CREATE PROC List_School
@name VARCHAR(50),
@address VARCHAR(100),
@NATIONAL BIT,
@international BIT
AS
SELECT *
FROM Schools
WHERE name = @name OR school_address = @address OR is_national = @NATIONAL OR is_international = @international
GO
--2
CREATE PROC Schools_By_Levels
@elementary BIT,
@middle BIT,
@high BIT
AS
SELECT name,is_elementary,is_middle,is_high
FROM Schools
WHERE is_elementary = @elementary OR is_middle = @middle OR is_high = @high
ORDER BY is_elementary, is_middle, is_high
GO
--3
CREATE PROC School_Info
@name VARCHAR(50)
AS
SELECT *
FROM Schools s
    INNER JOIN Parents_Write_Review_Schools ps ON ps.school_email = s.email
    INNER JOIN Employees e ON e.school_email = s.email
    WHERE s.name = @name
GO

----------------------------------------------------------------------------------------
--adminstrator

CREATE PROC ViewTeachers
@admin_email VARCHAR(50)
AS
    IF
        @admin_email IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin BIT
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
        IF @isAdmin = 1
            SELECT *
            FROM Employees e
            WHERE e.is_teacher = 1 AND e.school_email = @school_email
        ELSE
            PRINT 'Not Admin'
GO
CREATE PROC AssignTeacher
    @admin_email VARCHAR(50),
    @teacher_email VARCHAR(50),
    @user_name VARCHAR(25),
    @password VARCHAR(25)
AS
    IF
        @admin_email IS NULL OR
        @teacher_email IS NULL OR
        @user_name IS NULL OR
        @password IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin bit
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
        IF @isAdmin = 1
            UPDATE Employees
            SET employee_user_name = @user_name, employee_password = @password, salary = years_of_experience * 500
            WHERE email = @teacher_email
GO
CREATE PROC ViewStudents
@admin_email VARCHAR(50)
AS
BEGIN
    IF
        @admin_email IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin bit
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
        IF @isAdmin = 1
            SELECT *
            FROM Enrolled_Students e
            WHERE e.school_email = @school_email
END
GO
CREATE PROC AssignStudent
    @admin_email VARCHAR(50),
    @student_ssn VARCHAR(50),
    @user_name VARCHAR(25),
    @password VARCHAR(25)
AS
    IF
        @admin_email IS NULL OR
        @student_ssn IS NULL OR
        @user_name IS NULL OR
        @password IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin bit
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
        IF @isAdmin = 1
            UPDATE Enrolled_Students
            SET student_user_name = @user_name, student_password = @password
            WHERE ssn = @student_ssn
GO
CREATE PROC AddAdmin
    @s_admin_email VARCHAR(50),
    @first_name VARCHAR(25),
    @middle_name VARCHAR(25),
    @last_name VARCHAR(25),
    @birthdate DATE,
    @address VARCHAR(100),
    @email VARCHAR(50),
    @username VARCHAR(25),
    @password VARCHAR(25),
    @gender BIT
AS
    IF
        @s_admin_email IS NULL OR
        @first_name IS NULL OR
        @middle_name IS NULL OR
        @last_name IS NULL OR
        @birthdate IS NULL OR
        @address IS NULL OR
        @email IS NULL OR
        @username IS NULL OR
        @password IS NULL OR
        @gender IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin bit
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @s_admin_email
        IF @isAdmin = 1
        DECLARE @sal FLOAT
        DECLARE @is_international BIT
        SELECT @is_international = is_international
        FROM Schools s
        WHERE s.email = @school_email
        IF(@is_international = 1)
            SET @sal = 5000
        ELSE
            SET @sal = 3000
            INSERT INTO Employees(email,
                                    employee_user_name,
                                    employee_password,
                                    first_name,
                                    middle_name,
                                    last_name,
                                    gender,
                                    employee_address,
                                    enrollment_year,
                                    school_email,
                                    is_administrator,
                                    is_teacher,
                                    is_supervisor,
                                    salary)
            VALUES(@email, @username, @password, @first_name, @middle_name, @last_name, @gender, @address, CAST(GETDATE() AS DATE), @school_email, 1, 0, 0, @sal)
GO
CREATE PROC DeleteEmployee
    @admin_email VARCHAR(50),
    @emp_email VARCHAR(50)
AS
    IF
        @admin_email IS NULL OR
        @emp_email IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin bit
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
            IF @isAdmin = 1
                DELETE FROM Employees
                WHERE school_email = @school_email AND email = @emp_email
GO
CREATE PROC DeleteStudent
    @admin_email VARCHAR(50),
    @student_ssn VARCHAR(50)
AS
    IF
        @admin_email IS NULL OR
        @student_ssn IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin bit
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
            IF @isAdmin = 1
                DELETE FROM Enrolled_Students
                WHERE school_email = @school_email AND ssn = @student_ssn
GO

/*EXEC EditSchool
"ashraf.hassan@ST.Fatima.edu.eg"
, "Shwyfat@edu.eg"
, "asdfasedf"
, "asdfasdf"
, "sadfasdfasdf"
, 1 , 1
, "654"
, "sdfgsdfg"
, "54"
, "fdgfgdfg"
, "fgfg"
, "sadfafasd"
, 1 , 1 , 1*/
GO

CREATE PROC EditSchool
    @admin_email VARCHAR(50),
    @email VARCHAR(50),
    @vision VARCHAR(300),
    @main_language VARCHAR (30),
    @mission VARCHAR(300),
    @is_national BIT,
    @is_international BIT,
    @phone_number VARCHAR(20),
    @home_page VARCHAR(100),
    @fees FLOAT,
    @school_address VARCHAR(100),
    @general_information VARCHAR(300),
    @name VARCHAR(50),
    @is_elementary BIT,
    @is_middle BIT,
    @is_high BIT
AS
    IF
        @admin_email IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin bit
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
            IF @isAdmin = 1
                UPDATE Schools
                SET
                    email = @email,
                    vision = @vision,
                    main_language = @main_language,
                    mission = @mission,
                    is_national = @is_national,
                    is_international = @is_international,
                    phone_number = @phone_number,
                    home_page = @home_page,
                    fees = @fees,
                    school_address = @school_address,
                    general_information = @general_information,
                    name = @name,
                    is_elementary = @is_elementary,
                    is_middle = @is_middle,
                    is_high = @is_high
                WHERE email = @school_email
GO

--EXEC PostAnnoucement "bb" , "a" , "aa" , "aaa"


CREATE PROC PostAnnoucement
    @admin_email VARCHAR(50),
    @title VARCHAR(25),
    @description VARCHAR(300),
    @TYPE VARCHAR(20)
AS
    IF
        @admin_email IS NULL OR
        @title IS NULL OR
        @description IS NULL OR
        @TYPE IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin bit
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
            IF @isAdmin = 1
                INSERT INTO Announcements(administrator_email,
                                            announcement_date,
                                            announcement_time,
                                            announcement_type,
                                            title,
                                            announcement_description,
                                            school_email)
                VALUES(@admin_email,  CAST(GETDATE() AS DATE),  CAST(GETDATE() AS TIME), @TYPE, @title, @description, @school_email)
GO
CREATE PROC CreateActivity
    @admin_email VARCHAR(50),
    @activity_date DATE,
    @activity_location VARCHAR(30),
    @teacher_email VARCHAR(50),
    @activity_type VARCHAR(20),
    @activity_description VARCHAR(300),
    @equipment VARCHAR(50)
AS
    IF
        @admin_email IS NULL OR
        @activity_date IS NULL OR
        @activity_location IS NULL OR
        @teacher_email IS NULL OR
        @activity_type IS NULL OR
        @activity_description IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin bit
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
            IF @isAdmin = 1
                INSERT INTO Activities(activity_date,
                                        activity_location,
                                        administrator_email,
                                        teacher_email,
                                        activity_type,
                                        activity_description,
                                        school_email)
                VALUES(@activity_date, @activity_location, @admin_email, @teacher_email, @activity_type, @activity_description, @school_email)
                IF @equipment IS NOT NULL
                    INSERT INTO Equipment(activity_date, activity_location, equipment)
                    VALUES(@activity_date, @activity_location, @equipment)
GO
CREATE PROC AssignTeacherToActivity
    @admin_email VARCHAR(50),
    @activity_date DATE,
    @activity_location VARCHAR(30),
    @teacher_email VARCHAR(50)
AS
    IF
        @admin_email IS NULL OR
        @activity_date IS NULL OR
        @activity_location IS NULL OR
        @teacher_email IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin bit
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
            IF @isAdmin = 1
                UPDATE Activities
                SET teacher_email = @teacher_email
                WHERE school_email = @school_email AND activity_date = @activity_date AND activity_location = @activity_location
GO
CREATE PROC AssignTeacherToCourse
    @admin_email VARCHAR(50),
    @teacher_email VARCHAR(50),
    @course_code VARCHAR(10)
AS
    IF
        @admin_email IS NULL OR
        @teacher_email IS NULL OR
        @course_code IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin bit
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
        IF @teacher_email IN (SELECT e2.email
                              FROM Employees e1 INNER JOIN Employees e2 ON e1.school_email = e2.school_email
                              WHERE e1.email = @admin_email)
                DECLARE @level INT
                DECLARE @high BIT
                DECLARE @mid BIT
                DECLARE @elem BIT
                SELECT @level = level_of_school
                FROM Courses
                WHERE code = @course_code
                SELECT @high = is_high, @mid = is_middle, @elem = is_elementary
                FROM Schools
                WHERE email = @school_email
                IF (@level > 9 AND @high = 1) OR (@level > 6 AND @mid = 1) OR (@level < 7 AND @elem = 1)
                    INSERT INTO Teaches(teacher_email, course_code)
                    VALUES(@teacher_email, @course_code)
GO
CREATE PROC AssignSupervisor
    @admin_email VARCHAR(50),
    @teacher_email VARCHAR(50),
    @supervisor_email VARCHAR(50)
AS
    IF
        @admin_email IS NULL OR
        @supervisor_email IS NULL OR
        @teacher_email IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin bit
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
        IF @isAdmin = 1
            UPDATE Employees
            SET supervisor_email = @supervisor_email
            WHERE email = @teacher_email AND school_email = @school_email
GO
CREATE PROC HandleSchoolApplication
    @admin_email VARCHAR(50),
    @child_ssn INT,
    @answer BIT
AS
    IF
        @admin_email IS NULL OR
        @child_ssn IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin bit
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
        IF @isAdmin = 1
            IF @answer = 1
            BEGIN
                DECLARE @parent_email VARCHAR(50)
                DECLARE @ssn INT
                DECLARE @name VARCHAR(50)
                DECLARE @gender BIT
                DECLARE @birth_date DATE
                SELECT @parent_email = a.parent_email, @ssn = a.ssn, @name = a.name, @gender = a.gender, @birth_date = a.birth_date
                FROM Applicants_Applies_Schools s INNER JOIN Applicants a ON  a.ssn = s.child_ssn
                WHERE school_email = @school_email AND child_ssn = @child_ssn
                INSERT INTO Enrolled_Students
                (ssn , name , gender , birth_date , school_email , parent_email)
                VALUES(@ssn, @name, @gender, @birth_date, @school_email, @parent_email)
            END
                DELETE FROM Applicants_Applies_Schools
                WHERE school_email = @school_email AND child_ssn = @ssn
                DELETE FROM Applicants
                WHERE ssn = @ssn
GO
---------------------------------------------------------------------------------------------------------------------------


--Student procedures
--1
CREATE PROC Enrolled_Student_Info_Update
@ssn_in INT,
@name_in VARCHAR(50),
@gender_in BIT,
@birth_date_in DATE,
@password_in VARCHAR(20),
@school_email_in VARCHAR(50),
@parent_email_in VARCHAR(50)
AS
    IF @ssn_in IS NULL
        PRINT 'Please enter SSN.'
    ELSE
        UPDATE Enrolled_Students
        SET
            name = ISNULL(@name_in , name),
            gender = ISNULL(@gender_in , gender),
            birth_date = ISNULL(@birth_date_in , birth_date),
            student_password = ISNULL(@password_in , student_password),
            school_email = ISNULL(@school_email_in , school_email),
            parent_email = ISNULL(@parent_email_in , parent_email)
        WHERE
            @ssn_in = Enrolled_Students.ssn
GO

--2
CREATE PROC Enrolled_Student_View_Courses
@ssn_in INT
AS
    IF @ssn_in = NULL
        PRINT 'Please enter SSN.'
    ELSE
        SELECT t.grade, c.name , c.code
        FROM Courses c INNER JOIN Attends t ON t.course_code = c.code
        WHERE t.enrolled_ssn = @ssn_in
        GROUP BY t.grade , c.name , c.code
        ORDER BY c.name
GO



--3
CREATE PROC Enrolled_Student_Post_Question
@course_code_in VARCHAR(10),
@question_itself_in VARCHAR(500),
@askers_ssn_in INT
AS
    IF @askers_ssn_in = NULL
        PRINT 'Please enter SSN.'
    ELSE
        INSERT INTO Questions (course_code,question_itself,askers_ssn)
        VALUES (@course_code_in,@question_itself_in,@askers_ssn_in)
GO










--4
CREATE PROC Enrolled_Student_View_Questions
@course_code_in VARCHAR(10),
@askers_ssn_in INT
AS
    IF @askers_ssn_in = NULL OR @course_code_in = NULL
        PRINT 'Input Incomplete.'
    ELSE IF NOT EXISTS
    (SELECT *
    FROM Enrolled_Students_View_Questions p --can use "Teaches" instead of this table. Redundant
    WHERE @course_code_in = p.course_code
    AND @askers_ssn_in = p.ssn)
        PRINT 'You do not have the permission to view questions on this course.'
    ELSE
        SELECT q.question_itself, q.question_answer
        FROM Questions q
        WHERE @course_code_in = q.course_code
GO



--EXEC Enrolled_Student_View_Questions "MATH501" , 2113524


--5
/*CREATE PROC Enrolled_Student_View_All_Assignments
@ssn_in INT
AS
    IF @ssn_in = NULL
        PRINT 'Please enter ssn.'
    ELSE
        SELECT asst.assignment_number , asst.course_code , asst.posting_date , asst.due_date , asst.content
        FROM Attends at
        INNER JOIN Assignments asst ON asst.course_code = at.course_code
        WHERE @ssn_in = at.enrolled_ssn
        SELECT t.assignment_number, t.course_code, t.posting_date, t.due_date, t.content
        FROM Teaches t
        --INNER JOIN Courses c ON t.course_code = c.code
        INNER JOIN Assignments a ON a.course_code = t.course_code
        WHERE t.enrolled_ssn = @ssn_in


ALTER PROC Enrolled_Student_View_All_Assignments
@ssn_in INT
AS
    IF @ssn_in = NULL
        PRINT 'Please enter ssn.'
    ELSE
        SELECT *
        FROM Assignments
        WHERE assignment_number IN (SELECT enrolled_ssn FROM Attends WHERE enrolled_ssn = @ssn_in)
GO
CREATE PROC Enrolled_Student_View_All_Assignments
@ssn_in INT
AS
    IF @ssn_in = NULL
        RAISERROR('Enter SSN', 16, 1)
    ELSE
        SELECT *
        FROM Assignments
        WHERE assignment_number IN (SELECT enrolled_ssn FROM Attends WHERE enrolled_ssn = @ssn_in)
GO*/
CREATE PROC Enrolled_Student_View_All_Assignments
@ssn_in INT
AS
    IF @ssn_in = NULL
    RAISERROR('Enter SSN', 16, 1)
    ELSE
    SELECT *
    FROM Assignments
    WHERE course_code IN (SELECT course_code FROM Attends WHERE enrolled_ssn = @ssn_in)
GO

CREATE PROC Enrolled_Student_View_Course_Assignments
@ssn_in INT,
@course_code_in VARCHAR(10)
AS
    IF @ssn_in = NULL
    RAISERROR('Enter SSN', 16, 1)
    ELSE
    SELECT *
    FROM Assignments
    WHERE course_code = @course_code_in
GO





--6
CREATE PROC Enrolled_Student_Solves_Assignment
@ssn_in INT,
@assignment_number_in INT,
@teacher_email_in VARCHAR(50),
@course_code_in VARCHAR(10),
@solution_in VARCHAR(3000)
AS
    IF @ssn_in = NULL
    OR @assignment_number_in = NULL
    OR @course_code_in = NULL
    OR @teacher_email_in = NULL
    OR @solution_in = NULL
        PRINT 'Incomplete Input.'
    ELSE IF NOT EXISTS(
    SELECT * FROM Attends t
    INNER JOIN Teaches tt ON t.course_code = tt.course_code
    WHERE t.enrolled_ssn = @ssn_in
    AND t.course_code = @course_code_in
    AND tt.teacher_email = @teacher_email_in)
        PRINT 'This course is not assigned to you.'
    ELSE
        INSERT INTO Assignments_Solved_By_Students
        (ssn,assignment_number,teacher_email,course_code,solution)
        VALUES (@ssn_in,@assignment_number_in,@teacher_email_in,@course_code_in,@solution_in)
GO






--7

CREATE PROC Enrolled_Students_Views_Assignments_Grades
@ssn_in INT
AS
    IF @ssn_in = NULL
        PRINT 'Please enter SSN.'
    ELSE
        SELECT s.course_code, s.assignment_number, s.grade
        FROM Assignments_Solved_By_Students s
        WHERE s.ssn = @ssn_in
GO







--8
--student's school
CREATE PROC Enrolled_Student_Views_Announcements
@ssn_in INT
AS
    IF @ssn_in = NULL
        PRINT 'Please enter SSN.'
    ELSE
        SELECT a.title, a.announcement_description
        FROM Announcements_Visible_To_Enrolled_Students av
        INNER JOIN Announcements a ON
        a.administrator_email = av.administrator_email
        AND a.announcement_date = av.announcement_date
        AND a.announcement_time = av.announcement_time
        WHERE @ssn_in = av.enrolled_ssn
        --AND DAY(a.announcement_date) > (DAY(CURRENT_TIMESTAMP) - 10)
        AND DATEDIFF(DAY,a.announcement_date,CONVERT(DATE, GETDATE())) < 10
        AND EXISTS
        (SELECT *
        FROM Enrolled_Students es
        WHERE es.ssn = @ssn_in
        AND a.school_email = es.school_email)
GO


--9
CREATE PROC Enrolled_Student_Views_Activities
@ssn_in INT
AS
    IF @ssn_in = NULL
        PRINT 'Please enter SSN.'
    ELSE
        SELECT a.activity_date, a.activity_location, a.activity_type, a.activity_description, e.first_name, e.middle_name, e.last_name, e.gender, e.email
        FROM Activities a INNER JOIN Employees e ON a.teacher_email = e.email
        WHERE e.is_teacher = 1
        AND EXISTS (
        SELECT *
        FROM Enrolled_Students es
        WHERE es.ssn = @ssn_in
        AND a.school_email = es.school_email)
GO




--10
--PLEASE CORRECT THIS PROCEDURE
--"date is not recognised as a built-in function name"
CREATE PROC Enrolled_Student_Applies_To_Activity
@ssn_in INT,
@activity_date_in DATE,
@activity_location_in VARCHAR(30)
AS
    IF @ssn_in = NULL
        PRINT 'Please enter SSN.'
    ELSE IF EXISTS(
    SELECT * FROM Activities_Applied_By_Enrolled_Students ae
    INNER JOIN Activities a ON ae.activity_date = ae.activity_date AND ae.activity_location = a.activity_location
    WHERE @ssn_in = ae.ssn AND EXISTS(
        SELECT * FROM Activities sa
        WHERE sa.activity_date = @activity_date_in
        AND sa.activity_location = @activity_location_in
        AND sa.activity_type = a.activity_type
        AND sa.activity_date = a.activity_date
    ))
        PRINT 'Can not join two activities on the same date of the same type!'
    ELSE
        INSERT INTO Activities_Applied_By_Enrolled_Students
        (ssn, activity_date, activity_location)
        VALUES (@ssn_in,@activity_date_in,@activity_location_in)
GO

--11
CREATE PROC Enrolled_Student_Join_Club
@ssn_in INT,
@club_name_in VARCHAR(50)
AS
    IF @ssn_in = NULL OR @club_name_in = NULL
        RAISERROR ('Incomplete input!' , 15 , 15)
    ELSE IF NOT EXISTS(
    SELECT * FROM Enrolled_Students es
    WHERE @ssn_in = es.ssn AND es.student_level > 9)
        RAISERROR ('You are not a high school student' , 15 , 15)
    ELSE IF NOT EXISTS(
    SELECT * FROM Enrolled_Students es
    INNER JOIN Clubs_Offered_By_Schools c ON c.school_email = es.school_email
    WHERE es.ssn = @ssn_in AND c.name = @club_name_in)
        RAISERROR ('Your school does not offer this club.' , 15 , 15)
    ELSE
        INSERT INTO Clubs_Joined_By_Enrolled_Student
        (club_name,enrolled_ssn)
        VALUES (@club_name_in,@ssn_in)
GO

--yaaFaragEllaaaah
CREATE PROC Enrolled_Student_Course_Search
@ssn_in INT,
@course_code_in VARCHAR(10),
@course_name_in VARCHAR(50)
AS
    IF @ssn_in IS NULL OR (@course_code_in IS NULL AND @course_name_in IS NULL)
        PRINT 'Incomplete input!'
    ELSE
        SELECT *
        FROM Attends a INNER JOIN Courses c ON a.course_code = c.code
        WHERE @course_name_in IS NOT NULL AND a.enrolled_ssn = @ssn_in AND c.name = @course_name_in
            UNION
        SELECT *
        FROM Attends a INNER JOIN Courses c ON a.course_code = c.code
        WHERE @course_code_in IS NOT NULL AND a.enrolled_ssn = @ssn_in AND a.course_code = @course_code_in
GO

--heyya name = ISNULL(@name_in , name) tenfa3? 2aw fee 7aga zayyaha
------------------------------------------------------------------------


--TEACHERRRRRRRRRRRRRR
CREATE PROC Teacher_Sign_Up
@fname_in VARCHAR(25),
@mname_in VARCHAR(25),
@lname_in VARCHAR(25),
@birthdate_in DATE,
@address_in VARCHAR(100),
@email_in VARCHAR(50),
@gender_in BIT
AS
    IF @fname_in = NULL
    OR @mname_in = NULL
    OR @lname_in = NULL
    OR @birthdate_in = NULL
    OR @address_in = NULL
    OR @email_in = NULL
    OR @gender_in = NULL
        PRINT 'Missing Info'
    ELSE
        INSERT INTO Employees
        (first_name , middle_name , last_name , birth_date , employee_address , email , gender , is_teacher)
        VALUES (@fname_in , @mname_in , @lname_in , @birthdate_in , @address_in , @email_in , @gender_in , '1')
GO
--2
CREATE PROC Teacher_View_His_Courses
@email_in VARCHAR(50)
AS
    IF @email_in IS NULL
        PRINT 'Please enter email!'
    ELSE
        SELECT c.code , c.name , c.level_of_school , c.course_description
        FROM Teaches t
        INNER JOIN Courses c ON c.code = t.course_code
        WHERE t.teacher_email = @email_in

GO
--3
CREATE PROC Post_Assignments
    @assignment_number INT,
    @course_code VARCHAR(10),
    @teacher_email VARCHAR(50),
    @posting_date DATE,
    @due_date DATE,
    @content VARCHAR(500)
AS
    IF
        @assignment_number IS NULL OR
        @course_code IS NULL OR
        @teacher_email IS NULL OR
        @posting_date IS NULL OR
        @due_date IS NULL OR
        @content IS NULL
        PRINT 'some data is null'
    ELSE
        INSERT INTO Assignments
        VALUES(@assignment_number,
        @course_code,
        @teacher_email,
        @posting_date,
        @due_date,
        @content )
GO
--4
CREATE PROC Students_Solutions
    @email_in VARCHAR(50),
    @course_code INT
AS
    SELECT a.ssn, a.solution
    FROM Assignments_Solved_By_Students a
    WHERE a.teacher_email = @email_in AND a.course_code = @course_code
    ORDER BY a.ssn
GO
--5
CREATE PROC Grade_Assignments
    @teacher_email VARCHAR(50),
    @enrolled_ssn INT,
    @course_code VARCHAR(10),
    @grade FLOAT
AS
    UPDATE Assignments_Solved_By_Students
    SET grade = @grade
    WHERE teacher_email = @teacher_email AND course_code = @course_code AND ssn = @enrolled_ssn
GO
--6
CREATE PROC Delete_Assignments
    @teacher_email VARCHAR(50),
    @course_code VARCHAR(10),
    @assignment_number INT
AS
DELETE FROM Assignments
WHERE teacher_email = @teacher_email AND course_code = @course_code AND assignment_number = @assignment_number
GO

--7
CREATE PROC Write_Report
    @teacher_email VARCHAR(50),
    @enrolled_ssn INT,
    @comment VARCHAR(200)
AS
    UPDATE Monthly_Reports
    SET comment = @comment , issue_date = CAST(GETDATE() AS DATE)
    WHERE writer = @teacher_email AND enrolled_ssn = @enrolled_ssn
GO
--8
CREATE PROC View_Questions
@teacher_email VARCHAR(50),
@course_code VARCHAR(10)
AS
SELECT q.question_number, e.name, q.question_itself
FROM Questions q
    INNER JOIN Enrolled_Students e ON q.askers_ssn = e.ssn
    INNER JOIN teaches t ON t.course_code = q.course_code
    WHERE q.course_code = @course_code AND t.teacher_email = @teacher_email
GO
--9
CREATE PROC Answer_Questions
@teacher_email VARCHAR(50),
@course_code VARCHAR(10),
@answer VARCHAR(500),
@NUMBER INT
AS
    UPDATE Questions
    SET question_answer = @answer
    WHERE question_number = @NUMBER
GO
--10
CREATE PROC ListStudents
    @teacher_email VARCHAR(50)
AS
    IF @teacher_email IS NULL
        PRINT 'Null Input'
    ELSE
        SELECT e.*
        FROM Enrolled_Students e
                INNER JOIN Attends a ON e.ssn = a.enrolled_ssn
                INNER JOIN Teaches t ON t.course_code = a.course_code
                WHERE t.teacher_email = @teacher_email
GO
--11
CREATE PROC StudentsWithoutActivities
AS
    SELECT ssn
    FROM Enrolled_Students
    WHERE ssn NOT IN(
        SELECT e.ssn
        FROM Enrolled_Students e
        INNER JOIN Activities_Applied_By_Enrolled_Students a ON e.ssn = a.ssn)
GO
--12
CREATE PROC StudentWithMostClubs
AS
    DECLARE @maxC INT
    SELECT @maxC = COUNT(*)
    FROM Enrolled_Students e INNER JOIN Clubs_Joined_By_Enrolled_Student c ON e.ssn = c.enrolled_ssn
    GROUP BY e.ssn, e.name
    SELECT e.name
    FROM Enrolled_Students e INNER JOIN Clubs_Joined_By_Enrolled_Student c ON e.ssn = c.enrolled_ssn

GO


--working insertions!!








INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('ST.Fatima@edu.eg',0,1,'02479106910','Heliopolice','ST.Fatima',1,1,0);
INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('ST.George@edu.eg',1,0,'02967910610','Shubra','ST.George',1,0,0);
INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('ELNozha@edu.eg',1,0,'021448323','Hegaz','EL Nozha',0,0,1);
INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('NewRamces@edu.eg',1,0,'0248825101','Heliopolice','New Ramces',0,0,1);
INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('Future@edu.eg',1,0,'029855952','Ramsees','Future',0,1,0);
INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('Karma@edu.eg',1,1,'029943119','5th settlement','Karma',1,1,1);
INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('Modern@edu.eg',0,1,'0223418103','Shubra','Modern',1,1,1);
INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('MSA@edu.eg',1,0,'027786922','Heliopolice','MSA',0,1,1);
INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('Rajak@edu.eg',0,1,'0210576831','Heliopolice','Rajak',0,0,1);
INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('ST.John@edu.eg',0,1,'0251610184','Shubra','St.John',1,0,1);
INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('Elite@edu.eg',1,0,'029923627','Heliopolice','Elite',0,0,1);
INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('Tabre@edu.eg',1,0,'022238343','5th settlement','Tabre',1,1,0);
INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('Sacrecoeur@edu.eg',1,1,'0210237531','Heliopolice','Sacre coeur',1,0,0);
INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
VALUES('Shwyfat@edu.eg',1,0,'0251313104','Shubra','Shwyfat',0,1,0);
GO



--SELECT * FROM Schools
---------------------------------
INSERT INTO Elementary_Supplies(supply, email)
VALUES('supplies', 'Sacrecoeur@edu.eg');
INSERT INTO Elementary_Supplies(supply, email)
VALUES('some_fries', 'Sacrecoeur@edu.eg');
INSERT INTO Elementary_Supplies(supply, email)
VALUES('some_fries', 'Tabre@edu.eg');
INSERT INTO Elementary_Supplies(supply, email)
VALUES('sunrise', 'Karma@edu.eg');
INSERT INTO Elementary_Supplies(supply, email)
VALUES('all_rise', 'ST.Fatima@edu.eg');
GO
---------------------------------
INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
VALUES('maged.awad@MSA.edu.eg','maged','awad','maged.awad','mego')
INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
VALUES('sherif.hanna@ST.Fatima.edu.eg','sherif','hanna','sherif.hanna','shico')
INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
VALUES('slim.abdel@ST.John.edu.eg','slim','abdel','slim.abdel','ballabizo')
INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
VALUES('aury.abdo@Shwyfat.edu.eg','aury','abdo','aury.abdo','ooory')
INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
VALUES('mira.ibrahim@Tabre.edu.eg','mira','ibrahim','mira.ibrahim','miiiraaa')
INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
VALUES('william.kaizar@ST.Fatima.edu.eg','william','kaizar','william.kaizar','wilo')
INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
VALUES('guirguis.samir@ST.George.edu.eg','guirguis','samir','guirguis.samir','gogo')
INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
VALUES('caren.miky@ST.Fatima.edu.eg','caren','miky','caren.miky','koko')
INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
VALUES('shady.azoz@MSA.edu.eg','shady','azoz','shady.azoz','shodz')
GO




--EXEC teachersOfChildren 'shady.azoz@MSA.edu.eg'



--SELECT * FROM Applicants_Applies_Schools a WHERE a.parent_email = 'william.kaizar@ST.Fatima.edu.eg' AND a.accepted = '1'
--GO
-----------------------------------------
INSERT INTO Parents_Mobiles(mobile , email)
VALUES(012,'maged.awad@MSA.edu.eg');
INSERT INTO Parents_Mobiles(mobile , email)
VALUES(011,'aury.abdo@Shwyfat.edu.eg');
INSERT INTO Parents_Mobiles(mobile , email)
VALUES(010,'william.kaizar@ST.Fatima.edu.eg');
INSERT INTO Parents_Mobiles(mobile , email)
VALUES(0122,'shady.azoz@MSA.edu.eg');
GO
-----------------------------------------
INSERT INTO Applicants(ssn,name,parent_email)
VALUES(1234,'david','maged.awad@MSA.edu.eg')
INSERT INTO Applicants(ssn,name,parent_email)
VALUES(12345,'peter','sherif.hanna@ST.Fatima.edu.eg')
INSERT INTO Applicants(ssn,name,parent_email)
VALUES(12346,'david','slim.abdel@ST.John.edu.eg')
INSERT INTO Applicants(ssn,name,parent_email)
VALUES(12347,'mina','william.kaizar@ST.Fatima.edu.eg')
INSERT INTO Applicants(ssn,name,parent_email)
VALUES(12348,'fadya','shady.azoz@MSA.edu.eg')
INSERT INTO Applicants(ssn,name,parent_email)
VALUES(12349,'sandra','guirguis.samir@ST.George.edu.eg')
INSERT INTO Applicants(ssn,name,parent_email)
VALUES(12342,'michael','aury.abdo@Shwyfat.edu.eg')
GO
----------------------------------------------
INSERT INTO Courses(code,name)
VALUES('CSEN501','databases')
INSERT INTO Courses(code,name)
VALUES('CSEN502','networks')
INSERT INTO Courses(code,name)
VALUES('CSEN503','theory')
INSERT INTO Courses(code,name)
VALUES('MATH501','math')
INSERT INTO Courses(code,name)
VALUES('CSEN601','dsd')
GO
-----------------------------------------------
INSERT INTO Employees (email , employee_user_name , employee_password)
VALUES ('aa' , 'aa' , 'aa')
INSERT INTO Employees (email , employee_user_name , employee_password , is_teacher , is_administrator)
VALUES ('bb' , 'bb' , 'bb' , 1 , 1)
INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
VALUES('ramy.mahfouz@ST.Fatima.edu.eg','ramy.mahfouz','rooooroooo','ramy','mahfouz','hanna',10000,'1996-1-1','ST.Fatima@edu.eg',0,0,1,NULL)
INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
VALUES('ashraf.hassan@ST.Fatima.edu.eg','ashraf.hassan','hoooosoooo','ashraf','hassan','ismail',5000,'2006-2-2','ST.John@edu.eg',1,0,0,'ramy.mahfouz@ST.Fatima.edu.eg')
INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
VALUES('kero.adel@ST.Fatima.edu.eg','kero.adel','kooookoooo','kero','adel','fahmy',5000,'2006-3-4','ST.Fatima@edu.eg',1,1,0,'ramy.mahfouz@ST.Fatima.edu.eg')
INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
VALUES('george.krahy@ST.Fatima.edu.eg','george.krahy','goooogoooo','george','krahy','kareem','5000','2006-5-6','ST.Fatima@edu.eg',1,0,0,'ramy.mahfouz@ST.Fatima.edu.eg')
INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
VALUES('michael.sherif@ST.Fatima.edu.eg','michael.sherif','moooomoooo','michael','sherif','hanna',10000,'1999-7-8','ST.Fatima@edu.eg',0,1,0,NULL)
INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
VALUES('teshtar@testar.com','teshtar.teshtar','teshhhhhh','teshtar','teshtaren','talata',10000,'1999-7-9','Tabre@edu.eg',0,1,0,NULL)
GO

--EXEC Teacher_View_His_Courses 'michael.sherif@ST.Fatima.edu.eg'

----------------------------------------------------------------------
--EXEC ViewUnverifiedTeachers 'kero.adel@ST.Fatima.edu.eg'

--EXEC Enrolled_Student_View_Courses 2355124;

--EXEC ViewUnverifiedTeachers 'ashraf.hassan@ST.Fatima.edu.eg'

--EXEC Enrolled_Students_Views_Assignments_Grades 2345124

INSERT INTO Enrolled_Students --school_email , parent_email
(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
VALUES (2345124,5,'7amada',0,'1995-1-2','3ayezArawwa7','Hamada','ST.Fatima@edu.eg','maged.awad@MSA.edu.eg')
INSERT INTO Enrolled_Students
(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
VALUES (2355124,5,'7amaden',0,'1995-2-2','3ayezArawwa7en','Hamaden','ST.Fatima@edu.eg','maged.awad@MSA.edu.eg') --e5wat
INSERT INTO Enrolled_Students --school_email , parent_email
(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
VALUES (2115124,5,'Samy',0,'1995-3-2','Mesh3ayezArawwa7','Samy','Rajak@edu.eg','slim.abdel@ST.John.edu.eg')
INSERT INTO Enrolled_Students --school_email , parent_email
(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
VALUES (2113524,5,'7ommos',0,'1996-3-2','Rawwa7t','Hommos','Shwyfat@edu.eg','shady.azoz@MSA.edu.eg')
INSERT INTO Enrolled_Students --school_email , parent_email
(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
VALUES (2115554,5,'7ommoseyya',0,'1996-4-2','Merwa7','Hommoseyya','Modern@edu.eg','shady.azoz@MSA.edu.eg')
GO
INSERT INTO Enrolled_Students
(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
VALUES (2115,5,'brens',0,'1996-4-2','bn','bns','ST.Fatima@edu.eg','shady.azoz@MSA.edu.eg')
INSERT INTO Enrolled_Students --school_email , parent_email
(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
VALUES (21,5,'brens2',0,'1995-3-2','b2','b2','ST.Fatima@edu.eg','slim.abdel@ST.John.edu.eg')
------------------------------------------------
INSERT INTO Courses_Prequisites
(course_code_1,course_code_2)
VALUES ('CSEN501','CSEN502')
GO
-------------------------------------------------
INSERT INTO Courses_Offered_By_Schools
(course_code , school_email)
VALUES ('CSEN601','ELNozha@edu.eg')
INSERT INTO Courses_Offered_By_Schools
(course_code , school_email)
VALUES ('CSEN601','ST.Fatima@edu.eg')
INSERT INTO Courses_Offered_By_Schools
(course_code , school_email)
VALUES ('MATH501','ST.Fatima@edu.eg')
GO
------------------------------------------------------------
INSERT INTO Activities --administrator_email , teacher_email , school_email
(activity_date,activity_location,activity_type,activity_description,administrator_email,teacher_email,school_email)
VALUES('2012-12-12','fel gam3a','trip','howwa keda','michael.sherif@ST.Fatima.edu.eg','kero.adel@ST.Fatima.edu.eg','ST.Fatima@edu.eg')
INSERT INTO Activities
(activity_date,activity_location,activity_type,activity_description,administrator_email,teacher_email,school_email)
VALUES('2012-11-11','barra el gam3a','trip','enter deskripshan heer','michael.sherif@ST.Fatima.edu.eg','george.krahy@ST.Fatima.edu.eg','ST.Fatima@edu.eg')
INSERT INTO Activities
(activity_date,activity_location,activity_type,activity_description,administrator_email,teacher_email,school_email)
VALUES('2012-10-10','ganb el gam3a','trip','kaman re7la wmalo','michael.sherif@ST.Fatima.edu.eg','kero.adel@ST.Fatima.edu.eg','ST.Fatima@edu.eg')
GO
-----------------------------------------------------------------------------
INSERT INTO Equipment --activity_date activity_location men Activities
(activity_date,activity_location,equipment)
VALUES ('2012-12-12','fel gam3a','3asaya')
INSERT INTO Equipment
(activity_date,activity_location,equipment)
VALUES ('2012-11-11','barra el gam3a','kora')
INSERT INTO Equipment --activity_date activity_location men Activities
(activity_date,activity_location,equipment)
VALUES ('2012-12-12','fel gam3a','gardal')
INSERT INTO Equipment --activity_date activity_location men Activities
(activity_date,activity_location,equipment)
VALUES ('2012-11-11','barra el gam3a','7anafeyya')
INSERT INTO Equipment --activity_date activity_location men Activities
(activity_date,activity_location,equipment)
VALUES ('2012-10-10','ganb el gam3a','mayya ma3daneyya')
INSERT INTO Equipment --activity_date activity_location men Activities
(activity_date,activity_location,equipment)
VALUES ('2012-10-10','ganb el gam3a','far5a zengeyya')
GO
----------------------------------------------------------
INSERT INTO Clubs
(name , purpose) VALUES ('MUNday','unknown')
INSERT INTO Clubs
(name , purpose) VALUES ('TImon','play mohaa')
INSERT INTO Clubs
(name , purpose) VALUES ('IEEEEEEEEE ya rasy','eat circuits for breakfast!')
INSERT INTO Clubs
(name , purpose) VALUES ('3ashan 5atrik ya gam3ety','ne3mel 7aga')
GO
------------------------------------------------------------
INSERT INTO Clubs_Offered_By_Schools
(name , school_email)
VALUES ('MUNday','ST.John@edu.eg')
INSERT INTO Clubs_Offered_By_Schools
(name , school_email)
VALUES ('TImon','Tabre@edu.eg')
INSERT INTO Clubs_Offered_By_Schools
(name , school_email)
VALUES ('TImon','Shwyfat@edu.eg')
INSERT INTO Clubs_Offered_By_Schools
(name , school_email)
VALUES ('3ashan 5atrik ya gam3ety','Tabre@edu.eg')
INSERT INTO Clubs_Offered_By_Schools
(name , school_email)
VALUES ('IEEEEEEEEE ya rasy','Shwyfat@edu.eg')

INSERT INTO Clubs_Offered_By_Schools
(name , school_email)
VALUES ('IEEEEEEEEE ya rasy','Modern@edu.eg')
INSERT INTO Clubs_Offered_By_Schools
(name , school_email)
VALUES ('IEEEEEEEEE ya rasy','Rajak@edu.eg')
GO
----------------------------------------------------------------
INSERT INTO Questions --course_code , askers_ssn
(course_code,question_itself,askers_ssn)
VALUES ('CSEN601','E7na leh bna5od el course dah?',2345124 )
INSERT INTO Questions
(course_code,question_itself,askers_ssn)
VALUES ('CSEN601','Ya3ny eh insert?',2355124 )
INSERT INTO Questions
(course_code,question_itself,askers_ssn)
VALUES ('CSEN601','Ya3ny eh delete?',2115124 )
INSERT INTO Questions
(course_code,question_itself,askers_ssn)
VALUES ('MATH501','Ya3ny eh logical sequence?',2345124 )
GO
--------------------------------------------------------
INSERT INTO Assignments --course_code , teacher_email
(assignment_number,course_code,teacher_email,posting_date,due_date,content)
VALUES (4,'CSEN601','kero.adel@ST.Fatima.edu.eg','2012-3-18','2012-3-19','e3mel database')
INSERT INTO Assignments
(assignment_number,course_code,teacher_email,posting_date,due_date,content)
VALUES (9,'CSEN601','kero.adel@ST.Fatima.edu.eg','2012-4-18','2012-4-19','e3mel social network')
INSERT INTO Assignments
(assignment_number,course_code,teacher_email,posting_date,due_date,content)
VALUES (900,'MATH501','michael.sherif@ST.Fatima.edu.eg','2012-5-18','2012-5-19','proove that the implication aslaha erd')
GO
------------------------------------------------------------------------
INSERT INTO Announcements --administrator_email men Employees , school_email
(administrator_email , announcement_date , announcement_time , announcement_type , title , announcement_description , school_email)
VALUES ('ashraf.hassan@ST.Fatima.edu.eg','2010-3-3','10:10','trip','el trip','hanroo7 henak','MSA@edu.eg')
INSERT INTO Announcements
(administrator_email , announcement_date , announcement_time , announcement_type , title , announcement_description , school_email)
VALUES ('george.krahy@ST.Fatima.edu.eg','2010-3-4','10:10','trip','trip tanya','hanroo7 henak bardo','Future@edu.eg')
INSERT INTO Announcements
(administrator_email , announcement_date , announcement_time , announcement_type , title , announcement_description , school_email)
VALUES ('kero.adel@ST.Fatima.edu.eg','2010-3-5','10:10','trip talta','fee akl','hanroo7 henak tany','Karma@edu.eg')
INSERT INTO Announcements
(administrator_email , announcement_date , announcement_time , announcement_type , title , announcement_description , school_email)
VALUES ('george.krahy@ST.Fatima.edu.eg','2016-11-28','11:10','trip taltaNEW','fee akl','hanroo7 henak tany','ST.Fatima@edu.eg') --change date to be visible
GO
---
INSERT INTO Announcements_Visible_To_Enrolled_Students
(administrator_email , announcement_date , announcement_time , enrolled_ssn)
VALUES ('ashraf.hassan@ST.Fatima.edu.eg','03-Mar-10','10:10:00',2355124)
INSERT INTO Announcements_Visible_To_Enrolled_Students
(administrator_email , announcement_date , announcement_time , enrolled_ssn)
VALUES ('george.krahy@ST.Fatima.edu.eg','04-Mar-10','10:10:00',2345124)
INSERT INTO Announcements_Visible_To_Enrolled_Students
(administrator_email , announcement_date , announcement_time , enrolled_ssn)
VALUES ('george.krahy@ST.Fatima.edu.eg','2016-11-28','11:10:00',2345124) --change date to be visible
GO
--SELECT * FROM Announcements
---------------------------------------------------------------------------
INSERT INTO Monthly_Reports --enrolled_ssn men Enrolled_student , writer men Employees
(enrolled_ssn , issue_date , comment , writer)
VALUES (2113524 , '2011-12-28' , 'good student' , 'michael.sherif@ST.Fatima.edu.eg')
INSERT INTO Monthly_Reports
(enrolled_ssn , issue_date , comment , writer)
VALUES (2115124 , '2011-12-18' , 'good student' , 'michael.sherif@ST.Fatima.edu.eg')
INSERT INTO Monthly_Reports
(enrolled_ssn , issue_date , comment , writer)
VALUES (2355124 , '2012-4-28' , 'bad student' , 'michael.sherif@ST.Fatima.edu.eg')
GO
--------------------------------------------------------------
INSERT INTO Teaches
(teacher_email , course_code)
VALUES ('kero.adel@ST.Fatima.edu.eg','CSEN503')
INSERT INTO Teaches
(teacher_email , course_code)
VALUES ('kero.adel@ST.Fatima.edu.eg','CSEN601')
INSERT INTO Teaches
(teacher_email , course_code)
VALUES ('michael.sherif@ST.Fatima.edu.eg','MATH501')
GO

------------------------------------------------
INSERT INTO Attends
(enrolled_ssn , course_code)
VALUES (2355124,'MATH501')
INSERT INTO Attends
(enrolled_ssn , course_code)
VALUES (2355124,'CSEN601')
INSERT INTO Attends
(enrolled_ssn , course_code)
VALUES (2115124,'MATH501')
INSERT INTO Attends
(enrolled_ssn , course_code)
VALUES (2115554,'MATH501')
INSERT INTO Attends
(enrolled_ssn , course_code)
VALUES (2115554,'CSEN601')
INSERT INTO Attends
(enrolled_ssn , course_code)
VALUES (2113524,'MATH501')
GO
------------------------------------------------------
INSERT INTO Clubs_Joined_By_Enrolled_Student
(club_name , enrolled_ssn)
VALUES ('IEEEEEEEEE ya rasy' , 2115124)
INSERT INTO Clubs_Joined_By_Enrolled_Student
(club_name , enrolled_ssn)
VALUES ('3ashan 5atrik ya gam3ety' , 2115124)
INSERT INTO Clubs_Joined_By_Enrolled_Student
(club_name , enrolled_ssn)
VALUES ('3ashan 5atrik ya gam3ety' , 2345124)
INSERT INTO Clubs_Joined_By_Enrolled_Student
(club_name , enrolled_ssn)
VALUES ('MUNday' , 2345124)
INSERT INTO Clubs_Joined_By_Enrolled_Student
(club_name , enrolled_ssn)
VALUES ('TImon' , 2345124)
GO


INSERT INTO Monthly_Reports_Replies
(enrolled_ssn , issue_date , parent_email , reply)
VALUES (2113524 , '28-Dec-11' , 'mira.ibrahim@Tabre.edu.eg' , 'thank you veery mutsh')
GO


INSERT INTO Enrolled_Students_View_Questions
(ssn , question_number , course_code)
VALUES (2355124 , 2 , 'CSEN601')
GO

INSERT INTO Teachers_Response_To_Questions
(question_number , course_code , teacher_email)
VALUES (1 , 'CSEN601' , 'teshtar@testar.com')
GO

INSERT INTO Parents_Ratings_To_Teachers
(employee_email , parent_email , rating)
VALUES ('michael.sherif@ST.Fatima.edu.eg', 'shady.azoz@MSA.edu.eg', 2)
INSERT INTO Parents_Ratings_To_Teachers
(employee_email , parent_email , rating)
VALUES ('michael.sherif@ST.Fatima.edu.eg', 'maged.awad@MSA.edu.eg', 2)
INSERT INTO Parents_Ratings_To_Teachers
(employee_email , parent_email , rating)
VALUES ('michael.sherif@ST.Fatima.edu.eg', 'slim.abdel@ST.John.edu.eg', 1)

INSERT INTO Announcements_Visible_To_Parents
(administrator_email , announcement_date , announcement_time , parent_email)
VALUES ('ashraf.hassan@ST.Fatima.edu.eg','2010-3-3','10:10', 'shady.azoz@MSA.edu.eg')
GO

INSERT INTO View_And_Grade_Assignments
(viewer_email , assignment_number , course_code , creator_email)
VALUES ('teshtar@testar.com' , 9 , 'CSEN601' , 'kero.adel@ST.Fatima.edu.eg')
GO

INSERT INTO Assignments_Solved_By_Students
(ssn , assignment_number , teacher_email , course_code , solution)
VALUES (2345124 , 9 , 'kero.adel@ST.Fatima.edu.eg' , 'CSEN601' , 'bottom implies top')
GO

INSERT INTO Activities_Applied_By_Enrolled_Students
(activity_date , activity_location , ssn)
VALUES ('2012-12-12' , 'fel gam3a' , 2115124)
GO

INSERT INTO Courses_Offered_By_Schools
(course_code , school_email)
VALUES ('MATH501','Shwyfat@edu.eg')
GO

INSERT INTO Applicants_Applies_Schools
(school_email , child_ssn , accepted , parent_email)
VALUES ('Shwyfat@edu.eg' , 12345 , 0 , 'sherif.hanna@ST.Fatima.edu.eg')
GO






--proc executions
/*


--@ssn_in INT,
--@name_in VARCHAR(50),
--@gender_in BIT,
--@birth_date_in DATE,
--@password_in VARCHAR(20),
--@school_email_in VARCHAR(50),
--@parent_email_in VARCHAR(50)


SELECT * FROM Enrolled_Students
EXEC Enrolled_Student_Info_Update  2113524 , 'Defet' , '0' , '1995-2-2' , 'mafeeshpasswerd' , 'Rajak@edu.eg' , 'shady.azoz@MSA.edu.eg'

EXEC Enrolled_Student_View_Courses 2345124
EXEC Enrolled_Student_View_Courses 2355124

EXEC Enrolled_Student_Post_Question 'MATH501' , '3ashan se2et?' , 2355124
SELECT * FROM Questions

EXEC Enrolled_Student_View_Questions 'MATH501' , 2355124

EXEC Enrolled_Student_View_Questions 'CSEN601' , 2355124

--proc 4 far2 el "e"

EXEC Enrolled_Student_View_All_Assignments 2355124
SELECT * FROM Assignments

EXEC Enrolled_Student_Solves_Assignment 2355124 , 4 , 'kero.adel@ST.Fatima.edu.eg' , 'CSEN601' , 'boss enta 5od el quiz 7otto fel 5allast yetla3lak social network agmad men facebook'
SELECT * FROM Assignments_Solved_By_Students

EXEC Enrolled_Students_Views_Assignments_Grades 2355124


PRINT DATEDIFF(DAY,'11-27-16','11-28-16')

EXEC Enrolled_Student_Views_Announcements5 2345124

SELECT * FROM Announcements
*/





--USE dec3h
--GO




--PHP PROCEDURES

CREATE PROC AdminLogIn
@unamein VARCHAR(25),
@passin VARCHAR(25)
AS
    SELECT *
    FROM Employees e
    WHERE e.employee_user_name = @unamein AND e.employee_password = @passin AND e.is_administrator = '1'
GO

CREATE PROC StudentLogIn
@unamein VARCHAR(25),
@passin VARCHAR(25)
AS
    SELECT *
    FROM Enrolled_Students e
    WHERE e.student_user_name = @unamein AND e.student_password = @passin
GO

CREATE PROC ParentLogIn
@unamein VARCHAR(25),
@passin VARCHAR(25)
AS
    SELECT *
    FROM Parents e
    WHERE e.parent_user_name = @unamein AND e.parent_password = @passin
GO


CREATE PROC TeacherLogIn
@unamein VARCHAR(25),
@passin VARCHAR(25)
AS
    SELECT *
    FROM Employees e
    WHERE e.employee_user_name = @unamein AND e.employee_password = @passin AND e.is_teacher = '1'
GO

--USE dec3h
--GO

--EXEC TeacherLogIn "bb" , "bb"

CREATE PROC TeacherSignUp
@unamein VARCHAR(25),
@passin VARCHAR(25)
AS
    SELECT first_name , email
    FROM Employees e
    WHERE e.employee_user_name = @unamein AND e.employee_password = @passin AND e.is_administrator = '1'
GO

--EXEC ParentLogIn "aa" , "aa"

--USE dec3h
--GO

--SELECT * FROM Enrolled_Students

INSERT INTO Enrolled_Students
(ssn , student_user_name , student_password)
VALUES(12341234 , 'aa' , 'aa')
INSERT INTO Enrolled_Students --school_email , parent_email
(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
VALUES (211555,5,'7ommoseyya',0,'1996-4-2','bb','bb','Modern@edu.eg','shady.azoz@MSA.edu.eg')
GO

--SELECT * FROM Employees

--SELECT * FROM Parents

--SELECT * FROM

--Teacher_Sign_Up

--EXEC InsertParents "ASDF","asdf","asfd","ads","adsf","asdf","asdf"

--EXEC InsertParents "aa","aa","aa","aa","aa","aa","aa"

--EXEC Enrolled_Student_Info_Update 12341234 ,

--InsertParents


/*CREATE PROC ParentSignUp
@unamein VARCHAR(25),
@passin VARCHAR(25),
@email_in VARCHAR(50),
@address_in VARCHAR(100),
@fname_in VARCHAR(25),
@lname_in VARCHAR(25),
@phone_in VARCHAR(20)
AS
    SELECT first_name , email
    FROM Employees e
    WHERE e.employee_user_name = @unamein AND e.employee_password = @passin AND e.is_administrator = '1'
GO*/

--SELECT * FROM Employees

--EXEC AdminLogIn "ashraf.hassan" , "hoooosoooo"



--SELECT *
--FROM Employees e
--WHERE e.is_verified = '0' AND e.is_teacher = '1'









CREATE PROC ES_View_Info
@ssn_in INT
AS
    SELECT *
    FROM Enrolled_Students es
    WHERE es.ssn = @ssn_in
GO
--THEN USE
--EXEC Enrolled_Student_Info_Update
/*
@ssn_in INT,
@name_in VARCHAR(50),
@gender_in BIT,
@birth_date_in DATE,
@password_in VARCHAR(20),
@school_email_in VARCHAR(50),
@parent_email_in VARCHAR(50)
*/




-- Enrolled_Student_View_Courses
--@ssn_in INT


--ok

--ok

--ok













--EXEC sp_who2
/*
USE master
GO
ALTER DATABASE dec3e
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

DROP DATABASE dec3e*/










--mikel dec5

CREATE PROC ViewSchools
@school_name VARCHAR(50),
@school_address VARCHAR(100),
@school_type bit
AS
    IF  @school_name IS NULL AND
        @school_address IS NULL AND
        @school_type IS NULL
        Print 'Null Input'
    ELSE
        IF @school_name IS NOT NULL
        BEGIN
            SELECT *
            FROM Schools
            WHERE @school_name IS NOT NULL AND name = @school_name
            RETURN
        END
        IF @school_address IS NOT NULL
        BEGIN
            SELECT *
            FROM Schools
            WHERE @school_address IS NOT NULL AND school_address = @school_address
            RETURN
        END
        IF @school_type IS NOT NULL
        BEGIN
            SELECT *
            FROM Schools
            WHERE @school_type IS NOT NULL AND is_international = @school_type
            RETURN
        END
GO


--exec ViewSchools 'Elite', Null, 1

CREATE PROC SchoolReviews
    @email VARCHAR(50)
AS
    SELECT *
    FROM Parents_Write_Review_Schools
    WHERE school_email = @email
GO

CREATE PROC SchoolAnonucements
    @email VARCHAR(50)
AS
    SELECT *
    FROM dbo.Announcements
    WHERE school_email = @email
GO
CREATE FUNCTION GetStudentSSN(@username VARCHAR(20), @pass VARCHAR(20))
    RETURNS INT
BEGIN
    DECLARE @returnedValue INT
    SET @returnedValue = -1
        SELECT @returnedValue = ssn
        FROM Enrolled_Students
        WHERE student_user_name = @username AND student_password = @pass
    RETURN @returnedValue
END
GO

CREATE FUNCTION GetParentEmail(@username VARCHAR(20), @pass VARCHAR(20))
    RETURNS VARCHAR(50)
BEGIN
    DECLARE @returnedValue VARCHAR(50)
    SET @returnedValue = ''
        SELECT @returnedValue = email
        FROM Parents
        WHERE parent_user_name = @username AND parent_password = @pass
    RETURN @returnedValue
END
GO

CREATE FUNCTION GetEmployeeEmail(@username VARCHAR(20), @pass VARCHAR(20), @isAdmin BIT)
    RETURNS VARCHAR(50)
BEGIN
    DECLARE @returnedValue VARCHAR(50)
    SET @returnedValue = ''
        IF @isAdmin = 1
            SELECT @returnedValue = email
            FROM Employees
            WHERE employee_user_name = @username AND employee_password = @pass AND is_administrator = 1
        ELSE
            SELECT @returnedValue = email
            FROM Employees
            WHERE employee_user_name = @username AND employee_password = @pass AND is_administrator = 0
    RETURN @returnedValue
END
GO

CREATE PROC ViewUnverifiedTeachers
@admin_email VARCHAR(50)
AS
    IF
        @admin_email IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin BIT
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
        IF @isAdmin = 1
            SELECT *
            FROM Employees e
            WHERE e.is_teacher = 1 AND e.school_email = @school_email AND employee_password IS NULL AND employee_user_name IS NULL
        ELSE
            PRINT 'Not Admin'
GO

CREATE FUNCTION CheckAdminExists(@username VARCHAR(20), @pass VARCHAR(20), @email VARCHAR(50))
    RETURNS BIT
BEGIN
    DECLARE @returnedValue BIT
    SET @returnedValue = 0
        IF EXISTS
        (
            SELECT *
            FROM Employees
            WHERE employee_user_name = @username AND employee_password = @pass AND email = @email AND is_administrator = 1
        )
        SET @returnedValue = 1
    RETURN @returnedValue
END
GO
CREATE FUNCTION CheckTeacherExists(@username VARCHAR(20), @pass VARCHAR(20), @email VARCHAR(50))
    RETURNS BIT
BEGIN
    DECLARE @returnedValue BIT
    SET @returnedValue = 0
        IF EXISTS
        (
            SELECT *
            FROM Employees
            WHERE employee_user_name = @username AND employee_password = @pass AND email = @email AND is_teacher = 1
        )
        SET @returnedValue = 1
    RETURN @returnedValue
END
GO
CREATE FUNCTION CheckParentExists(@username VARCHAR(20), @pass VARCHAR(20), @email VARCHAR(50))
    RETURNS BIT
BEGIN
    DECLARE @returnedValue BIT
    SET @returnedValue = 0
        IF EXISTS
        (
            SELECT *
            FROM Parents
            WHERE parent_user_name = @username AND parent_password = @pass AND email = @email
        )
        SET @returnedValue = 1
    RETURN @returnedValue
END
GO
CREATE FUNCTION CheckStudentExists(@username VARCHAR(20), @pass VARCHAR(20), @ssn INT)
    RETURNS BIT
BEGIN
    DECLARE @returnedValue BIT
    SET @returnedValue = 0
        IF EXISTS
        (
            SELECT *
            FROM Enrolled_Students
            WHERE student_user_name = @username AND student_password = @pass AND ssn = @ssn
        )
        SET @returnedValue = 1
    RETURN @returnedValue
END
GO

CREATE PROC ViewStudentApplications
@admin_email VARCHAR(50)
AS
    IF
        @admin_email IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin BIT
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
        IF @isAdmin = 1
            SELECT *
            FROM Applicants_Applies_Schools
            WHERE school_email = @school_email AND accepted = 0
        ELSE
            PRINT 'Not Admin'
GO


CREATE PROC ViewUnverifiedStudents
@admin_email VARCHAR(50)
AS
    IF
        @admin_email IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin BIT
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
        IF @isAdmin = 1
            SELECT *
            FROM Enrolled_Students
            WHERE school_email = @school_email AND student_password IS NULL AND student_user_name IS NULL
        ELSE
            PRINT 'Not Admin'
GO

CREATE PROC ViewActivities
@admin_email VARCHAR(50)
AS
    IF
        @admin_email IS NULL
        print 'Null Input'
    ELSE
        DECLARE @isAdmin BIT
        DECLARE @school_email VARCHAR(50)
        SELECT @isAdmin = is_administrator, @school_email = e.school_email
        FROM Employees e
        WHERE e.email = @admin_email
        IF @isAdmin = 1
            SELECT *
            FROM Activities
            WHERE administrator_email = @admin_email
        ELSE
            PRINT 'Not Admin'
GO


CREATE PROC tstasdfa
@teacher_email VARCHAR(50)
AS
    SELECT *
    FROM Enrolled_Students e
    INNER JOIN Attends a ON (e.ssn = a.enrolled_ssn)
    WHERE a.course_code IN
        (SELECT course_code
        FROM Teaches
        WHERE teacher_email = @teacher_email)
GO
/*
CREATE TABLE Employees
(
    email VARCHAR(50) PRIMARY KEY,
    birth_date DATE,
    employee_user_name VARCHAR(25) UNIQUE,
    employee_password VARCHAR(25),
    first_name VARCHAR(25),
    middle_name VARCHAR(25),
    last_name VARCHAR(25),
    gender BIT,
    employee_address VARCHAR(100),
    salary FLOAT,
    enrollment_year DATE,
    years_of_experience as (year(current_timestamp) - year(enrollment_year)),
    is_administrator BIT NOT NULL DEFAULT '0',
    is_teacher BIT NOT NULL DEFAULT '0',
    is_supervisor BIT NOT NULL DEFAULT '0',
    is_verified BIT NOT NULL DEFAULT '0',
    school_email VARCHAR(50),
    supervisor_email VARCHAR(50),
*/

--EXEC TeacherAvgRating 'michael.sherif@ST.Fatima.edu.eg'

--heyya dih!
CREATE FUNCTION TAvgR(@t VARCHAR(50)) RETURNS FLOAT
AS BEGIN
    DECLARE @total FLOAT
    DECLARE @c FLOAT
    DECLARE @AvgRate FLOAT
    SELECT @total = SUM(pt.rating) , @c = COUNT(pt.rating)
    FROM Parents_Ratings_To_Teachers pt
    WHERE @t = pt.employee_email
    SET @AvgRate = @total/@c
    RETURN @AvgRate
END
GO

--SELECT * FROM Parents_Ratings_To_Teachers

--SELECT dbo.TAvgR('michael.sherif@ST.Fatima.edu.eg')


--EXEC teachersOfChildren 'maged.awad@MSA.edu.eg'

CREATE PROC teachersOfChildren
@parent_email VARCHAR(50)
AS
    SELECT es.first_name , es.middle_name , es.last_name , es.email , es.gender , es.years_of_experience , dbo.TAvgR(es.email)
    FROM Attends a
    INNER JOIN Teaches b ON b.course_code = a.course_code
    INNER JOIN Employees es ON b.teacher_email = es.email
    WHERE es.is_teacher = '1'
    AND EXISTS
    (
        SELECT *
        FROM Enrolled_Students ess
        WHERE ess.parent_email = @parent_email
        AND ess.ssn = a.enrolled_ssn
    )
GO

CREATE PROC SchoolsOfChildren
@parent_email VARCHAR(50)
AS
    SELECT s.name , s.email
    FROM Schools s
    INNER JOIN Enrolled_Students es ON es.school_email = s.email
    AND EXISTS(
        SELECT *
        FROM Enrolled_Students ess
        WHERE ess.parent_email = @parent_email
        AND ess.ssn = es.ssn
    )
GO

--EXEC SchoolsOfChildren 'shady.azoz@MSA.edu.eg'

CREATE PROC aa
@parent_email_in VARCHAR(50)
AS
    SELECT *
    FROM Applicants_Applies_Schools a
    WHERE a.parent_email = @parent_email_in AND a.accepted = '1'
GO

--EXEC aa 'maged.awad@MSA.edu.eg'


CREATE PROC ab
@ssn_in INT
AS
    SELECT name
    FROM Enrolled_Students
    WHERE ssn = @ssn_in

    UNION

    SELECT name
    FROM Applicants
    WHERE ssn = @ssn_in
GO


CREATE PROC ViewParentReviews
@parent_email_in VARCHAR(50)
AS
    SELECT school_email , reviews
    FROM Parents_Write_Review_Schools
    WHERE parent_email = @parent_email_in
GO

--EXEC ViewParentReviews 'shady.azoz@MSA.edu.eg'


/*  parent_email VARCHAR(50),
    school_email VARCHAR(50),
    reviews VARCHAR(200),*/

CREATE PROC deleteReview
@parent_email_in VARCHAR(50),
@school_email_in VARCHAR(50)
AS
    DELETE FROM Parents_Write_Review_Schools
    WHERE parent_email = @parent_email_in AND school_email = @school_email_in
GO

--select * from Enrolled_Students where 2345124 = ssn



--select * from Assignments where course_code = 'CSEN601'


--SELECT * FROM Schools WHERE email IN (SELECT school_email FROM Employees WHERE email = 'bb')



CREATE PROC CheckHighSchool
@ssn_in INT
AS
    SELECT y.is_high
    FROM Enrolled_Students x
    INNER JOIN Schools y ON x.school_email = y.email AND x.ssn = 2345124
GO

/*
SELECT *
FROM Enrolled_Students z
WHERE EXISTS
(
    SELECT y.is_high
    FROM Enrolled_Students x
    INNER JOIN Schools y ON x.school_email = y.email AND x.ssn = z.ssn
    WHERE y.is_high = '1'
)*/




--SELECT * FROM Clubs_Offered_By_Schools
--SELECT * FROM Enrolled_Students
























--mo2akkatan
--USE dec3h
--GO



--EXEC Enrolled_Student_Info_Update 12341234 , "lkjlk" , 0 , "2016-2-5" , "aa" , "Modern@edu.eg" , 'maged.awad@MSA.edu.eg'


--EXEC Enrolled_Student_Info_Update 12341234 , "aaq" , 1 , "2011-2-2" , "aa" , "Modern@edu.eg" , "maged.awad@MSA.edu.eg"



--EXEC Enrolled_Student_View_Courses





CREATE PROC ES_View_School_Clubs
@ssn_in INT
AS
    SELECT c.*
    FROM Clubs c
    INNER JOIN Clubs_Offered_By_Schools co ON c.name = co.name
    WHERE EXISTS
    (
        SELECT *
        FROM Enrolled_Students ess
        WHERE ess.ssn = @ssn_in AND ess.school_email = co.school_email
    )
GO

--EXEC ES_View_School_Clubs 2113524

--EXEC Enrolled_Student_Join_Club 211555 , "TImon"

--SELECT * FROM Clubs_Joined_By_Enrolled_Student

--SELECT * FROM Enrolled_Students

/*UPDATE Enrolled_Students
SET student_level = 10
WHERE student_level = 5*/



--EXEC AdminLogIn "bb" , "bb"

--EXEC AdminLogIn "teshtar.teshtar" , "teshhhhhh"

--EXEC ViewUnverifiedTeachers "george.krahy@ST.Fatima.edu.eg"

--SELECT * FROM Employees
--GO

CREATE PROC AdminVerifiesTeacher
@teacher_mail_in VARCHAR(50)
AS
    UPDATE Employees
    SET is_verified = '1'
    WHERE email = @teacher_mail_in
GO

--EXEC AdminVerifiesTeacher 'bb'


--EXEC Enrolled_Student_View_Questions 'CSEN601', 2355124

--EXEC CreateActivity "ashraf.hassan@ST.Fatima.edu.eg" , "2016-3-3" , "ssasf" , "ashraf.hassan@ST.Fatima.edu.eg" , "add" , "sdsd" , "df"

--SELECT * FROM Activities

--SELECT * FROM Parents






--USE dec3h
--GO