-- VideoStream Database Schema
-- Compatible with MySQL, PostgreSQL, and SQLite
-- Created for localhost installation

-- Create database (MySQL only)
-- CREATE DATABASE videostream_db;
-- USE videostream_db;

-- Users table
CREATE TABLE users (
    id VARCHAR(25) PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255),
    avatar VARCHAR(500),
    bio TEXT,
    isVerified BOOLEAN DEFAULT FALSE,
    subscriberCount INTEGER DEFAULT 0,
    totalViews BIGINT DEFAULT 0,
    totalLikes BIGINT DEFAULT 0,
    role VARCHAR(20) DEFAULT 'USER',
    status VARCHAR(20) DEFAULT 'ACTIVE',
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Videos table
CREATE TABLE videos (
    id VARCHAR(25) PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    thumbnailUrl VARCHAR(500),
    videoUrl VARCHAR(500) NOT NULL,
    duration INTEGER NOT NULL,
    fileSize BIGINT NOT NULL,
    resolution VARCHAR(20),
    format VARCHAR(10),
    viewCount BIGINT DEFAULT 0,
    likeCount BIGINT DEFAULT 0,
    commentCount INTEGER DEFAULT 0,
    isPublic BOOLEAN DEFAULT TRUE,
    isMonetized BOOLEAN DEFAULT FALSE,
    allowComments BOOLEAN DEFAULT TRUE,
    allowDownloads BOOLEAN DEFAULT FALSE,
    ageRestriction BOOLEAN DEFAULT FALSE,
    category VARCHAR(100),
    tags TEXT,
    language VARCHAR(10),
    status VARCHAR(20) DEFAULT 'PUBLISHED',
    publishedAt DATETIME,
    uploaderId VARCHAR(25) NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (uploaderId) REFERENCES users(id) ON DELETE CASCADE
);

-- Playlists table
CREATE TABLE playlists (
    id VARCHAR(25) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    thumbnailUrl VARCHAR(500),
    isPublic BOOLEAN DEFAULT TRUE,
    videoCount INTEGER DEFAULT 0,
    totalViews BIGINT DEFAULT 0,
    creatorId VARCHAR(25) NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (creatorId) REFERENCES users(id) ON DELETE CASCADE
);

-- Playlist videos junction table
CREATE TABLE playlist_videos (
    id VARCHAR(25) PRIMARY KEY,
    playlistId VARCHAR(25) NOT NULL,
    videoId VARCHAR(25) NOT NULL,
    position INTEGER NOT NULL,
    addedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (playlistId) REFERENCES playlists(id) ON DELETE CASCADE,
    FOREIGN KEY (videoId) REFERENCES videos(id) ON DELETE CASCADE,
    UNIQUE(playlistId, videoId)
);

-- Comments table
CREATE TABLE comments (
    id VARCHAR(25) PRIMARY KEY,
    content TEXT NOT NULL,
    likeCount INTEGER DEFAULT 0,
    isPinned BOOLEAN DEFAULT FALSE,
    isEdited BOOLEAN DEFAULT FALSE,
    status VARCHAR(20) DEFAULT 'PUBLISHED',
    videoId VARCHAR(25) NOT NULL,
    authorId VARCHAR(25) NOT NULL,
    parentId VARCHAR(25),
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (videoId) REFERENCES videos(id) ON DELETE CASCADE,
    FOREIGN KEY (authorId) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (parentId) REFERENCES comments(id)
);

-- Likes table
CREATE TABLE likes (
    id VARCHAR(25) PRIMARY KEY,
    userId VARCHAR(25) NOT NULL,
    videoId VARCHAR(25),
    commentId VARCHAR(25),
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (videoId) REFERENCES videos(id) ON DELETE CASCADE,
    FOREIGN KEY (commentId) REFERENCES comments(id) ON DELETE CASCADE,
    UNIQUE(userId, videoId),
    UNIQUE(userId, commentId)
);

