-- ============================================
-- AEGIS Database Setup Script
-- Database: nbrp_aegis
-- ============================================

-- Create database
CREATE DATABASE IF NOT EXISTS `nbrp_aegis` 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE `nbrp_aegis`;

-- ============================================
-- ENUMS and TYPES
-- ============================================

-- ============================================
-- TABLES
-- ============================================

-- User table
CREATE TABLE `User` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `password` VARCHAR(191) NOT NULL,
    `role` ENUM('VERIAGENT', 'MODERATOR', 'DEVELOPERS', 'ADMIN', 'SUPER_ADMIN') NOT NULL DEFAULT 'VERIAGENT',
    `cfxId` VARCHAR(191) NULL,
    `isVerified` BOOLEAN NOT NULL DEFAULT false,
    `verificationToken` VARCHAR(191) NULL,
    `verificationExpires` DATETIME(3) NULL,
    `resetPasswordToken` VARCHAR(191) NULL,
    `resetPasswordExpires` DATETIME(3) NULL,
    `twoFactorEnabled` BOOLEAN NOT NULL DEFAULT false,
    `twoFactorSecret` VARCHAR(191) NULL,
    `twoFactorBackupCodes` JSON NULL,
    `loginAttempts` INTEGER NOT NULL DEFAULT 0,
    `lockedUntil` DATETIME(3) NULL,
    `canViewProducts` BOOLEAN NOT NULL DEFAULT true,
    `canPurchaseProducts` BOOLEAN NOT NULL DEFAULT true,
    `canAccessUserPortal` BOOLEAN NOT NULL DEFAULT true,
    `canAccessAdminPanel` BOOLEAN NOT NULL DEFAULT false,
    `canManageUsers` BOOLEAN NOT NULL DEFAULT false,
    `canManageProducts` BOOLEAN NOT NULL DEFAULT false,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `User_username_key`(`username`),
    UNIQUE INDEX `User_email_key`(`email`),
    UNIQUE INDEX `User_cfxId_key`(`cfxId`),
    UNIQUE INDEX `User_verificationToken_key`(`verificationToken`),
    UNIQUE INDEX `User_resetPasswordToken_key`(`resetPasswordToken`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- UserSession table
CREATE TABLE `UserSession` (
    `id` VARCHAR(191) NOT NULL,
    `userId` INTEGER NOT NULL,
    `token` VARCHAR(191) NOT NULL,
    `expiresAt` DATETIME(3) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `UserSession_token_key`(`token`),
    INDEX `UserSession_userId_fkey`(`userId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Category table
CREATE TABLE `Category` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(191) NOT NULL,
    `slug` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `Category_name_key`(`name`),
    UNIQUE INDEX `Category_slug_key`(`slug`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Product table
CREATE TABLE `Product` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `slug` VARCHAR(191) NOT NULL,
    `title` VARCHAR(191) NOT NULL,
    `description` TEXT NOT NULL,
    `shortDescription` VARCHAR(191) NULL,
    `thumbnail` VARCHAR(191) NULL,
    `heroBanner` VARCHAR(191) NULL,
    `encryptedPrice` INTEGER NOT NULL,
    `decryptedPrice` INTEGER NOT NULL,
    `status` ENUM('DRAFT', 'PUBLISHED', 'ARCHIVED') NOT NULL DEFAULT 'DRAFT',
    `isFeatured` BOOLEAN NOT NULL DEFAULT false,
    `isNew` BOOLEAN NOT NULL DEFAULT false,
    `onSale` BOOLEAN NOT NULL DEFAULT false,
    `salePrice` INTEGER NULL,
    `categoryId` INTEGER NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `Product_slug_key`(`slug`),
    INDEX `Product_categoryId_fkey`(`categoryId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ScriptVersion table
CREATE TABLE `ScriptVersion` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `productId` INTEGER NOT NULL,
    `version` VARCHAR(191) NOT NULL,
    `fileKey` VARCHAR(191) NOT NULL,
    `encryptedKey` VARCHAR(191) NULL,
    `isActive` BOOLEAN NOT NULL DEFAULT true,
    `releaseNotes` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `ScriptVersion_productId_fkey`(`productId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Purchase table
CREATE TABLE `Purchase` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `userId` INTEGER NOT NULL,
    `productId` INTEGER NOT NULL,
    `type` ENUM('ONE_TIME', 'SUBSCRIPTION') NOT NULL,
    `amount` INTEGER NOT NULL,
    `currency` VARCHAR(191) NOT NULL,
    `stripePaymentId` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `Purchase_userId_fkey`(`userId`),
    INDEX `Purchase_productId_fkey`(`productId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Subscription table
CREATE TABLE `Subscription` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `userId` INTEGER NOT NULL,
    `stripeSubId` VARCHAR(191) NOT NULL,
    `status` VARCHAR(191) NOT NULL,
    `currentPeriodEnd` DATETIME(3) NOT NULL,
    `cancelAtPeriodEnd` BOOLEAN NOT NULL DEFAULT false,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `Subscription_userId_key`(`userId`),
    UNIQUE INDEX `Subscription_stripeSubId_key`(`stripeSubId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Download table
CREATE TABLE `Download` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `purchaseId` INTEGER NOT NULL,
    `scriptVersionId` INTEGER NOT NULL,
    `downloadedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `ipAddress` VARCHAR(191) NULL,

    INDEX `Download_purchaseId_fkey`(`purchaseId`),
    INDEX `Download_scriptVersionId_fkey`(`scriptVersionId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ============================================
-- FOREIGN KEY CONSTRAINTS
-- ============================================

-- UserSession foreign keys
ALTER TABLE `UserSession` ADD CONSTRAINT `UserSession_userId_fkey` 
    FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- Product foreign keys
ALTER TABLE `Product` ADD CONSTRAINT `Product_categoryId_fkey` 
    FOREIGN KEY (`categoryId`) REFERENCES `Category`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- ScriptVersion foreign keys
ALTER TABLE `ScriptVersion` ADD CONSTRAINT `ScriptVersion_productId_fkey` 
    FOREIGN KEY (`productId`) REFERENCES `Product`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- Purchase foreign keys
ALTER TABLE `Purchase` ADD CONSTRAINT `Purchase_userId_fkey` 
    FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `Purchase` ADD CONSTRAINT `Purchase_productId_fkey` 
    FOREIGN KEY (`productId`) REFERENCES `Product`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- Subscription foreign keys
ALTER TABLE `Subscription` ADD CONSTRAINT `Subscription_userId_fkey` 
    FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- Download foreign keys
ALTER TABLE `Download` ADD CONSTRAINT `Download_purchaseId_fkey` 
    FOREIGN KEY (`purchaseId`) REFERENCES `Purchase`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `Download` ADD CONSTRAINT `Download_scriptVersionId_fkey` 
    FOREIGN KEY (`scriptVersionId`) REFERENCES `ScriptVersion`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- ============================================
-- INITIAL DATA (Optional - Admin User)
-- ============================================

-- Insert admin user (password: admin123 - remember to change!)
-- Note: Password is hashed with bcrypt
INSERT INTO `User` (
    `username`, 
    `email`, 
    `password`, 
    `role`, 
    `isVerified`, 
    `canAccessAdminPanel`, 
    `canManageUsers`, 
    `canManageProducts`
) VALUES (
    'admin',
    'admin@nbrp.no',
    '$2b$10$K7L/A0QJUgjjF5Ty8XoQ7e.qZ9QjGkX2O5u5H8J8gX7N9Y2W8V3Km', -- admin123
    'SUPER_ADMIN',
    1,
    1,
    1,
    1
);

-- Insert sample category
INSERT INTO `Category` (`name`, `slug`) VALUES 
    ('Scripts', 'scripts'),
    ('Resources', 'resources'),
    ('Maps', 'maps');

-- ============================================
-- INDEXES FOR PERFORMANCE
-- ============================================

-- Additional indexes for better performance
CREATE INDEX `idx_user_email` ON `User`(`email`);
CREATE INDEX `idx_user_username` ON `User`(`username`);
CREATE INDEX `idx_user_role` ON `User`(`role`);
CREATE INDEX `idx_user_created` ON `User`(`createdAt`);

CREATE INDEX `idx_product_status` ON `Product`(`status`);
CREATE INDEX `idx_product_featured` ON `Product`(`isFeatured`);
CREATE INDEX `idx_product_created` ON `Product`(`createdAt`);

CREATE INDEX `idx_purchase_created` ON `Purchase`(`createdAt`);
CREATE INDEX `idx_purchase_type` ON `Purchase`(`type`);

-- ============================================
-- COMPLETION MESSAGE
-- ============================================

SELECT 'Database nbrp_aegis has been created successfully!' as message;
SELECT 'Admin user created: admin@nbrp.no / admin123' as admin_info;
SELECT 'Remember to change the default admin password!' as security_warning;
