<?php
// api_invoice.php
require_once 'auth_check.php';
require_once 'db.php';

header('Content-Type: application/json');
$action = $_GET['action'] ?? '';

try {
    // 1. Ambil daftar DO yang siap di-generate jadi Invoice (Status 'Shipped' / belum ber-Invoice)
    if ($action === 'get_ready_dos') {
        $stmt = $pdo->query("
            SELECT do.id, do.do_number, do.delivery_date, so.so_number, c.customer_name 
            FROM delivery_orders do 
            JOIN sales_orders so ON do.so_id = so.id 
            JOIN customers c ON so.customer_id = c.id 
            LEFT JOIN invoices inv ON do.id = inv.do_id 
            WHERE inv.id IS NULL 
            ORDER BY do.id DESC
        ");
        echo json_encode(['status' => 'success', 'data' => $stmt->fetchAll()]);
    }

    // 2. Ambil detail barang dari DO beserta harga satuan dari SO
    elseif ($action === 'get_do_items_for_invoice') {
        $do_id = $_GET['do_id'] ?? 0;

        $stmt = $pdo->prepare("
            SELECT dod.product_id, p.product_code, p.product_name, p.unit, dod.qty_shipped, sod.unit_price 
            FROM delivery_order_details dod 
            JOIN delivery_orders do ON dod.do_id = do.id 
            JOIN sales_order_details sod ON (do.so_id = sod.so_id AND dod.product_id = sod.product_id) 
            JOIN products p ON dod.product_id = p.id 
            WHERE dod.do_id = ?
        ");
        $stmt->execute([$do_id]);
        $items = $stmt->fetchAll();

        // Hitung total nilai invoice
        $grand_total = 0;
        foreach ($items as &$item) {
            $item['subtotal'] = $item['qty_shipped'] * $item['unit_price'];
            $grand_total += $item['subtotal'];
        }

        echo json_encode(['status' => 'success', 'items' => $items, 'grand_total' => $grand_total]);
    }

    // 3. Simpan Invoice & Eksekusi Jurnal Otomatis Akuntansi
    elseif ($action === 'save_invoice') {
        $do_id        = $_POST['do_id'] ?? '';
        $invoice_date = $_POST['invoice_date'] ?? date('Y-m-d');
        $due_date     = $_POST['due_date'] ?? date('Y-m-d', strtotime('+30 days'));

        if (empty($do_id)) {
            echo json_encode(['status' => 'error', 'message' => 'Delivery Order wajib dipilih!']);
            exit;
        }

        // Ambil item DO & harga dari SO
        $stmtItems = $pdo->prepare("
            SELECT dod.product_id, dod.qty_shipped, sod.unit_price, do.do_number, c.customer_name 
            FROM delivery_order_details dod 
            JOIN delivery_orders do ON dod.do_id = do.id 
            JOIN sales_orders so ON do.so_id = so.id 
            JOIN sales_order_details sod ON (do.so_id = sod.so_id AND dod.product_id = sod.product_id) 
            JOIN customers c ON so.customer_id = c.id 
            WHERE dod.do_id = ?
        ");
        $stmtItems->execute([$do_id]);
        $items = $stmtItems->fetchAll();

        if (empty($items)) {
            echo json_encode(['status' => 'error', 'message' => 'Detail DO tidak ditemukan!']);
            exit;
        }

        $customer_name = $items[0]['customer_name'];
        $do_number     = $items[0]['do_number'];

        // Hitung Grand Total Invoice
        $grand_total = 0;
        foreach ($items as $item) {
            $grand_total += ($item['qty_shipped'] * $item['unit_price']);
        }

        // Generate Nomor Invoice Otomatis (Format: INV-YYYYMM-XXXX)
        $prefix = "INV-" . date('Ym') . "-";
        $stmtSeq = $pdo->query("SELECT COUNT(*) AS total FROM invoices WHERE invoice_number LIKE '$prefix%'");
        $seq = $stmtSeq->fetch()['total'] + 1;
        $invoice_number = $prefix . str_pad($seq, 4, '0', STR_PAD_LEFT);

        $pdo->beginTransaction();

        // A. Simpan Header Invoice
        $stmtInvHeader = $pdo->prepare("INSERT INTO invoices (invoice_number, do_id, invoice_date, due_date, subtotal, grand_total, status) VALUES (?, ?, ?, ?, ?, ?, 'Unpaid')");
        $stmtInvHeader->execute([$invoice_number, $do_id, $invoice_date, $due_date, $grand_total, $grand_total]);
        $invoice_id = $pdo->lastInsertId();

        // B. Simpan Detail Invoice
        $stmtInvDetail = $pdo->prepare("INSERT INTO invoice_details (invoice_id, product_id, qty, unit_price, subtotal) VALUES (?, ?, ?, ?, ?)");
        foreach ($items as $item) {
            $subtotal = $item['qty_shipped'] * $item['unit_price'];
            $stmtInvDetail->execute([$invoice_id, $item['product_id'], $item['qty_shipped'], $item['unit_price'], $subtotal]);
        }

        // C. LOGIKA AUTO-JOURNAL AKUNTANSI PENJUALAN & PIUTANG
        // Generate Nomor Jurnal Otomatis (Format: JRN-YYYYMM-XXXX)
        $jrnPrefix = "JRN-" . date('Ym') . "-";
        $stmtJrnSeq = $pdo->query("SELECT COUNT(*) AS total FROM journal_entries WHERE journal_number LIKE '$jrnPrefix%'");
        $jrnSeq = $stmtJrnSeq->fetch()['total'] + 1;
        $journal_number = $jrnPrefix . str_pad($jrnSeq, 4, '0', STR_PAD_LEFT);

        $description = "Penjualan Barang a/n $customer_name (Inv: $invoice_number, Ref DO: $do_number)";

        // 1. Insert Header Jurnal
        $stmtJrnHeader = $pdo->prepare("INSERT INTO journal_entries (journal_number, journal_date, reference_type, reference_id, description, total_debit, total_credit) VALUES (?, ?, 'Invoice', ?, ?, ?, ?)");
        $stmtJrnHeader->execute([$journal_number, $invoice_date, $invoice_id, $description, $grand_total, $grand_total]);
        $journal_id = $pdo->lastInsertId();

        // 2. Insert Detail Jurnal Debit (1120 - Piutang Usaha)
        $stmtJrnDetail = $pdo->prepare("INSERT INTO journal_details (journal_id, account_code, debit, credit) VALUES (?, ?, ?, ?)");
        $stmtJrnDetail->execute([$journal_id, '1120', $grand_total, 0.00]);

        // 3. Insert Detail Jurnal Kredit (4100 - Penjualan Barang)
        $stmtJrnDetail->execute([$journal_id, '4100', 0.00, $grand_total]);

        $pdo->commit();
        echo json_encode([
            'status' => 'success', 
            'message' => "Invoice $invoice_number dan Jurnal Akuntansi $journal_number berhasil diterbitkan!",
            'invoice_number' => $invoice_number
        ]);
    }

    // 4. Ambil Daftar Invoice (List View dengan Pagination & Filter Tahun)
    // 4. Ambil Daftar Invoice (List View dengan Pagination, Filter Tahun, & Pencarian)
    elseif ($action === 'get_invoice_list') {
        $year   = isset($_GET['year']) && $_GET['year'] !== '' ? intval($_GET['year']) : date('Y');
        $search = isset($_GET['search']) ? trim($_GET['search']) : '';
        $page   = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
        $limit  = isset($_GET['limit']) ? max(1, intval($_GET['limit'])) : 10;
        $offset = ($page - 1) * $limit;

        // Ambil daftar tahun unik
        $stmtYears = $pdo->query("SELECT DISTINCT YEAR(invoice_date) AS yr FROM invoices ORDER BY yr DESC");
        $years = $stmtYears->fetchAll(PDO::FETCH_COLUMN);
        if (empty($years)) {
            $years = [date('Y')];
        }

        // Susun kueri dasar dengan pencarian ke semua kolom utama
        $whereClause = "WHERE YEAR(inv.invoice_date) = ?";
        $params = [$year];

        if ($search !== '') {
            $whereClause .= " AND (inv.invoice_number LIKE ? OR do.do_number LIKE ? OR inv.invoice_date LIKE ? OR inv.due_date LIKE ? OR c.customer_name LIKE ? OR inv.grand_total LIKE ? OR inv.status LIKE ?)";
            $searchKeyword = "%{$search}%";
            for ($i = 0; $i < 7; $i++) {
                $params[] = $searchKeyword;
            }
        }

        // Hitung total data
        $stmtCount = $pdo->prepare("
            SELECT COUNT(*) AS total 
            FROM invoices inv 
            JOIN delivery_orders do ON inv.do_id = do.id 
            JOIN sales_orders so ON do.so_id = so.id 
            JOIN customers c ON so.customer_id = c.id 
            $whereClause
        ");
        $stmtCount->execute($params);
        $totalRecords = $stmtCount->fetch()['total'];
        $totalPages   = ceil($totalRecords / $limit);

        // Ambil data dengan limit & offset
        $sqlData = "
            SELECT inv.*, do.do_number, c.customer_name 
            FROM invoices inv 
            JOIN delivery_orders do ON inv.do_id = do.id 
            JOIN sales_orders so ON do.so_id = so.id 
            JOIN customers c ON so.customer_id = c.id 
            $whereClause 
            ORDER BY inv.id DESC 
            LIMIT ? OFFSET ?
        ";
        
        $stmtData = $pdo->prepare($sqlData);
        $bindIndex = 1;
        foreach ($params as $val) {
            $stmtData->bindValue($bindIndex, $val);
            $bindIndex++;
        }
        $stmtData->bindValue($bindIndex, $limit, PDO::PARAM_INT);
        $bindIndex++;
        $stmtData->bindValue($bindIndex, $offset, PDO::PARAM_INT);
        $stmtData->execute();

        echo json_encode([
            'status'        => 'success',
            'data'          => $stmtData->fetchAll(),
            'pagination'    => [
                'current_page'    => $page,
                'limit'           => $limit,
                'total_records'   => $totalRecords,
                'total_pages'     => $totalPages,
                'selected_year'   => $year,
                'available_years' => $years
            ]
        ]);
    }
	
	// 5. Hapus Invoice & Jurnal Akuntansi Terkait
    elseif ($action === 'delete_invoice') {
        $id = $_POST['id'] ?? '';
        
        if (empty($id)) {
            echo json_encode(['status' => 'error', 'message' => 'ID Invoice tidak valid!']);
            exit;
        }

        try {
            $pdo->beginTransaction();

            // 1. Hapus detail item invoice terlebih dahulu
            $stmtDelDetail = $pdo->prepare("DELETE FROM invoice_details WHERE invoice_id = ?");
            $stmtDelDetail->execute([$id]);

            // 2. Cari jurnal akuntansi yang berelasi dengan Invoice ini
            $stmtGetJrn = $pdo->prepare("SELECT id FROM journal_entries WHERE reference_type = 'Invoice' AND reference_id = ?");
            $stmtGetJrn->execute([$id]);
            $journal = $stmtGetJrn->fetch();

            if ($journal) {
                $journal_id = $journal['id'];

                // Hapus detail jurnal
                $stmtDelJrnDetail = $pdo->prepare("DELETE FROM journal_details WHERE journal_id = ?");
                $stmtDelJrnDetail->execute([$journal_id]);

                // Hapus header jurnal
                $stmtDelJrnHeader = $pdo->prepare("DELETE FROM journal_entries WHERE id = ?");
                $stmtDelJrnHeader->execute([$journal_id]);
            }

            // 3. Hapus header invoice
            $stmtDelInv = $pdo->prepare("DELETE FROM invoices WHERE id = ?");
            $stmtDelInv->execute([$id]);

            $pdo->commit();
            echo json_encode(['status' => 'success', 'message' => 'Invoice dan Jurnal Akuntansi terkait berhasil dihapus!']);
        } catch (Exception $e) {
            $pdo->rollBack();
            echo json_encode(['status' => 'error', 'message' => 'Gagal menghapus Invoice: ' . $e->getMessage()]);
        }
    }
	
	
	//6. Ambil Detail Auto-Journal berdasarkan Invoice ID
    elseif ($action === 'get_invoice_journal') {
        $invoice_id = $_GET['invoice_id'] ?? 0;

        // Ambil Header Jurnal
        $stmtJrn = $pdo->prepare("
            SELECT * FROM journal_entries 
            WHERE reference_type = 'Invoice' AND reference_id = ?
        ");
        $stmtJrn->execute([$invoice_id]);
        $journal = $stmtJrn->fetch();

        if ($journal) {
            // Ambil Detail Jurnal (Debit & Kredit)
            $stmtDetail = $pdo->prepare("
                SELECT jd.*, 
                       CASE 
                           WHEN jd.account_code = '1100' THEN 'Kas / Bank'
                           WHEN jd.account_code = '1120' THEN 'Piutang Usaha'
                           WHEN jd.account_code = '4100' THEN 'Penjualan Barang'
                           ELSE 'Akun Lainnya'
                       END AS account_name
                FROM journal_details jd 
                WHERE jd.journal_id = ?
                ORDER BY jd.id ASC
            ");
            $stmtDetail->execute([$journal['id']]);
            $details = $stmtDetail->fetchAll();

            echo json_encode([
                'status' => 'success',
                'journal' => $journal,
                'details' => $details
            ]);
        } else {
            echo json_encode([
                'status' => 'error',
                'message' => 'Jurnal akuntansi tidak ditemukan untuk invoice ini.'
            ]);
        }
    }

} catch (Exception $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo json_encode(['status' => 'error', 'message' => 'Error: ' . $e->getMessage()]);
}
?>