<?php
// database.php


class Database {
    /**
     * mysqli connection object
     * @var mysqli|null
     */
    private $connection = null;
    
    // Database configuration
    private $host = 'localhost';
    private $username = 'crsigdya_industreet';
    private $password = 'mH5TifxNt3DeZvP';
    private $dbname = 'crsigdya_calcufastintelligence';

    // ==================== CONNECTION FUNCTIONS ====================
    
    public function __construct() {
        $this->connect_db();
    }

    public function connect_db(){
        // Create connection
        $this->connection = mysqli_connect($this->host, $this->username, $this->password, $this->dbname);
        
        if (!$this->connection) {
            die("Database Connection Failed: " . mysqli_connect_error());
        }

        // Set charset
        mysqli_set_charset($this->connection, 'utf8mb4');
    }

    /**
     * Sanitize input using mysqli_real_escape_string
     */
    public function sanitize($var){
        return mysqli_real_escape_string($this->connection, $var);
    }

    

    // ==================== CONTACT FORM FUNCTIONS ====================

    /**
     * Insert contact inquiry (uses prepared statement)
     * returns inserted id on success, false on failure
     */
    public function Contact_Insert($full_name, $email, $phone_number, $inquiry_type, $case_reference, 
                                  $urgency, $message, $confidentiality_agreed, $consent_given, 
                                  $ip_address = '', $user_agent = '', $priority = 'medium') {
        
        $stmt = $this->connection->prepare("INSERT INTO `contacts` 
            (`full_name`, `email`, `phone_number`, `inquiry_type`, `case_reference`, 
             `urgency`, `message`, `confidentiality_agreed`, `consent_given`, 
             `ip_address`, `user_agent`, `priority`, `status`) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'new')");
        
        if (!$stmt) {
            error_log("Prepare failed: " . $this->connection->error);
            return false;
        }

        $stmt->bind_param("ssssssssisss", 
            $full_name, $email, $phone_number, $inquiry_type, $case_reference,
            $urgency, $message, $confidentiality_agreed, $consent_given,
            $ip_address, $user_agent, $priority);

        $exec = $stmt->execute();
        if ($exec) {
            $last_id = $this->connection->insert_id;
            $stmt->close();
            return $last_id;
        } else {
            error_log("Execute failed: " . $stmt->error);
            $stmt->close();
            return false;
        }
    }

    /**
     * Check if email already has recent inquiry (within 24 hours)
     */
    public function check_recent_inquiry($email) {
        $stmt = $this->connection->prepare("SELECT id FROM contacts WHERE email = ? AND created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)");
        if (!$stmt) {
            return false;
        }

        $stmt->bind_param("s", $email);
        $stmt->execute();
        $stmt->store_result();

        $exists = $stmt->num_rows > 0;
        $stmt->close();

        return $exists;
    }

