-- Link na Bio - Script de Criação do Banco de Dados
-- Criado em: 21/04/2025

-- Criação do banco de dados
CREATE DATABASE IF NOT EXISTS linkbio CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE linkbio;

-- Tabela de links
CREATE TABLE IF NOT EXISTS links (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    url VARCHAR(2048) NOT NULL,
    icon VARCHAR(100) NULL,
    is_active BOOLEAN DEFAULT TRUE,
    click_count INT DEFAULT 0,
    order INT DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de banners
CREATE TABLE IF NOT EXISTS banners (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    url VARCHAR(2048) NULL,
    is_active BOOLEAN DEFAULT TRUE,
    order INT DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de redes sociais
CREATE TABLE IF NOT EXISTS social_media (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    platform VARCHAR(50) NOT NULL,
    url VARCHAR(2048) NOT NULL,
    icon VARCHAR(100) NULL,
    is_active BOOLEAN DEFAULT TRUE,
    order INT DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de layouts
CREATE TABLE IF NOT EXISTS layouts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    background_type ENUM('solid', 'gradient', 'image') DEFAULT 'solid',
    background_value TEXT NOT NULL,
    text_color VARCHAR(20) DEFAULT '#000000',
    font_family VARCHAR(100) DEFAULT 'Roboto, sans-serif',
    layout_template VARCHAR(50) DEFAULT 'default',
    link_style JSON NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de seções
CREATE TABLE IF NOT EXISTS sections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    type ENUM('links', 'banners', 'youtube', 'social', 'text') NOT NULL,
    content TEXT NULL,
    icon VARCHAR(100) NULL,
    is_active BOOLEAN DEFAULT TRUE,
    order INT DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de analytics
CREATE TABLE IF NOT EXISTS analytics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    visitor_ip VARCHAR(45) NULL,
    visitor_device VARCHAR(100) NULL,
    visitor_browser VARCHAR(100) NULL,
    visitor_os VARCHAR(100) NULL,
    visitor_country VARCHAR(100) NULL,
    page_viewed VARCHAR(255) NULL,
    link_clicked VARCHAR(2048) NULL,
    visit_duration INT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de backups
CREATE TABLE IF NOT EXISTS backups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    type ENUM('database', 'media', 'full') NOT NULL,
    size BIGINT NULL,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de failed jobs
CREATE TABLE IF NOT EXISTS failed_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(255) NOT NULL UNIQUE,
    connection TEXT NOT NULL,
    queue TEXT NOT NULL,
    payload LONGTEXT NOT NULL,
    exception LONGTEXT NOT NULL,
    failed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Índices para melhorar a performance
CREATE INDEX idx_links_user_id ON links(user_id);
CREATE INDEX idx_banners_user_id ON banners(user_id);
CREATE INDEX idx_social_media_user_id ON social_media(user_id);
CREATE INDEX idx_layouts_user_id ON layouts(user_id);
CREATE INDEX idx_sections_user_id ON sections(user_id);
CREATE INDEX idx_analytics_user_id ON analytics(user_id);
CREATE INDEX idx_backups_user_id ON backups(user_id);

-- Inserir layout padrão para o usuário administrador
INSERT INTO layouts (user_id, background_type, background_value, text_color, font_family, layout_template, link_style, created_at, updated_at)
VALUES (1, 'gradient', 'linear-gradient(135deg, #6a11cb 0%, #2575fc 100%)', '#ffffff', 'Poppins, sans-serif', 'default', 
'{"border_radius": 8, "border_width": 1, "border_color": "#ffffff", "background_color": "rgba(255, 255, 255, 0.1)", "text_color": "#ffffff", "hover_background_color": "rgba(255, 255, 255, 0.2)", "hover_text_color": "#ffffff"}',
NOW(), NOW());

-- Inserir seções padrão para o usuário administrador
INSERT INTO sections (user_id, title, type, icon, is_active, order, created_at, updated_at)
VALUES 
(1, 'Meus Links', 'links', 'fas fa-link', 1, 0, NOW(), NOW()),
(1, 'Banners', 'banners', 'fas fa-image', 1, 1, NOW(), NOW()),
(1, 'YouTube', 'youtube', 'fab fa-youtube', 1, 2, NOW(), NOW()),
(1, 'Redes Sociais', 'social', 'fas fa-share-alt', 1, 3, NOW(), NOW());

-- Inserir alguns links de exemplo
INSERT INTO links (user_id, title, url, icon, is_active, order, created_at, updated_at)
VALUES 
(1, 'Meu Site', 'https://exemplo.com', 'fas fa-globe', 1, 0, NOW(), NOW()),
(1, 'Meu Blog', 'https://exemplo.com/blog', 'fas fa-blog', 1, 1, NOW(), NOW()),
(1, 'Contato', 'mailto:contato@exemplo.com', 'fas fa-envelope', 1, 2, NOW(), NOW());

-- Inserir algumas redes sociais de exemplo
INSERT INTO social_media (user_id, platform, url, icon, is_active, order, created_at, updated_at)
VALUES 
(1, 'instagram', 'https://instagram.com/exemplo', 'fab fa-instagram', 1, 0, NOW(), NOW()),
(1, 'twitter', 'https://twitter.com/exemplo', 'fab fa-twitter', 1, 1, NOW(), NOW()),
(1, 'facebook', 'https://facebook.com/exemplo', 'fab fa-facebook', 1, 2, NOW(), NOW());