-- Subscriptions table
CREATE TABLE subscriptions (
    id VARCHAR(25) PRIMARY KEY,
    subscriberId VARCHAR(25) NOT NULL,
    channelId VARCHAR(25) NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subscriberId) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (channelId) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE(subscriberId, channelId)
);

-- Watch history table
CREATE TABLE watch_history (
    id VARCHAR(25) PRIMARY KEY,
    userId VARCHAR(25) NOT NULL,
    videoId VARCHAR(25) NOT NULL,
    watchTime INTEGER NOT NULL,
    completed BOOLEAN DEFAULT FALSE,
    watchedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (videoId) REFERENCES videos(id) ON DELETE CASCADE,
    UNIQUE(userId, videoId)
);

-- Notifications table
CREATE TABLE notifications (
    id VARCHAR(25) PRIMARY KEY,
    userId VARCHAR(25) NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type VARCHAR(50) NOT NULL,
    isRead BOOLEAN DEFAULT FALSE,
    data TEXT,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
);

-- Reports table
CREATE TABLE reports (
    id VARCHAR(25) PRIMARY KEY,
    reporterId VARCHAR(25) NOT NULL,
    videoId VARCHAR(25),
    commentId VARCHAR(25),
    userId VARCHAR(25),
    reason VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'PENDING',
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    resolvedAt DATETIME,
    FOREIGN KEY (reporterId) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (videoId) REFERENCES videos(id) ON DELETE CASCADE,
    FOREIGN KEY (commentId) REFERENCES comments(id) ON DELETE CASCADE,
    FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
);

-- Create indexes for better performance
CREATE INDEX idx_videos_uploaderId ON videos(uploaderId);
CREATE INDEX idx_videos_status ON videos(status);
CREATE INDEX idx_videos_category ON videos(category);
CREATE INDEX idx_videos_publishedAt ON videos(publishedAt);
CREATE INDEX idx_comments_videoId ON comments(videoId);
CREATE INDEX idx_comments_authorId ON comments(authorId);
CREATE INDEX idx_comments_parentId ON comments(parentId);
CREATE INDEX idx_likes_userId ON likes(userId);
CREATE INDEX idx_subscriptions_subscriberId ON subscriptions(subscriberId);
CREATE INDEX idx_subscriptions_channelId ON subscriptions(channelId);
CREATE INDEX idx_watch_history_userId ON watch_history(userId);
CREATE INDEX idx_notifications_userId ON notifications(userId);
CREATE INDEX idx_notifications_isRead ON notifications(isRead);

-- Insert sample data for testing
INSERT INTO users (id, email, username, name, bio, isVerified, subscriberCount, totalViews, totalLikes, role, status) VALUES
('user_1', 'admin@videostream.com', 'admin', 'Admin User', 'Platform administrator', TRUE, 1000, 1000000, 50000, 'ADMIN', 'ACTIVE'),
('user_2', 'john@videostream.com', 'johndoe', 'John Doe', 'Content creator and developer', TRUE, 5200, 125000, 15200, 'USER', 'ACTIVE'),
('user_3', 'jane@videostream.com', 'janedoe', 'Jane Smith', 'Tech educator and tutorial creator', TRUE, 8900, 250000, 38000, 'USER', 'ACTIVE'),
('user_4', 'mike@videostream.com', 'miketech', 'Mike Johnson', 'Gaming and technology content', FALSE, 1200, 45000, 6700, 'USER', 'ACTIVE'),
('user_5', 'sarah@videostream.com', 'sarahdev', 'Sarah Wilson', 'Web development tutorials', TRUE, 3400, 89000, 12400, 'USER', 'ACTIVE');

