*/ public const ACTIONS = [ 'pay','payMultiple','storno','updateMember','saveExpense','deleteExpense', 'saveTransfer','saveExtraIncome','deleteExtraIncome','saveReceiptPdf', ]; /** @var list */ private const REQUIRED_TABLES = [ 'app_settings','number_sequences','mahallas','board_members','app_users','api_sessions', 'google_sheet_documents','google_sheet_tabs','households','membership_rates','household_years', 'payments','payment_items','active_payment_links','receipts','expenses','transfers','extra_income', 'board_adjustments','idempotency_requests','sheet_row_links','sync_queue','sync_conflicts','audit_log', ]; private PDO $pdo; public function __construct(?PDO $pdo = null) { $this->pdo = $pdo ?? Database::connection(); } /** @return array */ public function status(): array { $schema = $this->schemaStatus(); $database = $this->databaseStatus(); $documents = $this->documentInventory(); $references = $this->referenceStatus(); $residue = $this->a6R1Residue(); $checks = [ 'schemaReady' => ($schema['valid'] ?? false) === true, 'databaseReady' => ($database['valid'] ?? false) === true, 'documentInventoryReady' => ($documents['valid'] ?? false) === true, 'referencesReady' => ($references['valid'] ?? false) === true, 'noA6R1Residue' => array_sum($residue) === 0, ]; $valid = !in_array(false, $checks, true); $fingerprintPayload = [ 'schema' => $schema, 'database' => $database, 'documents' => $documents, 'references' => $references, 'residue' => $residue, ]; return [ 'phase' => self::PHASE, 'version' => self::VERSION, 'mode' => self::MODE, 'valid' => $valid, 'ready' => $valid, 'writesPerformed' => false, 'productionBusinessTablesWritten' => false, 'mysqlBusinessWrites' => 0, 'googleSheetsCalls' => 0, 'googleSheetsWrites' => 0, 'actionContracts' => self::ACTIONS, 'actionCount' => count(self::ACTIONS), 'checks' => $checks, 'schema' => $schema, 'database' => $database, 'documents' => $documents, 'references' => $references, 'a6R1Residue' => $residue, 'stateFingerprint' => hash('sha256', self::json($fingerprintPayload)), 'checkedAtUtc' => gmdate('c'), ]; } /** @return array */ public function preparePrivateManifest( string $privateRoot, string $documentRoot, int $userId, string $expectedFingerprint, array $fileGate, array $appsScriptHealth ): array { if ($userId <= 0) throw new ApiException('USER_INVALID', 'Korisnik za A6-R2 nije ispravan.', 401); $status = $this->status(); if (($status['valid'] ?? false) !== true) { throw new ApiException('A6_R2_DATABASE_GATE_NOT_READY', 'A6-R2 stanje baze nije spremno.', 409, ['databaseStatus' => $status]); } if (!hash_equals($expectedFingerprint, (string) ($status['stateFingerprint'] ?? ''))) { throw new ApiException('STATE_CHANGED', 'Stanje baze se promenilo od preview-a. Ponovite preview.', 409); } if (($fileGate['valid'] ?? false) !== true) { throw new ApiException('FILE_GATE_CHANGED', 'Zaštićeni fajlovi više nisu u očekivanom stanju.', 409, ['fileGate' => $fileGate]); } if (($appsScriptHealth['reachable'] ?? false) !== true || ($appsScriptHealth['ok'] ?? false) !== true) { throw new ApiException('APPS_SCRIPT_NOT_REACHABLE', 'Postojeći Apps Script deployment nije dostupan za read-only proveru.', 409, ['appsScriptHealth' => $appsScriptHealth]); } $id = 'A6R2-' . gmdate('Ymd-His') . '-' . substr((string) $status['stateFingerprint'], 0, 12); $relative = 'backups/phase7c-a6-r2/' . $id; $dir = rtrim($privateRoot, '/\\') . '/' . $relative; if (!is_dir($dir) && !mkdir($dir, 0700, true) && !is_dir($dir)) { throw new ApiException('PRIVATE_MANIFEST_DIR_FAILED', 'Privatni A6-R2 folder nije mogao biti kreiran.', 500); } @chmod($dir, 0700); $target = $dir . '/activation-preflight.json'; $payload = [ 'phase' => self::PHASE, 'version' => self::VERSION, 'mode' => self::MODE, 'manifestId' => $id, 'createdAtUtc' => gmdate('c'), 'createdByUserId' => $userId, 'documentRoot' => $documentRoot, 'stateFingerprint' => $status['stateFingerprint'], 'protectedFiles' => $fileGate, 'databaseStatus' => $status, 'appsScriptHealth' => $appsScriptHealth, 'activationScope' => [ 'productionIndex' => 'NOT_CHANGED_BY_A6_R2', 'productionClient' => 'NOT_CHANGED_BY_A6_R2', 'businessWriteRoutes' => 'NOT_ACTIVATED_BY_A6_R2', 'mysqlBusinessData' => 'NOT_CHANGED_BY_A6_R2', 'googleSheets' => 'READ_ONLY_HEALTH_CALL_ONLY', 'nextPackage' => '7C-A6-R2-R1_FINAL_BACKEND_ACTIVATION_AND_CLIENT_CUTOVER', ], ]; self::atomicWrite($target, self::json($payload) . "\n"); @chmod($target, 0600); $actualHash = (string) hash_file('sha256', $target); $readBack = json_decode((string) file_get_contents($target), true, 512, JSON_THROW_ON_ERROR); $checks = [ 'privateManifestWritten' => is_file($target), 'manifestReadable' => is_array($readBack), 'fingerprintPreserved' => is_array($readBack) && hash_equals((string) $status['stateFingerprint'], (string) ($readBack['stateFingerprint'] ?? '')), 'productionFilesUntouched' => true, 'mysqlBusinessDataUntouched' => true, 'googleSheetsWritesZero' => true, ]; $valid = !in_array(false, $checks, true); return [ 'phase' => self::PHASE, 'version' => self::VERSION, 'mode' => 'PRIVATE_ACTIVATION_MANIFEST_PREPARATION', 'valid' => $valid, 'writesPerformed' => true, 'writeType' => 'PRIVATE_FILE_ONLY', 'publicFilesChanged' => false, 'productionApplicationChanged' => false, 'productionBusinessTablesWritten' => false, 'mysqlBusinessWrites' => 0, 'googleSheetsCalls' => 1, 'googleSheetsWrites' => 0, 'manifest' => [ 'id' => $id, 'privateRelativePath' => $relative, 'file' => 'activation-preflight.json', 'sha256' => $actualHash, 'sizeBytes' => filesize($target), 'checks' => $checks, ], 'databaseStatus' => $status, 'appsScriptHealth' => $appsScriptHealth, 'readyForNextPhase' => $valid, 'nextStep' => $valid ? 'READY_FOR_7C_A6_R2_R1_FINAL_BACKEND_ACTIVATION_AND_CLIENT_CUTOVER' : 'STOP_AND_REVIEW_A6_R2_MANIFEST', 'importantNote' => 'A6-R2 nije promenio evidencija.html, index.php, poslovne MySQL redove niti Google Sheets. Pripremljen je privatni autoritativni manifest za završnu aktivaciju.', 'checkedAtUtc' => gmdate('c'), ]; } /** @return array */ private function schemaStatus(): array { $stmt = $this->pdo->query("SELECT TABLE_NAME AS table_name,ENGINE AS engine FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE()"); $engines = []; foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) { $engines[(string) $row['table_name']] = strtoupper((string) $row['engine']); } $missing = []; $nonInno = []; foreach (self::REQUIRED_TABLES as $table) { if (!isset($engines[$table])) $missing[] = $table; elseif ($engines[$table] !== 'INNODB') $nonInno[$table] = $engines[$table]; } $requiredColumns = [ 'payments' => ['id','payment_uuid','request_id','household_id','receipt_number','receipt_year','cash_year','payment_type','collector_id','membership_total','donation_total','payment_status','receipt_mode','row_version'], 'payment_items' => ['id','payment_id','household_year_id','membership_amount','donation_amount','item_status','row_version'], 'receipts' => ['id','payment_id','receipt_sheet_year','receipt_sheet_row','pdf_sha256','receipt_status','row_version'], 'household_years' => ['id','household_id','year','first_name','last_name','mahalla_id','members_count','due_amount','record_status','row_version'], 'idempotency_requests' => ['request_id','action_name','request_hash','response_body','request_status','expires_at'], 'sync_queue' => ['id','direction','entity_type','entity_id','operation_name','dedupe_key','payload_json','queue_status','attempts'], 'google_sheet_documents' => ['id','year','spreadsheet_id','document_title','is_primary','is_active'], 'google_sheet_tabs' => ['id','document_id','sheet_name','tab_role','is_active'], 'sheet_row_links' => ['id','entity_type','entity_id','sheet_tab_id','sheet_row','db_version','sheet_version'], ]; $colStmt = $this->pdo->query("SELECT TABLE_NAME AS table_name,COLUMN_NAME AS column_name,COLUMN_TYPE AS column_type,IS_NULLABLE AS is_nullable FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()"); $columns = []; $meta = []; foreach ($colStmt->fetchAll(PDO::FETCH_ASSOC) as $row) { $table = (string) $row['table_name']; $column = (string) $row['column_name']; $columns[$table][$column] = true; $meta[$table][$column] = ['type' => (string) $row['column_type'], 'nullable' => (string) $row['is_nullable']]; } $missingColumns = []; foreach ($requiredColumns as $table => $list) foreach ($list as $column) if (!isset($columns[$table][$column])) $missingColumns[] = $table . '.' . $column; $checks = [ 'allRequiredTablesPresent' => $missing === [], 'allRequiredTablesInnoDB' => $nonInno === [], 'allRequiredColumnsPresent' => $missingColumns === [], 'receiptModeElectronicSupported' => str_contains(strtoupper((string) ($meta['payments']['receipt_mode']['type'] ?? '')), 'ELECTRONIC'), 'receiptSheetRowNullable' => (string) ($meta['receipts']['receipt_sheet_row']['nullable'] ?? '') === 'YES', ]; return [ 'valid' => !in_array(false, $checks, true), 'checks' => $checks, 'requiredTableCount' => count(self::REQUIRED_TABLES), 'missingTables' => $missing, 'nonInnoDbTables' => $nonInno, 'missingColumns' => $missingColumns, 'engines' => array_intersect_key($engines, array_flip(self::REQUIRED_TABLES)), 'receiptModeColumnType' => (string) ($meta['payments']['receipt_mode']['type'] ?? ''), ]; } /** @return array */ private function databaseStatus(): array { $settings = []; foreach ($this->pdo->query("SELECT setting_key,setting_value FROM app_settings WHERE setting_key IN ('financial_migration_status','financial_migration_locked','phase_7a_status','schema_version')")->fetchAll(PDO::FETCH_ASSOC) as $row) { $settings[(string) $row['setting_key']] = (string) $row['setting_value']; } $sequence = (int) $this->pdo->query("SELECT current_value FROM number_sequences WHERE BINARY sequence_name=BINARY 'receipt'")->fetchColumn(); $maxReceipt = (int) $this->pdo->query("SELECT COALESCE(MAX(receipt_number),0) FROM payments WHERE receipt_number IS NOT NULL AND receipt_number>0")->fetchColumn(); $maxActive = (int) $this->pdo->query("SELECT COALESCE(MAX(receipt_number),0) FROM payments WHERE payment_status='ACTIVE' AND receipt_number IS NOT NULL AND receipt_number>0")->fetchColumn(); $pending = (int) $this->pdo->query("SELECT COUNT(*) FROM sync_queue WHERE queue_status IN ('PENDING','PROCESSING','FAILED','CONFLICT')")->fetchColumn(); $conflicts = (int) $this->pdo->query("SELECT COUNT(*) FROM sync_conflicts WHERE conflict_status='OPEN'")->fetchColumn(); $stuck = (int) $this->pdo->query("SELECT COUNT(*) FROM idempotency_requests WHERE request_status='PROCESSING' AND updated_atfetchColumn(); $dupes = (int) $this->pdo->query("SELECT COUNT(*) FROM (SELECT receipt_number FROM payments WHERE payment_status='ACTIVE' AND receipt_number IS NOT NULL AND receipt_number>0 GROUP BY receipt_number HAVING COUNT(*)>1)x")->fetchColumn(); $canaryStmt = $this->pdo->query("SELECT p.id AS payment_id,p.payment_status,p.origin,p.receipt_number,p.membership_total,p.donation_total,p.receipt_mode,r.id AS receipt_id,r.receipt_status,r.receipt_sheet_row,COUNT(DISTINCT apl.household_year_id) AS active_links FROM payments p JOIN receipts r ON r.payment_id=p.id LEFT JOIN payment_items pi ON pi.payment_id=p.id LEFT JOIN active_payment_links apl ON apl.payment_item_id=pi.id WHERE p.receipt_number=104 GROUP BY p.id,p.payment_status,p.origin,p.receipt_number,p.membership_total,p.donation_total,p.receipt_mode,r.id,r.receipt_status,r.receipt_sheet_row"); $rows = $canaryStmt->fetchAll(PDO::FETCH_ASSOC); $canary = $rows[0] ?? null; $canaryChecks = [ 'exactlyOneReceipt104' => count($rows) === 1, 'paymentActive' => is_array($canary) && (string) $canary['payment_status'] === 'ACTIVE', 'originApp' => is_array($canary) && (string) $canary['origin'] === 'APP', 'receiptElectronic' => is_array($canary) && strtoupper((string) $canary['receipt_mode']) === 'ELECTRONIC', 'membership30' => is_array($canary) && number_format((float) $canary['membership_total'], 2, '.', '') === '30.00', 'donationZero' => is_array($canary) && number_format((float) $canary['donation_total'], 2, '.', '') === '0.00', 'receiptActive' => is_array($canary) && (string) $canary['receipt_status'] === 'ACTIVE', 'receiptSheetRow91' => is_array($canary) && (int) $canary['receipt_sheet_row'] === 91, 'oneActiveLink' => is_array($canary) && (int) $canary['active_links'] === 1, ]; $checks = [ 'migrationCompleted' => ($settings['financial_migration_status'] ?? '') === 'COMPLETED', 'migrationLocked' => ($settings['financial_migration_locked'] ?? '') === '1', 'shadowReadReady' => ($settings['phase_7a_status'] ?? '') === 'SHADOW_READ_READY', 'sequenceIs104' => $sequence === 104, 'maxReceiptIs104' => $maxReceipt === 104, 'maxActiveReceiptIs104' => $maxActive === 104, 'noPendingSync' => $pending === 0, 'noOpenConflicts' => $conflicts === 0, 'noStuckIdempotency' => $stuck === 0, 'noDuplicateActiveReceipts' => $dupes === 0, 'canaryHealthy' => !in_array(false, $canaryChecks, true), ]; return [ 'valid' => !in_array(false, $checks, true), 'checks' => $checks, 'settings' => $settings, 'sequenceValue' => $sequence, 'maxReceiptNumber' => $maxReceipt, 'maxActiveReceiptNumber' => $maxActive, 'pendingSync' => $pending, 'openConflicts' => $conflicts, 'stuckIdempotency' => $stuck, 'duplicateActiveReceipts' => $dupes, 'canary' => ['checks' => $canaryChecks, 'rowCount' => count($rows), 'paymentId' => is_array($canary) ? (int) $canary['payment_id'] : null, 'receiptId' => is_array($canary) ? (int) $canary['receipt_id'] : null, 'receiptNumber' => '00104'], ]; } /** @return array */ private function documentInventory(): array { $documents = $this->pdo->query("SELECT id,year,spreadsheet_id,document_title,is_primary,is_active FROM google_sheet_documents ORDER BY year,id")->fetchAll(PDO::FETCH_ASSOC); $tabs = $this->pdo->query("SELECT id,document_id,sheet_name,tab_role,is_active FROM google_sheet_tabs ORDER BY document_id,id")->fetchAll(PDO::FETCH_ASSOC); $safeDocs = []; $primaryByYear = []; foreach ($documents as $row) { $doc = ['id'=>(int)$row['id'],'year'=>(int)$row['year'],'spreadsheetId'=>(string)$row['spreadsheet_id'],'documentTitle'=>(string)$row['document_title'],'isPrimary'=>(int)$row['is_primary']===1,'isActive'=>(int)$row['is_active']===1]; $safeDocs[] = $doc; if ($doc['isPrimary'] && $doc['isActive']) $primaryByYear[$doc['year']][] = $doc; } $safeTabs = []; foreach ($tabs as $row) $safeTabs[] = ['id'=>(int)$row['id'],'documentId'=>(int)$row['document_id'],'sheetName'=>(string)$row['sheet_name'],'tabRole'=>(string)$row['tab_role'],'isActive'=>(int)$row['is_active']===1]; $years = range(2020, 2026); $onePrimaryEach = true; foreach ($years as $year) if (count($primaryByYear[$year] ?? []) !== 1) $onePrimaryEach = false; $main2026 = ($primaryByYear[2026][0] ?? null); $annualYears = []; $receiptYears = []; $docYearById = []; foreach ($safeDocs as $doc) if ($doc['isPrimary'] && $doc['isActive']) $docYearById[$doc['id']] = $doc['year']; foreach ($safeTabs as $tab) { if (!$tab['isActive'] || !isset($docYearById[$tab['documentId']])) continue; $year = $docYearById[$tab['documentId']]; if (strtoupper($tab['tabRole']) === 'ANNUAL') $annualYears[$year] = true; if (strtoupper($tab['tabRole']) === 'RECEIPTS' || strtoupper($tab['sheetName']) === 'PRIZNANICE') $receiptYears[$year] = true; } $checks = [ 'allSevenYearsHaveOneActivePrimaryDocument' => $onePrimaryEach, 'allSevenYearsHaveAnnualTab' => count(array_intersect($years, array_map('intval', array_keys($annualYears)))) === 7, 'allSevenYearsHaveReceiptTab' => count(array_intersect($years, array_map('intval', array_keys($receiptYears)))) === 7, 'main2026DocumentExact' => is_array($main2026) && hash_equals(self::EXPECTED_MAIN_2026_DOCUMENT_ID, (string) $main2026['spreadsheetId']), 'main2026TitleExact' => is_array($main2026) && (string) $main2026['documentTitle'] === 'ODBOR - 2026', ]; return [ 'valid' => !in_array(false, $checks, true), 'checks' => $checks, 'expectedMain2026SpreadsheetId' => self::EXPECTED_MAIN_2026_DOCUMENT_ID, 'main2026' => $main2026, 'primaryDocumentsByYear' => $primaryByYear, 'documents' => $safeDocs, 'tabs' => $safeTabs, ]; } /** @return array */ private function referenceStatus(): array { $activeUsers = (int) $this->pdo->query("SELECT COUNT(*) FROM app_users WHERE is_active=1 AND can_use_app=1")->fetchColumn(); $moneyMembers = (int) $this->pdo->query("SELECT COUNT(*) FROM board_members WHERE is_active=1 AND handles_money=1")->fetchColumn(); $mahallas = (int) $this->pdo->query("SELECT COUNT(*) FROM mahallas WHERE is_active=1")->fetchColumn(); $rates = (int) $this->pdo->query("SELECT COUNT(*) FROM membership_rates WHERE year IN (2024,2025,2026) AND is_active=1")->fetchColumn(); $activeHouseholdYears = (int) $this->pdo->query("SELECT COUNT(*) FROM household_years WHERE year BETWEEN 2020 AND 2026 AND record_status='ACTIVE'")->fetchColumn(); $unlinkedHouseholdYears = (int) $this->pdo->query("SELECT COUNT(*) FROM household_years hy LEFT JOIN sheet_row_links l ON l.entity_type='HOUSEHOLD_YEAR' AND l.entity_id=hy.id WHERE hy.year BETWEEN 2020 AND 2026 AND hy.record_status='ACTIVE' AND l.id IS NULL")->fetchColumn(); $linkCounts = []; foreach ($this->pdo->query("SELECT entity_type,COUNT(*) AS total FROM sheet_row_links GROUP BY entity_type ORDER BY entity_type")->fetchAll(PDO::FETCH_ASSOC) as $row) $linkCounts[(string)$row['entity_type']] = (int)$row['total']; $checks = [ 'activeApplicationUserPresent' => $activeUsers >= 1, 'moneyBoardMembersPresent' => $moneyMembers >= 2, 'mahallasPresent' => $mahallas >= 1, 'rates2024To2026Present' => $rates === 3, 'activeHouseholdYearsPresent' => $activeHouseholdYears > 0, 'allActiveHouseholdYearsLinked' => $unlinkedHouseholdYears === 0, 'sheetRowLinksPresent' => array_sum($linkCounts) > 0, ]; return [ 'valid' => !in_array(false, $checks, true), 'checks' => $checks, 'activeUsers' => $activeUsers, 'moneyBoardMembers' => $moneyMembers, 'activeMahallas' => $mahallas, 'activeMembershipRates2024To2026' => $rates, 'activeHouseholdYears2020To2026' => $activeHouseholdYears, 'unlinkedActiveHouseholdYears' => $unlinkedHouseholdYears, 'sheetRowLinkCounts' => $linkCounts, ]; } /** @return array */ private function a6R1Residue(): array { $queries = [ 'payments' => "SELECT COUNT(*) FROM payments WHERE request_id LIKE 'PHASE7C-A6-R1-%'", 'expenses' => "SELECT COUNT(*) FROM expenses WHERE request_id LIKE 'PHASE7C-A6-R1-%'", 'transfers' => "SELECT COUNT(*) FROM transfers WHERE request_id LIKE 'PHASE7C-A6-R1-%'", 'extraIncome' => "SELECT COUNT(*) FROM extra_income WHERE request_id LIKE 'PHASE7C-A6-R1-%'", 'idempotency' => "SELECT COUNT(*) FROM idempotency_requests WHERE request_id LIKE 'PHASE7C-A6-R1-%'", 'syncQueue' => "SELECT COUNT(*) FROM sync_queue WHERE dedupe_key LIKE 'A6R1:%'", 'audit' => "SELECT COUNT(*) FROM audit_log WHERE request_id LIKE 'PHASE7C-A6-R1-%'", ]; $result = []; foreach ($queries as $name => $sql) $result[$name] = (int) $this->pdo->query($sql)->fetchColumn(); return $result; } private static function json(mixed $value): string { return json_encode($value, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES|JSON_PRESERVE_ZERO_FRACTION|JSON_THROW_ON_ERROR); } private static function atomicWrite(string $target, string $content): void { $tmp = $target . '.tmp-' . bin2hex(random_bytes(6)); if (file_put_contents($tmp, $content, LOCK_EX) === false) throw new ApiException('PRIVATE_MANIFEST_WRITE_FAILED', 'Privatni manifest nije mogao biti zapisan.', 500); @chmod($tmp, 0600); if (!@rename($tmp, $target)) { @unlink($tmp); throw new ApiException('PRIVATE_MANIFEST_RENAME_FAILED', 'Privatni manifest nije mogao biti atomski postavljen.', 500); } } }