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


-- Dumping structure for table tasnedarbs.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=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table tasnedarbs.emails: ~4 rows (approximately)
INSERT INTO `emails` (`id`, `email`) VALUES
	(1, 'test@work.com'),
	(2, 'gang@star.com'),
	(4, 'yoyoyo@email.com'),
	(5, 'ganggang@email.com');

-- Dumping structure for table tasnedarbs.entries
CREATE TABLE IF NOT EXISTS `entries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT 1,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `edited_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table tasnedarbs.entries: ~2 rows (approximately)
INSERT INTO `entries` (`id`, `user_id`, `title`, `description`, `created_at`, `edited_at`) VALUES
	(2, 1, 'another test', 'hope this works', '2025-02-27 13:22:11', NULL),
	(3, 1, 'yoyoyo', 'this is fire entry yo', '2025-02-27 17:20:43', NULL);

-- Dumping structure for procedure tasnedarbs.get_entry
DELIMITER //
CREATE PROCEDURE `get_entry`(
	IN `entry_id` INT
)
BEGIN
SELECT
	e.id,
	u.username,
	e.title,
	e.description,
	e.created_at,
	e.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 table tasnedarbs.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=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table tasnedarbs.passwords: ~2 rows (approximately)
INSERT INTO `passwords` (`id`, `password`) VALUES
	(1, '12345678'),
	(2, '87654321');

-- Dumping structure for procedure tasnedarbs.search_data
DELIMITER //
CREATE PROCEDURE `search_data`(
	IN `param` TEXT
)
BEGIN
SELECT
	e.id,
	u.username,
	e.title,
	e.description
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 table tasnedarbs.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`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table tasnedarbs.users: ~4 rows (approximately)
INSERT INTO `users` (`id`, `username`, `email`, `password`) VALUES
	(1, 'test_user', 1, 1),
	(2, 'yoyoyo', 2, 1),
	(3, 'karlitos', 4, 2),
	(4, 'testeris', 5, 2);

