-- --------------------------------------------------------
-- Server OS:                    Win64
-- HeidiSQL Version:             12.5.0.6677
-- --------------------------------------------------------



-- 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 `entry_id` 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 = entry_id;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.delete_file
DELIMITER //
CREATE PROCEDURE `delete_file`(
	IN `file_id` INT
)
    COMMENT 'Sets a value on "deleted_at" column for a file (used in FileController.php)'
BEGIN
UPDATE files SET deleted_at = current_timestamp WHERE id = file_id;
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=11 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
	(6, 'b@b.b'),
	(8, '12345');

-- 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,
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'URL name',
  `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_topic_id` (`topic_id`),
  CONSTRAINT `fk_topic_id` FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores all entry data.';

-- Dumping data for table wikimedia.entries: ~6 rows (approximately)
DELETE FROM `entries`;
INSERT INTO `entries` (`id`, `topic_id`, `name`, `title`, `content`, `created_at`, `edited_at`, `deleted_at`) VALUES
	(2, 1, 'Bingus', 'Bingus', '<h2>Šmingus</h2>\nViņš ir <span style="color: blue">zils</span> :(\ncehck', '2025-09-16 12:55:20', '2025-12-10 13:27:16', '2025-12-10 13:28:04'),
	(3, 1, 'Formatting_stuff', 'Formatting stuff', '# Heading 1\n{| content-list |}\n## Heading 2\n### Heading 3\n#### Heading 4\n##### Heading 5\n###### Heading 6\n\n\'\'italic\'\'\n==redacted==\n**bold**\n__underlined__\n~~crossed out~~\n\n```code```\n\n> List\n>> Indented list\n>>> yo\n\n[[link="https://www.w3schools.com/tags/tag_audio.asp"]]\n[[link="https://www.w3schools.com/tags/tag_audio.asp"|text="link"]]\n\n[[file="1759314153_41e38eac3d205a355258.jpg"|text="**img of floppa**"|style="width:200px"]]\n[[file="1759314243_533f50b6de29b8d10b35.mp3"|style="width: 400px; background-color: yellow"|text="Audio file or smt"]]\n\n{{style="color: blue"|text="styled text"}}\n\n<h1>ghgassgf</h1>\n\n{| borders\n|-\n!! Heading 1\n!! Heading 2\n|-\n|| Cell 1 || \n|| Cell 2\n|-\n|| Cell 3 whaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaat\n|| Cell 4\n|}\n\n{| borders style="width: 800px"\n|-\n!! {80%} Heading 1\n!! {20%} Heading 2\n|-\n|| Cell 1 \n|| [[file="1759314243_533f50b6de29b8d10b35.mp3"|style="width: 400px; background-color: yellow"|text="Audio file or smt"]]\n|-\n|| Cell 3\n|| Cell 4\n|}\n', '2025-09-16 14:14:23', '2025-12-17 14:36:40', NULL),
	(5, 2, 'massive_been', 'massive been', '{| content-list |}\ngang gang gang gang', '2025-12-16 12:02:29', '2025-12-16 12:29:04', '2025-12-16 12:29:16'),
	(6, 2, 'Testing_comments', 'Testing comments', 'By adding &&& at the beginning, the user can create a comment that is only visible while editing.\n**Example:**\n&&& This is a comment\nThis is a paragraph', '2025-12-17 10:10:31', '2025-12-17 10:16:12', NULL),
	(7, 2, 'load_content.js', 'load_content.js', 'You can [[link="/test/load_content.js?a=edit"|text="edit"]] this page here\n\n&&& cool comment\n{| content-list |}\n\ntext\n# \'\'Heading 1\'\'\ntext\n## ==Heading 2==\ntext\n### **Heading 3**\ntext\n#### __Heading 4__\ntext\n##### ~~Heading 5~~\ntext\n###### Heading 6\n\n\'\'text\'\'\n==text==\n**text**\n__text__\n~~text~~\n\n# {{style="color: blue"|text="styled text"}}\n\nLorem ipsum __~~dolor sit **amet, co**nsectetur~~ adipiscing el==it, sed do eiusmod tempor incididunt== ut \'\'labore et dolore magna\'\' aliqua__.\n\nThis is ```code text```\nIt should disable everything ```check\n\nLorem ipsum __~~dolor sit **amet, co**nsectetur~~ adipiscing el==it, sed do eiusmod tempor incididunt== ut \'\'labore et dolore magna\'\' aliqua__.\n\nshould be it```\n\n**LIST!!!!!!!!**\n> text\n> text\n>> text\n>>> text\n> {{style="color: blue"|text="styled **text**"}}\n>>>> text\n>> text\n> > what\n\n[[link="dadada"|text="ba {{style="color: blue"|text="bink"}}"]]\n\n[[file="1765878110_f35932e2fc15785a83c9.png"|text="text {{style="color: blue"|text="styled text"}}"|style="width:200px"]]', '2025-12-17 10:28:18', '2026-01-08 14:13:33', NULL),
	(8, 2, 'bink', 'bink', 'You can [[link="/test/bink?a=edit"|text="edit"]] this page here\n\n{| content-list |}\n\n# {{style="color: blue"|text="styled text"}}\n\n```{{style="color: blue"|text="[[link="/test/bink?a=edit"|text="edit"]] ep"}}\n\n```\n\n[[link="/test/load_content.js?a=edit"|text="edit"]] and this [[link="/test/load_content.js?a=edit"|text="jiggle [[that]]bell"]]\n\n[[file="1765878110_f35932e2fc15785a83c9.png"|text="[[link="/test/load_content.js?a=edit"|text="==yooooooooo== and "quotes""]]"|style="width: 200px"]]', '2026-01-08 14:20:32', '2026-01-12 12:22:54', 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,
  `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`),
  CONSTRAINT `fk_topic` FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores all file data';

