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

-- Tabela de Usuários
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR NOT NULL,
    email VARCHAR NOT NULL UNIQUE,
    email_verified_at DATETIME,
    password VARCHAR NOT NULL,
    remember_token VARCHAR,
    created_at DATETIME,
    updated_at DATETIME
);

-- Tabela de Cache
CREATE TABLE IF NOT EXISTS cache (
    key VARCHAR NOT NULL PRIMARY KEY,
    value MEDIUMTEXT NOT NULL,
    expiration INTEGER NOT NULL
);

-- Tabela de Jobs
CREATE TABLE IF NOT EXISTS jobs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    queue VARCHAR NOT NULL,
    payload LONGTEXT NOT NULL,
    attempts INTEGER NOT NULL,
    reserved_at INTEGER,
    available_at INTEGER NOT NULL,
    created_at INTEGER NOT NULL
);

-- Tabela de Produtos
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR NOT NULL,
    description TEXT,
    price DECIMAL(8, 2) NOT NULL,
    image VARCHAR,
    stock INTEGER DEFAULT 0,
    category VARCHAR,
    created_at DATETIME,
    updated_at DATETIME
);

-- Tabela de Pedidos
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    status VARCHAR DEFAULT 'pending',
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Tabela de Itens do Pedido
CREATE TABLE IF NOT EXISTS order_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    price DECIMAL(8, 2) NOT NULL,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Índices para melhor performance
CREATE INDEX IF NOT EXISTS idx_jobs_queue ON jobs(queue);
CREATE INDEX IF NOT EXISTS idx_products_category ON products(category);
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_order_items_product_id ON order_items(product_id);

-- Inserir usuário de teste
INSERT INTO users (name, email, email_verified_at, password, created_at, updated_at) 
VALUES (
    'Test User',
    'test@example.com',
    NULL,
    '$2y$12$RW2XqOjYYHaC0fNqQGGKVODddRLANCWt0EqjqPsGb5m3W6VPQzDim',
    datetime('now'),
    datetime('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', datetime('now'), datetime('now')),
('Calça Jeans', 'Calça jeans clássica, perfeita para o dia a dia', 129.90, 30, 'Roupas', datetime('now'), datetime('now')),
('Jaqueta de Couro', 'Jaqueta de couro genuíno, estilo e durabilidade', 299.90, 15, 'Roupas', datetime('now'), datetime('now')),
('Sapato Social', 'Sapato social em couro, elegante e confortável', 189.90, 25, 'Calçados', datetime('now'), datetime('now')),
('Tênis Esportivo', 'Tênis com tecnologia de amortecimento', 159.90, 40, 'Calçados', datetime('now'), datetime('now')),
('Bolsa de Mão', 'Bolsa de mão em couro sintético', 89.90, 35, 'Acessórios', datetime('now'), datetime('now')),
('Relógio Analógico', 'Relógio clássico com pulseira em couro', 199.90, 20, 'Acessórios', datetime('now'), datetime('now')),
('Óculos de Sol', 'Óculos de sol com proteção UV', 79.90, 45, 'Acessórios', datetime('now'), datetime('now'));