-- MySQL dump 10.13  Distrib 5.6.41-84.1, for Linux (x86_64)
--
-- Host: localhost    Database: agineekc_web
-- ------------------------------------------------------
-- Server version	5.6.41-84.1
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping routines for database 'agineekc_web'
--
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE PROCEDURE `Assign_Club`(
    IN p_owner_id INT UNSIGNED,
    IN p_club_name VARCHAR(50) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci,
    IN p_StatementType VARCHAR(20) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci)
BEGIN
-- Local variables
DECLARE v_club_id INT UNSIGNED;
BEGIN

START TRANSACTION; -- START A TRANSACTION TO BE ABLE TO ROLL BACK CHANGES IF THE OPERATION GIVES US AN ERROR, OR SOMETHING GETS MESSED UP IN THE PROCESS.
	-- CHECKING STATEMENT TYPE IN ORDER TO DETERMINE WHETHER WE'RE UPDATING AN EXISTING CLUB OR CREATING A NEW CLUB
    IF p_StatementType = 'Insert'  -- IF IT'S AN INSERT, THEN WE'RE CREATING A NEW CLUB IN THE DATABASE
        THEN
            INSERT INTO agineekc_web.clubs
            ( owner_id, club_name, creation_date)
            VALUES
            ( p_owner_id, p_club_name, NOW(3));

            SELECT club_id INTO v_club_id
            FROM agineekc_web.clubs
            WHERE club_name = p_club_name;

        END IF;
    /* IF p_StatementType = 'Update' -- IF IT'S AN UPDATE, THEN WE'RE UPDATING AN EXISTING CLUB IN THE DATABASE
        THEN
            UPDATE agineekc_web.clubs
            SET club_name = p_club_name
            WHERE club_name IN
            (
                SELECT TOP 1 club_name
                FROM Club WHERE owner_id = p_owner_id
                ORDER BY club_id ASC
            );
        END IF; */

COMMIT;