-- Dumping data for table wikimedia.files: ~3 rows (approximately)
DELETE FROM `files`;
INSERT INTO `files` (`id`, `topic_id`, `name`, `description`, `created_at`, `edited_at`, `deleted_at`) VALUES
	(5, 1, '1759314153_41e38eac3d205a355258.jpg', 'gaba gooo', '2025-10-01 13:22:33', '2025-12-05 09:30:37', NULL),
	(6, 1, '1759314243_533f50b6de29b8d10b35.mp3', 'Old remix of it', '2025-10-01 13:24:03', NULL, NULL),
	(7, 1, '1764920112_44f8bd3955873294b367.png', 'less goo', '2025-12-05 09:35:12', NULL, '2025-12-07 20:39:59'),
	(10, 1, '1764926245_c0368e0239a725cb656b.jpg', 'Should have a description now', '2025-12-05 11:17:25', '2025-12-07 20:27:33', NULL),
	(11, 2, '1765878110_f35932e2fc15785a83c9.png', '{{style="color: red"|text="ŠUKUNA"}}', '2025-12-16 11:41:50', '2025-12-16 11:52:21', NULL);

-- Dumping structure for procedure wikimedia.find_email
DELIMITER //
CREATE PROCEDURE `find_email`(
	IN `email` TEXT
)
    COMMENT 'Give email, find email 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 'Give email and password id, find user id and name (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 'Give username, find user 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 'Find all entries data with the same topic id (used in EntryController.php)'
BEGIN
SELECT
	e.id,
	e.name,
	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_name` VARCHAR(50)
)
    COMMENT 'Give entry name, get entry data (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.name = entry_name AND
	ISNULL(e.deleted_at);
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_file
DELIMITER //
CREATE PROCEDURE `get_file`(
	IN `givenfilename` VARCHAR(255)
)
    COMMENT 'Give file name, get file data (used in FileController.php)'
BEGIN
SELECT
	f.id,
	f.name,
	f.description,
	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.name = givenfilename AND
	ISNULL(f.deleted_at);
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_password_using_email
DELIMITER //
CREATE PROCEDURE `get_password_using_email`(
	IN `emailID` INT
)
    COMMENT 'Give email id, get password data (used in UserController.php)'
BEGIN
SELECT
	p.id,
	p.`password`,
	p.salt
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)
)
    COMMENT 'Give topic name, get topic data (used in TopicController.php)'
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_files
DELIMITER //
CREATE PROCEDURE `get_topic_files`(
	IN `topic_id` INT
)
    COMMENT 'Give topic id, get all files related to that topic (used in FileController.php)'
BEGIN
SELECT
	f.id,
	f.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_id
DELIMITER //
CREATE PROCEDURE `get_topic_id`(
	IN `given_name` VARCHAR(50)
)
    COMMENT 'Give topic name, get topic id (used in TopicController.php)'
BEGIN
SELECT
	t.id
FROM
	topics AS t
WHERE
	t.name = given_name;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.get_topic_owner
DELIMITER //
CREATE PROCEDURE `get_topic_owner`(
	IN `given_name` VARCHAR(50)
)
    COMMENT 'Give topic name, get topic owner (used in TopicController.php)'
BEGIN
SELECT
	t.id,
	t.owner
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,
  `salt` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 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`, `salt`) VALUES
	(5, '$2y$10$NtIiP7/27XCn4s5uuF5R5uSM/Z3QSZtQVGGm58mmOSOx1EUKwWH0a', 'kS8pPVAbKM'),
	(7, '$2y$10$1Oj47u8logCJcrTcGKuVluO8opOUCsSpVjtdt9nbwf.pX3JdGYpGS', 'dBbgYUHLQh');

