accountdb.sql

-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 05, 2024 at 12:57 PM
-- Server version: 10.4.27-MariaDB
-- PHP Version: 8.2.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `accountdb`
--

-- --------------------------------------------------------

--
-- Table structure for table `accounts`
--

CREATE TABLE `accounts` (
  `AccId` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `Email` longtext NOT NULL,
  `PasswordHashed` longtext NOT NULL,
  `Balance` decimal(18,2) NOT NULL,
  `SubscriptionValidUntil` datetime(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `accounts`
--

INSERT INTO `accounts` (`AccId`, `Email`, `PasswordHashed`, `Balance`, `SubscriptionValidUntil`) VALUES
('08dc4207-fec0-4e97-8f80-3ae5052a305a', 'raga70@abv.bg', '$2a$10$4p5oxVW6bi0PrfGN.N3oa.rM0mOakLsajGLmzeVAkoWu1Te6nwBVa', '0.00', NULL),
('08dc420a-030f-4ba6-86bb-a4407768cf8b', 'admin@abv.bg', '$2a$10$/KxjBCP8ebr2MuxbCNoZzugxo4Dkx9Xta8RX2ZztBSi/M60ZkTn02', '0.00', NULL),
('08dc4346-b1d1-4e6f-83c0-6d9559103b7b', 'maja@email.com', '$2a$10$q98ug8UiYttA6sKxlyj7/OrK6A8S867v8z8nSFxdLhP3IojUajfNm', '0.00', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `__efmigrationshistory`
--

CREATE TABLE `__efmigrationshistory` (
  `MigrationId` varchar(150) NOT NULL,
  `ProductVersion` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `__efmigrationshistory`
--

INSERT INTO `__efmigrationshistory` (`MigrationId`, `ProductVersion`) VALUES
('20240309121534_InitialCreate', '8.0.2');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `accounts`
--
ALTER TABLE `accounts`
  ADD PRIMARY KEY (`AccId`);

--
-- Indexes for table `__efmigrationshistory`
--
ALTER TABLE `__efmigrationshistory`
  ADD PRIMARY KEY (`MigrationId`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

RecommendationStoredProcedure.sql

DELIMITER $$

CREATE PROCEDURE GetAlgoRecommendedVideos(
    IN p_accId CHAR(36),
    IN p_topN INT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_videoId CHAR(36);
    DECLARE cur CURSOR FOR SELECT VideoId FROM VideoStats ORDER BY PublishedAt DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Temporary tables for processing
    DROP TEMPORARY TABLE IF EXISTS TempLikedVideos;
    CREATE TEMPORARY TABLE TempLikedVideos (
        VideoId CHAR(36)
    );

    DROP TEMPORARY TABLE IF EXISTS TempSimilarUsers;
    CREATE TEMPORARY TABLE TempSimilarUsers (
        UserId CHAR(36)
    );

    DROP TEMPORARY TABLE IF EXISTS TempVideoScores;
    CREATE TEMPORARY TABLE TempVideoScores (
        VideoId CHAR(36),
        PopularityScore DECIMAL(10, 2),
        LikeSimilarityScore INT,
        Category VARCHAR(255),
        FinalScore DECIMAL(10, 2)
    );

    DROP TEMPORARY TABLE IF EXISTS TempTopCategories;
    CREATE TEMPORARY TABLE TempTopCategories (
        Category VARCHAR(255)
    );

    -- Populate liked videos
    INSERT INTO TempLikedVideos (VideoId)
    SELECT DISTINCT wh.VideoId
    FROM WatchHistories wh
    WHERE wh.UserId = p_accId AND wh.Liked = 1;

    -- Populate similar users
    INSERT INTO TempSimilarUsers (UserId)
    SELECT DISTINCT wh.UserId
    FROM WatchHistories wh
    WHERE wh.VideoId IN (SELECT VideoId FROM TempLikedVideos) AND wh.UserId != p_accId AND wh.Liked = 1;

    -- Calculate initial video scores
    INSERT INTO TempVideoScores (VideoId, PopularityScore, LikeSimilarityScore, Category)
    SELECT 
        wh.VideoId, 
        SUM(CASE WHEN wh.Liked = 1 THEN 1 ELSE 0 END) / 
        GREATEST(SUM(CASE WHEN wh.Liked = 2 THEN 1 ELSE 0 END), 1) AS PopularityScore,
        (SELECT COUNT(*) FROM WatchHistories wh2 WHERE wh2.UserId IN (SELECT UserId FROM TempSimilarUsers) AND wh2.VideoId = wh.VideoId AND wh2.Liked = 1) AS LikeSimilarityScore,
        vs.Category
    FROM WatchHistories wh
    JOIN VideoStats vs ON wh.VideoId = vs.VideoId
    GROUP BY wh.VideoId;

    -- Identify top categories
    INSERT INTO TempTopCategories (Category)
    SELECT vs.Category
    FROM VideoStats vs
    JOIN WatchHistories wh ON vs.VideoId = wh.VideoId
    WHERE wh.UserId = p_accId
    GROUP BY vs.Category
    ORDER BY COUNT(*) DESC
    LIMIT 5;

    -- Update final scores accounting for category preference
    UPDATE TempVideoScores tvs
    JOIN TempTopCategories ttc ON tvs.Category = ttc.Category
    SET tvs.FinalScore = (tvs.PopularityScore * 0.5) + (tvs.LikeSimilarityScore * 2) + 1
    WHERE tvs.Category = ttc.Category;

    UPDATE TempVideoScores tvs
    SET tvs.FinalScore = (tvs.PopularityScore * 0.5) + (tvs.LikeSimilarityScore * 2)
    WHERE NOT EXISTS (SELECT 1 FROM TempTopCategories ttc WHERE tvs.Category = ttc.Category);

    -- Final recommendations with new videos for insufficient history
    DROP TEMPORARY TABLE IF EXISTS TempFinalRecommendations;
    CREATE TEMPORARY TABLE TempFinalRecommendations AS
    SELECT VideoId FROM TempVideoScores
    ORDER BY FinalScore DESC, VideoId DESC
    LIMIT p_topN;

    SELECT COUNT(*) INTO @count FROM TempFinalRecommendations;
    SET @toInject = GREATEST(p_topN - @count, 5);

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_videoId;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF NOT EXISTS (SELECT 1 FROM TempFinalRecommendations WHERE VideoId = v_videoId) THEN
            INSERT INTO TempFinalRecommendations (VideoId) VALUES (v_videoId);
            SET @toInject = @toInject - 1;
            IF @toInject = 0 THEN
                LEAVE read_loop

statisticsdb.sql

-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 05, 2024 at 12:57 PM
-- Server version: 10.4.27-MariaDB
-- PHP Version: 8.2.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `statisticsdb`
--

-- --------------------------------------------------------

--
-- Table structure for table `videostats`
--

CREATE TABLE `videostats` (
  `VideoId` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `VideoLength` decimal(65,30) NOT NULL,
  `Category` int(11) NOT NULL,
  `PublishedAt` datetime(6) NOT NULL DEFAULT '0001-01-01 00:00:00.000000'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `watchhistories`
--

CREATE TABLE `watchhistories` (
  `UserId` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `VideoId` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `WatchedTime` decimal(18,2) NOT NULL,
  `Liked` int(11) NOT NULL,
  `FullyWatched` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `__efmigrationshistory`
--

CREATE TABLE `__efmigrationshistory` (
  `MigrationId` varchar(150) NOT NULL,
  `ProductVersion` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `__efmigrationshistory`
--

INSERT INTO `__efmigrationshistory` (`MigrationId`, `ProductVersion`) VALUES
('20240309120844_InitialCreate', '8.0.2'),
('20240402145546_addedPublishDate', '8.0.2');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `videostats`
--
ALTER TABLE `videostats`
  ADD PRIMARY KEY (`VideoId`);

--
-- Indexes for table `watchhistories`
--
ALTER TABLE `watchhistories`
  ADD PRIMARY KEY (`UserId`,`VideoId`),
  ADD KEY `IX_WatchHistories_VideoId` (`VideoId`);

--
-- Indexes for table `__efmigrationshistory`
--
ALTER TABLE `__efmigrationshistory`
  ADD PRIMARY KEY (`MigrationId`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `watchhistories`
--
ALTER TABLE `watchhistories`
  ADD CONSTRAINT `FK_WatchHistories_VideoStats_VideoId` FOREIGN KEY (`VideoId`) REFERENCES `videostats` (`VideoId`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

videolibdb.sql

-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 05, 2024 at 12:57 PM
-- Server version: 10.4.27-MariaDB
-- PHP Version: 8.2.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `videolibdb`
--

-- --------------------------------------------------------

--
-- Table structure for table `videos`
--

CREATE TABLE `videos` (
  `VideoId` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `Title` varchar(255) NOT NULL,
  `Description` longtext NOT NULL,
  `Category` int(11) NOT NULL,
  `videoLength` decimal(18,2) NOT NULL,
  `VideoUri` longtext NOT NULL,
  `ThumbnailUri` longtext NOT NULL,
  `uploadedByAccoutId` char(36) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `UploadDateTime` datetime(6) NOT NULL,
  `IsPublic` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `__efmigrationshistory`
--

CREATE TABLE `__efmigrationshistory` (
  `MigrationId` varchar(150) NOT NULL,
  `ProductVersion` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `__efmigrationshistory`
--

INSERT INTO `__efmigrationshistory` (`MigrationId`, `ProductVersion`) VALUES
('20240309122752_InitialCreate', '8.0.2');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `videos`
--
ALTER TABLE `videos`
  ADD PRIMARY KEY (`VideoId`);

--
-- Indexes for table `__efmigrationshistory`
--
ALTER TABLE `__efmigrationshistory`
  ADD PRIMARY KEY (`MigrationId`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;