-- ScriptShare Database Initialization Script
-- This script sets up the initial database structure and default data

USE scriptshare;

-- Set proper character set and collation
ALTER DATABASE scriptshare CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create users table if it doesn't exist
CREATE TABLE IF NOT EXISTS `users` (
  `id` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL UNIQUE,
  `username` varchar(100) NOT NULL UNIQUE,
  `displayName` varchar(255) NOT NULL,
  `passwordHash` varchar(255) NOT NULL,
  `avatarUrl` text,
  `bio` text,
  `isAdmin` boolean DEFAULT false,
  `isModerator` boolean DEFAULT false,
  `isVerified` boolean DEFAULT false,
  `createdAt` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_users_email` (`email`),
  INDEX `idx_users_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create scripts table if it doesn't exist
CREATE TABLE IF NOT EXISTS `scripts` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `content` longtext NOT NULL,
  `authorId` varchar(255) NOT NULL,
  `categories` json,
  `tags` json,
  `compatibleOs` json,
  `language` varchar(50) DEFAULT 'bash',
  `isApproved` boolean DEFAULT false,
  `isPublic` boolean DEFAULT true,
  `viewCount` int DEFAULT 0,
  `downloadCount` int DEFAULT 0,
  `createdAt` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`authorId`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_scripts_author` (`authorId`),
  INDEX `idx_scripts_approved` (`isApproved`),
  INDEX `idx_scripts_public` (`isPublic`),
  INDEX `idx_scripts_created` (`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create ratings table if it doesn't exist
CREATE TABLE IF NOT EXISTS `ratings` (
  `id` varchar(255) NOT NULL,
  `scriptId` varchar(255) NOT NULL,
  `userId` varchar(255) NOT NULL,
  `rating` int NOT NULL CHECK (rating >= 1 AND rating <= 5),
  `comment` text,
  `createdAt` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_script_rating` (`scriptId`, `userId`),
  FOREIGN KEY (`scriptId`) REFERENCES `scripts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_ratings_script` (`scriptId`),
  INDEX `idx_ratings_user` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create script_analytics table if it doesn't exist
CREATE TABLE IF NOT EXISTS `script_analytics` (
  `id` varchar(255) NOT NULL,
  `scriptId` varchar(255) NOT NULL,
  `eventType` varchar(50) NOT NULL,
  `userId` varchar(255),
  `userAgent` text,
  `ipAddress` varchar(45),
  `referrer` text,
  `metadata` json,
  `createdAt` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`scriptId`) REFERENCES `scripts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_analytics_script` (`scriptId`),
  INDEX `idx_analytics_event` (`eventType`),
  INDEX `idx_analytics_created` (`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create script_collections table if it doesn't exist
CREATE TABLE IF NOT EXISTS `script_collections` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `authorId` varchar(255) NOT NULL,
  `isPublic` boolean DEFAULT false,
  `createdAt` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`authorId`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_collections_author` (`authorId`),
  INDEX `idx_collections_public` (`isPublic`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create collection_scripts table if it doesn't exist
CREATE TABLE IF NOT EXISTS `collection_scripts` (
  `id` varchar(255) NOT NULL,
  `collectionId` varchar(255) NOT NULL,
  `scriptId` varchar(255) NOT NULL,
  `addedAt` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_collection_script` (`collectionId`, `scriptId`),
  FOREIGN KEY (`collectionId`) REFERENCES `script_collections`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`scriptId`) REFERENCES `scripts`(`id`) ON DELETE CASCADE,
  INDEX `idx_collection_scripts_collection` (`collectionId`),
  INDEX `idx_collection_scripts_script` (`scriptId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create script_versions table if it doesn't exist
CREATE TABLE IF NOT EXISTS `script_versions` (
  `id` varchar(255) NOT NULL,
  `scriptId` varchar(255) NOT NULL,
  `version` varchar(50) NOT NULL,
  `content` longtext NOT NULL,
  `changelog` text,
  `createdAt` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`scriptId`) REFERENCES `scripts`(`id`) ON DELETE CASCADE,
  INDEX `idx_versions_script` (`scriptId`),
  INDEX `idx_versions_created` (`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin user (password: admin123)
-- Note: In production, use proper password hashing
INSERT IGNORE INTO `users` (
  `id`, 
  `email`, 
  `username`, 
  `displayName`, 
  `passwordHash`, 
  `isAdmin`, 
  `isModerator`,
  `isVerified`
) VALUES (
  'admin-default-001', 
  'admin@scriptshare.local', 
  'admin', 
  'System Administrator', 
  '$2b$10$8K5YBvK8H.UX3JQ2K9J9x.RQfFr6bF7UE9FJm.LrEY8K.QG8wH8G6', -- admin123
  true, 
  true,
  true
);

-- Insert sample categories data
INSERT IGNORE INTO `script_collections` (
  `id`,
  `name`,
  `description`,
  `authorId`,
  `isPublic`
) VALUES
  ('collection-system-001', 'System Administration', 'Essential system administration scripts', 'admin-default-001', true),
  ('collection-devops-001', 'DevOps Automation', 'CI/CD and deployment automation scripts', 'admin-default-001', true),
  ('collection-security-001', 'Security Tools', 'Security scanning and hardening scripts', 'admin-default-001', true),
  ('collection-backup-001', 'Backup & Recovery', 'Data backup and recovery automation', 'admin-default-001', true);

-- Insert sample script
INSERT IGNORE INTO `scripts` (
  `id`,
  `name`,
  `description`,
  `content`,
  `authorId`,
  `categories`,
  `tags`,
  `compatibleOs`,
  `language`,
  `isApproved`,
  `isPublic`
) VALUES (
  'script-welcome-001',
  'System Information Script',
  'A simple script to display system information including OS, CPU, memory, and disk usage.',
  '#!/bin/bash\n\necho "=== System Information ==="\necho "Hostname: $(hostname)"\necho "OS: $(uname -s)"\necho "Kernel: $(uname -r)"\necho "Architecture: $(uname -m)"\necho ""\necho "=== CPU Information ==="\necho "CPU: $(lscpu | grep \"Model name\" | cut -d: -f2 | xargs)"\necho "Cores: $(nproc)"\necho ""\necho "=== Memory Information ==="\nfree -h\necho ""\necho "=== Disk Usage ==="\ndf -h\necho ""\necho "=== System Uptime ==="\nuptime',
  'admin-default-001',
  '["System Administration", "Monitoring"]',
  '["system", "info", "monitoring", "diagnostics"]',
  '["linux", "macos"]',
  'bash',
  true,
  true
);

-- Add the sample script to system collection
INSERT IGNORE INTO `collection_scripts` (
  `id`,
  `collectionId`,
  `scriptId`
) VALUES (
  'cs-001',
  'collection-system-001',
  'script-welcome-001'
);

-- Create indexes for performance optimization
CREATE INDEX IF NOT EXISTS `idx_scripts_name` ON `scripts`(`name`);
CREATE INDEX IF NOT EXISTS `idx_scripts_language` ON `scripts`(`language`);
CREATE INDEX IF NOT EXISTS `idx_analytics_user` ON `script_analytics`(`userId`);
CREATE INDEX IF NOT EXISTS `idx_collections_name` ON `script_collections`(`name`);

-- Set up database optimization settings
SET GLOBAL innodb_buffer_pool_size = 268435456; -- 256MB
SET GLOBAL max_connections = 200;
SET GLOBAL innodb_file_per_table = 1;

-- Print initialization complete message
SELECT 'ScriptShare database initialization completed successfully!' as message;
SELECT COUNT(*) as total_users FROM users;
SELECT COUNT(*) as total_scripts FROM scripts;
SELECT COUNT(*) as total_collections FROM script_collections;