-- --------------------------------------------------------
-- 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 post_site
CREATE DATABASE IF NOT EXISTS `post_site` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `post_site`;

-- Dumping structure for table post_site.account
CREATE TABLE IF NOT EXISTS `account` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'User ID',
  `email` varchar(20) NOT NULL,
  `username` varchar(20) NOT NULL COMMENT 'User name',
  `phone` varchar(11) NOT NULL DEFAULT '0',
  `password` varchar(20) NOT NULL COMMENT 'User password',
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Dumping data for table post_site.account: ~7 rows (approximately)
INSERT INTO `account` (`ID`, `email`, `username`, `phone`, `password`) VALUES
	(1, 'ral@', 'ral', '0', 'a1'),
	(4, 'ral@1', 'ral1', '1', 'a2'),
	(5, 'ral@2', 'ral2', '2', 'a2'),
	(25, 'wgaretsnrd@gare', 'ralh', '76864747', 'grahsdgn'),
	(26, 'wdefrdfg@frgsbdfgx', 'grsbgdfnh c', '7654543', 'gsdfbxvc'),
	(29, 'ral@4', 'ral4', '4', 'a4'),
	(33, 'ral@5', 'ral5', '5', 'a5'),
	(34, 'ral@6', 'ral6', '6', 'a6');

-- Dumping structure for function post_site.check_user
DELIMITER //
CREATE FUNCTION `check_user`(`mail` VARCHAR(50),
	`number` INT
) RETURNS varchar(40) CHARSET utf8 COLLATE utf8_unicode_ci
    SQL SECURITY INVOKER
BEGIN #pārbauda vai lietotājvārds, epasts un telefona nummurs nav jau saglabāts datubāzē
DECLARE email_result VARCHAR(30);
DECLARE number_result VARCHAR(30);
DECLARE result VARCHAR(30);

SET email_result = (SELECT if (EXISTS (SELECT email FROM account WHERE email = mail), "email", "0"));
SET number_result = (SELECT if (EXISTS (SELECT number FROM account WHERE phone = number), "phone", "0"));

SET result = CONCAT_WS(",",email_result,number_result);
RETURN (result);
END//
DELIMITER ;

-- Dumping structure for function post_site.create_account
DELIMITER //
CREATE FUNCTION `create_account`(`email` VARCHAR(50),
	`username` VARCHAR(50),
	`phone` INT,
	`password` VARCHAR(50)
) RETURNS varchar(60) CHARSET utf8 COLLATE utf8_unicode_ci
    SQL SECURITY INVOKER
BEGIN #izveido lietotāja kontu, izmantojot lietotājvārdu, epastu, telefona numuru, paroli un check_user funkciju
DECLARE account_exists VARCHAR(60);
DECLARE account_id int;

set account_exists = (SELECT check_user(email, phone));

if account_exists = "0,0" then
INSERT INTO account (email, username, phone, PASSWORD) VALUE (email, username, phone, PASSWORD);
SET account_id = (SELECT id FROM account o WHERE o.email=email);
RETURN(CONCAT(account_id,"-account_created"));
ELSE 
RETURN(account_exists);
END if;

END//
DELIMITER ;

-- Dumping structure for function post_site.login
DELIMITER //
CREATE FUNCTION `login`(`name` VARCHAR(50),
	`pass` VARCHAR(50)
) RETURNS varchar(40) CHARSET utf8 COLLATE utf8_unicode_ci
    SQL SECURITY INVOKER
BEGIN DECLARE USER VARCHAR(20); DECLARE user_id INT;


if LOCATE("@", NAME) = 0 THEN SET USER = "username"; #pārbauda vai ievadītie dati ir ēpasts vai lietottājvārds, meklējot @ simbolu
SET user_id = (
SELECT id
FROM account
WHERE username = NAME); ELSE SET USER = "email"; SET user_id = (
SELECT id
FROM account
WHERE email = NAME); END if;

