-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.28-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win64
-- HeidiSQL Version:             12.5.0.6677
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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 database structure for wikimedia
CREATE DATABASE IF NOT EXISTS `wikimedia` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `wikimedia`;

-- Dumping structure for procedure wikimedia.delete_entry
DELIMITER //
CREATE PROCEDURE `delete_entry`(
	IN `param` INT
)
BEGIN
DELETE FROM entries WHERE id = param;
END//
DELIMITER ;

-- Dumping structure for table wikimedia.emails
CREATE TABLE IF NOT EXISTS `emails` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data exporting was unselected.

-- Dumping structure for table wikimedia.entries
CREATE TABLE IF NOT EXISTS `entries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `content` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `edited_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_user_id` (`user_id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data exporting was unselected.

-- Dumping structure for procedure wikimedia.find_email
DELIMITER //
CREATE PROCEDURE `find_email`(
	IN `email` TEXT
)
BEGIN
SELECT
	e.id
FROM
	emails AS e
WHERE
	e.email = email;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.find_password
DELIMITER //
CREATE PROCEDURE `find_password`(
	IN `password` VARCHAR(255)
)
BEGIN
SELECT
 	p.id
FROM
	passwords AS p
WHERE
	p.password = PASSWORD;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.find_user
DELIMITER //
CREATE PROCEDURE `find_user`(
	IN `email` INT,
	IN `password` INT
)
BEGIN
SELECT
	u.id,
	u.username
FROM
	users AS u
WHERE
	u.email = email AND
	u.password = PASSWORD;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.find_user_by_name
DELIMITER //
CREATE PROCEDURE `find_user_by_name`(
	IN `name` VARCHAR(30)
)
BEGIN
SELECT
	u.id
FROM
	users AS u
WHERE
	u.username = NAME;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_entries
DELIMITER //
CREATE PROCEDURE `get_entries`()
BEGIN
SELECT
	e.id,
	e.title
FROM
	entries AS e;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_entry
DELIMITER //
CREATE PROCEDURE `get_entry`(
	IN `entry_id` INT
)
BEGIN
SELECT
	e.id,
	u.username,
	e.title,
	e.description,
	e.content,
	DATE_FORMAT(e.created_at, "%b %d, %Y at %H:%i:%s") AS created_at,
	DATE_FORMAT(e.edited_at, "%b %d, %Y at %H:%i:%s") AS edited_at
FROM
	entries AS e
LEFT JOIN
	users AS u
ON e.user_id = u.id
WHERE
	e.id = entry_id;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_entry_section
DELIMITER //
CREATE PROCEDURE `get_entry_section`(
	IN `id` INT
)
BEGIN
SELECT
	e.id,
	u.username,
	e.content
FROM
	entries AS e
LEFT JOIN
	users AS u
ON e.user_id = u.id
WHERE
	e.id = id;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_entry_user_id
DELIMITER //
CREATE PROCEDURE `get_entry_user_id`(
	IN `id` INT
)
BEGIN
SELECT
 	e.user_id
FROM
	entries AS e
WHERE
	e.id = id;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_password_using_email
DELIMITER //
CREATE PROCEDURE `get_password_using_email`(
	IN `emailID` INT
)
BEGIN
SELECT
	p.id,
	p.`password`
FROM
	users AS u
LEFT JOIN passwords AS p
ON p.id = u.`password`
WHERE u.email = emailID;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_user_entry
DELIMITER //
CREATE PROCEDURE `get_user_entry`(
	IN `user_id` INT
)
BEGIN
SELECT
	e.id,
	e.title
FROM
	entries AS e
WHERE
	e.user_id = user_id;
END//
DELIMITER ;

-- Dumping structure for table wikimedia.passwords
CREATE TABLE IF NOT EXISTS `passwords` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `password` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data exporting was unselected.

-- Dumping structure for function wikimedia.post_email
DELIMITER //
CREATE FUNCTION `post_email`(`param` TEXT
) RETURNS int(11)
BEGIN
DECLARE emailID INT;
SET emailID = (SELECT e.id FROM emails AS e WHERE e.email = param);
IF(ISNULL(emailID) = TRUE) THEN
	INSERT INTO emails (email) VALUES (param);
	RETURN (SELECT e.id FROM emails AS e ORDER BY e.id DESC LIMIT 1);
ELSE
	RETURN 0;
END IF;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.post_entry
DELIMITER //
CREATE PROCEDURE `post_entry`(
	IN `object` JSON
)
BEGIN
INSERT INTO entries (user_id, title, description) VALUES (JSON_VALUE(object, "$.user_id"), JSON_VALUE(object, "$.title"), JSON_VALUE(object, "$.description"));
END//
DELIMITER ;

-- Dumping structure for function wikimedia.post_password
DELIMITER //
CREATE FUNCTION `post_password`(`param` TEXT
) RETURNS int(11)
BEGIN
INSERT INTO passwords (password) VALUES (param);
RETURN (SELECT p.id FROM passwords AS p ORDER BY p.id DESC LIMIT 1);
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.post_user
DELIMITER //
CREATE PROCEDURE `post_user`(
	IN `object` JSON
)
BEGIN
INSERT INTO users (username, email, PASSWORD) VALUES (JSON_VALUE(object, "$.username"), JSON_VALUE(object, "$.email"), JSON_VALUE(object, "$.password"));
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.put_entry
DELIMITER //
CREATE PROCEDURE `put_entry`(
	IN `object` JSON,
	IN `entry_id` INT
)
BEGIN
UPDATE entries SET title = JSON_VALUE(object, "$.title"), description = JSON_VALUE(object, "$.description"), content = JSON_VALUE(object, "$.content"), edited_at = current_timestamp WHERE id = entry_id;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.search_data
DELIMITER //
CREATE PROCEDURE `search_data`(
	IN `param` TEXT
)
BEGIN
SELECT
	e.id,
	u.username,
	e.title,
	IF(LENGTH(e.description) > 100, CONCAT(LEFT(e.description, 100), "..."), e.description) AS "description",
	DATE_FORMAT(e.created_at, "%b %d, %Y at %H:%i:%s") AS created_at
FROM
	entries AS e
LEFT JOIN
	users AS u
ON e.user_id = u.id
WHERE e.title LIKE CONCAT(param, "%") OR
e.title LIKE CONCAT("%", param, "%") OR
e.title LIKE CONCAT("%", param);
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.update_section
DELIMITER //
CREATE PROCEDURE `update_section`(
	IN `object` JSON,
	IN `entry_id` INT
)
BEGIN
UPDATE entries SET content = JSON_VALUE(object, "$.content"), edited_at = current_timestamp WHERE id = entry_id;
END//
DELIMITER ;

-- Dumping structure for table wikimedia.users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` int(10) unsigned NOT NULL DEFAULT 0,
  `password` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `fk_email_id` (`email`),
  KEY `fk_password_id` (`password`),
  CONSTRAINT `fk_email_id` FOREIGN KEY (`email`) REFERENCES `emails` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_password_id` FOREIGN KEY (`password`) REFERENCES `passwords` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data exporting was unselected.

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;