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

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

-- Dumping structure for procedure login_system.add_permission_to_role
DELIMITER //
CREATE PROCEDURE `add_permission_to_role`(
	IN `p_role_name` VARCHAR(50),
	IN `p_permission_name` VARCHAR(50)
)
BEGIN
 DECLARE v_role_id INT;
    DECLARE v_permission_id INT;

    -- Pārbaudām, vai loma eksistē
    SELECT id INTO v_role_id FROM Roles WHERE name = p_role_name;
    
    -- Pārbaudām, vai atļauja eksistē
    SELECT id INTO v_permission_id FROM Permissions WHERE name = p_permission_name;

    -- Ja abas vērtības eksistē, pievienojam tās tabulā Role_Permissions
    IF v_role_id IS NOT NULL AND v_permission_id IS NOT NULL THEN
        -- Pārbaudām, vai ieraksts jau eksistē
        IF NOT EXISTS (SELECT 1 FROM Role_Permissions WHERE role_id = v_role_id AND permission_id = v_permission_id) THEN
            INSERT INTO role_permissions (role_id, permission_id) VALUES (v_role_id, v_permission_id);
        END IF;
    END IF;
END//
DELIMITER ;

-- Dumping structure for procedure login_system.add_verified_email
DELIMITER //
CREATE PROCEDURE `add_verified_email`(
	IN `mail` VARCHAR(250),
	IN `usname` VARCHAR(250),
	IN `rol` VARCHAR(255)
)
BEGIN
DECLARE roleId INT DEFAULT NULL;
    -- Mēģinām atrast lomas ID pēc lomas nosaukuma
    SELECT id INTO roleId FROM Roles WHERE name = rol LIMIT 1;

    -- Ja atrasts, tad pievienojam lietotāju ar šo role_id
    IF roleId IS NOT NULL THEN
        INSERT INTO verified_users (email, sname, role_id)
        VALUES (mail, usname, roleId);
   END if;
END//
DELIMITER ;

-- Dumping structure for function login_system.check_email
DELIMITER //
CREATE FUNCTION `check_email`(`mail` VARCHAR(50)
) RETURNS int(11)
BEGIN
DECLARE usercheck INT;
SET @usercheck = 0;
SELECT vu.id INTO @usercheck
FROM verified_users vu
WHERE vu.email = mail;
RETURN @usercheck;
END//
DELIMITER ;

-- Dumping structure for table login_system.files
CREATE TABLE IF NOT EXISTS `files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `checksum` varchar(64) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `folder` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `checksum` (`checksum`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `files_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Data exporting was unselected.

-- Dumping structure for table login_system.folders
CREATE TABLE IF NOT EXISTS `folders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Data exporting was unselected.

-- Dumping structure for table login_system.permissions
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=UTF8MB4_GENERAL_CI;

-- Dumping data for table login_system.permissions: ~5 rows (approximately)
INSERT INTO `permissions` (`id`, `name`) VALUES
	(4, 'add_user'),
	(2, 'delete_users'),
	(1, 'edit_posts'),
	(5, 'get_roles'),
	(3, 'view_dashboard');

-- Data exporting was unselected.

-- Dumping structure for table login_system.roles
CREATE TABLE IF NOT EXISTS `roles` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table login_system.roles: ~3 rows (approximately)
INSERT INTO `roles` (`id`, `name`) VALUES
	(1, 'admin'),
	(2, 'moderator'),
	(3, 'user');

-- Data exporting was unselected.

-- Dumping structure for table login_system.role_permissions
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `role_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table login_system.role_permissions: ~4 rows (approximately)
INSERT INTO `role_permissions` (`role_id`, `permission_id`) VALUES
	(1, 1),
	(1, 2),
	(1, 3),
	(2, 3),
	(3, 1); 

-- Data exporting was unselected.

-- Dumping structure for table login_system.shared_files
CREATE TABLE IF NOT EXISTS `shared_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_id` int(11) NOT NULL,
  `sender_id` int(11) NOT NULL,
  `receiver_id` int(11) NOT NULL,
  `permission` enum('view','edit') NOT NULL DEFAULT 'view',
  `shared_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `file_id` (`file_id`),
  KEY `sender_id` (`sender_id`),
  KEY `receiver_id` (`receiver_id`),
  CONSTRAINT `shared_files_ibfk_1` FOREIGN KEY (`file_id`) REFERENCES `files` (`id`),
  CONSTRAINT `shared_files_ibfk_2` FOREIGN KEY (`sender_id`) REFERENCES `verified_users` (`id`),
  CONSTRAINT `shared_files_ibfk_3` FOREIGN KEY (`receiver_id`) REFERENCES `verified_users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Data exporting was unselected.

-- Dumping structure for table login_system.users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email_id` varchar(100) NOT NULL,
  `otp` varchar(6) DEFAULT NULL,
  `otp_expiry` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Data exporting was unselected.

-- Dumping structure for table login_system.verified_users
CREATE TABLE IF NOT EXISTS `verified_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `sname` varchar(250) NOT NULL,
  `Role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `fk_verifieduser_role` (`Role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_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) */;