/*
SQLyog Professional v12.09 (64 bit)
MySQL - 10.4.22-MariaDB : Database - fcp
*********************************************************************
*/




CREATE DATABASE /*!32312 IF NOT EXISTS*/`fcp` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `fcp`;

/* Procedure structure for procedure `CheckBrandInStore` */

/*!50003 DROP PROCEDURE IF EXISTS  `CheckBrandInStore` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `CheckBrandInStore`(IN store_id INT)
BEGIN
    SELECT b.id,b.brand, b.brand_id,
           CASE
               WHEN EXISTS (
                    SELECT 1
                    FROM stores s
                    WHERE s.store_id = store_id
                      AND FIND_IN_SET(b.brand_id, REPLACE(REPLACE(s.brand_groups, '[', ''), ']', ''))
                )
                THEN 1
                ELSE 0
           END AS is_listed
    FROM brands b;
END */$$
DELIMITER ;

/* Procedure structure for procedure `CheckUserInStore` */

/*!50003 DROP PROCEDURE IF EXISTS  `CheckUserInStore` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `CheckUserInStore`(IN store_id INT)
BEGIN
SELECT u.id, u.lname, u.fname, u.mname, u.email, r.description AS role_desc,
       CASE
           WHEN EXISTS (
                SELECT 1
                FROM stores s
                WHERE s.store_id = store_id
                  AND FIND_IN_SET(s.store_id, REPLACE(REPLACE(u.store_groups, '[', ''), ']', '')) > 0
            )
            THEN 1
            ELSE 0
       END AS is_listed
FROM users u
JOIN roles r 
ON u.role = r.id
WHERE role IN (2,3);
END */$$
DELIMITER ;

