-- --------------------------------------------------------
-- 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
)
    COMMENT 'Sets a value on "deleted_at" column for an entry (Used in EntryController.php)'
BEGIN
UPDATE entries SET deleted_at = current_timestamp 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 AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores all emails';

-- Dumping data for table wikimedia.emails: ~3 rows (approximately)
DELETE FROM `emails`;
INSERT INTO `emails` (`id`, `email`) VALUES
	(1, 'email@email.com'),
	(2, 'gmail@gmail.com');

-- Dumping structure for table wikimedia.entries
CREATE TABLE IF NOT EXISTS `entries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `topic_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `edited_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_user_id` (`user_id`),
  KEY `fk_topic_id` (`topic_id`),
  CONSTRAINT `fk_topic_id` FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores all entry data. Has user id who created the entry, entry''s title and content. Also stores dates when it was created, when was it last changed and when was it deleted.';

-- Dumping data for table wikimedia.entries: ~2 rows (approximately)
DELETE FROM `entries`;
INSERT INTO `entries` (`id`, `topic_id`, `user_id`, `title`, `content`, `created_at`, `edited_at`, `deleted_at`) VALUES
	(2, 1, 1, 'Bingus', '<h2>Šmingus</h2>\nViņš ir <span style="color: blue">zils</span> :(', '2025-09-16 12:55:20', '2025-09-16 12:57:18', NULL),
	(3, 1, 1, 'What stays from HTML Tags', '<h2>HTML tags</h2><hr>\nb - <b>bold text</b><br>\nbr - \\n thingy<br>\nhr - long line<br>\ni - <i>italic text</i><br>\ns - <s>crossed out</s><br>\nspan style="color: [hex, rgb, name]" - <span style="color: red">color</span><br>\nu - <u>underline</u><br>\n<h2>Different tags</h2><hr>\nHeadings / Sections<br>\n== Heading 2 ==<br>\n=== Heading 3 ===<br>\n==== Heading 4 ====<br>\n<h2>Still guessing</h2><hr>\na<br>\ntable, td, th, tr<br>\nol, ul, li<br>', '2025-09-16 14:14:23', '2025-09-16 14:18:25', NULL);

-- Dumping structure for table wikimedia.files
CREATE TABLE IF NOT EXISTS `files` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `topic_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `edited_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_topic` (`topic_id`),
  KEY `fk_uploader` (`user_id`),
  CONSTRAINT `fk_topic` FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_uploader` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table wikimedia.files: ~2 rows (approximately)
DELETE FROM `files`;
INSERT INTO `files` (`id`, `topic_id`, `user_id`, `file_name`, `display_name`, `description`, `created_at`, `edited_at`, `deleted_at`) VALUES
	(5, 1, 1, '1759314153_41e38eac3d205a355258.jpg', 'Smile cat', 'gaba gooo', '2025-10-01 13:22:33', NULL, NULL),
	(6, 1, 1, '1759314243_533f50b6de29b8d10b35.mp3', 'AnotherChamber.mp3', 'Old remix of it', '2025-10-01 13:24:03', NULL, NULL);

-- Dumping structure for procedure wikimedia.find_email
DELIMITER //
CREATE PROCEDURE `find_email`(
	IN `email` TEXT
)
    COMMENT 'Finds email by given email and returns id (Used in UserController.php)'
BEGIN
SELECT
	e.id
FROM
	emails AS e
WHERE
	e.email = email;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.find_user
DELIMITER //
CREATE PROCEDURE `find_user`(
	IN `email` INT,
	IN `password` INT
)
    COMMENT 'Finds user by found email and password and returns id and username (Used in UserController.php)'
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)
)
    COMMENT 'Finds user by their username and returns id (Used in UserController.php)'
BEGIN
SELECT
	u.id
FROM
	users AS u
WHERE
	u.username = NAME;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_entries_by_topic
DELIMITER //
CREATE PROCEDURE `get_entries_by_topic`(
	IN `topic_id` INT
)
    COMMENT 'Gets all entries that haven''t been deleted in a certain topic and returns entry id and title (Used in EntryController.php)'
BEGIN
SELECT
	e.id,
	e.title,
	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
WHERE
	e.topic_id = topic_id AND
	ISNULL(e.deleted_at);
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_entry
DELIMITER //
CREATE PROCEDURE `get_entry`(
	IN `entry_id` INT
)
    COMMENT 'Finds entry by id and returns all data about it (Used in EntryController.php)'
BEGIN
SELECT
	e.id,
	e.title,
	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
WHERE
	e.id = entry_id AND
	ISNULL(e.deleted_at);
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_entry_user_id
DELIMITER //
CREATE PROCEDURE `get_entry_user_id`(
	IN `id` INT
)
    COMMENT 'Returns entry''s user id by entry id (Used in EntryController.php)'
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
)
    COMMENT 'From found email, gets the user''s password and returns id with password (Used in UserController.php)'
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_topic_by_name
DELIMITER //
CREATE PROCEDURE `get_topic_by_name`(
	IN `topic_name` VARCHAR(50)
)
BEGIN
SELECT
	t.id,
	t.title,
	t.content,
	DATE_FORMAT(t.created_at, "%b %d, %Y at %H:%i:%s") AS created_at,
	DATE_FORMAT(t.edited_at, "%b %d, %Y at %H:%i:%s") AS edited_at
FROM
	topics AS t
WHERE
	t.name = topic_name AND
	ISNULL(t.deleted_at);
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_topic_file
DELIMITER //
CREATE PROCEDURE `get_topic_file`(
	IN `name` VARCHAR(255)
)
    COMMENT 'Retrieves a file'
