CREATE DATABASE  IF NOT EXISTS `dogmative_network`;

CREATE TABLE IF NOT EXISTS `dogmative_network`.`bites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `type_` char(1) NOT NULL,
  `share_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- /*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for TABLE  IF NOT EXISTS `chat`
--

-- DROP TABLE IF EXISTS `chat`;
-- /*!40101 SET @saved_cs_client     = @@character_set_client */;
-- /*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `dogmative_network`.`chat` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `msg` varchar(355) NOT NULL,
  `m_author` int(11) NOT NULL,
  `m_receip` int(11) DEFAULT NULL,
  `status` char(1) NOT NULL,
  `time_` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- /*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for TABLE  IF NOT EXISTS `dawgs`
--

-- DROP TABLE IF EXISTS `dawgs`;
CREATE TABLE IF NOT EXISTS `dogmative_network`.`dawgs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `tail_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for TABLE  IF NOT EXISTS `drool`
--

-- DROP TABLE IF EXISTS `drool`;
CREATE TABLE IF NOT EXISTS `dogmative_network`.`drool` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `msg_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` char(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Table structure for TABLE  IF NOT EXISTS `films`
--

-- DROP TABLE IF EXISTS `films`;
CREATE TABLE IF NOT EXISTS `dogmative_network`.`films` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `synopsis` varchar(300) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `type_` char(1) NOT NULL,
  `time_` datetime NOT NULL,
  `creation_date` datetime DEFAULT NULL,
  `poster` varchar(100) DEFAULT '01.jpg',
  `link` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `dogmative_network`.`msg` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `m_type` char(1) NOT NULL,
  `content` varchar(255) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `time_` datetime NOT NULL,
  `title` varchar(45) DEFAULT 'dogmative',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=latin1;
-- /*!40101 SET character_set_client = @saved_cs_client */;

--
-- Temporary table structure for view `recent_films`
--

-- DROP TABLE IF EXISTS `recent_films`;
-- /*!50001 DROP VIEW IF EXISTS `recent_films`*/;
-- SET @saved_cs_client     = @@character_set_client;
-- SET character_set_client = utf8;
-- /*!50001 CREATE VIEW `recent_films` AS SELECT 
--  1 AS `id`,
--  1 AS `name`,
--  1 AS `synopsis`,
--  1 AS `user_id`,
--  1 AS `type_`,
--  1 AS `time_`,
--  1 AS `creation_date`,
--  1 AS `poster`,
--  1 AS `link`,
--  1 AS `director`,
--  1 AS `drools`,
--  1 AS `bites`*/;
-- SET character_set_client = @saved_cs_client;

--
-- Table structure for TABLE IF NOT EXISTS `dogmative_network`.`social`
--