INSERT INTO videos (id, title, description, thumbnailUrl, videoUrl, duration, fileSize, resolution, format, viewCount, likeCount, commentCount, isPublic, category, tags, language, status, publishedAt, uploaderId) VALUES
('video_1', 'Tutorial Next.js 15 - Build Modern Web Apps', 'Learn Next.js 15 with this comprehensive tutorial covering all the new features and best practices.', '/thumbnails/video1.jpg', '/videos/video1.mp4', 754, 125829120, '1080p', 'mp4', 10000, 1200, 45, TRUE, 'Tutorial', '["nextjs", "react", "web development", "tutorial"]', 'id', 'PUBLISHED', '2024-01-15 10:00:00', 'user_2'),
('video_2', 'React Hooks Explained - Complete Guide', 'Deep dive into React Hooks and learn how to use them effectively in your applications.', '/thumbnails/video2.jpg', '/videos/video2.mp4', 1125, 188743680, '1080p', 'mp4', 25000, 3500, 89, TRUE, 'Tutorial', '["react", "hooks", "javascript", "tutorial"]', 'id', 'PUBLISHED', '2024-01-14 14:30:00', 'user_3'),
('video_3', 'TypeScript Best Practices 2024', 'Master TypeScript with these best practices and patterns for 2024.', '/thumbnails/video3.jpg', '/videos/video3.mp4', 920, 157286400, '720p', 'mp4', 8000, 980, 23, TRUE, 'Tutorial', '["typescript", "javascript", "programming"]', 'id', 'PUBLISHED', '2024-01-13 09:15:00', 'user_3'),
('video_4', 'Tailwind CSS Tutorial - Responsive Design', 'Learn how to create beautiful responsive designs with Tailwind CSS.', '/thumbnails/video4.jpg', '/videos/video4.mp4', 1330, 209715200, '1080p', 'mp4', 15000, 2100, 67, TRUE, 'Tutorial', '["tailwindcss", "css", "design", "responsive"]', 'id', 'PUBLISHED', '2024-01-12 16:45:00', 'user_5'),
('video_5', 'Prisma ORM - Database Made Easy', 'Simplify database operations with Prisma ORM in this complete tutorial.', '/thumbnails/video5.jpg', '/videos/video5.mp4', 1195, 178257920, '1080p', 'mp4', 12000, 1800, 34, TRUE, 'Tutorial', '["prisma", "database", "sql", "nodejs"]', 'id', 'PUBLISHED', '2024-01-11 11:20:00', 'user_5'),
('video_6', 'JavaScript ES2024 - New Features', 'Explore the latest JavaScript features in ES2024 and how to use them.', '/thumbnails/video6.jpg', '/videos/video6.mp4', 990, 167772160, '720p', 'mp4', 20000, 4200, 78, TRUE, 'Tutorial', '["javascript", "es2024", "programming", "web"]', 'id', 'PUBLISHED', '2024-01-10 13:00:00', 'user_2'),
('video_7', 'Node.js Performance Optimization', 'Optimize your Node.js applications for better performance and scalability.', '/thumbnails/video7.jpg', '/videos/video7.mp4', 1515, 251658240, '1080p', 'mp4', 18000, 3100, 56, TRUE, 'Tutorial', '["nodejs", "performance", "backend", "javascript"]', 'id', 'PUBLISHED', '2024-01-09 15:30:00', 'user_4'),
('video_8', 'Docker Containerization Guide', 'Learn Docker containerization from basics to advanced concepts.', '/thumbnails/video8.jpg', '/videos/video8.mp4', 1230, 209715200, '1080p', 'mp4', 22000, 3800, 92, TRUE, 'Tutorial', '["docker", "devops", "containers", "linux"]', 'id', 'PUBLISHED', '2024-01-08 10:45:00', 'user_4');

INSERT INTO playlists (id, name, description, creatorId, videoCount) VALUES
('playlist_1', 'Web Development Tutorials', 'Complete collection of web development tutorials', 'user_2', 4),
('playlist_2', 'React Masterclass', 'Everything you need to know about React', 'user_3', 2),
('playlist_3', 'Backend Development', 'Server-side programming tutorials', 'user_4', 2);

