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

-- Dumping structure for table portfolio.about
CREATE TABLE IF NOT EXISTS `about` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `image` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.about: ~3 rows (approximately)
INSERT INTO `about` (`id`, `title`, `description`, `image`) VALUES
	(1, 'Where I\'m From', 'I come from a small, sunny town called "Beja", located in the south of Portugal.', '/portfolio/icons/about/beja.webp'),
	(2, 'Graduated in Game Development', 'I completed a Bachelor\'s degree in Game Development at IADE - Creative University, located in Lisbon, Portugal.', '/portfolio/icons/about/iade.webp'),
	(3, 'Erasmus Experience in the United Kingdom', 'During the final semester of my Bachelor\'s degree, I participated in the Erasmus program at Abertay University in Dundee, Scotland.', '/portfolio/icons/about/abertay.webp');

-- Dumping structure for table portfolio.categories
CREATE TABLE IF NOT EXISTS `categories` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT COMMENT 'Category ID',
  `name` varchar(50) DEFAULT NULL COMMENT 'Category name',
  `logo` varchar(255) DEFAULT NULL COMMENT 'Category logo',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.categories: ~4 rows (approximately)
INSERT INTO `categories` (`id`, `name`, `logo`) VALUES
	(1, 'Games', '/portfolio/icons/categories/games.webp'),
	(2, 'Game Systems', '/portfolio/icons/categories/game_systems.webp'),
	(3, 'Websites', '/portfolio/icons/categories/websites.webp'),
	(4, 'Apps', '/portfolio/icons/categories/apps.webp');

-- Dumping structure for table portfolio.experience
CREATE TABLE IF NOT EXISTS `experience` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Experience id',
  `title` varchar(255) DEFAULT NULL COMMENT 'Experience title',
  `org` varchar(50) DEFAULT NULL,
  `start_date` date DEFAULT NULL COMMENT 'Experice start date',
  `end_date` date DEFAULT NULL COMMENT 'Experice end date',
  `context` enum('Academic','Professional') DEFAULT NULL COMMENT 'Experience context',
  `description` text DEFAULT NULL COMMENT 'Description about the experience',
  `logo` varchar(255) DEFAULT NULL COMMENT 'Experience logo url',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.experience: ~2 rows (approximately)
INSERT INTO `experience` (`id`, `title`, `org`, `start_date`, `end_date`, `context`, `description`, `logo`) VALUES
	(1, 'Bachelor\'s degree, Games Development', 'IADE - Creative University', '2020-09-16', '2024-07-20', 'Academic', ' • Designed and developed games using tools such as Unity, Love2D, and P5.js, with scripting experience in C#, Lua, and JavaScript.\\n\\n\r\n • Created a single-player Unity 2D and Unity 3D project, implementing FSM-driven AI behaviors and custom shaders while collaborating with multidisciplinary teams.\\n\\n\r\n • Built a simple multiplayer game prototype in Unreal Engine using C++ and Blueprints, gaining foundational knowledge of design patterns and server matchmaking systems.\\n\\n\r\n • Worked with basic database systems (MySQL) and backend development using Node.js to manage simple game data and server communications.\\n\\n\r\n • Applied data structures to enhance game functionality and optimize performance.\\n\\n\r\n • Learned foundational concepts in game security, including reverse engineering and memory hacking with tools like CheatEngine and DotPeek.\\n\\n\r\n • Strengthened project management and teamwork skills using tools like Trello and ClickUp to lead collaborative game development projects.', '/portfolio/icons/experience/iade.webp'),
	(2, ' Bachelor\'s degree, Computer Games Application Development', 'Abertay University', '2023-01-22', '2023-06-18', 'Academic', ' • During the 6th semester of my Bachelor\'s in Game Development, I participated in an exchange program at Abertay University in Dundee, Scotland. As the game programmer in a team working with Astrodreamer Studio, I collaborated with game producers and designers to develop a Unity 3D game. This project, supervised by Abertay University, honed my skills in programming, teamwork, and project management within a professional studio environment.', '/portfolio/icons/experience/abertay.webp');

