CREATE DATABASE _93aa_ksig;
USE _93aa_ksig;
CREATE TABLE students(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
address VARCHAR(50) NOT NULL,
egn VARCHAR (10) NOT NULL UNIQUE,
phone VARCHAR(10) NULL DEFAULT NULL,
class VARCHAR(5) NULL DEFAULT NULL
);
INSERT INTO students(name, egn, address, phone, class)
VALUES
('Ilyan Ivanov', '9401150045', 'Sofia-Mladost', '0895345123', '10'),
('Ivan Iliev', '9510104512', 'Sofia-Lulin', '0896786453', '11'),
('Elena Petrovs', '9505052154', 'Sofia-Mladost2', '0899123456', '11'),
('Ivan Iliev', '9510104542', 'Sofia-Mladost3', '0897123456', '11'),
('Maria Dimova', '9510104547', 'Sofia-Mladost4', '088898654', '11'),
('Antoaneta Ivanova', '9411104547', 'Sofia-Krasno selo', '0877123456', '10');

CREATE TABLE sports(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
INSERT INTO sports
VALUES
(NULL, 'football'),
(NULL, 'voleyball');

CREATE  TABLE coaches(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
egn VARCHAR (10) NOT NULL UNIQUE
);
INSERT INTO coaches(name, egn)
VALUES
('Ivan Petrov', '75090412'),
('Petyr Dimitrov', '2345678901'),
('Kaloyan Kolev', '3456789012');

CREATE TABLE sportGroups(
id INT AUTO_INCREMENT PRIMARY KEY,
location VARCHAR(50) NOT NULL,
dayOfWeek ENUM('Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat', 'Sun'),
hourOfTraining TIME NOT NULL,
sport_id INT NOT NULL,
CONSTRAINT FOREIGN KEY (sport_id) REFERENCES sports(id),
coach_id INT NOT NULL,
CONSTRAINT FOREIGN KEY(coach_id) REFERENCES coaches(id),
UNIQUE KEY (location, dayOfWeek,  hourOfTraining)
);
INSERT INTO sportGroups(location, dayOfWeek, hourOfTraining, sport_id, coach_id)
VALUES
('Sofia-Mladost1', 'Mon', '8:00:00', 1, 1),
('Sofia-Mladost1', 'Mon', '9:00:00', 1, 2),
('Sofia-Lulin', 'Sun', '8:00:00', 2, 3),
('Sofia-Lulin', 'Sun', '9:30:00', 2, 2),
('Plovdiv', 'Mon', '12:00:00', 1, 1);

CREATE TABLE sportgr_student(
student_id INT NOT NULL,
CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(id),
sportgr_id INT NOT NULL,
CONSTRAINT FOREIGN KEY (sportgr_id) REFERENCES sportgroups(id),
PRIMARY KEY (student_id, sportgr_id)
);
INSERT INTO sportgr_student
VALUES
(1,1),
(2,1),
(3,2),
(4,2),
(5,1),
(6,2);

CREATE TABLE programmers(
id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    startWorkingDate DATE,/**YYYY-MM-DD**/
    teamLead_id INT NULL DEFAULT NULL,
    CONSTRAINT FOREIGN KEY (teamLead_id) REFERENCES programmers(id)
);

INSERT INTO programmers (name, address, startWorkingDate, teamLead_id)
VALUES
('Ivan Ivanov', 'Sofia', '1999-05-25', NULL),
('Georgi Petkov Todorov', 'Bulgaria - Sofia Nadezhda, bl. 35', '2002-12-01', 1),
('Todor Petkov', 'Sofia - Liylin 7', '2009-11-01', 1),
('Sofiq Dimitrova Petrova', 'Sofia - Mladost 4, bl. 7', '2010-01-01', 1),
('Teodor Ivanov Stoyanov', 'Sofia - Obelya, bl. 48', '2011-10-01', NULL),
('Iliya Stoynov Todorov', 'Sofia Nadezhda, bl. 28', '2000-02-01', 5),
('Mariela Dimitrova Yordanova', 'Sofia - Knyajevo, bl. 17', '2005-05-01', 5),
('Elena Miroslavova Georgieva', 'Sofia - Krasno Selo, bl. 27', '2008-04-01', 5),
('Teodor Milanov Milanov', 'Sofia - Lozenetz', '2012-04-01', 5);

SELECT progr.name as ProgramerName, progr.address as ProgramerAddress, teamLeads.name as TeamLeadName
FROM programmers as progr JOIN programmers as teamLeads
WHERE progr.teamLead_id = teamLeads.id;

SELECT sportGroups.location,
sportGroups.dayOfWeek,
sportGroups.hourOfTraining,
sports.name
from sportGroups inner join sports
on sportGroups.sport_id = sports.id;

SELECT coaches.name, sports.name
FROM coaches JOIN sports
on coaches.id IN(
SELECT coach_id
from sportGroups
WHERE sportGroups.sport_id = sports.id
);

SELECT distinct coaches.name, sports.name
from coaches JOIN sportGroups
on coach_id = sportGroups.coach_id
JOIN sports
on sportGroups.sport_id = sports.id;


 delimiter |
 create procedure getAllSportGroupsWithSports()
 begin
 select sg.location as locationOfGroup,
 sg.DayOfWeek as trainigDay,
 sg.hourOfTraining as trainingHour,
 sp.name as sportName
 from sportgroups as sg join sports as sp
 on sg.sport_id = sp.id;
 end
 |
 delimiter ;

 call getAllSportGroupsWithSports();
delimiter |
create procedure proc_in (in var varchar(50))
begin
set @coach_name = var;
end;
|
delimiter ;
call proc_in ('Ivan Petrov');
select * from coaches
where name = @coach_name;

 DELIMITER $$
CREATE procedure checkMonthTax(IN studId INT, IN groupId INT, IN paymentMonth INT, IN paymentYear INT)
BEGIN
DECLARE result char(1);
SET result = 0;
    IF( (SELECT paymentAmount
        FROM taxespayment
        WHERE student_id = studId
        AND group_id = groupId
        AND month = paymentMonth
        AND year = paymentYear) IS NOT NULL)
    THEN
        SET result = 1;
    ELSE
        SET result = 0;
    END IF;
SELECT result AS IsTaxPayed;
END$$;
DELIMITER ;
CALL checkMonthTax(1,1,1,2023);

DELIMITER $$
CREATE PROCEDURE getPaymentPeriod(IN studId INT, IN groupId INT, IN paymentYear INT)
BEGIN
DECLARE countOfMonths int;
DECLARE monthStr VARCHAR(10);
DECLARE yearStr VARCHAR(10);
SET monthStr = 'MONTH';
SET yearStr = 'YEAR';
    SELECT COUNT(*)
    INTO countOfMonths
    FROM taxespayment
    WHERE student_id = studId
    AND group_id = groupId
    AND year = paymentYear;

CASE countOfMonths
    WHEN 0 THEN SELECT 'This student has not paid for this group/year!' as PAYMENT_PERIOD;
    WHEN 1 THEN SELECT CONCAT('ONE_',monthStr) as PAYMENT_PERIOD;
    WHEN 3 THEN SELECT CONCAT('THREE_',monthStr,'S') as PAYMENT_PERIOD;
    WHEN 6 THEN SELECT CONCAT('SIX_',monthStr,'S') as PAYMENT_PERIOD;
    WHEN 12 THEN SELECT yearStr as PAYMENT_PERIOD;
    ELSE
            SELECT CONCAT(countOfMonths, monthStr, 'S') as PAYMENT_PERIOD;
        END CASE;
END$$;
DELIMITER ;
CALL getPaymentPeriod(4,2,2022);


DROP PROCEDURE IF EXISTS getAllPaymentsAmount;
DELIMITER |
CREATE PROCEDURE getAllPaymentsAmount(
    IN firstMonth INT,
    IN secMonth INT,
    IN paymentYear INT,
    IN studId INT
)
BEGIN
    DECLARE totalAmount DECIMAL(10,2) DEFAULT 0;
    DECLARE monthCount INT;

    IF(firstMonth > secMonth) THEN
        SELECT 'Please enter correct months!' AS RESULT;
    ELSEIF((SELECT COUNT(*) FROM taxesPayment WHERE student_id = studId) = 0) THEN
        SELECT 'Please enter correct student_id!' AS RESULT;
    ELSE

        SET monthCount = secMonth - firstMonth + 1;


        SELECT IFNULL(SUM(paymentAmount), 0) INTO totalAmount
        FROM taxesPayment
        WHERE student_id = studId AND year = paymentYear AND month BETWEEN firstMonth AND secMonth;


        SELECT
            student_id,
            group_id,
            paymentAmount,
            month,
            monthCount AS months_in_period,
            totalAmount AS total_paid
        FROM taxesPayment
        WHERE student_id = studId AND year = paymentYear AND month BETWEEN firstMonth AND secMonth;
    END IF;
END;
|
DELIMITER ;

CALL getAllPaymentsAmount(1,6,2021,1);