-- ============================================
-- 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`;

-- 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;

-- 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;

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

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;