INSERT INTO playlist_videos (id, playlistId, videoId, position) VALUES
('pv_1', 'playlist_1', 'video_1', 1),
('pv_2', 'playlist_1', 'video_3', 2),
('pv_3', 'playlist_1', 'video_4', 3),
('pv_4', 'playlist_1', 'video_6', 4),
('pv_5', 'playlist_2', 'video_2', 1),
('pv_6', 'playlist_2', 'video_6', 2),
('pv_7', 'playlist_3', 'video_5', 1),
('pv_8', 'playlist_3', 'video_7', 2);

INSERT INTO comments (id, content, videoId, authorId, likeCount) VALUES
('comment_1', 'Great tutorial! Very helpful for learning Next.js 15.', 'video_1', 'user_3', 12),
('comment_2', 'Can you make a video about advanced React patterns?', 'video_2', 'user_4', 8),
('comment_3', 'This TypeScript tutorial saved me hours of debugging!', 'video_3', 'user_5', 15),
('comment_4', 'Tailwind CSS is amazing. Thanks for the comprehensive guide!', 'video_4', 'user_2', 6),
('comment_5', 'Prisma ORM really simplifies database work. Great explanation!', 'video_5', 'user_3', 9);

INSERT INTO likes (id, userId, videoId) VALUES
('like_1', 'user_3', 'video_1'),
('like_2', 'user_4', 'video_1'),
('like_3', 'user_5', 'video_1'),
('like_4', 'user_2', 'video_2'),
('like_5', 'user_4', 'video_2'),
('like_6', 'user_5', 'video_2'),
('like_7', 'user_2', 'video_3'),
('like_8', 'user_4', 'video_3'),
('like_9', 'user_3', 'video_4'),
('like_10', 'user_2', 'video_4');

INSERT INTO subscriptions (id, subscriberId, channelId) VALUES
('sub_1', 'user_3', 'user_2'),
('sub_2', 'user_4', 'user_2'),
('sub_3', 'user_5', 'user_2'),
('sub_4', 'user_2', 'user_3'),
('sub_5', 'user_4', 'user_3'),
('sub_6', 'user_5', 'user_3'),
('sub_7', 'user_2', 'user_4'),
('sub_8', 'user_3', 'user_4'),
('sub_9', 'user_2', 'user_5'),
('sub_10', 'user_3', 'user_5');

-- Update user subscriber counts
UPDATE users SET subscriberCount = 3 WHERE id = 'user_2';
UPDATE users SET subscriberCount = 3 WHERE id = 'user_3';
UPDATE users SET subscriberCount = 2 WHERE id = 'user_4';
UPDATE users SET subscriberCount = 2 WHERE id = 'user_5';

-- Create triggers for automatic timestamp updates (MySQL syntax)
-- DELIMITER //
-- CREATE TRIGGER update_users_updatedAt BEFORE UPDATE ON users
-- FOR EACH ROW
-- BEGIN
--     SET NEW.updatedAt = CURRENT_TIMESTAMP;
-- END//
-- 
-- CREATE TRIGGER update_videos_updatedAt BEFORE UPDATE ON videos
-- FOR EACH ROW
-- BEGIN
--     SET NEW.updatedAt = CURRENT_TIMESTAMP;
-- END//
-- 
-- CREATE TRIGGER update_playlists_updatedAt BEFORE UPDATE ON playlists
-- FOR EACH ROW
-- BEGIN
--     SET NEW.updatedAt = CURRENT_TIMESTAMP;
-- END//
-- 
-- CREATE TRIGGER update_comments_updatedAt BEFORE UPDATE ON comments
-- FOR EACH ROW
-- BEGIN
--     SET NEW.updatedAt = CURRENT_TIMESTAMP;
-- END//
-- DELIMITER ;