<?php
/* =====================================================
   File: /home/jewellersbd/public_html/erp.jewellersbd.com/api/purchases/create_barcodes.php
   Purpose: New Purchase -> Manual Barcode Create
   Rule:
   - Barcode এ rate / purchase price / sale price থাকবে না
   - প্রতিটি barcode আলাদা weight save হবে
   - barcode_stock এর সব common weight column-এ value save হবে
   ===================================================== */

ini_set('display_errors', 0);
error_reporting(E_ALL);
header('Content-Type: application/json; charset=utf-8');

require_once __DIR__ . '/../../config/session.php';
require_once __DIR__ . '/../../config/db.php';

function out_json($code, $success, $message, $data = null) {
    http_response_code($code);
    echo json_encode([
        'success' => (bool)$success,
        'status'  => (bool)$success,
        'message' => $message,
        'data'    => $data
    ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

if (function_exists('is_logged_in') && !is_logged_in()) {
    out_json(401, false, 'লগইন প্রয়োজন', null);
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    out_json(405, false, 'শুধু POST অনুরোধ গ্রহণযোগ্য', null);
}

if (!isset($pdo) || !($pdo instanceof PDO)) {
    out_json(500, false, 'PDO DB connection পাওয়া যায়নি', null);
}

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("SET NAMES utf8mb4");

$data = json_decode(file_get_contents('php://input'), true);
if (!is_array($data)) {
    out_json(400, false, 'Invalid JSON data', null);
}

function clean_name($name) {
    return preg_replace('/[^a-zA-Z0-9_]/', '', (string)$name);
}

function table_exists(PDO $pdo, $table) {
    $table = clean_name($table);
    $st = $pdo->query("SHOW TABLES LIKE " . $pdo->quote($table));
    return (bool)$st->fetchColumn();
}

function column_exists(PDO $pdo, $table, $column) {
    $table = clean_name($table);
    $column = clean_name($column);
    $st = $pdo->query("SHOW COLUMNS FROM `{$table}` LIKE " . $pdo->quote($column));
    return (bool)$st->fetch(PDO::FETCH_ASSOC);
}

function ensure_column(PDO $pdo, $table, $column, $definition) {
    $table = clean_name($table);
    $column = clean_name($column);
    if (!column_exists($pdo, $table, $column)) {
        $pdo->exec("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
    }
}

function get_columns(PDO $pdo, $table) {
    $table = clean_name($table);
    $rows = $pdo->query("SHOW COLUMNS FROM `{$table}`")->fetchAll(PDO::FETCH_ASSOC);
    $cols = [];
    foreach ($rows as $r) $cols[$r['Field']] = true;
    return $cols;
}

function pick_col($cols, $names) {
    foreach ($names as $n) {
        if (isset($cols[$n])) return $n;
    }
    return null;
}

function first_value($arr, $keys, $default = null) {
    foreach ($keys as $k) {
        if (isset($arr[$k]) && $arr[$k] !== '' && $arr[$k] !== null) return $arr[$k];
    }
    return $default;
}

function first_int($arr, $keys, $default = 0) {
    foreach ($keys as $k) {
        if (isset($arr[$k]) && $arr[$k] !== '' && $arr[$k] !== null) return (int)$arr[$k];
    }
    return (int)$default;
}

function first_float($arr, $keys, $default = 0) {
    foreach ($keys as $k) {
        if (isset($arr[$k]) && $arr[$k] !== '' && $arr[$k] !== null) return (float)$arr[$k];
    }
    return (float)$default;
}

function add_insert(&$cols, &$marks, &$params, $dbCols, $col, $value) {
    if (isset($dbCols[$col])) {
        $cols[] = "`{$col}`";
        $marks[] = "?";
        $params[] = $value;
    }
}

function gram_from_parts($vori, $ana, $roti, $point) {
    $VORI = 11.664;
    return round(
        ((float)$vori * $VORI) +
        ((float)$ana * ($VORI / 16)) +
        ((float)$roti * ($VORI / 96)) +
        ((float)$point * ($VORI / 960)),
        3
    );
}

function purity_percent($karat, $fallback = 91.667) {
    $map = ['24K'=>100, '22K'=>91.667, '21K'=>87.5, '18K'=>75, 'other'=>91.667];
    return $map[$karat] ?? $fallback;
}

function create_barcode_table_if_missing(PDO $pdo) {
    if (!table_exists($pdo, 'barcode_stock')) {
        $pdo->exec("
            CREATE TABLE `barcode_stock` (
                `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
                `barcode_no` VARCHAR(80) NULL,
                `barcode` VARCHAR(80) NULL,
                `barcode_code` VARCHAR(80) NULL,
                `purchase_id` INT NULL,
                `item_name` VARCHAR(255) NULL,
                `party_id` INT NULL,
                `karat` VARCHAR(20) NULL,
                `purity_percent` DECIMAL(10,3) NOT NULL DEFAULT 0,
                `location_id` INT NULL,
                `vori` DECIMAL(12,3) NOT NULL DEFAULT 0,
                `ana` DECIMAL(12,3) NOT NULL DEFAULT 0,
                `roti` DECIMAL(12,3) NOT NULL DEFAULT 0,
                `point` DECIMAL(12,3) NOT NULL DEFAULT 0,
                `gross_weight_gram` DECIMAL(12,3) NOT NULL DEFAULT 0,
                `gross_weight` DECIMAL(12,3) NOT NULL DEFAULT 0,
                `weight` DECIMAL(12,3) NOT NULL DEFAULT 0,
                `weight_gram` DECIMAL(12,3) NOT NULL DEFAULT 0,
                `total_weight_gram` DECIMAL(12,3) NOT NULL DEFAULT 0,
                `pure_weight_gram` DECIMAL(12,3) NOT NULL DEFAULT 0,
                `status` VARCHAR(30) NOT NULL DEFAULT 'in_stock',
                `note` TEXT NULL,
                `created_by` INT NULL,
                `created_at` DATETIME NULL,
                `updated_at` DATETIME NULL,
                PRIMARY KEY (`id`),
                KEY `idx_purchase_id` (`purchase_id`),
                KEY `idx_status` (`status`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
    }
}

function ensure_barcode_columns(PDO $pdo) {
    create_barcode_table_if_missing($pdo);

    ensure_column($pdo, 'barcode_stock', 'barcode_no', "VARCHAR(80) NULL");
    ensure_column($pdo, 'barcode_stock', 'barcode', "VARCHAR(80) NULL");
    ensure_column($pdo, 'barcode_stock', 'barcode_code', "VARCHAR(80) NULL");
    ensure_column($pdo, 'barcode_stock', 'purchase_id', "INT NULL");
    ensure_column($pdo, 'barcode_stock', 'source_purchase_id', "INT NULL");

    ensure_column($pdo, 'barcode_stock', 'item_name', "VARCHAR(255) NULL");
    ensure_column($pdo, 'barcode_stock', 'name', "VARCHAR(255) NULL");
    ensure_column($pdo, 'barcode_stock', 'product_name', "VARCHAR(255) NULL");

    ensure_column($pdo, 'barcode_stock', 'party_id', "INT NULL");
    ensure_column($pdo, 'barcode_stock', 'supplier_id', "INT NULL");

    ensure_column($pdo, 'barcode_stock', 'karat', "VARCHAR(20) NULL");
    ensure_column($pdo, 'barcode_stock', 'purity', "VARCHAR(20) NULL");
    ensure_column($pdo, 'barcode_stock', 'purity_percent', "DECIMAL(10,3) NOT NULL DEFAULT 0");

    ensure_column($pdo, 'barcode_stock', 'location_id', "INT NULL");
    ensure_column($pdo, 'barcode_stock', 'stock_location_id', "INT NULL");

    ensure_column($pdo, 'barcode_stock', 'vori', "DECIMAL(12,3) NOT NULL DEFAULT 0");
    ensure_column($pdo, 'barcode_stock', 'bhori', "DECIMAL(12,3) NOT NULL DEFAULT 0");
    ensure_column($pdo, 'barcode_stock', 'ana', "DECIMAL(12,3) NOT NULL DEFAULT 0");
    ensure_column($pdo, 'barcode_stock', 'roti', "DECIMAL(12,3) NOT NULL DEFAULT 0");
    ensure_column($pdo, 'barcode_stock', 'rati', "DECIMAL(12,3) NOT NULL DEFAULT 0");
    ensure_column($pdo, 'barcode_stock', 'point', "DECIMAL(12,3) NOT NULL DEFAULT 0");

    ensure_column($pdo, 'barcode_stock', 'gross_weight_gram', "DECIMAL(12,3) NOT NULL DEFAULT 0");
    ensure_column($pdo, 'barcode_stock', 'gross_weight', "DECIMAL(12,3) NOT NULL DEFAULT 0");
    ensure_column($pdo, 'barcode_stock', 'weight', "DECIMAL(12,3) NOT NULL DEFAULT 0");
    ensure_column($pdo, 'barcode_stock', 'weight_gram', "DECIMAL(12,3) NOT NULL DEFAULT 0");
    ensure_column($pdo, 'barcode_stock', 'total_weight_gram', "DECIMAL(12,3) NOT NULL DEFAULT 0");
    ensure_column($pdo, 'barcode_stock', 'net_weight', "DECIMAL(12,3) NOT NULL DEFAULT 0");

    ensure_column($pdo, 'barcode_stock', 'pure_weight_gram', "DECIMAL(12,3) NOT NULL DEFAULT 0");
    ensure_column($pdo, 'barcode_stock', 'pure_weight', "DECIMAL(12,3) NOT NULL DEFAULT 0");

    ensure_column($pdo, 'barcode_stock', 'status', "VARCHAR(30) NOT NULL DEFAULT 'in_stock'");
    ensure_column($pdo, 'barcode_stock', 'note', "TEXT NULL");
    ensure_column($pdo, 'barcode_stock', 'created_by', "INT NULL");
    ensure_column($pdo, 'barcode_stock', 'created_at', "DATETIME NULL");
    ensure_column($pdo, 'barcode_stock', 'updated_at', "DATETIME NULL");
}

function barcode_exists(PDO $pdo, $barcodeNo) {
    $cols = get_columns($pdo, 'barcode_stock');

    foreach (['barcode_no', 'barcode', 'barcode_code'] as $c) {
        if (isset($cols[$c])) {
            $st = $pdo->prepare("SELECT id FROM barcode_stock WHERE `{$c}` = ? LIMIT 1");
            $st->execute([$barcodeNo]);
            if ($st->fetch(PDO::FETCH_ASSOC)) return true;
        }
    }
    return false;
}

function generate_barcode_no(PDO $pdo, $purchaseId, $seq) {
    $prefix = 'BDJ' . date('ymd') . str_pad((string)$purchaseId, 4, '0', STR_PAD_LEFT);

    for ($i = 0; $i < 100; $i++) {
        $no = $prefix . str_pad((string)($seq + $i), 3, '0', STR_PAD_LEFT);
        if (!barcode_exists($pdo, $no)) return $no;
    }

    return $prefix . strtoupper(substr(md5(uniqid('', true)), 0, 6));
}

function weight_sum_expr($cols) {
    $parts = [];
    foreach (['gross_weight_gram','gross_weight','weight','weight_gram','total_weight_gram','net_weight'] as $c) {
        if (isset($cols[$c])) $parts[] = "NULLIF(`{$c}`,0)";
    }
    if (empty($parts)) return "0";
    return "COALESCE(" . implode(',', $parts) . ",0)";
}

$purchase_id = first_int($data, ['purchase_id', 'id'], 0);
$barcode_rows = [];

if (isset($data['barcodes']) && is_array($data['barcodes'])) $barcode_rows = $data['barcodes'];
elseif (isset($data['items']) && is_array($data['items'])) $barcode_rows = $data['items'];
elseif (isset($data['rows']) && is_array($data['rows'])) $barcode_rows = $data['rows'];

if ($purchase_id <= 0) out_json(400, false, 'Purchase ID পাওয়া যায়নি', null);
if (empty($barcode_rows)) out_json(400, false, 'কমপক্ষে ১টি বারকোড আইটেম দিন', null);

try {
    if (!table_exists($pdo, 'purchases')) {
        out_json(500, false, 'purchases table পাওয়া যায়নি', null);
    }

    ensure_barcode_columns($pdo);

    $pCols = get_columns($pdo, 'purchases');
    $purchaseIdCol = pick_col($pCols, ['id', 'purchase_id']);

    if (!$purchaseIdCol) {
        out_json(500, false, 'purchases table ID column পাওয়া যায়নি', null);
    }

    $pStmt = $pdo->prepare("SELECT * FROM purchases WHERE `{$purchaseIdCol}` = ? LIMIT 1");
    $pStmt->execute([$purchase_id]);
    $purchase = $pStmt->fetch(PDO::FETCH_ASSOC);

    if (!$purchase) {
        out_json(404, false, 'ক্রয় এন্ট্রি পাওয়া যায়নি', null);
    }

    $purchase_item_name = trim((string)first_value($purchase, ['item_name','name','product_name','item'], 'আইটেম নাম নেই'));
    $purchase_party_id = first_int($purchase, ['party_id','supplier_id','customer_id'], 0);
    $purchase_location_id = first_int($purchase, ['location_id','stock_location_id'], 0);
    $purchase_karat = trim((string)first_value($purchase, ['karat','purity','system_purity_label'], '22K'));

    $purchase_total_pieces = first_int($purchase, ['total_pieces','pieces','qty','pcs','piece_count','quantity'], 0);

    $purchase_gross = first_float($purchase, ['gross_weight','gross_weight_gram','total_weight_gram','weight_gram','weight','net_weight'], 0);
    if ($purchase_gross <= 0) {
        $purchase_gross = gram_from_parts(
            first_float($purchase, ['vori','bhori'], 0),
            first_float($purchase, ['ana','aana'], 0),
            first_float($purchase, ['roti','rati'], 0),
            first_float($purchase, ['point','points'], 0)
        );
    }

    $purchase_purity = first_float($purchase, [
        'actual_purity','actual_purity_percent','system_purity','system_purity_percent','purity_percent','karat_ratio'
    ], purity_percent($purchase_karat));

    $bCols = get_columns($pdo, 'barcode_stock');
    $wExpr = weight_sum_expr($bCols);

    $existingPieces = 0;
    $existingGross = 0;

    if (isset($bCols['purchase_id'])) {
        $sumSql = "
            SELECT COUNT(*) AS total_pieces, COALESCE(SUM({$wExpr}),0) AS total_gross
            FROM barcode_stock
            WHERE purchase_id = ?
        ";
        if (isset($bCols['status'])) {
            $sumSql .= " AND (`status` IS NULL OR `status` NOT IN ('deleted','cancelled')) ";
        }

        $sumStmt = $pdo->prepare($sumSql);
        $sumStmt->execute([$purchase_id]);
        $sumRow = $sumStmt->fetch(PDO::FETCH_ASSOC);

        if ($sumRow) {
            $existingPieces = (int)$sumRow['total_pieces'];
            $existingGross = (float)$sumRow['total_gross'];
        }
    }

    $prepared = [];
    $newPieces = 0;
    $newGross = 0;

    foreach ($barcode_rows as $idx => $row) {
        if (!is_array($row)) continue;

        $vori  = first_float($row, ['vori','bhori'], 0);
        $ana   = first_float($row, ['ana','aana'], 0);
        $roti  = first_float($row, ['roti','rati'], 0);
        $point = first_float($row, ['point','points'], 0);

        $gross = first_float($row, ['gross_weight_gram','gross_weight','weight','weight_gram','total_weight_gram'], 0);
        if ($gross <= 0) $gross = gram_from_parts($vori, $ana, $roti, $point);

        if ($gross <= 0) {
            out_json(400, false, 'বারকোড আইটেম ' . ($idx + 1) . '-এর সঠিক ওজন দিন', null);
        }

        $itemName = trim((string)first_value($row, ['item_name','name'], $purchase_item_name));
        if ($itemName === '') $itemName = $purchase_item_name;

        $karat = trim((string)first_value($row, ['karat','purity'], $purchase_karat));
        if ($karat === '') $karat = $purchase_karat;

        $purity = first_float($row, ['purity_percent','actual_purity','system_purity'], $purchase_purity);
        if ($purity <= 0) $purity = purity_percent($karat, $purchase_purity);

        $pureWeight = round($gross * ($purity / 100), 3);

        $locationId = first_int($row, ['location_id','stock_location_id'], $purchase_location_id);
        $partyId = first_int($row, ['party_id','supplier_id','customer_id'], $purchase_party_id);
        $partyId = $partyId > 0 ? $partyId : null;

        $prepared[] = [
            'item_name' => $itemName,
            'party_id' => $partyId,
            'karat' => $karat,
            'purity_percent' => $purity,
            'location_id' => $locationId,
            'vori' => $vori,
            'ana' => $ana,
            'roti' => $roti,
            'point' => $point,
            'gross_weight_gram' => round($gross, 3),
            'pure_weight_gram' => $pureWeight,
            'note' => trim((string)first_value($row, ['note','remarks'], ''))
        ];

        $newPieces++;
        $newGross += $gross;
    }

    if ($purchase_total_pieces > 0 && ($existingPieces + $newPieces) > $purchase_total_pieces) {
        out_json(400, false, 'মোট বারকোড পিস ক্রয়ের মোট পিসের বেশি হতে পারবে না', [
            'purchase_pieces' => $purchase_total_pieces,
            'already_barcode_pieces' => $existingPieces,
            'new_pieces' => $newPieces,
            'remaining_pieces' => max(0, $purchase_total_pieces - $existingPieces)
        ]);
    }

    if ($purchase_gross > 0 && ($existingGross + $newGross) > ($purchase_gross + 0.005)) {
        out_json(400, false, 'মোট বারকোড ওজন ক্রয়ের মোট ওজনের বেশি হতে পারবে না', [
            'purchase_gross_weight_gram' => round($purchase_gross, 3),
            'already_barcode_weight_gram' => round($existingGross, 3),
            'new_barcode_weight_gram' => round($newGross, 3),
            'remaining_weight_gram' => round(max(0, $purchase_gross - $existingGross), 3)
        ]);
    }

    $pdo->beginTransaction();

    $created = [];
    $seqStart = $existingPieces + 1;

    foreach ($prepared as $i => $row) {
        $barcodeNo = generate_barcode_no($pdo, $purchase_id, $seqStart + $i);

        $cols = [];
        $marks = [];
        $params = [];

        add_insert($cols, $marks, $params, $bCols, 'barcode_no', $barcodeNo);
        add_insert($cols, $marks, $params, $bCols, 'barcode', $barcodeNo);
        add_insert($cols, $marks, $params, $bCols, 'barcode_code', $barcodeNo);

        add_insert($cols, $marks, $params, $bCols, 'purchase_id', $purchase_id);
        add_insert($cols, $marks, $params, $bCols, 'source_purchase_id', $purchase_id);

        add_insert($cols, $marks, $params, $bCols, 'item_name', $row['item_name']);
        add_insert($cols, $marks, $params, $bCols, 'name', $row['item_name']);
        add_insert($cols, $marks, $params, $bCols, 'product_name', $row['item_name']);

        add_insert($cols, $marks, $params, $bCols, 'party_id', $row['party_id']);
        add_insert($cols, $marks, $params, $bCols, 'supplier_id', $row['party_id']);

        add_insert($cols, $marks, $params, $bCols, 'karat', $row['karat']);
        add_insert($cols, $marks, $params, $bCols, 'purity', $row['karat']);
        add_insert($cols, $marks, $params, $bCols, 'purity_percent', $row['purity_percent']);

        add_insert($cols, $marks, $params, $bCols, 'location_id', $row['location_id']);
        add_insert($cols, $marks, $params, $bCols, 'stock_location_id', $row['location_id']);

        add_insert($cols, $marks, $params, $bCols, 'vori', $row['vori']);
        add_insert($cols, $marks, $params, $bCols, 'bhori', $row['vori']);
        add_insert($cols, $marks, $params, $bCols, 'ana', $row['ana']);
        add_insert($cols, $marks, $params, $bCols, 'roti', $row['roti']);
        add_insert($cols, $marks, $params, $bCols, 'rati', $row['roti']);
        add_insert($cols, $marks, $params, $bCols, 'point', $row['point']);

        add_insert($cols, $marks, $params, $bCols, 'gross_weight_gram', $row['gross_weight_gram']);
        add_insert($cols, $marks, $params, $bCols, 'gross_weight', $row['gross_weight_gram']);
        add_insert($cols, $marks, $params, $bCols, 'weight', $row['gross_weight_gram']);
        add_insert($cols, $marks, $params, $bCols, 'weight_gram', $row['gross_weight_gram']);
        add_insert($cols, $marks, $params, $bCols, 'total_weight_gram', $row['gross_weight_gram']);
        add_insert($cols, $marks, $params, $bCols, 'net_weight', $row['gross_weight_gram']);

        add_insert($cols, $marks, $params, $bCols, 'pure_weight_gram', $row['pure_weight_gram']);
        add_insert($cols, $marks, $params, $bCols, 'pure_weight', $row['pure_weight_gram']);

        add_insert($cols, $marks, $params, $bCols, 'status', 'in_stock');
        add_insert($cols, $marks, $params, $bCols, 'note', $row['note']);
        add_insert($cols, $marks, $params, $bCols, 'remarks', $row['note']);

        add_insert($cols, $marks, $params, $bCols, 'created_by', $_SESSION['user_id'] ?? null);
        add_insert($cols, $marks, $params, $bCols, 'created_at', date('Y-m-d H:i:s'));
        add_insert($cols, $marks, $params, $bCols, 'updated_at', date('Y-m-d H:i:s'));

        $sql = "INSERT INTO barcode_stock (" . implode(',', $cols) . ") VALUES (" . implode(',', $marks) . ")";
        $st = $pdo->prepare($sql);
        $st->execute($params);

        $created[] = [
            'id' => (int)$pdo->lastInsertId(),
            'barcode_no' => $barcodeNo,
            'item_name' => $row['item_name'],
            'karat' => $row['karat'],
            'vori' => $row['vori'],
            'ana' => $row['ana'],
            'roti' => $row['roti'],
            'point' => $row['point'],
            'gross_weight_gram' => $row['gross_weight_gram'],
            'pure_weight_gram' => $row['pure_weight_gram'],
            'status' => 'in_stock'
        ];
    }

    $afterPieces = $existingPieces + $newPieces;
    $afterGross = $existingGross + $newGross;

    $updateCols = [];
    $updateParams = [];

    foreach (['barcode_created_pieces','barcode_done','barcode_pieces','created_barcode'] as $c) {
        if (isset($pCols[$c])) {
            $updateCols[] = "`{$c}` = ?";
            $updateParams[] = $afterPieces;
        }
    }

    foreach (['barcode_created_weight','barcode_weight_gram'] as $c) {
        if (isset($pCols[$c])) {
            $updateCols[] = "`{$c}` = ?";
            $updateParams[] = round($afterGross, 3);
        }
    }

    foreach (['remaining_pieces','barcode_remaining','pending_pieces'] as $c) {
        if (isset($pCols[$c])) {
            $updateCols[] = "`{$c}` = ?";
            $updateParams[] = max(0, $purchase_total_pieces - $afterPieces);
        }
    }

    if (!empty($updateCols)) {
        $updateParams[] = $purchase_id;
        $upSql = "UPDATE purchases SET " . implode(', ', $updateCols) . " WHERE `{$purchaseIdCol}` = ?";
        $upStmt = $pdo->prepare($upSql);
        $upStmt->execute($updateParams);
    }

    $pdo->commit();

    out_json(200, true, 'বারকোড সফলভাবে তৈরি হয়েছে', [
        'purchase_id' => $purchase_id,
        'created_count' => count($created),
        'created_barcodes' => $created,
        'purchase_total_pieces' => $purchase_total_pieces,
        'barcode_pieces_after' => $afterPieces,
        'remaining_pieces' => max(0, $purchase_total_pieces - $afterPieces),
        'barcode_weight_after_gram' => round($afterGross, 3),
        'remaining_weight_gram' => round(max(0, $purchase_gross - $afterGross), 3)
    ]);

} catch (Throwable $e) {
    if ($pdo instanceof PDO && $pdo->inTransaction()) $pdo->rollBack();
    out_json(500, false, 'সার্ভার সমস্যা: ' . $e->getMessage(), null);
}