CREATE DATABASE  IF NOT EXISTS `cv` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `cv`;
--
-- Host: localhost    Database: cv
-- ------------------------------------------------------


--
-- Table structure for table `organizations`
--

CREATE TABLE `organizations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `about` text,
  `created` datetime DEFAULT NULL,
  `lastupdated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ByName` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `organizations`
--

INSERT INTO `organizations` VALUES (1,'Johann CC',NULL,NULL,NULL,NULL),(2,'Jan CC',NULL,NULL,NULL,NULL),(3,'Both CC',NULL,NULL,NULL,NULL);

--
-- Table structure for table `accounts`
--

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID of the Accounts',
  `user` int(11) DEFAULT NULL COMMENT 'User ID of the User who owns this account.',
  `provider` varchar(15) DEFAULT NULL,
  `uid` varchar(255) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `lastupdated` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `UIDFromProvider` (`provider`,`uid`),
  KEY `AccountsByUser` (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `accounts`
--


--
-- Table structure for table `organization_members`
--

CREATE TABLE `organization_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `organization` int(11) DEFAULT NULL,
  `user` int(11) DEFAULT NULL,
  `public` tinyint(4) DEFAULT '0',
  `created` datetime DEFAULT NULL,
  `lastupdated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ByOrg` (`organization`),
  KEY `ByUser` (`user`),
  KEY `ByOrgPublic` (`public`,`organization`),
  KEY `ByUserPublic` (`user`,`public`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `organization_members`
--

INSERT INTO `organization_members` VALUES (1,1,1,0,NULL,NULL),(2,2,2,0,NULL,NULL),(3,3,1,0,NULL,NULL),(4,3,2,0,NULL,NULL);

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `lastlogin` datetime DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `lastupdated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ByUsername` (`username`),
  KEY `ByEMail` (`email`),
  KEY `ByUsernameEMail` (`username`,`email`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES (1,'johann.dutoit','exxonno@gmail.com',NULL,NULL,NULL),(2,'jan.sal',NULL,NULL,NULL,NULL);

--
-- Table structure for table `trusts`
--

CREATE TABLE `trusts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `organization` int(11) DEFAULT NULL,
  `user` int(11) DEFAULT NULL,
  `thread` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `lastupdated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ByUser` (`user`,`status`),
  KEY `ByUserOrg` (`organization`,`user`,`status`),
  KEY `ByUserOrgThread` (`organization`,`thread`,`user`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `trusts`
--

INSERT INTO `trusts` VALUES (1,2,1,NULL,2,NULL,NULL),(2,1,2,NULL,2,NULL,NULL),(3,1,1,NULL,2,NULL,NULL);

--
-- Dumping routines for database 'cv'
--
/*!50003 DROP FUNCTION IF EXISTS `get_memberships` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `get_memberships`(user_id int) RETURNS text CHARSET latin1
BEGIN

return (
    SELECT group_concat(organizations.id) FROM organizations, organization_members, users where users.id = organization_members.user and organizations.id = organization_members.organization and users.id=user_id
);

END */;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `get_members_of_organization` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `get_members_of_organization`(org_id int) RETURNS text CHARSET latin1
BEGIN

return (
    SELECT group_concat(users.id) FROM organizations, organization_members, users where users.id = organization_members.user and organizations.id = organization_members.organization and organizations.id=org_id
);

END */;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `get_trusted_users_of_organization` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `get_trusted_users_of_organization`(org_id int) RETURNS text CHARSET latin1
BEGIN

return (
    SELECT group_concat(users.id) FROM organizations, organization_members, trusts, users where organizations.id = organization_members.organization and trusts.organization = organizations.id and users.id = trusts.user and organizations.id = org_id and trusts.status = 2
);

END */;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `get_user_trust_level` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `get_user_trust_level`(target_user_id int, organization_id int) RETURNS int(11)
BEGIN
DECLARE level INT;

IF find_in_set(target_user_id, get_trusted_users_of_organization(organization_id)) > 0 THEN SET level = 5;
ELSE SET level = 1;
END IF;
    
return (level);

END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `list_memberships` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `list_memberships`(IN user_id INT)
BEGIN
    DECLARE memberships text;
    SET memberships = get_memberships(user_id);
    SELECT organizations.* FROM organizations where find_in_set(organizations.id, memberships);
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `list_members_of_organization` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `list_members_of_organization`(IN organization_id INT)
BEGIN
    DECLARE members_of_organization text;
    SET members_of_organization = get_members_of_organization(organization_id);
    SELECT users.* FROM users where find_in_set(users.id, members_of_organization);
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `list_trusted_users_of_organization` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `list_trusted_users_of_organization`(IN organization_id INT)
BEGIN
    DECLARE trusted_users_of_organization text;
    SET trusted_users_of_organization = get_trusted_users_of_organization(organization_id);
    SELECT users.* FROM users where find_in_set(users.id, trusted_users_of_organization);
END */;;
DELIMITER ;



