<?php
/**
 * Import Data Arsip dari File CSV/Excel
 * Alternative untuk sinkronisasi jika server SIPP tidak dapat diakses
 */

require 'auth.php';
require_admin(); // Only admin can import

$sqlite_path = 'data/sipp_sync.sqlite';

// Function untuk setup SQLite
function setupSQLite($path) {
    $dir = dirname($path);
    if (!is_dir($dir)) {
        mkdir($dir, 0755, true);
    }
    
    $pdo = new PDO("sqlite:$path", null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
    
    // Create tables
    $sql = "CREATE TABLE IF NOT EXISTS arsip (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nomor_perkara TEXT NOT NULL,
        nomor_arsip TEXT,
        no_ruang TEXT,
        no_lemari TEXT,
        no_rak TEXT,
        no_berkas TEXT,
        tanggal_sync DATETIME DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(nomor_perkara, nomor_arsip)
    )";
    $pdo->exec($sql);
    
    $sql = "CREATE TABLE IF NOT EXISTS sync_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        sync_date DATETIME DEFAULT CURRENT_TIMESTAMP,
        total_records INTEGER,
        success_records INTEGER,
        error_records INTEGER,
        sync_duration REAL,
        status TEXT,
        error_message TEXT
    )";
    $pdo->exec($sql);
    
    return $pdo;
}

// Process file upload
$upload_result = null;
$error_message = '';

if (isset($_POST['import_csv']) && isset($_FILES['csv_file'])) {
    try {
        $file = $_FILES['csv_file'];
        
        if ($file['error'] !== UPLOAD_ERR_OK) {
            throw new Exception('File upload error: ' . $file['error']);
        }
        
        $allowed_types = ['text/csv', 'application/vnd.ms-excel', 'application/csv'];
        $file_extension = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        
        if (!in_array($file_extension, ['csv', 'txt'])) {
            throw new Exception('Only CSV files are allowed');
        }
        
        $sqlite_pdo = setupSQLite($sqlite_path);
        
        // Clear old data
        $sqlite_pdo->exec("DELETE FROM arsip");
        
        $handle = fopen($file['tmp_name'], 'r');
        if (!$handle) {
            throw new Exception('Cannot read uploaded file');
        }
        
        $start_time = microtime(true);
        $total_records = 0;
        $success_records = 0;
        $error_records = 0;
        
        // Skip header row
        $header = fgetcsv($handle);
        
        $insert_sql = "INSERT OR REPLACE INTO arsip 
                      (nomor_perkara, nomor_arsip, no_ruang, no_lemari, no_rak, no_berkas) 
                      VALUES (?, ?, ?, ?, ?, ?)";
        $insert_stmt = $sqlite_pdo->prepare($insert_sql);
        
        $sqlite_pdo->beginTransaction();
        
        while (($data = fgetcsv($handle)) !== FALSE) {
            $total_records++;
            
            try {
                // Expected CSV format: nomor_perkara, nomor_arsip, no_ruang, no_lemari, no_rak, no_berkas
                $insert_stmt->execute([
                    $data[0] ?? '', // nomor_perkara
                    $data[1] ?? '', // nomor_arsip
                    $data[2] ?? '', // no_ruang
                    $data[3] ?? '', // no_lemari
                    $data[4] ?? '', // no_rak
                    $data[5] ?? ''  // no_berkas
                ]);
                $success_records++;
            } catch (PDOException $e) {
                $error_records++;
            }
        }
        
        $sqlite_pdo->commit();
        fclose($handle);
        
        $end_time = microtime(true);
        $duration = $end_time - $start_time;
        
        // Log import
        $log_sql = "INSERT INTO sync_log 
                   (total_records, success_records, error_records, sync_duration, status, error_message) 
                   VALUES (?, ?, ?, ?, ?, ?)";
        $log_stmt = $sqlite_pdo->prepare($log_sql);
        $log_stmt->execute([
            $total_records,
            $success_records,
            $error_records,
            $duration,
            ($error_records == 0) ? 'SUCCESS' : 'PARTIAL_SUCCESS',
            'CSV Import'
        ]);
        
        $upload_result = [
            'total' => $total_records,
            'success' => $success_records,
            'error' => $error_records,
            'duration' => $duration
        ];
        
    } catch (Exception $e) {
        $error_message = $e->getMessage();
    }
}

