-- Enterprise QR Code Generator Database Schema
-- Internal Company Use Only

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

-- QR Codes Table
CREATE TABLE qr_codes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    user_id INT UNSIGNED NOT NULL,
    type ENUM('static', 'dynamic', 'multilink') NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    slug VARCHAR(255) NULL UNIQUE,
    
    -- Static QR Data
    content_type ENUM('url', 'text', 'email', 'phone', 'sms', 'wifi', 'vcard') NULL,
    static_content TEXT NULL,
    
    -- Dynamic QR Data
    destination_url TEXT NULL,
    
    -- Design Options
    size INT UNSIGNED NOT NULL DEFAULT 300,
    margin INT UNSIGNED NOT NULL DEFAULT 10,
    foreground_color VARCHAR(7) NOT NULL DEFAULT '#000000',
    background_color VARCHAR(7) NOT NULL DEFAULT '#FFFFFF',
    logo_path VARCHAR(255) NULL,
    error_correction ENUM('L', 'M', 'Q', 'H') NOT NULL DEFAULT 'M',
    
    -- File Storage
    file_path VARCHAR(255) NULL,
    file_format ENUM('png', 'svg', 'pdf') NOT NULL DEFAULT 'png',
    
    -- Tracking
    scan_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_scanned_at DATETIME NULL,
    
    -- Status & Metadata
    status ENUM('active', 'inactive', 'archived') NOT NULL DEFAULT 'active',
    expires_at DATETIME NULL,
    campaign_id INT UNSIGNED NULL,
    tags JSON NULL,
    metadata JSON NULL,
    
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_uuid (uuid),
    INDEX idx_user_id (user_id),
    INDEX idx_type (type),
    INDEX idx_slug (slug),
    INDEX idx_status (status),
    INDEX idx_campaign_id (campaign_id),
    FULLTEXT idx_search (name, description),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Multi-Link Pages Table
CREATE TABLE multilink_pages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    qr_code_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    
    -- Branding
    logo_url VARCHAR(255) NULL,
    background_type ENUM('color', 'gradient', 'image') NOT NULL DEFAULT 'color',
    background_value VARCHAR(255) NOT NULL DEFAULT '#FFFFFF',
    theme ENUM('light', 'dark', 'custom') NOT NULL DEFAULT 'light',
    custom_css TEXT NULL,
    
    -- SEO
    meta_title VARCHAR(255) NULL,
    meta_description TEXT NULL,
    meta_image VARCHAR(255) NULL,
    
    -- Analytics
    view_count INT UNSIGNED NOT NULL DEFAULT 0,
    
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (qr_code_id) REFERENCES qr_codes(id) ON DELETE CASCADE,
    INDEX idx_qr_code_id (qr_code_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Multi-Link Items Table
CREATE TABLE multilink_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    multilink_page_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    url TEXT NOT NULL,
    icon VARCHAR(100) NULL,
    description TEXT NULL,
    display_order INT UNSIGNED NOT NULL DEFAULT 0,
    click_count INT UNSIGNED NOT NULL DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (multilink_page_id) REFERENCES multilink_pages(id) ON DELETE CASCADE,
    INDEX idx_multilink_page_id (multilink_page_id),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Campaigns Table
CREATE TABLE campaigns (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    user_id INT UNSIGNED NOT NULL,
    status ENUM('draft', 'active', 'paused', 'completed', 'archived') NOT NULL DEFAULT 'draft',
    
    -- Campaign Period
    starts_at DATETIME NULL,
    ends_at DATETIME NULL,
    
    -- Goals
    target_scans INT UNSIGNED NULL,
    budget DECIMAL(10, 2) NULL,
    
    -- Metadata
    tags JSON NULL,
    metadata JSON NULL,
    
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- QR Scans Analytics Table
CREATE TABLE qr_scans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    qr_code_id INT UNSIGNED NOT NULL,
    
    -- Device & Location
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    device_type ENUM('mobile', 'tablet', 'desktop', 'other') NULL,
    browser VARCHAR(100) NULL,
    os VARCHAR(100) NULL,
    country VARCHAR(2) NULL,
    city VARCHAR(100) NULL,
    
    -- Referrer
    referrer TEXT NULL,
    
    -- Timestamp
    scanned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (qr_code_id) REFERENCES qr_codes(id) ON DELETE CASCADE,
    INDEX idx_qr_code_id (qr_code_id),
    INDEX idx_scanned_at (scanned_at),
    INDEX idx_device_type (device_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Multi-Link Click Analytics
CREATE TABLE multilink_clicks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    multilink_item_id INT UNSIGNED NOT NULL,
    
    -- Device & Location
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    device_type ENUM('mobile', 'tablet', 'desktop', 'other') NULL,
    
    clicked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (multilink_item_id) REFERENCES multilink_items(id) ON DELETE CASCADE,
    INDEX idx_multilink_item_id (multilink_item_id),
    INDEX idx_clicked_at (clicked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Logs Table
CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100) NULL,
    entity_id INT UNSIGNED NULL,
    description TEXT NULL,
    
    -- Request Info
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Templates Table (for reusable QR designs)
CREATE TABLE qr_templates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    
    -- Design Settings
    size INT UNSIGNED NOT NULL DEFAULT 300,
    margin INT UNSIGNED NOT NULL DEFAULT 10,
    foreground_color VARCHAR(7) NOT NULL DEFAULT '#000000',
    background_color VARCHAR(7) NOT NULL DEFAULT '#FFFFFF',
    logo_path VARCHAR(255) NULL,
    error_correction ENUM('L', 'M', 'Q', 'H') NOT NULL DEFAULT 'M',
    
    is_public BOOLEAN NOT NULL DEFAULT FALSE,
    usage_count INT UNSIGNED NOT NULL DEFAULT 0,
    
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_is_public (is_public)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
