-- 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;

-- Users Table (Staff Only)
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'manager', 'staff', 'viewer') NOT NULL DEFAULT 'staff',
    status ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'active',
    department VARCHAR(100) NULL,
    phone VARCHAR(20) NULL,
    avatar VARCHAR(255) NULL,
    last_login_at DATETIME NULL,
    last_login_ip VARCHAR(45) NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_status (status),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 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;

-- Settings Table (System Configuration)
CREATE TABLE settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(255) NOT NULL UNIQUE,
    value TEXT NULL,
    type ENUM('string', 'integer', 'boolean', 'json') NOT NULL DEFAULT 'string',
    description TEXT NULL,
    is_public BOOLEAN NOT NULL DEFAULT FALSE,
    
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_key (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert Default Admin User (password: Admin@123)
INSERT INTO users (name, email, password, role, status) VALUES
('System Administrator', 'admin@company.internal', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'active');

-- Insert Default Settings
INSERT INTO settings (`key`, value, type, description, is_public) VALUES
('company_name', 'Your Company Name', 'string', 'Company name displayed in the system', TRUE),
('company_logo', '/assets/logo.png', 'string', 'Company logo URL', TRUE),
('default_qr_size', '300', 'integer', 'Default QR code size in pixels', FALSE),
('max_qr_size', '1000', 'integer', 'Maximum QR code size in pixels', FALSE),
('allow_custom_domains', 'false', 'boolean', 'Allow custom domains for QR redirects', FALSE),
('analytics_retention_days', '365', 'integer', 'Number of days to retain analytics data', FALSE);