-- Dumping structure for table portfolio.experience_languages
CREATE TABLE IF NOT EXISTS `experience_languages` (
  `experience_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  PRIMARY KEY (`experience_id`,`language_id`),
  KEY `fk_exp_language_language_id` (`language_id`),
  CONSTRAINT `fk_exp_language_experience_id` FOREIGN KEY (`experience_id`) REFERENCES `experience` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_exp_language_language_id` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.experience_languages: ~6 rows (approximately)
INSERT INTO `experience_languages` (`experience_id`, `language_id`) VALUES
	(1, 1),
	(1, 2),
	(1, 3),
	(1, 5),
	(1, 6),
	(2, 2);

-- Dumping structure for table portfolio.experience_tech
CREATE TABLE IF NOT EXISTS `experience_tech` (
  `experience_id` int(11) NOT NULL,
  `tech_id` int(11) NOT NULL,
  PRIMARY KEY (`experience_id`,`tech_id`),
  KEY `fk_exp_tech_tech_id` (`tech_id`),
  CONSTRAINT `fk_exp_tech_experience_id` FOREIGN KEY (`experience_id`) REFERENCES `experience` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_exp_tech_tech_id` FOREIGN KEY (`tech_id`) REFERENCES `tech` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.experience_tech: ~4 rows (approximately)
INSERT INTO `experience_tech` (`experience_id`, `tech_id`) VALUES
	(1, 1),
	(1, 2),
	(1, 5),
	(2, 1);

-- Dumping structure for table portfolio.experience_tools
CREATE TABLE IF NOT EXISTS `experience_tools` (
  `experience_id` int(11) NOT NULL COMMENT 'Experience ID',
  `tool_id` int(11) NOT NULL COMMENT 'Tool ID',
  PRIMARY KEY (`experience_id`,`tool_id`),
  KEY `fk_exp_tools_tool_id` (`tool_id`),
  CONSTRAINT `fk_exp_tools_experience_id` FOREIGN KEY (`experience_id`) REFERENCES `experience` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_exp_tools_tool_id` FOREIGN KEY (`tool_id`) REFERENCES `tools` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.experience_tools: ~13 rows (approximately)
INSERT INTO `experience_tools` (`experience_id`, `tool_id`) VALUES
	(1, 1),
	(1, 2),
	(1, 3),
	(1, 4),
	(1, 5),
	(1, 7),
	(1, 9),
	(1, 10),
	(1, 11),
	(1, 12),
	(2, 1),
	(2, 5),
	(2, 8);

-- Dumping structure for table portfolio.languages
CREATE TABLE IF NOT EXISTS `languages` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier for each language',
  `name` varchar(50) DEFAULT NULL COMMENT 'Programming language name',
  `logo` varchar(255) DEFAULT NULL COMMENT 'Programming language logo path',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.languages: ~7 rows (approximately)
INSERT INTO `languages` (`id`, `name`, `logo`) VALUES
	(1, 'C++', '/portfolio/icons/languages/cpp.webp'),
	(2, 'C#', '/portfolio/icons/languages/csharp.webp'),
	(3, 'C', '/portfolio/icons/languages/c.webp'),
	(5, 'Javascript', '/portfolio/icons/languages/javascript.webp'),
	(6, 'Lua', '/portfolio/icons/languages/lua.webp'),
	(7, 'HTML5', '/portfolio/icons/languages/html5.webp'),
	(8, 'CSS', '/portfolio/icons/languages/css.webp');

-- Dumping structure for table portfolio.languages_skill_proficiency
CREATE TABLE IF NOT EXISTS `languages_skill_proficiency` (
  `language_id` int(11) NOT NULL COMMENT 'Language ID',
  `skill_proficiency_id` int(11) NOT NULL COMMENT 'Skill Proficiency',
  PRIMARY KEY (`language_id`,`skill_proficiency_id`),
  KEY `fk_languages_skill_profeciency_id` (`skill_proficiency_id`),
  CONSTRAINT `fk_languages_skill_id` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_languages_skill_profeciency_id` FOREIGN KEY (`skill_proficiency_id`) REFERENCES `skill_proficiency_ranking` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.languages_skill_proficiency: ~7 rows (approximately)
INSERT INTO `languages_skill_proficiency` (`language_id`, `skill_proficiency_id`) VALUES
	(1, 2),
	(2, 3),
	(3, 1),
	(5, 2),
	(6, 3),
	(7, 2),
	(8, 2);

-- Dumping structure for table portfolio.links
CREATE TABLE IF NOT EXISTS `links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL COMMENT 'Link name',
  `logo` varchar(255) DEFAULT NULL COMMENT 'Link logo',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.links: ~1 rows (approximately)
INSERT INTO `links` (`id`, `name`, `logo`) VALUES
	(1, 'build', NULL);

-- Dumping structure for table portfolio.logos
CREATE TABLE IF NOT EXISTS `logos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(50) DEFAULT NULL COMMENT 'Type of logo',
  `reference_id` int(11) DEFAULT NULL COMMENT 'ID from related table',
  `logo_path` varchar(255) DEFAULT NULL COMMENT 'Path to the logo image',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.logos: ~4 rows (approximately)
INSERT INTO `logos` (`id`, `type`, `reference_id`, `logo_path`) VALUES
	(1, 'Date', NULL, '/portfolio/icons/misc/calendar.svg'),
	(2, 'Academic', NULL, '/portfolio/icons/misc/academic.svg'),
	(3, 'Professional', NULL, '/portfolio/icons/misc/professional.svg'),
	(4, 'Personal', NULL, '/portfolio/icons/misc/personal.svg');

-- Dumping structure for table portfolio.page
CREATE TABLE IF NOT EXISTS `page` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `background_image` varchar(255) DEFAULT NULL,
  `project_logo` varchar(255) DEFAULT NULL,
  `page_color` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.page: ~6 rows (approximately)
INSERT INTO `page` (`id`, `name`, `background_image`, `project_logo`, `page_color`) VALUES
	(1, 'dreamsturbia', '/portfolio/projects/dreamsturbia/images/background.jpg', '/portfolio/projects/dreamsturbia/images/logo.png', NULL),
	(2, 'portfolio', '', '', NULL),
	(3, 'sky_frontier', '/portfolio/projects/sky_frontier/images/background.png', NULL, NULL),
	(4, 'hfsm_unity_3d', NULL, NULL, NULL),
	(5, 'hardpoint', '/portfolio/projects/hardpoint/images/hardpoint_background.webp', NULL, NULL),
	(6, 'the_vengeance', '/portfolio/projects/the_vengeance/images/the_vengeance_background.png', NULL, NULL);

-- Dumping structure for table portfolio.projects
CREATE TABLE IF NOT EXISTS `projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier for each project.',
  `title` varchar(50) NOT NULL COMMENT '	Title of the project.',
  `description` text DEFAULT NULL COMMENT 'Detailed description of the project.',
  `category_id` tinyint(4) DEFAULT NULL COMMENT 'Category ID',
  `hook` text DEFAULT NULL COMMENT 'Brief description of the project.',
  `start_date` date DEFAULT NULL COMMENT 'Development start date.',
  `end_date` date DEFAULT NULL COMMENT 'Development end date.',
  `type` enum('Academic','Professional','Personal') DEFAULT NULL COMMENT 'Type of project.',
  `git_rep` varchar(255) DEFAULT NULL COMMENT 'Path to the project Git Repository',
  `trailer` varchar(50) DEFAULT NULL COMMENT 'YouTube link for the trailer.',
  `thumbnail_path` varchar(255) DEFAULT NULL COMMENT 'Path to the folder containing project images.',
  PRIMARY KEY (`id`),
  KEY `fk_project_category_id` (`category_id`),
  CONSTRAINT `fk_project_category_id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.projects: ~6 rows (approximately)
INSERT INTO `projects` (`id`, `title`, `description`, `category_id`, `hook`, `start_date`, `end_date`, `type`, `git_rep`, `trailer`, `thumbnail_path`) VALUES
	(1, 'Dreamsturbia', 'Dreamsturbia is a captivating 3D action-puzzle-adventure prototype that explores the haunting world of insomnia and the inner demons it creates. Players step into the role of a protagonist tormented by traumatic memories and relentless phobias, preventing her from finding peace in sleep. Seeking help from a therapist, she undergoes a trance-induced lucid dream where she must confront manifestations of her deepest fears.\r\n\r\nThis prototype was developed during my 3rd semester at university and was my first Unity 3D project. It was created in collaboration with a multidisciplinary team, where I worked alongside a design team. On this project i was responsible to create part of the NPC\'s AI, game core mechanics and level concept and design.', 1, 'A 3D action-puzzle-adventure exploring insomnia and its haunting inner demons.', '2022-02-14', '2022-06-20', 'Academic', 'Dreamsturbia-Project-IADE-Unity3D', 'https://www.youtube.com/embed/aFNQjcJDlnI', '/portfolio/projects/dreamsturbia/images/thumbnail.webp'),
	(2, 'Portfolio', 'I fully developed this website myself as my first project using React and Tailwind. It includes sections about me, my skills, and my educational qualifications. I also built the backend using Node.js, and this was my first time hosting both the frontend and backend on my NAS (Network-Attached Storage) using Docker. For data storage, I used MariaDB and managed the database with HeidiSQL.', 3, 'My portfolio website!', '2025-01-07', NULL, 'Personal', 'My-Portfolio', NULL, '/portfolio/projects/portfolio/images/portfolio_thumbnail.webp'),
	(3, 'The Vengeance', 'The Vengeance is a Unity 2D vertical slice action-adventure single-player game set in a medieval fantasy world. I originally developed this project for my 3rd semester at university. Later, I reworked it, adding more content and polishing it further by implementing finite state machines for NPCs. I also integrated a dialogue system using Ink and reworked the core mechanics of the prototype.', 1, 'The Vengeance is a 2D action-adventure single-player game set in a medieval fantasy world.', '2024-09-01', NULL, 'Academic', 'TheVengeance-Project-IADE-Unity2D', NULL, '/portfolio/projects/the_vengeance/images/the_vengeance_thumbnail.png'),
	(4, 'Sky Frontier', 'Sky Frontier is a 3D multiplayer shooter developed in Unreal Engine 4. This was my first project in UE4, created during my 5th university semester. It also introduced me to using C++ in game development.', 1, 'Sky Frontier is a 3D multiplayer shooter. Where Air force pilots enter a virtual reality world to experiment with different classes of planes and train their skills in the high skies by destroying targets and fighting against other pilots.', '2022-09-10', '2023-01-10', 'Academic', 'SkyFrontier-Project-IADE-UE4-3D', 'https://www.youtube.com/embed/2VbHDiMxcCw', '/portfolio/projects/sky_frontier/images/sky_frontier_thumbnail.webp'),
	(8, 'Hierarchical Finite State Machines', 'A simple Hierarchical State Machine system created in Unity 3D using ScriptableObjects. This is a small project I developed myself. I created this project after working with finite state machines and decided to adapt my previous implementations, applying the same philosophy to a Hierarchical Finite State Machine.', 2, 'Hierarchical State Machine system created in Unity 3D.', '2024-10-14', '2024-10-21', 'Personal', 'HierarchicalFSM-Unity3D', NULL, '/portfolio/projects/hfsm_unity_3d/images/hfsm_thumbnail.webp'),
	(9, 'HardPoint', 'Hardpoint is a 3D roguelike action-adventure shooter developed in Unity 3D during my Erasmus semester at Abertay University. The game features procedurally generated levels composed of interconnected rooms, a teleporter system for strategic loot management, and a dynamic combat system with two playable characters. I contributed to refining the game’s mechanics, implementing enemy AI.', 1, 'Hardpoint is a 3D roguelike action game', '2023-02-01', '2023-06-05', 'Academic', 'HardPoint-Project-Abertay-University-Unity3D', NULL, '/portfolio/projects/hardpoint/images/hardpoint_thumbnail.webp');

-- Dumping structure for table portfolio.project_languages
CREATE TABLE IF NOT EXISTS `project_languages` (
  `project_id` int(11) NOT NULL COMMENT 'Foreign key referencing projects.id',
  `language_id` int(11) NOT NULL COMMENT 'Foreign key referencing languages.id.',
  PRIMARY KEY (`project_id`,`language_id`),
  KEY `fk_projects_languages_language_id` (`language_id`),
  CONSTRAINT `fk_projects_languages_language_id` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_projects_languages_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.project_languages: ~8 rows (approximately)
INSERT INTO `project_languages` (`project_id`, `language_id`) VALUES
	(1, 2),
	(2, 5),
	(2, 7),
	(2, 8),
	(3, 2),
	(4, 1),
	(8, 2),
	(9, 2);

-- Dumping structure for table portfolio.project_page
CREATE TABLE IF NOT EXISTS `project_page` (
  `project_id` int(11) NOT NULL,
  `page_id` int(11) NOT NULL,
  PRIMARY KEY (`project_id`,`page_id`) USING BTREE,
  KEY `fk_project_page_page_id` (`page_id`),
  CONSTRAINT `fk_project_page_page_id` FOREIGN KEY (`page_id`) REFERENCES `page` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_project_page_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.project_page: ~6 rows (approximately)
INSERT INTO `project_page` (`project_id`, `page_id`) VALUES
	(1, 1),
	(2, 2),
	(3, 6),
	(4, 3),
	(8, 4),
	(9, 5);

-- Dumping structure for table portfolio.project_tags
CREATE TABLE IF NOT EXISTS `project_tags` (
  `project_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`project_id`,`tag_id`),
  KEY `fk_project_tag_tag_id` (`tag_id`),
  CONSTRAINT `fk_project_tag_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_project_tag_tag_id` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.project_tags: ~9 rows (approximately)
INSERT INTO `project_tags` (`project_id`, `tag_id`) VALUES
	(1, 2),
	(1, 4),
	(3, 1),
	(3, 4),
	(4, 2),
	(4, 3),
	(8, 7),
	(9, 2),
	(9, 4);

-- Dumping structure for table portfolio.project_tech
CREATE TABLE IF NOT EXISTS `project_tech` (
  `project_id` int(11) NOT NULL COMMENT 'Foreign key referencing projects.id',
  `tech_id` int(11) NOT NULL COMMENT 'Foreign key referencing technologies.id',
  PRIMARY KEY (`project_id`,`tech_id`) USING BTREE,
  KEY `fk_project_technologies_technology_id` (`tech_id`) USING BTREE,
  CONSTRAINT `fk_project_technologies_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_project_technologies_technology_id` FOREIGN KEY (`tech_id`) REFERENCES `tech` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.project_tech: ~12 rows (approximately)
INSERT INTO `project_tech` (`project_id`, `tech_id`) VALUES
	(1, 1),
	(1, 6),
	(2, 3),
	(2, 4),
	(2, 9),
	(3, 1),
	(3, 6),
	(4, 2),
	(8, 1),
	(8, 6),
	(9, 1),
	(9, 6);

-- Dumping structure for table portfolio.project_tools
CREATE TABLE IF NOT EXISTS `project_tools` (
  `project_id` int(11) NOT NULL COMMENT 'Foreign key referencing projects.id.',
  `tool_id` int(11) NOT NULL COMMENT 'Foreign key referencing tools.id.',
  PRIMARY KEY (`project_id`,`tool_id`),
  KEY `fk_project_tools_tool_id` (`tool_id`) USING BTREE,
  CONSTRAINT `fk_project_tools_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_project_tools_tool_id` FOREIGN KEY (`tool_id`) REFERENCES `tools` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.project_tools: ~20 rows (approximately)
INSERT INTO `project_tools` (`project_id`, `tool_id`) VALUES
	(1, 1),
	(1, 5),
	(2, 13),
	(3, 1),
	(3, 2),
	(3, 5),
	(3, 6),
	(3, 10),
	(4, 1),
	(4, 2),
	(4, 4),
	(4, 10),
	(4, 12),
	(4, 14),
	(8, 5),
	(8, 6),
	(9, 1),
	(9, 2),
	(9, 5),
	(9, 8);

-- Dumping structure for table portfolio.skill_proficiency_ranking
CREATE TABLE IF NOT EXISTS `skill_proficiency_ranking` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `level` tinyint(4) unsigned NOT NULL COMMENT 'Skill Proficiency  Level',
  `description` varchar(50) NOT NULL COMMENT 'Skill Proficiency Description',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.skill_proficiency_ranking: ~5 rows (approximately)
INSERT INTO `skill_proficiency_ranking` (`id`, `level`, `description`) VALUES
	(1, 1, 'Beginner'),
	(2, 2, 'Familiar'),
	(3, 3, 'Comfortable'),
	(4, 4, 'Proficient'),
	(5, 5, 'Expert');

-- Dumping structure for table portfolio.social_media
CREATE TABLE IF NOT EXISTS `social_media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL COMMENT 'Social Media name',
  `username` varchar(50) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL COMMENT 'Social Media url',
  `logo` varchar(255) DEFAULT NULL COMMENT 'Social Media logo url',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.social_media: ~5 rows (approximately)
INSERT INTO `social_media` (`id`, `name`, `username`, `url`, `logo`) VALUES
	(1, 'instagram', 'nuno97teixeira', 'https://www.instagram.com/nuno97teixeira/', '/portfolio/icons/socials/instagram.webp'),
	(2, 'linkedin', 'nunoteixeira97', 'https://www.linkedin.com/in/nunoteixeira97/', '/portfolio/icons/socials/linkedin.webp'),
	(3, 'gmail', 'nuno97teixeira@gmail.com', NULL, '/portfolio/icons/socials/gmail.webp'),
	(5, 'facebook', 'nuno.teixeira.940', 'https://www.facebook.com/nuno.teixeira.940/', '/portfolio/icons/socials/facebook.webp'),
	(6, 'gitbucket', NULL, 'https://jmpteixeira.myasustor.com:8443/Nuno_Teixeira', '/portfolio/icons/socials/gitbucket.webp');

-- Dumping structure for table portfolio.tags
CREATE TABLE IF NOT EXISTS `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Tag ID',
  `name` varchar(50) DEFAULT NULL COMMENT 'Tag Name',
  `category_id` tinyint(4) DEFAULT NULL COMMENT 'Category ID',
  PRIMARY KEY (`id`),
  KEY `fk_category_id` (`category_id`),
  CONSTRAINT `fk_category_id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.tags: ~8 rows (approximately)
INSERT INTO `tags` (`id`, `name`, `category_id`) VALUES
	(1, '2D', 1),
	(2, '3D', 1),
	(3, 'Multiplayer', 1),
	(4, 'Singleplayer', 1),
	(5, 'Unity', 1),
	(6, 'Unreal Engine', 1),
	(7, 'AI', 2),
	(8, 'Physics', 2);

-- Dumping structure for table portfolio.tag_logo
CREATE TABLE IF NOT EXISTS `tag_logo` (
  `tag_id` int(11) NOT NULL COMMENT 'Tag id',
  `logo_id` int(11) NOT NULL COMMENT 'Logo id',
  PRIMARY KEY (`tag_id`,`logo_id`),
  KEY `fk_tag_logo_logo_id` (`logo_id`),
  CONSTRAINT `fk_tag_logo_logo_id` FOREIGN KEY (`logo_id`) REFERENCES `logos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_tag_logo_tag_id` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.tag_logo: ~0 rows (approximately)

-- Dumping structure for table portfolio.tech
CREATE TABLE IF NOT EXISTS `tech` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier for each technology',
  `name` varchar(100) DEFAULT NULL COMMENT '	Technology name',
  `logo` varchar(255) DEFAULT NULL COMMENT 'Technology logo path',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.tech: ~8 rows (approximately)
INSERT INTO `tech` (`id`, `name`, `logo`) VALUES
	(1, 'Unity', '/portfolio/icons/tech/unity.webp'),
	(2, 'Unreal Engine', '/portfolio/icons/tech/unreal_engine.webp'),
	(3, 'React', '/portfolio/icons/tech/react.webp'),
	(4, 'Tailwind', '/portfolio/icons/tech/tailwind.webp'),
	(5, 'Love2D', '/portfolio/icons/tech/love2D.webp'),
	(6, 'Git', '/portfolio/icons/tech/git.webp'),
	(7, 'p5.js', '/portfolio/icons/tech/p5_js.webp'),
	(9, 'node.js', '/portfolio/icons/tech/nodejs.webp');

-- Dumping structure for table portfolio.tech_skill_proficiency
CREATE TABLE IF NOT EXISTS `tech_skill_proficiency` (
  `tech_id` int(11) NOT NULL COMMENT 'Tech ID',
  `skill_proficiency_id` int(11) NOT NULL COMMENT 'Skill Proficiency ID',
  PRIMARY KEY (`tech_id`,`skill_proficiency_id`),
  KEY `fk_tech_skill_proficiency_id` (`skill_proficiency_id`),
  CONSTRAINT `fk_tech_skill_id` FOREIGN KEY (`tech_id`) REFERENCES `tech` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_tech_skill_proficiency_id` FOREIGN KEY (`skill_proficiency_id`) REFERENCES `skill_proficiency_ranking` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.tech_skill_proficiency: ~8 rows (approximately)
INSERT INTO `tech_skill_proficiency` (`tech_id`, `skill_proficiency_id`) VALUES
	(1, 3),
	(2, 2),
	(3, 1),
	(4, 1),
	(5, 2),
	(6, 2),
	(7, 1),
	(9, 2);

-- Dumping structure for table portfolio.tools
CREATE TABLE IF NOT EXISTS `tools` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier for each tool.',
  `name` varchar(50) DEFAULT NULL COMMENT 'Tool name',
  `type` enum('Development','Managment','Design') DEFAULT NULL COMMENT 'Tool type',
  `logo` varchar(255) DEFAULT NULL COMMENT 'Logo path',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.tools: ~14 rows (approximately)
INSERT INTO `tools` (`id`, `name`, `type`, `logo`) VALUES
	(1, 'GitHub', 'Development', '/portfolio/icons/tools/github.webp'),
	(2, 'Google Docs', 'Managment', '/portfolio/icons/tools/google_docs.webp'),
	(3, 'ClickUp', 'Managment', '/portfolio/icons/tools/clickup.webp'),
	(4, 'Photoshop', 'Design', '/portfolio/icons/tools/photoshop.webp'),
	(5, 'Visual Studio', 'Development', '/portfolio/icons/tools/visual_studio.webp'),
	(6, 'GitBucket', 'Development', '/portfolio/icons/tools/gitbucket.webp'),
	(7, 'DragonBones', 'Design', '/portfolio/icons/tools/dragonbones.webp'),
	(8, 'Jira', 'Managment', '/portfolio/icons/tools/jira.webp'),
	(9, 'Visual Studio Code', 'Development', '/portfolio/icons/tools/vscode.webp'),
	(10, 'Trello', 'Managment', '/portfolio/icons/tools/trello.webp'),
	(11, 'Adobe Illustrator', 'Design', '/portfolio/icons/tools/adobe_illustrator.webp'),
	(12, 'JetBrains Rider', 'Development', '/portfolio/icons/tools/rider.webp'),
	(13, 'HeidiSQL', 'Development', '/portfolio/icons/tools/heidi.webp'),
	(14, 'Vegas Pro', 'Design', '/portfolio/icons/tools/vegas_pro.webp');

-- Dumping structure for table portfolio.tools_skill_proficiency
CREATE TABLE IF NOT EXISTS `tools_skill_proficiency` (
  `tool_id` int(11) NOT NULL COMMENT 'Tool ID',
  `skill_proficiency_id` int(11) NOT NULL COMMENT 'Skill Proficiency ID',
  PRIMARY KEY (`tool_id`,`skill_proficiency_id`),
  KEY `fk_tools_skill_profeciency_id` (`skill_proficiency_id`),
  CONSTRAINT `fk_tools_skill_id` FOREIGN KEY (`tool_id`) REFERENCES `tools` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_tools_skill_profeciency_id` FOREIGN KEY (`skill_proficiency_id`) REFERENCES `skill_proficiency_ranking` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table portfolio.tools_skill_proficiency: ~14 rows (approximately)
INSERT INTO `tools_skill_proficiency` (`tool_id`, `skill_proficiency_id`) VALUES
	(1, 2),
	(2, 4),
	(3, 2),
	(4, 1),
	(5, 3),
	(6, 3),
	(7, 1),
	(8, 2),
	(9, 3),
	(10, 4),
	(11, 1),
	(12, 3),
	(13, 3),
	(14, 2);

-- Dumping structure for trigger portfolio.languages_before_insert_logo
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE Teixeira`@`%` TRIGGER `languages_before_insert_logo` BEFORE UPDATE ON `languages` FOR EACH ROW BEGIN
    IF NEW.logo IS NOT NULL OR NEW.logo = '' THEN
        SET NEW.logo = CONCAT('/portfolio/icons/languages/', NEW.logo);
    END IF;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

-- Dumping structure for trigger portfolio.page_before_insert_images
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE Teixeira`@`%` TRIGGER `page_before_insert_images` BEFORE UPDATE ON `page` FOR EACH ROW BEGIN
    /*IF NEW.background_image IS NOT NULL OR NEW.background_image = '' THEN
        SET NEW.background_image = CONCAT('/portfolio/projects/', NEW.background_image);
    END IF;*/
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

-- Dumping structure for trigger portfolio.projects_before_insert_thumbail
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE Teixeira`@`%` TRIGGER `projects_before_insert_thumbail` BEFORE INSERT ON `projects` FOR EACH ROW BEGIN
    IF NEW.thumbnail_path IS NOT NULL OR NEW.thumbnail_path = '' THEN
        SET NEW.thumbnail_path = CONCAT('/portfolio/projects/', NEW.thumbnail_path);
    END IF;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

-- Dumping structure for trigger portfolio.tech_before_insert_logo
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE Teixeira`@`%` TRIGGER `tech_before_insert_logo` BEFORE UPDATE ON `tech` FOR EACH ROW BEGIN
    IF NEW.logo IS NOT NULL OR NEW.logo = '' THEN
        SET NEW.logo = CONCAT('/portfolio/icons/tech/', NEW.logo);
    END IF;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

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