-- Adminer 4.2.5 MySQL dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DELIMITER ;;

DROP PROCEDURE IF EXISTS `get_users_location`;;
CREATE PROCEDURE `get_users_location`(IN `dist` DOUBLE, IN `lat` DOUBLE, IN `lon` DOUBLE, IN `page` INT)
    READS SQL DATA
SELECT insta_id,name as username,latitude,longitude,(ACOS(SIN(latitude)*SIN(lat) + COS(latitude)*COS(lat)*COS(longitude-lon))*6371000) as distance FROM users WHERE (ACOS(SIN(latitude)*SIN(lat) + COS(latitude)*COS(lat)*COS(longitude-lon))*6371000)<dist
ORDER BY distance,users.insta_id ASC LIMIT page,5;;

DELIMITER ;

DROP TABLE IF EXISTS `fake_users`;
CREATE TABLE `fake_users` (
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `male` tinyint(4) DEFAULT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `follows`;
CREATE TABLE `follows` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `follower_insta_id` bigint(20) unsigned NOT NULL,
  `req_id` bigint(20) unsigned DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `follower_insta_id` (`follower_insta_id`),
  KEY `req_id` (`req_id`),
  CONSTRAINT `follows_ibfk_1` FOREIGN KEY (`follower_insta_id`) REFERENCES `users` (`insta_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `follows_ibfk_2` FOREIGN KEY (`req_id`) REFERENCES `follow_requests` (`req_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DELIMITER ;;

CREATE TRIGGER `after_insert_follows` AFTER INSERT ON `follows` FOR EACH ROW
BEGIN

	UPDATE users SET users.gem=users.gem+1 WHERE users.insta_id=NEW.follower_insta_id;
    
    UPDATE follow_requests SET follow_requests.remaining_follow=GREATEST(cast(follow_requests.remaining_follow as SIGNED)-1,0) WHERE follow_requests.req_id=NEW.req_id;

END;;

CREATE TRIGGER `after_delete_follows` AFTER DELETE ON `follows` FOR EACH ROW
BEGIN

DECLARE myvar BIGINT UNSIGNED DEFAULT 0;

UPDATE users SET users.gem=users.gem-2 WHERE users.insta_id=OLD.follower_insta_id;

SELECT follow_requests.insta_id INTO myvar FROM follow_requests WHERE follow_requests.req_id=OLD.req_id;

UPDATE users SET users.gem=users.gem+2 WHERE users.insta_id=myvar;

INSERT INTO follow_refunds(count,insta_id) VALUES(2,myvar) ON DUPLICATE KEY UPDATE count=count+2;

END;;

DELIMITER ;

DROP TABLE IF EXISTS `follow_refunds`;
CREATE TABLE `follow_refunds` (
  `insta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`insta_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `follow_reports`;
CREATE TABLE `follow_reports` (
  `req_id` bigint(20) unsigned NOT NULL,
  `insta_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`req_id`,`insta_id`),
  KEY `insta_id` (`insta_id`),
  CONSTRAINT `follow_reports_ibfk_1` FOREIGN KEY (`req_id`) REFERENCES `follow_requests` (`req_id`) ON UPDATE CASCADE,
  CONSTRAINT `follow_reports_ibfk_2` FOREIGN KEY (`insta_id`) REFERENCES `users` (`insta_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `follow_requests`;
CREATE TABLE `follow_requests` (
  `req_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `insta_id` bigint(20) unsigned NOT NULL,
  `remaining_follow` int(10) unsigned NOT NULL,
  `count` int(10) unsigned NOT NULL,
  `state` tinyint(4) NOT NULL DEFAULT '1',
  `caption` text CHARACTER SET utf8 COLLATE utf8_bin,
  `male` tinyint(1) DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`req_id`),
  KEY `insta_id` (`insta_id`),
  KEY `remaining_follow` (`remaining_follow`),
  KEY `remaining_follow_req_id` (`remaining_follow`,`req_id`),
  KEY `state` (`state`),
  CONSTRAINT `follow_requests_ibfk_1` FOREIGN KEY (`insta_id`) REFERENCES `users` (`insta_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DELIMITER ;;

CREATE TRIGGER `after_follow_requests_update` AFTER UPDATE ON `follow_requests` FOR EACH ROW
BEGIN

IF NEW.remaining_follow=0 THEN

INSERT INTO notifications_to_sent (insta_id,type) VALUES(NEW.insta_id,1);

END IF;


END;;

DELIMITER ;

DROP TABLE IF EXISTS `likes`;
CREATE TABLE `likes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `insta_id` bigint(20) unsigned NOT NULL,
  `media_id` varchar(50) NOT NULL,
  `req_id` bigint(20) unsigned NOT NULL,
  `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `req_id` (`req_id`),
  KEY `insta_id` (`insta_id`),
  CONSTRAINT `likes_ibfk_1` FOREIGN KEY (`req_id`) REFERENCES `like_requests` (`req_id`),
  CONSTRAINT `likes_ibfk_2` FOREIGN KEY (`insta_id`) REFERENCES `users` (`insta_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DELIMITER ;;

CREATE TRIGGER `after_insert_likes` AFTER INSERT ON `likes` FOR EACH ROW
BEGIN
	
	UPDATE users SET users.coin=users.coin+1 WHERE users.insta_id=NEW.insta_id;
    UPDATE like_requests SET like_requests.remaining_like=GREATEST(cast(like_requests.remaining_like as SIGNED)-1,0) WHERE like_requests.req_id=NEW.req_id;
	
END;;

CREATE TRIGGER `after_delete_likes` AFTER DELETE ON `likes` FOR EACH ROW
BEGIN

DECLARE myvar BIGINT UNSIGNED DEFAULT 0;

UPDATE users SET users.coin=GREATEST(cast(users.coin as SIGNED)-1,0) WHERE users.insta_id=OLD.insta_id;

SELECT like_requests.insta_id INTO myvar FROM like_requests WHERE like_requests.req_id=OLD.req_id;

UPDATE users SET users.coin=users.coin+2 WHERE users.insta_id=myvar;

END;;

DELIMITER ;

DROP TABLE IF EXISTS `like_reports`;
CREATE TABLE `like_reports` (
  `report_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `req_id` bigint(20) unsigned NOT NULL,
  `insta_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`report_id`),
  KEY `req_id` (`req_id`),
  KEY `insta_id` (`insta_id`),
  CONSTRAINT `like_reports_ibfk_1` FOREIGN KEY (`req_id`) REFERENCES `like_requests` (`req_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `like_reports_ibfk_2` FOREIGN KEY (`insta_id`) REFERENCES `users` (`insta_id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `like_requests`;
CREATE TABLE `like_requests` (
  `req_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `insta_id` bigint(20) unsigned NOT NULL,
  `media_id` varchar(50) NOT NULL,
  `image_url` text,
  `remaining_like` int(10) unsigned NOT NULL,
  `active` tinyint(10) NOT NULL DEFAULT '1',
  `count` int(10) unsigned NOT NULL,
  `count_extra` int(11) NOT NULL DEFAULT '0',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `caption` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `male` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`req_id`),
  KEY `insta_id` (`insta_id`),
  KEY `media_id` (`media_id`),
  CONSTRAINT `like_requests_ibfk_1` FOREIGN KEY (`insta_id`) REFERENCES `users` (`insta_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DELIMITER ;;

CREATE TRIGGER `after_like_requests_update` AFTER UPDATE ON `like_requests` FOR EACH ROW
BEGIN

IF NEW.remaining_like=0 THEN

INSERT INTO notifications_to_sent (insta_id,type) VALUES(NEW.insta_id,0);

END IF;

END;;

DELIMITER ;

DROP TABLE IF EXISTS `notifications_to_sent`;
CREATE TABLE `notifications_to_sent` (
  `insta_id` bigint(20) unsigned NOT NULL,
  `type` tinyint(4) NOT NULL DEFAULT '0',
  KEY `insta_id` (`insta_id`),
  CONSTRAINT `notifications_to_sent_ibfk_1` FOREIGN KEY (`insta_id`) REFERENCES `users` (`insta_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `purchases`;
CREATE TABLE `purchases` (
  `token` varchar(1000) NOT NULL,
  `item` varchar(10) NOT NULL,
  `count` int(11) NOT NULL,
  `order_id` varchar(200) NOT NULL,
  `insta_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `insta_id` (`insta_id`),
  CONSTRAINT `purchases_ibfk_1` FOREIGN KEY (`insta_id`) REFERENCES `users` (`insta_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DELIMITER ;;

CREATE TRIGGER `after_purchase_insert` AFTER INSERT ON `purchases` FOR EACH ROW
BEGIN

IF NEW.item='coin' THEN
UPDATE users SET users.coin=users.coin+NEW.count WHERE users.insta_id=NEW.insta_id;
END IF;

IF NEW.item='gem' THEN
UPDATE users SET users.gem=users.gem+NEW.count WHERE users.insta_id=NEW.insta_id;
END IF;

END;;

DELIMITER ;

DROP TABLE IF EXISTS `transfers`;
CREATE TABLE `transfers` (
  `transfer_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `insta_id_sender` bigint(20) unsigned NOT NULL,
  `insta_id_reciever` bigint(20) unsigned NOT NULL,
  `type` enum('coin','gem') NOT NULL,
  `count` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transfer_id`),
  KEY `insta_id_sender` (`insta_id_sender`),
  KEY `insta_id_reciever` (`insta_id_reciever`),
  CONSTRAINT `transfers_ibfk_1` FOREIGN KEY (`insta_id_sender`) REFERENCES `users` (`insta_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `transfers_ibfk_2` FOREIGN KEY (`insta_id_reciever`) REFERENCES `users` (`insta_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `insta_id` bigint(20) unsigned NOT NULL,
  `name` varchar(50) NOT NULL,
  `password` varchar(60) NOT NULL,
  `token` varchar(60) NOT NULL,
  `gem` int(10) NOT NULL DEFAULT '40',
  `coin` int(10) NOT NULL DEFAULT '40',
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `notif_token` varchar(50) DEFAULT NULL,
  `male` tinyint(1) DEFAULT NULL,
  `last_gotten_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `profile_pic` text,
  PRIMARY KEY (`insta_id`),
  UNIQUE KEY `insta_id` (`insta_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- 2016-10-07 08:58:18