SELECT v_club_id;
END;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE PROCEDURE `Create_Player`(
	IN p_email VARCHAR(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci,
	IN p_phone_number VARCHAR (15) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci,
	IN p_first_name VARCHAR (50) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci,
	IN p_last_name VARCHAR(50) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci,
    IN p_date_of_birth DATE,
    IN p_club_id INT UNSIGNED
)
BEGIN
-- Local variables
DECLARE v_avatar_id INT UNSIGNED DEFAULT 0;
DECLARE v_player_id INT UNSIGNED;
BEGIN

START TRANSACTION;
	INSERT INTO agineekc_web.players
    (email, phone_number, first_name, last_name, date_of_birth, avatar_id, creation_date)
    VALUES
    (p_email, p_phone_number, p_first_name, p_last_name, p_date_of_birth, v_avatar_id, NOW(3));

    SELECT player_id INTO v_player_id
    FROM agineekc_web.players
    WHERE email = p_email;

    INSERT INTO agineekc_web.club_membership
    (club_id, player_id)
    VALUES
    (p_club_id, v_player_id);
COMMIT;

-- figure out if you actually need to return the 'v_player_id'
END;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE PROCEDURE `GetGroupMembership`(IN owner_email VARCHAR(255))
BEGIN

DECLARE owner_id INT UNSIGNED DEFAULT 0;


SELECT id INTO owner_id
FROM users
WHERE email COLLATE utf8mb4_unicode_ci = owner_email;



SELECT GM.group_id, GM.player_id
FROM
(
    (
        SELECT group_id
        FROM
        (
            SELECT club_id
            FROM clubs
            WHERE owner_id = owner_id

        ) AS C
        JOIN
        (
            SELECT group_id, club_id
            FROM club_groups
        ) AS G
        ON C.club_id = G.club_id
    ) AS G
    JOIN
    (
        SELECT *
        FROM club_group_membership
    ) AS GM
    ON G.group_id = GM.group_id
);


END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE PROCEDURE `GetPlayers`(IN owner_email VARCHAR(255))
BEGIN

DECLARE owner_id INT UNSIGNED DEFAULT 0;

SELECT id INTO owner_id
FROM users
WHERE email COLLATE utf8mb4_unicode_ci = owner_email;

SELECT P.*
FROM
(
    (
        SELECT CM.player_id
        FROM
        (
            (SELECT club_id
            FROM clubs
            WHERE clubs.owner_id = owner_id) AS C
            JOIN
            (SELECT club_id, player_id
            FROM club_membership) AS CM
            ON C.club_id = CM.club_id
        )
    ) AS CM
    JOIN
    (
        SELECT
            player_id, email, rating, phone_number,
            first_name, last_name, date_of_birth, creation_date
        FROM players
    ) AS P
    ON P.player_id = CM.player_id
);

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE PROCEDURE `GetPlayersWithGroup`(IN in_club_id INT UNSIGNED, IN in_group_id INT UNSIGNED)
BEGIN

SELECT gm.player_id FROM
(
    (SELECT player_id
    FROM club_membership WHERE club_id = in_club_id) AS cm
    JOIN
    (SELECT player_id
    FROM club_group_membership WHERE group_id = in_group_id) AS gm
    ON cm.player_id = gm.player_id
);

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE PROCEDURE `GetPlayersWithoutGroup`(IN in_club_id INT UNSIGNED)
BEGIN

DROP TABLE IF EXISTS `Groups`;
DROP TABLE IF EXISTS `ClubPlayers`;
DROP TABLE IF EXISTS `GroupPlayers`;


-- groups in the club
CREATE TABLE `Groups` SELECT group_id FROM club_groups WHERE club_id = in_club_id;

-- players in the club
CREATE TABLE `ClubPlayer` SELECT player_id FROM club_membership WHERE club_id = in_club_id;

-- players in the groups
CREATE TABLE `GroupPlayers` SELECT GM.player_id
FROM
(
    (SELECT player_id FROM CLUB_PLAYERS) AS CP
    JOIN
    (SELECT player_id, group_id FROM club_group_membership) AS GM
    ON CP.player_id = GM.player_id
);

-- Get the player data
SELECT P.* FROM
(
    -- All player data
    (SELECT * FROM players) AS P
    JOIN
    -- Player IDS are filtered here (In L but not in R)
    (
        SELECT L.* FROM
        (SELECT player_id FROM CLUB_PLAYERS) AS L
        LEFT JOIN
        (SELECT player_id FROM GROUP_PLAYERS) AS R
        ON R.player_id = L.player_id
        WHERE R.player_id is NULL
    ) AS NON_GROUP
    ON P.player_id = NON_GROUP.player_id
);

DROP TABLE IF EXISTS `Groups`;
DROP TABLE IF EXISTS `ClubPlayers`;
DROP TABLE IF EXISTS `GroupPlayers`;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE PROCEDURE `Get_Clubs`(
	p_owner_email VARCHAR(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
)
BEGIN
DECLARE v_owner_id INT UNSIGNED;

SELECT id INTO v_owner_id
FROM agineekc_web.users
WHERE email = p_owner_email;

SELECT *
FROM agineekc_web.clubs
WHERE owner_id = v_owner_id;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE PROCEDURE `Get_Club_Memberships`(
	IN p_owner_email VARCHAR(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
)
BEGIN
DECLARE v_owner_id INT UNSIGNED;

SELECT id INTO v_owner_id
FROM agineekc_web.users
WHERE email = p_owner_email;

SELECT club_membership.club_id, club_membership.player_id
FROM
(
	(SELECT club_id FROM agineekc_web.clubs
    WHERE owner_id = v_owner_id) AS C
    JOIN
    (SELECT * FROM agineekc_web.club_membership) AS CM
    ON C.club_id = CM.club_id
);
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE PROCEDURE `Get_Groups`(
	p_owner_email VARCHAR(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
)
BEGIN
DECLARE v_owner_id INT UNSIGNED;

SELECT id INTO v_owner_id
FROM agineekc_web.users
WHERE email = p_owner_email;

SELECT club_groups.group_id, club_groups.creation_date, club_groups.group_name,
		club_groups.club_id, club_groups.group_desc
FROM
(
	(SELECT club_id
    FROM agineekc_web.clubs
    WHERE owner_id = v_owner_id) AS C
    JOIN
    (SELECT *
    FROM agineekc_web.club_groups) AS G
    ON C.club_id = G.club_id
);

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
CREATE PROCEDURE `Get_Oldest_Index`(
	IN p_owner_email VARCHAR(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
)
BEGIN
DECLARE v_owner_id INT UNSIGNED;

SELECT id INTO v_owner_id
FROM agineekc_web.users
WHERE email = p_owner_email;

SELECT
	android_player_id, email, rating, phone_number,
    first_name, last_name, date_of_birth, creation_date
FROM agineekc_web.players
WHERE owner_id = v_owner_id;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-11-11  4:25:00