if USER = "username" THEN 
if (
SELECT PASSWORD
FROM account
WHERE username = name) = pass THEN
if EXISTS(
SELECT account_id
FROM USER
WHERE account_id=user_id) THEN
UPDATE USER SET TIME = NOW()
WHERE account_id=user_id; ELSE
INSERT INTO USER (account_id, TIME) VALUES (user_id, NOW()); END if; RETURN(CONCAT (user_id,"-loged in successfully")); ELSE RETURN("incorrect username or password"); END if; ELSEIF
(
SELECT PASSWORD
FROM account
WHERE email = name) = pass THEN
if EXISTS(
SELECT account_id
FROM USER
WHERE account_id=user_id) THEN
UPDATE USER SET TIME = NOW(); ELSE
INSERT INTO USER (account_id, TIME) VALUES (user_id, NOW()); END if; RETURN(user_id,"-loged in successfully"); ELSE RETURN("incorrect email or password"); END if; END//
DELIMITER ;

-- Dumping structure for table post_site.posts
CREATE TABLE IF NOT EXISTS `posts` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int(11) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `price` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `image` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL,
  `tags_id` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Dumping data for table post_site.posts: ~3 rows (approximately)
INSERT INTO `posts` (`ID`, `account_id`, `title`, `price`, `description`, `image`, `created_at`, `updated_at`, `tags_id`) VALUES
	(8, 0, '3', '3', '3', '[{"filename":"scunt_1756893687_4ddaad0bc9c0ac4eeb30.jpg","originalfilename":"scunt.jpg","filetitle":"scunt.jpg"},{"filename":"shriek_1756893687_79a85dd3a2d0b31c2367.jpg","originalfilename":"shriek.jpg","filetitle":"shriek.jpg"},{"filename":"super-dumb_1756893687_0b3c124e5a7ee860a4e4.jpg","originalfilename":"super dumb.jpg","filetitle":"super dumb.jpg"}]', '2025-09-03 07:01:27', NULL, '["s"]'),
	(9, 0, '4', '4', '4', '[{"filename":"shriek_1756894429_c08ddb5a0fc784002126.jpg","originalfilename":"shriek.jpg","filetitle":"shriek.jpg"}]', '2025-09-03 07:13:49', NULL, '[""]'),
	(10, 0, '5', '5', '5', '[{"filename":"america-hurricane_1758017265_80a54133b9b90d615387.png","originalfilename":"america-hurricane.png","filetitle":"america-hurricane.png"},{"filename":"scunt_1758017265_27a369aef4bbb6c104e0.jpg","originalfilename":"scunt.jpg","filetitle":"scunt.jpg"},{"filename":"shriek_1758017265_348b500833dfc7f001b5.jpg","originalfilename":"shriek.jpg","filetitle":"shriek.jpg"},{"filename":"super-dumb_1758017265_718202e6ba40ea91a17b.jpg","originalfilename":"super dumb.jpg","filetitle":"super dumb.jpg"},{"filename":"testicular_torsion_wizard_meme_banner_1758017265_678534f6e1c396bf77cc.jpg","originalfilename":"Testicular_Torsion_Wizard_meme_banner.jpg","filetitle":"Testicular_Torsion_Wizard_meme_banner.jpg"}]', '2025-09-16 07:07:45', NULL, '["fsafe"]');

-- Dumping structure for table post_site.tags
CREATE TABLE IF NOT EXISTS `tags` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `tags` longtext NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Dumping data for table post_site.tags: ~0 rows (approximately)

-- Dumping structure for function post_site.timeout
DELIMITER //
CREATE FUNCTION `timeout`(`user_id` INT
) RETURNS varchar(20) CHARSET utf8 COLLATE utf8_unicode_ci
    SQL SECURITY INVOKER
    COMMENT 'maybe'
BEGIN


#when user logs in or refreshes, check the timeout time, if the time is past timeout time, log out, if the time is before
#timeout time, refresh the timout time
#select TIME FROM user WHERE account_id = user_id;
RETURN("fraewga");
END


#when user logs in or refreshes, check the timeout time, if the time is past timeout time, log out, if the time is before
#timeout time, refresh the timout time//
DELIMITER ;

-- Dumping structure for table post_site.user
CREATE TABLE IF NOT EXISTS `user` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Login ID',
  `account_id` int(10) unsigned NOT NULL COMMENT 'User id',
  `time` datetime NOT NULL COMMENT 'Time to work',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Dumping data for table post_site.user: ~1 rows (approximately)
INSERT INTO `user` (`ID`, `account_id`, `time`) VALUES
	(18, 1, '2026-01-27 14:07:00');

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