CREATE DATABASE IF NOT EXISTS scriptmarket CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE scriptmarket;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','customer') NOT NULL DEFAULT 'customer',
    two_factor_secret VARCHAR(255) NULL,
    status ENUM('active','blocked') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id BIGINT UNSIGNED NULL,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE,
    description TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(190) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    short_description VARCHAR(255) NOT NULL,
    description LONGTEXT NOT NULL,
    thumbnail VARCHAR(255) NULL,
    gallery JSON NULL,
    video_url VARCHAR(255) NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    promo_price DECIMAL(12,2) NULL,
    version VARCHAR(50) NULL,
    file_size VARCHAR(50) NULL,
    compatibility VARCHAR(255) NULL,
    demo_url VARCHAR(255) NULL,
    features LONGTEXT NULL,
    min_requirements LONGTEXT NULL,
    changelog LONGTEXT NULL,
    tags JSON NULL,
    featured TINYINT(1) NOT NULL DEFAULT 0,
    sales_count INT NOT NULL DEFAULT 0,
    status ENUM('draft','active','inactive') NOT NULL DEFAULT 'draft',
    meta_title VARCHAR(190) NULL,
    meta_description VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

CREATE TABLE digital_files (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    storage_name VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    extension VARCHAR(20) NOT NULL,
    size_bytes BIGINT UNSIGNED NOT NULL,
    checksum VARCHAR(128) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_files_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    reference VARCHAR(80) NOT NULL UNIQUE,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    discount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    payment_method ENUM('paypal','pagseguro','pix') NOT NULL,
    payment_gateway_status VARCHAR(100) NULL,
    status ENUM('pending','paid','cancelled','refunded') NOT NULL DEFAULT 'pending',
    paid_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    product_name VARCHAR(190) NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    total DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_items_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE download_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    token CHAR(64) NOT NULL UNIQUE,
    max_downloads INT NOT NULL DEFAULT 1,
    download_count INT NOT NULL DEFAULT 0,
    expires_at TIMESTAMP NOT NULL,
    last_download_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_download_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_download_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE coupons (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    type ENUM('percent','fixed') NOT NULL,
    value DECIMAL(12,2) NOT NULL,
    min_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    usage_limit INT NULL,
    usage_count INT NOT NULL DEFAULT 0,
    expires_at TIMESTAMP NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE reviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    author_name VARCHAR(120) NOT NULL,
    rating TINYINT UNSIGNED NOT NULL,
    comment TEXT NOT NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_reviews_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    CONSTRAINT fk_reviews_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE favorites (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_favorite (user_id, product_id),
    CONSTRAINT fk_favorites_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_favorites_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE faqs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    question VARCHAR(255) NOT NULL,
    answer TEXT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active'
);

CREATE TABLE settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    group_name VARCHAR(120) NOT NULL,
    key_name VARCHAR(120) NOT NULL UNIQUE,
    value_text LONGTEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE admin_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(190) NOT NULL,
    ip_address VARCHAR(45) NULL,
    context_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, email, password, role) VALUES
('Administrador', 'admin@scriptmarket.local', '$2y$10$X4R4Lq8zwQWvv1pbDBfY7.8RrM6QGQkdrG1FzHbD0u9bujYxKtf0C', 'admin');

INSERT INTO categories (name, slug, description) VALUES
('Marketplaces', 'marketplaces', 'Plataformas de e-commerce e venda digital'),
('Automação', 'automacao', 'Bots, integrações e automações empresariais'),
('SaaS', 'saas', 'Sistemas prontos em modelo software as a service');

INSERT INTO products (category_id, name, slug, short_description, description, thumbnail, price, promo_price, version, file_size, compatibility, demo_url, featured, sales_count, status, meta_title, meta_description, created_at) VALUES
(1, 'Marketplace de Scripts Pro', 'marketplace-de-scripts-pro', 'Marketplace completo com área do cliente, pagamentos e downloads seguros.', 'Sistema completo para venda de scripts com MVC, SEO, segurança, gateway de pagamento, downloads protegidos e painel administrativo.', 'https://via.placeholder.com/600x420', 497.00, 397.00, '1.0.0', '48 MB', 'PHP 8.1+, MySQL 8+, Apache/Nginx', 'https://demo.exemplo.com', 1, 122, 'active', 'Marketplace de Scripts Pro', 'Marketplace profissional para venda de scripts.', NOW()),
(2, 'Painel SaaS Financeiro', 'painel-saas-financeiro', 'Painel financeiro com gráficos e relatórios.', 'Aplicação SaaS com dashboard, relatórios e autenticação forte.', 'https://via.placeholder.com/600x420', 349.00, NULL, '2.3.0', '31 MB', 'PHP 8.1+, MySQL 8+', 'https://demo.exemplo.com/finance', 1, 78, 'active', 'Painel SaaS Financeiro', 'Dashboard SaaS com foco financeiro.', NOW()),
(3, 'Bot de Atendimento WhatsApp', 'bot-de-atendimento-whatsapp', 'Bot com integrações e automação de vendas.', 'Bot robusto com integrações para suporte e automação comercial.', 'https://via.placeholder.com/600x420', 289.00, 249.00, '1.6.0', '22 MB', 'PHP 8.1+, MySQL 8+', 'https://demo.exemplo.com/bot', 0, 53, 'active', 'Bot de Atendimento WhatsApp', 'Automação comercial com WhatsApp.', NOW());

INSERT INTO reviews (product_id, author_name, rating, comment, status) VALUES
(1, 'Mariana Costa', 5, 'Excelente estrutura e muito fácil de personalizar.', 'approved'),
(1, 'Rafael Souza', 5, 'Entrega profissional, layout moderno e boa organização do código.', 'approved'),
(2, 'Juliano Lima', 4, 'Boa base para evoluir um SaaS completo.', 'approved');