-- =============================================
-- Digital Marketplace - Raw SQL for phpMyAdmin
-- =============================================

CREATE DATABASE IF NOT EXISTS `digital_marketplace` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `digital_marketplace`;

-- Categories Table
CREATE TABLE `categories` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL UNIQUE,
    `description` TEXT NULL DEFAULT NULL,
    `image` VARCHAR(255) NULL DEFAULT NULL,
    `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Products Table
CREATE TABLE `products` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `category_id` BIGINT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL UNIQUE,
    `description` LONGTEXT NULL DEFAULT NULL,
    `image` VARCHAR(255) NULL DEFAULT NULL,
    `screenshots` JSON NULL DEFAULT NULL,
    `preview_link` VARCHAR(255) NULL DEFAULT NULL,
    `original_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `file_path` VARCHAR(255) NULL DEFAULT NULL,
    `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    `download_count` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment Methods Table
CREATE TABLE `payment_methods` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `number` VARCHAR(255) NOT NULL,
    `type` VARCHAR(255) NOT NULL DEFAULT 'mobile_banking',
    `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Orders Table
CREATE TABLE `orders` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `order_number` VARCHAR(255) NOT NULL UNIQUE,
    `total` DECIMAL(10,2) NOT NULL,
    `payment_method` VARCHAR(255) NOT NULL,
    `transaction_id` VARCHAR(255) NOT NULL,
    `status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    `notes` TEXT NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Order Items Table
CREATE TABLE `order_items` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id` BIGINT UNSIGNED NOT NULL,
    `product_id` BIGINT UNSIGNED NOT NULL,
    `product_title` VARCHAR(255) NOT NULL,
    `price` DECIMAL(10,2) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Testimonials Table
CREATE TABLE `testimonials` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `role` VARCHAR(255) NULL DEFAULT NULL,
    `content` TEXT NOT NULL,
    `avatar` VARCHAR(255) NULL DEFAULT NULL,
    `rating` TINYINT NOT NULL DEFAULT 5,
    `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Categories
INSERT INTO `categories` (`name`, `slug`, `description`, `status`, `created_at`, `updated_at`) VALUES
('WordPress Themes', 'wordpress-themes', 'Premium WordPress themes for every need', 'active', NOW(), NOW()),
('HTML Templates', 'html-templates', 'Clean and modern HTML templates', 'active', NOW(), NOW()),
('UI Kits', 'ui-kits', 'Professional UI kits and design systems', 'active', NOW(), NOW()),
('Plugins', 'plugins', 'Powerful plugins and extensions', 'active', NOW(), NOW()),
('Graphics', 'graphics', 'Icons, illustrations, and graphic assets', 'active', NOW(), NOW()),
('Scripts', 'scripts', 'Ready-to-use PHP, JS & Python scripts', 'active', NOW(), NOW());

-- Products
INSERT INTO `products` (`category_id`, `title`, `slug`, `description`, `original_price`, `price`, `preview_link`, `status`, `download_count`, `created_at`, `updated_at`) VALUES
(1, 'suspended - Business Theme', 'suspended-business-theme', 'A professional multipurpose WordPress theme perfect for businesses, agencies, and startups.', 79.00, 49.00, 'https://example.com/preview', 'active', 234, NOW(), NOW()),
(1, 'Developer Blog Theme', 'developer-blog-theme', 'Minimal and elegant blog theme for developers and tech writers.', 49.00, 29.00, 'https://example.com/preview2', 'active', 156, NOW(), NOW()),
(2, 'Dashboard Template', 'suspended-dashboard-template', 'Modern admin dashboard template with 100+ UI components.', 59.00, 35.00, NULL, 'active', 412, NOW(), NOW()),
(3, 'Pro UI Kit', 'suspended-pro-ui-kit', 'Comprehensive UI kit with 500+ components for Figma.', 99.00, 69.00, NULL, 'active', 89, NOW(), NOW()),
(4, 'SEO Plugin Pro', 'seo-plugin-pro', 'All-in-one SEO plugin for WordPress with schema markup.', 39.00, 25.00, NULL, 'active', 678, NOW(), NOW()),
(5, '3D Icon Pack', '3d-icon-pack', '200+ premium 3D icons in multiple formats.', 45.00, 30.00, NULL, 'active', 321, NOW(), NOW());

-- Payment Methods
INSERT INTO `payment_methods` (`name`, `number`, `type`, `status`, `created_at`, `updated_at`) VALUES
('bKash', '01700000000', 'mobile_banking', 'active', NOW(), NOW()),
('Nagad', '01800000000', 'mobile_banking', 'active', NOW(), NOW()),
('Rocket', '01900000000', 'mobile_banking', 'inactive', NOW(), NOW());

-- Testimonials
INSERT INTO `testimonials` (`name`, `role`, `content`, `rating`, `status`, `created_at`, `updated_at`) VALUES
('Sarah Johnson', 'Web Developer', 'The quality of themes here is incredible. I have been using them for all my client projects!', 5, 'active', NOW(), NOW()),
('Mike Chen', 'Designer', 'Best marketplace for UI kits and design resources. The support team is also very responsive.', 5, 'active', NOW(), NOW()),
('Amina Rahman', 'Business Owner', 'Saved me thousands of dollars on development. The templates are professional and easy to customize.', 4, 'active', NOW(), NOW());
