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

DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS messages;
DROP TABLE IF EXISTS leads;
DROP TABLE IF EXISTS product_images;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS brands;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','editor') DEFAULT 'admin',
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    site_name VARCHAR(150) NOT NULL,
    site_description TEXT NULL,
    logo VARCHAR(255) NULL,
    favicon VARCHAR(255) NULL,
    primary_color VARCHAR(20) NULL,
    secondary_color VARCHAR(20) NULL,
    whatsapp_number VARCHAR(30) NULL,
    instagram_url VARCHAR(255) NULL,
    facebook_url VARCHAR(255) NULL,
    tiktok_url VARCHAR(255) NULL,
    meta_title VARCHAR(255) NULL,
    meta_description TEXT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE brands (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NULL,
    brand_id INT NULL,
    name VARCHAR(180) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    short_description VARCHAR(255) NULL,
    description LONGTEXT NULL,
    sku VARCHAR(100) NULL,
    price DECIMAL(10,2) NULL,
    show_price TINYINT(1) DEFAULT 1,
    cover_image VARCHAR(255) NULL,
    is_featured TINYINT(1) DEFAULT 0,
    status TINYINT(1) DEFAULT 1,
    seo_title VARCHAR(255) NULL,
    seo_description TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    CONSTRAINT fk_products_brand FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE product_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    image VARCHAR(255) NOT NULL,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_product_images_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NULL,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(30) NULL,
    whatsapp VARCHAR(30) NULL,
    message TEXT NULL,
    source VARCHAR(100) NULL,
    status ENUM('novo','lido','em_contato','convertido','arquivado') DEFAULT 'novo',
    priority ENUM('baixa','media','alta') DEFAULT 'media',
    admin_notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_leads_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NULL,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(30) NULL,
    subject VARCHAR(180) NULL,
    message TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_messages_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(50) NOT NULL,
    title VARCHAR(180) NOT NULL,
    content TEXT NULL,
    reference_id INT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO users (name, email, password, role, status) VALUES
('Administrador', 'admin@catalogo.local', '$2y$10$hDQUYbQeDTH9iVQxhl5rQO0F8f4nXk5qkP4xMIOKgHdwNwp0UrEGK', 'admin', 1);

INSERT INTO settings
(site_name, site_description, primary_color, secondary_color, whatsapp_number, instagram_url, facebook_url, tiktok_url, meta_title, meta_description)
VALUES
('TechCatalog', 'Catálogo moderno, funcional e focado em conversão para produtos eletrônicos.', '#5b6cff', '#111827', '5511999999999', 'https://instagram.com/sualoja', 'https://facebook.com/sualoja', 'https://tiktok.com/@sualoja', 'TechCatalog - Catálogo de Eletrônicos', 'Catálogo online com leads, mensagens, dashboard administrativa e links amigáveis.');

INSERT INTO categories (name, slug, status) VALUES
('Smartphones', 'smartphones', 1),
('Notebooks', 'notebooks', 1),
('Acessórios', 'acessorios', 1),
('Games', 'games', 1);

INSERT INTO brands (name, slug, status) VALUES
('Samsung', 'samsung', 1),
('Apple', 'apple', 1),
('Dell', 'dell', 1),
('Logitech', 'logitech', 1);

INSERT INTO products
(category_id, brand_id, name, slug, short_description, description, sku, price, show_price, cover_image, is_featured, status, seo_title, seo_description)
VALUES
(1, 1, 'Galaxy S24 Ultra 256GB', 'galaxy-s24-ultra-256gb', 'Smartphone premium com câmera avançada e tela de alto brilho.', 'Modelo premium com ótimo desempenho, acabamento refinado e ideal para quem deseja produtividade e mídia com padrão top de linha.', 'SM-S24U-256', 6999.90, 1, NULL, 1, 1, 'Galaxy S24 Ultra 256GB', 'Smartphone premium com câmera avançada e tela de alto brilho.'),
(2, 3, 'Notebook Dell Inspiron 15', 'notebook-dell-inspiron-15', 'Notebook versátil para trabalho, estudo e produtividade.', 'Notebook com processador moderno, SSD rápido e construção equilibrada para rotina profissional ou acadêmica.', 'DELL-INS15', 4299.90, 1, NULL, 1, 1, 'Notebook Dell Inspiron 15', 'Notebook moderno para produtividade.'),
(3, 4, 'Mouse Gamer Logitech G502', 'mouse-gamer-logitech-g502', 'Mouse ergonômico com sensor preciso para uso gamer e profissional.', 'Acessório com excelente ergonomia, ótima resposta e design marcante para setups modernos.', 'LOG-G502', 0.00, 0, NULL, 0, 1, 'Mouse Gamer Logitech G502', 'Mouse gamer e profissional com excelente ergonomia.'),
(4, 2, 'Console PlayStation 5 Slim', 'console-playstation-5-slim', 'Console de última geração com design compacto e alta performance.', 'Equipamento para jogos de alto desempenho, com excelente biblioteca e visual moderno.', 'PS5-SLIM', 0.00, 0, NULL, 1, 1, 'Console PlayStation 5 Slim', 'Console premium para alto desempenho.');

INSERT INTO leads (product_id, name, email, phone, whatsapp, message, source, status, priority) VALUES
(1, 'Carlos Andrade', 'carlos@email.com', '11988887777', '11988887777', 'Quero saber condição de pagamento.', 'site-produto', 'novo', 'alta'),
(3, 'Fernanda Lima', 'fernanda@email.com', '11977776666', '11977776666', 'Tem pronta entrega?', 'site-produto', 'em_contato', 'media');

INSERT INTO messages (product_id, name, email, phone, subject, message, is_read) VALUES
(NULL, 'João Souza', 'joao@email.com', '11955554444', 'Parceria comercial', 'Gostaria de conversar sobre revenda.', 0),
(2, 'Marina Costa', 'marina@email.com', '11944443333', 'Dúvida técnica', 'Esse notebook aceita expansão de memória?', 1);

INSERT INTO notifications (type, title, content, reference_id, is_read) VALUES
('lead', 'Novo lead recebido', 'Lead para Galaxy S24 Ultra 256GB', 1, 0),
('message', 'Nova mensagem recebida', 'Parceria comercial', 1, 0),
('product', 'Produto criado', 'Console PlayStation 5 Slim', 4, 1);