BEGIN
SELECT
	f.id,
	f.topic_id,
	u.username,
	f.display_name,
	f.description,
	f.created_at,
	f.edited_at
FROM
	files AS f
LEFT JOIN users AS u
	ON u.id = f.user_id
WHERE
	f.file_name = name AND
	ISNULL(f.deleted_at);
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_topic_files
DELIMITER //
CREATE PROCEDURE `get_topic_files`(
	IN `topic_id` INT
)
BEGIN
SELECT
	f.id,
	f.file_name,
	f.display_name,
	DATE_FORMAT(f.created_at, "%b %d, %Y at %H:%i:%s") AS created_at,
	DATE_FORMAT(f.edited_at, "%b %d, %Y at %H:%i:%s") AS edited_at
FROM
	files AS f
WHERE
	f.topic_id = topic_id AND
	ISNULL(f.deleted_at);
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_topic_for_put
DELIMITER //
CREATE PROCEDURE `get_topic_for_put`(
	IN `given_name` VARCHAR(50)
)
BEGIN
SELECT
	t.id,
	t.owner
FROM
	topics AS t
WHERE
	t.name = given_name;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_topic_id
DELIMITER //
CREATE PROCEDURE `get_topic_id`(
	IN `given_name` VARCHAR(50)
)
BEGIN
SELECT
	t.id
FROM
	topics AS t
WHERE
	t.name = given_name;
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 AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores encrypted passwords';

-- Dumping data for table wikimedia.passwords: ~2 rows (approximately)
DELETE FROM `passwords`;
INSERT INTO `passwords` (`id`, `password`) VALUES
	(1, '$2y$10$UOOpYq9fNbxY4pVgo6rqQ.yAoo9YhpRPIvh.YfzkWcA.e9uRWDVMS'),
	(2, '$2y$10$peu6ailK9LkRaWhfUg/UKen0FfE.b3kKNy3NUXcKm/PBjgOBh6sze');

-- Dumping structure for function wikimedia.post_email
DELIMITER //
CREATE FUNCTION `post_email`(`param` TEXT
) RETURNS int(11)
    COMMENT 'Inserts a new email if it doesn''t exist and returns id for newly made emails (Used in UserController.php)'
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
)
    COMMENT 'Creates a new entry (Used in EntryController.php)'
BEGIN
INSERT INTO entries (topic_id, user_id, title, content) VALUES (JSON_VALUE(object, "$.topic_id"), JSON_VALUE(object, "$.user_id"), JSON_VALUE(object, "$.title"), JSON_VALUE(object, "$.content"));
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.post_file
DELIMITER //
CREATE PROCEDURE `post_file`(
	IN `object` JSON
)
BEGIN
INSERT INTO files (topic_id, user_id, file_name, display_name, description) VALUES (JSON_VALUE(object, "$.topic_id"), JSON_VALUE(object, "$.user_id"), JSON_VALUE(object, "$.file_name"), JSON_VALUE(object, "$.display_name"), JSON_VALUE(object, "$.description"));
END//
DELIMITER ;

-- Dumping structure for function wikimedia.post_password
DELIMITER //
CREATE FUNCTION `post_password`(`param` TEXT
) RETURNS int(11)
    COMMENT 'Inserts a new password and returns its id (Used in UserController.php)'
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
)
    COMMENT 'Creates a new user after adding new email and password to tables (Used in UserController.php)'
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
)
    COMMENT 'Updates existing entry (Used in EntryController.php)'
BEGIN
UPDATE entries SET title = JSON_VALUE(object, "$.title"), content = JSON_VALUE(object, "$.content"), edited_at = current_timestamp WHERE id = entry_id;
END//
DELIMITER ;

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

-- Dumping structure for procedure wikimedia.search_data
DELIMITER //
CREATE PROCEDURE `search_data`(
	IN `param` TEXT
)
    COMMENT 'Finds if any entrry matches with the given text (Used in EntryController.php)'
BEGIN
SELECT
	e.id,
	e.title
FROM
	entries AS e
WHERE
	(LOWER(e.title) LIKE CONCAT(param, "%") OR
	LOWER(e.title) LIKE CONCAT("%", param, "%") OR
	LOWER(e.title) LIKE CONCAT("%", param)) AND
	ISNULL(e.deleted_at);
END//
DELIMITER ;

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

-- Dumping data for table wikimedia.topics: ~0 rows (approximately)
DELETE FROM `topics`;
INSERT INTO `topics` (`id`, `owner`, `name`, `title`, `content`, `created_at`, `edited_at`, `deleted_at`) VALUES
	(1, 1, 'wiki', 'User guide', 'Link to the <a href="/wiki/admin">admin panel</a>.', '2025-09-16 09:09:12', '2025-09-22 13:07:33', NULL);

-- 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 AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores all created user profiles. Has username, email id and password id.';

-- Dumping data for table wikimedia.users: ~2 rows (approximately)
DELETE FROM `users`;
INSERT INTO `users` (`id`, `username`, `email`, `password`) VALUES
	(1, 'NewUser1225', 1, 1),
	(2, 'NewUser9385', 2, 2);

-- Dumping structure for procedure wikimedia.user_allowed
DELIMITER //
CREATE PROCEDURE `user_allowed`(
	IN `user_id` INT,
	IN `entry_id` INT
)
    COMMENT 'Checks if user created set entry by user and entry id and returns entry id (Used in EntryController.php)'
BEGIN
SELECT
	e.id
FROM
	entries AS e
WHERE
	e.user_id = user_id AND
	e.id = entry_id;
END//
DELIMITER ;

/*!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) */;