    /**
     * Get case by ID
     */
    public function get_case_by_id($case_id) {
        $stmt = $this->connection->prepare("SELECT * FROM contacts WHERE id = ?");
        if (!$stmt) {
            return null;
        }

        $stmt->bind_param("i", $case_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $case = $result->fetch_assoc();
        $stmt->close();

        return $case;
    }

    /**
     * Get all contacts for admin view
     */
    public function get_all_contacts($limit = 100) {
        $stmt = $this->connection->prepare("SELECT * FROM contacts ORDER BY created_at DESC LIMIT ?");
        if (!$stmt) {
            return [];
        }

        $stmt->bind_param("i", $limit);
        $stmt->execute();
        $result = $stmt->get_result();
        $contacts = [];

        while ($row = $result->fetch_assoc()) {
            $contacts[] = $row;
        }

        $stmt->close();
        return $contacts;
    }

    /**
     * Update contact status
     */
    public function update_contact_status($contact_id, $status) {
        $stmt = $this->connection->prepare("UPDATE contacts SET status = ? WHERE id = ?");
        if (!$stmt) {
            return false;
        }

        $stmt->bind_param("si", $status, $contact_id);
        $exec = $stmt->execute();
        $stmt->close();

        return $exec;
    }

    // ==================== NEWSLETTER FUNCTIONS ====================

    /**
     * Newsletter insert
     */
    // ==================== NEWSLETTER FUNCTIONS ====================

/**
 * Newsletter insert with IP and user agent tracking
 * @param string $email Email address
 * @param string $ip_address Optional IP address (will auto-detect if null)
 * @param string $user_agent Optional User Agent (will auto-detect if null)
 * @return bool|string "exists" if already subscribed, true on success, false on error
 */
public function Newsletter_Insert($email, $ip_address = null, $user_agent = null) {
    if (!$this->connection) {
        return false;
    }

    // Auto-detect IP and User Agent if not provided (for backward compatibility)
    if ($ip_address === null) {
        $ip_address = $_SERVER['REMOTE_ADDR'] ?? '';
    }
    
    if ($user_agent === null) {
        $user_agent = $_SERVER['HTTP_USER_AGENT'] ?? '';
    }

    // Check existing
    $stmt = $this->connection->prepare("SELECT id FROM newsletter_subscribers WHERE email = ?");
    if (!$stmt) {
        return false;
    }

    $stmt->bind_param("s", $email);
    $stmt->execute();
    $stmt->store_result();

    if ($stmt->num_rows > 0) {
        $stmt->close();
        return "exists";
    }

    $stmt->close();

    // Insert with IP and user agent
    $stmt = $this->connection->prepare("INSERT INTO newsletter_subscribers (email, ip_address, user_agent, unsubscribe_token) VALUES (?, ?, ?, ?)");
    if (!$stmt) {
        return false;
    }

    $unsubscribe_token = bin2hex(random_bytes(16));
    $stmt->bind_param("ssss", $email, $ip_address, $user_agent, $unsubscribe_token);
    $exec = $stmt->execute();
    $stmt->close();

    return $exec ? true : false;
}

    // ==================== UTILITY FUNCTIONS ====================

    /**
     * Generate case reference
     */
    public function generate_case_reference($inquiry_type = 'general') {
        $prefixes = [
            'crypto_recovery' => 'CRYP',
            'romance_scam' => 'ROM',
            'business_fraud' => 'BIZ',
            'wire_fraud' => 'WIRE',
            'investment_scam' => 'INV',
            'credit_fraud' => 'CC',
            'real_estate' => 'RE',
            'prevention' => 'PREV',
            'general' => 'GEN'
        ];
        
        $prefix = $prefixes[$inquiry_type] ?? 'GEN';
        $date = date('ymd');
        $random = strtoupper(bin2hex(random_bytes(3)));
        
        return "RE-{$prefix}-{$date}-{$random}";
    }

    /**
     * Set priority based on urgency
     */
    public function get_priority($urgency) {
        $priority_map = [
            'normal' => 'medium',
            'high' => 'high',
            'urgent' => 'urgent'
        ];
        return $priority_map[$urgency] ?? 'medium';
    }

    /**
     * Get database connection for direct use if needed
     */
    public function get_connection() {
        return $this->connection;
    }

    /**
     * Check if table exists
     */
    public function table_exists($table_name) {
        $result = $this->connection->query("SHOW TABLES LIKE '$table_name'");
        return $result && $result->num_rows > 0;
    }

    /**
     * Create contacts table if not exists
     */
    public function create_contacts_table() {
        $sql = "
        CREATE TABLE IF NOT EXISTS contacts (
            id INT AUTO_INCREMENT PRIMARY KEY,
            full_name VARCHAR(100) NOT NULL,
            email VARCHAR(100) NOT NULL,
            phone_number VARCHAR(20) NOT NULL,
            inquiry_type ENUM(
                'crypto_recovery',
                'romance_scam',
                'business_fraud',
                'wire_fraud',
                'investment_scam',
                'credit_fraud',
                'real_estate',
                'prevention',
                'general'
            ) NOT NULL,
            case_reference VARCHAR(20) DEFAULT NULL,
            urgency ENUM('normal', 'high', 'urgent') DEFAULT 'normal',
            message TEXT NOT NULL,
            confidentiality_agreed BOOLEAN DEFAULT FALSE,
            consent_given BOOLEAN DEFAULT FALSE,
            ip_address VARCHAR(45) DEFAULT NULL,
            user_agent TEXT DEFAULT NULL,
            status ENUM('new', 'in_progress', 'responded', 'closed') DEFAULT 'new',
            priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
            assigned_to VARCHAR(50) DEFAULT NULL,
            response_notes TEXT DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_email (email),
            INDEX idx_status (status),
            INDEX idx_created_at (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        ";
        
        return $this->connection->query($sql);
    }

    /**
     * Create activity log table
     */
    public function create_activity_log_table() {
        $sql = "
        CREATE TABLE IF NOT EXISTS activity_log (
            id INT AUTO_INCREMENT PRIMARY KEY,
            action VARCHAR(50) NOT NULL,
            details TEXT,
            contact_id INT DEFAULT NULL,
            ip_address VARCHAR(45),
            user_agent TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
            INDEX idx_action (action),
            INDEX idx_created_at (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        ";
        
        return $this->connection->query($sql);
    }

    // ==================== FRAUD REPORT FUNCTIONS ====================

/**
 * Create fraud_reports table
 */
public function create_fraud_reports_table() {
    $sql = "
    CREATE TABLE IF NOT EXISTS fraud_reports (
        id INT AUTO_INCREMENT PRIMARY KEY,
        fraud_type ENUM(
            'crypto_recovery',
            'romance_scam',
            'business_fraud',
            'wire_fraud',
            'investment_scam',
            'credit_fraud',
            'real_estate',
            'prevention',
            'general'
        ) NOT NULL,
        first_noticed DATETIME NOT NULL,
        fraud_description TEXT NOT NULL,
        ongoing ENUM('yes', 'no', 'unknown') NOT NULL,
        reporter_name VARCHAR(100) NOT NULL,
        reporter_email VARCHAR(100) NOT NULL,
        reporter_phone VARCHAR(20) NOT NULL,
        case_reference VARCHAR(20) DEFAULT NULL,
        contact_preference ENUM('email', 'phone', 'both', 'secure') NOT NULL DEFAULT 'email',
        additional_info TEXT DEFAULT NULL,
        evidence_files TEXT DEFAULT NULL,
        fraud_case_number VARCHAR(20) DEFAULT NULL,
        amount_involved DECIMAL(15,2) DEFAULT NULL,
        currency VARCHAR(10) DEFAULT NULL,
        platform VARCHAR(100) DEFAULT NULL,
        ip_address VARCHAR(45) DEFAULT NULL,
        user_agent TEXT DEFAULT NULL,
        status ENUM('new', 'investigating', 'evidence_review', 'in_progress', 'resolved', 'closed') DEFAULT 'new',
        priority ENUM('low', 'medium', 'high', 'emergency') DEFAULT 'medium',
        investigator_id INT DEFAULT NULL,
        notes TEXT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_email (reporter_email),
        INDEX idx_case_number (fraud_case_number),
        INDEX idx_status (status),
        INDEX idx_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    
    return $this->connection->query($sql);
}
/**
 * Create newsletter subscribers table if not exists
 */
public function create_newsletter_table() {
    $sql = "
    CREATE TABLE IF NOT EXISTS newsletter_subscribers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(100) NOT NULL UNIQUE,
        subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        status ENUM('active', 'unsubscribed', 'bounced') DEFAULT 'active',
        ip_address VARCHAR(45) DEFAULT NULL,
        user_agent TEXT DEFAULT NULL,
        unsubscribe_token VARCHAR(32) DEFAULT NULL,
        last_notified TIMESTAMP NULL DEFAULT NULL,
        INDEX idx_email (email),
        INDEX idx_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    
    return $this->connection->query($sql);
}
/**
 * Insert fraud report
 */
/**
 * Insert fraud report
 */
/**
 * Insert fraud report - FIXED VERSION
 */
public function FraudReport_Insert($data) {
    // Generate fraud case number
    $fraud_case_number = $this->generate_fraud_case_number($data['fraud_type']);
    
    // Handle evidence files (store as JSON)
    $evidence_files = null;
    if (isset($data['evidence_files']) && is_array($data['evidence_files'])) {
        $evidence_files = json_encode($data['evidence_files']);
    }
    
    // Set default values for nullable fields
    $case_reference = !empty($data['case_reference']) ? $data['case_reference'] : null;
    $additional_info = !empty($data['additional_info']) ? $data['additional_info'] : null;
    $amount_involved = $data['amount_involved'] ?? null;
    $currency = $data['currency'] ?? null;
    $platform = $data['platform'] ?? null;
    $ip_address = $data['ip_address'] ?? '';
    $user_agent = $data['user_agent'] ?? '';
    
    // Ensure null values are properly handled for decimal
    if ($amount_involved === null || $amount_involved === '') {
        $amount_involved = null;
    }
    
    $stmt = $this->connection->prepare("INSERT INTO `fraud_reports` 
        (`fraud_type`, `first_noticed`, `fraud_description`, `ongoing`, 
         `reporter_name`, `reporter_email`, `reporter_phone`, `case_reference`,
         `contact_preference`, `additional_info`, `evidence_files`, `fraud_case_number`,
         `amount_involved`, `currency`, `platform`, `ip_address`, `user_agent`,
         `status`, `priority`) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'new', 'emergency')");
    
    if (!$stmt) {
        error_log("Prepare failed for fraud report: " . $this->connection->error);
        return false;
    }

    // Debug log
    error_log("Fraud report data - Preparing to insert");
    
    // FIX: Cannot pass null values directly to bind_param as references
    // We need to pass variables, not literals
    $params = [
        $data['fraud_type'],
        $data['first_noticed'],
        $data['fraud_description'],
        $data['ongoing'],
        $data['reporter_name'],
        $data['reporter_email'],
        $data['reporter_phone'],
        $case_reference,
        $data['contact_preference'],
        $additional_info,
        $evidence_files,
        $fraud_case_number,
        $amount_involved,
        $currency,
        $platform,
        $ip_address,
        $user_agent
    ];
    
    // Debug each parameter
    foreach ($params as $index => $param) {
        error_log("Param $index: " . (is_null($param) ? 'NULL' : (is_string($param) ? substr($param, 0, 50) : $param)));
    }
    
    // FIXED: The types string - s = string, d = double/decimal, b = blob
    // IMPORTANT: Pass variables, not values directly
    $stmt->bind_param("ssssssssssssdssss",
        $params[0],  // fraud_type
        $params[1],  // first_noticed
        $params[2],  // fraud_description
        $params[3],  // ongoing
        $params[4],  // reporter_name
        $params[5],  // reporter_email
        $params[6],  // reporter_phone
        $params[7],  // case_reference
        $params[8],  // contact_preference
        $params[9],  // additional_info
        $params[10], // evidence_files
        $params[11], // fraud_case_number
        $params[12], // amount_involved (can be null)
        $params[13], // currency
        $params[14], // platform
        $params[15], // ip_address
        $params[16], // user_agent
        // status and priority are hardcoded in SQL
    );

    $exec = $stmt->execute();
    
    if ($exec) {
        $last_id = $this->connection->insert_id;
        $stmt->close();
        
        error_log("Fraud report inserted successfully. ID: $last_id, Case: $fraud_case_number");
        
        return ['id' => $last_id, 'case_number' => $fraud_case_number];
    } else {
        error_log("Execute failed for fraud report: " . $stmt->error);
        error_log("SQL Error: " . $this->connection->error);
        $stmt->close();
        return false;
    }
}
/**
 * Generate fraud case number
 */
public function generate_fraud_case_number($fraud_type = 'general') {
    $prefixes = [
        'crypto_recovery' => 'F-CRYPT',
        'romance_scam' => 'F-ROM',
        'business_fraud' => 'F-BIZ',
        'wire_fraud' => 'F-WIRE',
        'investment_scam' => 'F-INV',
        'credit_fraud' => 'F-CC',
        'real_estate' => 'F-RE',
        'prevention' => 'F-PREV',
        'general' => 'F-GEN'
    ];
    
    $prefix = $prefixes[$fraud_type] ?? 'F-GEN';
    $date = date('ymd');
    $random = strtoupper(bin2hex(random_bytes(3)));
    
    return "{$prefix}-{$date}-{$random}";
}

/**
 * Check if email already has recent fraud report (within 12 hours)
 */
public function check_recent_fraud_report($email) {
    $stmt = $this->connection->prepare("SELECT id FROM fraud_reports WHERE reporter_email = ? AND created_at >= DATE_SUB(NOW(), INTERVAL 12 HOUR)");
    if (!$stmt) {
        return false;
    }

    $stmt->bind_param("s", $email);
    $stmt->execute();
    $stmt->store_result();

    $exists = $stmt->num_rows > 0;
    $stmt->close();

    return $exists;
}

/**
 * Get fraud report by ID
 */
public function get_fraud_report_by_id($report_id) {
    $stmt = $this->connection->prepare("SELECT * FROM fraud_reports WHERE id = ?");
    if (!$stmt) {
        return null;
    }

    $stmt->bind_param("i", $report_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $report = $result->fetch_assoc();
    
    // Decode evidence files JSON
    if ($report && !empty($report['evidence_files'])) {
        $report['evidence_files'] = json_decode($report['evidence_files'], true);
    }
    
    $stmt->close();
    return $report;
}

/**
 * Get all fraud reports for admin
 */
public function get_all_fraud_reports($limit = 100) {
    $stmt = $this->connection->prepare("SELECT * FROM fraud_reports ORDER BY created_at DESC LIMIT ?");
    if (!$stmt) {
        return [];
    }

    $stmt->bind_param("i", $limit);
    $stmt->execute();
    $result = $stmt->get_result();
    $reports = [];

    while ($row = $result->fetch_assoc()) {
        // Decode evidence files JSON
        if (!empty($row['evidence_files'])) {
            $row['evidence_files'] = json_decode($row['evidence_files'], true);
        }
        $reports[] = $row;
    }

    $stmt->close();
    return $reports;
}

    /**
     * Log activity
     */
    /**
 * Log activity
 */
public function log_activity($action, $details, $contact_id = null) {
    // Prepare the statement
    $stmt = $this->connection->prepare("INSERT INTO activity_log (action, details, contact_id, ip_address, user_agent) VALUES (?, ?, ?, ?, ?)");
    
    if (!$stmt) {
        error_log("Prepare failed for activity log: " . $this->connection->error);
        return false;
    }
    
    // Get IP address and user agent
    $ip_address = $_SERVER['REMOTE_ADDR'] ?? '';
    $user_agent = $_SERVER['HTTP_USER_AGENT'] ?? '';
    
    // Bind parameters - contact_id might be null, so handle it properly
    if ($contact_id === null) {
        $stmt->bind_param("ssiss", $action, $details, $contact_id, $ip_address, $user_agent);
    } else {
        $stmt->bind_param("ssiss", $action, $details, $contact_id, $ip_address, $user_agent);
    }
    
    $exec = $stmt->execute();
    if (!$exec) {
        error_log("Execute failed for activity log: " . $stmt->error);
    }
    
    $stmt->close();
    
    return $exec;
}

    /**
     * Close database connection
     */
    public function close_connection() {
        if ($this->connection) {
            mysqli_close($this->connection);
            $this->connection = null;
        }
    }

    /**
     * Destructor to close connection
     */
    public function __destruct() {
        $this->close_connection();
    }
}

// CSRF token generation (store in session)
if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}

// Instantiate Database
// Instantiate Database
try {
    $database = new Database();
    
    // Create tables if they don't exist
    if (!$database->table_exists('contacts')) {
        $database->create_contacts_table();
        error_log("Contacts table created");
    }
    
    if (!$database->table_exists('activity_log')) {
        $database->create_activity_log_table();
        error_log("Activity log table created");
    }
    
    // Create fraud_reports table if it doesn't exist
    if (!$database->table_exists('fraud_reports')) {
        $database->create_fraud_reports_table();
        error_log("Fraud reports table created");
    }
    // Add after creating other tables
if (!$database->table_exists('newsletter_subscribers')) {
    $database->create_newsletter_table();
    error_log("Newsletter subscribers table created");
}
    
    // Verify fraud_reports table structure
    $result = $database->get_connection()->query("DESCRIBE fraud_reports");
    if (!$result) {
        // Table might not exist or have wrong structure
        error_log("Fraud reports table structure issue, recreating...");
        $database->get_connection()->query("DROP TABLE IF EXISTS fraud_reports");
        $database->create_fraud_reports_table();
    } else {
        $result->free();
    }
    
} catch (Exception $e) {
    error_log("Database initialization failed: " . $e->getMessage());
    
    // Display user-friendly error message
    if (!isset($_SERVER['REQUEST_METHOD']) || $_SERVER['REQUEST_METHOD'] !== 'POST') {
        echo "<div style='padding: 20px; text-align: center;'>
                <h3>Database Connection Error</h3>
                <p>We're experiencing technical difficulties. Please try again later.</p>
                <p><small>If you're the site administrator, check the error logs.</small></p>
             </div>";
    }

}
?>