-- Dumping structure for function wikimedia.post_email
DELIMITER //
CREATE FUNCTION `post_email`(`param` TEXT
) RETURNS int(11)
    COMMENT 'Creates new email, gives email id (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, name, title, content) VALUES (JSON_VALUE(object, "$.topic_id"), JSON_VALUE(object, "$.name"), 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
)
    COMMENT 'Creates a file entry (used in FileController.php)'
BEGIN
INSERT INTO files (topic_id, name, description) VALUES (JSON_VALUE(object, "$.topic_id"), JSON_VALUE(object, "$.name"), JSON_VALUE(object, "$.description"));
END//
DELIMITER ;

-- Dumping structure for function wikimedia.post_password
DELIMITER //
CREATE FUNCTION `post_password`(`hashed` TEXT,
	`salt` TEXT
) RETURNS int(11)
    COMMENT 'Creates new password, gives password id (used in UserController.php)'
BEGIN
INSERT INTO passwords (PASSWORD, salt) VALUES (hashed, salt);
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 (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 entry''s content (used in EntryController.php)'
BEGIN
UPDATE entries SET NAME = JSON_VALUE(object, "$.name"), 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_file
DELIMITER //
CREATE PROCEDURE `put_file`(
	IN `file_id` INT,
	IN `file_desc` TEXT
)
    COMMENT 'Updates file''s description (used in FileController.php)'
BEGIN
UPDATE files SET description = file_desc, edited_at = current_timestamp WHERE id = file_id;
END//
DELIMITER ;

-- Dumping structure for procedure wikimedia.put_topic
DELIMITER //
CREATE PROCEDURE `put_topic`(
	IN `object` JSON,
	IN `topic_id` INT
)
    COMMENT 'Updates topic content (used in TopicController.php)'
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,
	IN `topic_id` INT
)
    COMMENT 'Give text, find entries (used in EntryController.php)'
BEGIN
SELECT
	e.id,
	e.name,
	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
	e.topic_id = topic_id 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=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores all topic data';

-- 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, 5, 'wiki', 'User guide', 'Link to the [[link="/wiki/Page:Admin"|text="admin panel"]].', '2025-09-16 09:09:12', '2025-12-10 11:25:49', NULL),
	(2, 8, 'test', 'Testing', '[[link="/test/Page:Admin"]]\n\n[[file="1765878110_f35932e2fc15785a83c9.png"|style="width: 500px; border: solid 2px red"]]', '2025-12-16 11:09:43', '2025-12-16 12:27:59', 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=11 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
	(5, 'badmin', 6, 5),
	(8, 'tester001', 8, 7);

-- Dumping structure for procedure wikimedia.verify_file
DELIMITER //
CREATE PROCEDURE `verify_file`(
	IN `givenfilename` VARCHAR(50),
	IN `giventopicname` VARCHAR(255)
)
    COMMENT 'Give topic and file name, get file id (used in FileController.php)'
BEGIN
SELECT
	f.id
FROM
	files AS f
LEFT JOIN topics AS t
ON t.id = f.topic_id
WHERE
	f.name = givenfilename AND
	t.name = giventopicname AND
	ISNULL(f.deleted_at);
END//
DELIMITER ;