?>
<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Import Data Arsip - Alternative Mode</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.2/css/all.min.css">
</head>
<body>
    <div class="container mt-4">
        <div class="row justify-content-center">
            <div class="col-md-8">
                <div class="card">
                    <div class="card-header bg-warning text-dark">
                        <h5><i class="fa fa-upload"></i> Import Data Arsip (Alternative Mode)</h5>
                    </div>
                    <div class="card-body">
                        
                        <div class="alert alert-info">
                            <h6><i class="fa fa-info-circle"></i> Kapan Menggunakan Mode Ini?</h6>
                            <ul class="mb-0">
                                <li>Server SIPP (192.168.1.240) tidak dapat diakses</li>
                                <li>Koneksi database SIPP bermasalah</li>
                                <li>Ingin import data arsip dari file backup</li>
                                <li>Testing dengan sample data</li>
                            </ul>
                        </div>
                        
                        <?php if ($upload_result): ?>
                        <div class="alert alert-success">
                            <h6><i class="fa fa-check-circle"></i> Import Berhasil!</h6>
                            <div class="row">
                                <div class="col-md-3">
                                    <strong>Total Records:</strong><br>
                                    <span class="h5 text-primary"><?php echo number_format($upload_result['total']); ?></span>
                                </div>
                                <div class="col-md-3">
                                    <strong>Berhasil:</strong><br>
                                    <span class="h5 text-success"><?php echo number_format($upload_result['success']); ?></span>
                                </div>
                                <div class="col-md-3">
                                    <strong>Error:</strong><br>
                                    <span class="h5 text-danger"><?php echo number_format($upload_result['error']); ?></span>
                                </div>
                                <div class="col-md-3">
                                    <strong>Duration:</strong><br>
                                    <span class="h5 text-info"><?php echo number_format($upload_result['duration'], 2); ?>s</span>
                                </div>
                            </div>
                        </div>
                        <?php endif; ?>
                        
                        <?php if ($error_message): ?>
                        <div class="alert alert-danger">
                            <h6><i class="fa fa-exclamation-triangle"></i> Import Gagal</h6>
                            <p><?php echo htmlspecialchars($error_message); ?></p>
                        </div>
                        <?php endif; ?>
                        
                        <div class="card">
                            <div class="card-header">
                                <h6><i class="fa fa-file-csv"></i> Upload File CSV</h6>
                            </div>
                            <div class="card-body">
                                <form method="POST" enctype="multipart/form-data">
                                    <div class="mb-3">
                                        <label for="csv_file" class="form-label">Pilih File CSV:</label>
                                        <input type="file" class="form-control" id="csv_file" name="csv_file" 
                                               accept=".csv,.txt" required>
                                        <div class="form-text">
                                            File CSV harus berformat: nomor_perkara, nomor_arsip, no_ruang, no_lemari, no_rak, no_berkas
                                        </div>
                                    </div>
                                    
                                    <button type="submit" name="import_csv" class="btn btn-warning">
                                        <i class="fa fa-upload"></i> Import Data
                                    </button>
                                </form>
                            </div>
                        </div>
                        
                        <div class="card mt-4">
                            <div class="card-header">
                                <h6><i class="fa fa-download"></i> Template CSV</h6>
                            </div>
                            <div class="card-body">
                                <p>Download template CSV untuk format yang benar:</p>
                                <a href="create-csv-template.php" class="btn btn-info btn-sm">
                                    <i class="fa fa-download"></i> Download Template
                                </a>
                                
                                <hr>
                                
                                <h6>Format CSV yang Diharapkan:</h6>
                                <div class="table-responsive">
                                    <table class="table table-sm table-bordered">
                                        <thead>
                                            <tr>
                                                <th>nomor_perkara</th>
                                                <th>nomor_arsip</th>
                                                <th>no_ruang</th>
                                                <th>no_lemari</th>
                                                <th>no_rak</th>
                                                <th>no_berkas</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <tr>
                                                <td>123/Pdt.P/2024/PN Wtp</td>
                                                <td>001</td>
                                                <td>A</td>
                                                <td>01</td>
                                                <td>001</td>
                                                <td>001</td>
                                            </tr>
                                            <tr>
                                                <td>124/Pdt.P/2024/PN Wtp</td>
                                                <td>002</td>
                                                <td>A</td>
                                                <td>01</td>
                                                <td>001</td>
                                                <td>002</td>
                                            </tr>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                        
                        <hr>
                        
                        <div class="d-flex gap-2">
                            <a href="sync-sipp-to-sqlite.php" class="btn btn-primary">
                                <i class="fa fa-sync"></i> Coba Sync Normal
                            </a>
                            <a href="sync-enhanced-debug.php" class="btn btn-warning">
                                <i class="fa fa-bug"></i> Debug Mode
                            </a>
                            <a href="test-sqlite-arsip.php" class="btn btn-success">
                                <i class="fa fa-eye"></i> Test Data
                            </a>
                            <a href="index.php" class="btn btn-secondary">
                                <i class="fa fa-arrow-left"></i> Kembali
                            </a>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
