-- Wiliete Store - SQL para criação do banco de dados
-- MySQL

-- Criar banco de dados
CREATE DATABASE IF NOT EXISTS loja CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE loja;

-- Tabela de Usuários
CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    email_verified_at TIMESTAMP NULL,
    password VARCHAR(255) NOT NULL,
    remember_token VARCHAR(100) NULL,
    is_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Cache
CREATE TABLE IF NOT EXISTS cache (
    `key` VARCHAR(255) NOT NULL PRIMARY KEY,
    value MEDIUMTEXT NOT NULL,
    expiration INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Sessions
CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(255) NOT NULL PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    payload LONGTEXT NOT NULL,
    last_activity INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_sessions_user_id (user_id),
    INDEX idx_sessions_last_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Jobs
CREATE TABLE IF NOT EXISTS jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    queue VARCHAR(255) NOT NULL,
    payload LONGTEXT NOT NULL,
    attempts TINYINT UNSIGNED NOT NULL,
    reserved_at INT UNSIGNED NULL,
    available_at INT UNSIGNED NOT NULL,
    created_at INT UNSIGNED NOT NULL,
    INDEX idx_jobs_queue (queue)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Produtos
CREATE TABLE IF NOT EXISTS products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description LONGTEXT NULL,
    price DECIMAL(8, 2) NOT NULL,
    image VARCHAR(255) NULL,
    stock INT DEFAULT 0,
    category VARCHAR(100) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_products_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Pedidos
CREATE TABLE IF NOT EXISTS orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    status VARCHAR(255) DEFAULT 'pending',
    payment_method VARCHAR(50) NULL,
    phone_reference VARCHAR(20) NULL,
    payment_reference VARCHAR(15) NULL,
    payment_status VARCHAR(20) DEFAULT 'pending',
    emis_id VARCHAR(255) NULL,
    payment_response TEXT NULL,
    payment_entity VARCHAR(50) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_orders_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Itens do Pedido
CREATE TABLE IF NOT EXISTS order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(8, 2) NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_order_items_order_id FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_items_product_id FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_order_items_order_id (order_id),
    INDEX idx_order_items_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inserir usuário de teste
INSERT INTO users (name, email, password, created_at, updated_at) 
VALUES (
    'Test User',
    'test@example.com',
    '$2y$12$RW2XqOjYYHaC0fNqQGGKVODddRLANCWt0EqjqPsGb5m3W6VPQzDim',
    NOW(),
    NOW()
);

-- Inserir produtos de teste
INSERT INTO products (name, description, price, stock, category, created_at, updated_at) VALUES
('Camiseta Básica', 'Camiseta 100% algodão, confortável e versátil', 49.90, 50, 'Roupas', NOW(), NOW()),
('Calça Jeans', 'Calça jeans clássica, perfeita para o dia a dia', 129.90, 30, 'Roupas', NOW(), NOW()),
('Jaqueta de Couro', 'Jaqueta de couro genuíno, estilo e durabilidade', 299.90, 15, 'Roupas', NOW(), NOW()),
('Sapato Social', 'Sapato social em couro, elegante e confortável', 189.90, 25, 'Calçados', NOW(), NOW()),
('Tênis Esportivo', 'Tênis com tecnologia de amortecimento', 159.90, 40, 'Calçados', NOW(), NOW()),
('Bolsa de Mão', 'Bolsa de mão em couro sintético', 89.90, 35, 'Acessórios', NOW(), NOW()),
('Relógio Analógico', 'Relógio clássico com pulseira em couro', 199.90, 20, 'Acessórios', NOW(), NOW()),
('Óculos de Sol', 'Óculos de sol com proteção UV', 79.90, 45, 'Acessórios', NOW(), NOW());