-- DROP TABLE IF EXISTS `social`;
-- /*!40101 SET @saved_cs_client     = @@character_set_client */;
-- /*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `dogmative_network`.`social` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `url` varchar(72) NOT NULL,
  `plat` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `dogmative_network`.`user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(55) NOT NULL,
  `name` varchar(55) DEFAULT NULL,
  `surname` varchar(55) DEFAULT NULL,
  `email` varchar(72) NOT NULL,
  `password` varchar(66) NOT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `status` int(6) DEFAULT 0,
  `dob` date DEFAULT NULL,
  `country` varchar(20) DEFAULT NULL,
  `time_` datetime DEFAULT CURRENT_TIMESTAMP,
  `profilepic` varchar(74) DEFAULT 'dp.jpg',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`,`email`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `dogmative_network`.`view` (
  `view_id` int(11) NOT NULL,
  `view_profile_user` varchar(255) NOT NULL,
  `view_current_user` varchar(255) NOT NULL,
  `view_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Drop function if exists `dogmative_network`.`BTS`;
CREATE DEFINER=`root`@`localhost` FUNCTION `dogmative_network`.`BTS`(idx int , typ_ char) RETURNS int(11)
BEGIN
declare res integer default 0;
 select count(id) into res from `dogmative_network`.`bites` where share_id = idx and type_ = typ_;
RETURN res;
END ;

Drop function if exists `dogmative_network`.`DROOLS`;
CREATE DEFINER=`root`@`localhost` FUNCTION `dogmative_network`.`DROOLS`(idx int , typ_ char) RETURNS int(11)
BEGIN
declare res integer default 0;
 select count(id) into res from `dogmative_network`.`drool` where msg_id = idx and `type` = typ_ ;
return  res;
END ;
Drop function if exists `dogmative_network`.`GET_PROPIC`;
CREATE DEFINER=`root`@`localhost` FUNCTION `dogmative_network`.`GET_PROPIC`(id int) RETURNS varchar(77) CHARSET latin1
BEGIN
declare nme varchar(77) default "dp.jpg";
     select `user`.`profilepic` into nme   from `dogmative_network`.`user` where `user`.`id` = id ;
	return nme;
END ;

Drop function if exists `dogmative_network`.`GET_USERNAME`;
CREATE DEFINER=`root`@`localhost` FUNCTION `dogmative_network`.`GET_USERNAME`(id int) RETURNS varchar(35) CHARSET latin1
BEGIN
	declare nme varchar(15) default "";
    declare surname varchar(15) default "";
    declare uname varchar(34) default "username";
    declare fin varchar(32) default "username";
     select ifnull(`user`.`name`,"0") into nme   from `user` where `user`.`id` = id ;
     select `user`.`surname` into surname   from `user` where `user`.`id` = id ;
     Set fin = concat(concat(nme, " " ),ifnull(surname,"")); 
     if nme="0"  then 
		select `user`.`username` into uname from `dogmative_network`.`user` where `user`.`id` = id ;
		set fin = uname;
	 end if;
	return fin;
END ;


CREATE or Replace 
 VIEW `dogmative_network`.`barks` AS 
 select distinct `m`.`id` AS `no`,`m`.`content` AS `txt`,
 `m`.`m_type` AS `type`,`m`.`time_` AS `time`,`m`.`title` AS `title`,
 `m`.`user_id` AS `uid`,`dogmative_network`.`GET_USERNAME`(`m`.`user_id`) AS `name`,
 `dogmative_network`.`BTS`(`m`.`id`,`m`.`m_type`) AS `bites`,
 `dogmative_network`.`DROOLS`(`m`.`id`,`m`.`m_type`) AS `drools`,
 `dogmative_network`.`GET_PROPIC`(`m`.`user_id`) AS `profilepic` from `dogmative_network`.`msg` `m` ;

 CREATE or Replace 
  VIEW `dogmative_network`.`films_of_the_week` AS select now() AS `tim` ;

 CREATE or Replace 
  VIEW `dogmative_network`.`recent_films` AS select `films`.`id` AS `id`,`films`.`name` AS `name`,
  `films`.`synopsis` AS `synopsis`,`films`.`user_id` AS `user_id`,
  `films`.`type_` AS `type_`,`films`.`time_` AS `time_`,
  `films`.`creation_date` AS `creation_date`,`films`.`poster` AS `poster`,
  `films`.`link` AS `link`,`dogmative_network`.`GET_USERNAME`(`films`.`user_id`) AS `director`,
  `dogmative_network`.`DROOLS`(`films`.`id`,`films`.`type_`) AS `drools`,
  `dogmative_network`.`BTS`(`films`.`id`,`films`.`type_`) AS `bites` from `dogmative_network`.`films` order by `films`.`time_` desc limit 4 ;


 CREATE or Replace
 VIEW `dogmative_network`.`top_dawgs` AS 
 select `user`.`id` AS `id`,`user`.`username` AS `username`,
 `dogmative_network`.`GET_USERNAME`(`user`.`id`) AS `name`,
 `user`.`gender` AS `gender`,`user`.`country` AS `country`,
 `user`.`profilepic` AS `propic` from `dogmative_network`.`user` ;

