-- ============================================================
-- PROGETTO DEMO COMPLETO — Full flow showcase con download ZIP
-- ============================================================
INSERT INTO projects (
  id, name, description, status,
  repo_url, source_language, target_language,
  created_at, updated_at, completed_at,
  total_files, total_lines, converted_files,
  accuracy, test_coverage, risk_score,
  estimated_time, elapsed_time,
  team, tags, activity,
  max_reached_step, current_step,
  config,
  user_id
) VALUES (
  'proj-demo-full',
  'SECB Core Banking — Full Demo',
  'Complete end-to-end migration of SECB COBOL-85 banking system to Java Spring Boot 3.2. Includes pre-analysis, translation, verification, artifacts, and downloadable ZIP.',
  'completed',
  'https://github.com/scriba-ai-dev/test-cobol',
  'COBOL-85',
  'Java 22',
  NOW() - INTERVAL '2 days',
  NOW() - INTERVAL '30 minutes',
  NOW() - INTERVAL '30 minutes',
  15, 4820, 18,
  96.8, 91.4, 18.5,
  '45 min', '38 min 12s',
  '[
    {"name":"Michele Laurelli","role":"Project Lead","avatar":"ML"},
    {"name":"Marco Bianchi","role":"Backend Engineer","avatar":"MB"},
    {"name":"Sara Conti","role":"QA Engineer","avatar":"SC"}
  ]'::jsonb,
  '["banking","cobol","java","spring-boot","mainframe","demo"]'::jsonb,
  '[
    {"id":"act-20","timestamp":"2026-04-11T23:30:00Z","action":"ZIP package ready for download","user":"Scriba Engine"},
    {"id":"act-19","timestamp":"2026-04-11T23:28:00Z","action":"Migration completed — 96.8% parity score","user":"Scriba Engine"},
    {"id":"act-18","timestamp":"2026-04-11T23:27:00Z","action":"Final verification passed — 8/8 quality gates","user":"Scriba Engine"},
    {"id":"act-17","timestamp":"2026-04-11T23:25:00Z","action":"Security scan: 0 critical, score 97.8/100","user":"Scriba Engine"},
    {"id":"act-16","timestamp":"2026-04-11T23:22:00Z","action":"147 unit tests + 42 integration tests generated","user":"Scriba Engine"},
    {"id":"act-15","timestamp":"2026-04-11T23:15:00Z","action":"Translation: 6 COBOL → 18 Java classes","user":"Scriba Engine"},
    {"id":"act-14","timestamp":"2026-04-11T23:05:00Z","action":"Analysis: 15 files, 4820 LOC, avg complexity 14.2","user":"Scriba Engine"},
    {"id":"act-13","timestamp":"2026-04-11T23:00:00Z","action":"Migration started","user":"Michele Laurelli"},
    {"id":"act-12","timestamp":"2026-04-11T22:55:00Z","action":"Pre-analysis completed","user":"Scriba Engine"},
    {"id":"act-11","timestamp":"2026-04-11T22:50:00Z","action":"Repository connected","user":"Michele Laurelli"},
    {"id":"act-10","timestamp":"2026-04-09T14:00:00Z","action":"Project created","user":"Michele Laurelli"}
  ]'::jsonb,
  7, 7,
  '{
    "branch": "main",
    "sourceVersion": "COBOL-85",
    "targetVersion": "Java 22",
    "architecturePattern": "layered",
    "translationStrategy": "semantic",
    "selectedDatabase": "postgresql",
    "testingFramework": "junit5",
    "selectedPriority": "high",
    "riskLevel": "medium",
    "selectedCompliance": ["gdpr","sox","pci"],
    "deadline": "2026-04-20",
    "projectOwner": "Algoretico",
    "businessUnit": "Core Banking",
    "confidenceThreshold": 95,
    "enableSecurityScan": true,
    "enableHealthChecks": true,
    "preserveBusinessLogic": true,
    "preserveComments": true,
    "namingConvention": "camelCase",
    "preAnalysis": {
      "scanResults": {
        "totalFiles": 15,
        "totalSourceFiles": 6,
        "totalDirectories": 5,
        "totalSize": 187400,
        "totalLines": 4820,
        "totalCodeLines": 3640,
        "totalCommentLines": 780,
        "totalBlankLines": 400,
        "languages": [
          {"name": "COBOL", "bytes": 135648, "files": 6, "percentage": 72.4},
          {"name": "JCL", "bytes": 29584, "files": 3, "percentage": 15.8},
          {"name": "Copybook", "bytes": 15355, "files": 3, "percentage": 8.2},
          {"name": "Text", "bytes": 6813, "files": 3, "percentage": 3.6}
        ],
        "extensions": [
          {"ext": ".cbl", "count": 6, "percentage": 40.0},
          {"ext": ".cpy", "count": 3, "percentage": 20.0},
          {"ext": ".jcl", "count": 3, "percentage": 20.0},
          {"ext": ".txt", "count": 3, "percentage": 20.0}
        ]
      },
      "complexity": {
        "avgCyclomatic": 14.2,
        "maxCyclomatic": 28,
        "avgNesting": 3.8,
        "maxNesting": 7,
        "totalProcedures": 42,
        "totalSections": 18,
        "avgLines": 607,
        "maxLines": 1240
      },
      "dependencies": {
        "total": 24,
        "copybooks": 3,
        "externalCalls": 8,
        "circularDeps": 0,
        "orphanFiles": 0,
        "list": ["ACCTCOMN.cpy", "ACCTMASTR.cpy", "MOVRECORD.cpy", "ACCTBAL", "ACCTOPEN", "ACCTCLSE", "ACCTPROC", "ACCTREPT"]
      },
      "risk": {
        "high": 1,
        "medium": 3,
        "low": 2,
        "files": [
          {"name": "ACCTPROC.cbl", "path": "src/ACCTPROC.cbl", "lines": 1240, "codeLines": 980, "complexity": 28, "nesting": 7, "risk": "high", "dependencies": 5, "procedures": 12, "size": 42800},
          {"name": "ACCTMAIN.cbl", "path": "src/ACCTMAIN.cbl", "lines": 890, "codeLines": 680, "complexity": 22, "nesting": 5, "risk": "medium", "dependencies": 6, "procedures": 8, "size": 31200},
          {"name": "ACCTREPT.cbl", "path": "src/ACCTREPT.cbl", "lines": 720, "codeLines": 540, "complexity": 16, "nesting": 4, "risk": "medium", "dependencies": 3, "procedures": 6, "size": 25100},
          {"name": "ACCTBAL.cbl", "path": "src/ACCTBAL.cbl", "lines": 650, "codeLines": 490, "complexity": 14, "nesting": 4, "risk": "medium", "dependencies": 2, "procedures": 5, "size": 22400},
          {"name": "ACCTOPEN.cbl", "path": "src/ACCTOPEN.cbl", "lines": 480, "codeLines": 360, "complexity": 8, "nesting": 3, "risk": "low", "dependencies": 2, "procedures": 4, "size": 16700},
          {"name": "ACCTCLSE.cbl", "path": "src/ACCTCLSE.cbl", "lines": 380, "codeLines": 290, "complexity": 6, "nesting": 2, "risk": "low", "dependencies": 1, "procedures": 3, "size": 13200}
        ]
      },
      "analyzedFileCount": 15
    },
    "conversionResult": {
      "status": "success",
      "conversionId": "cv-demo-full-001",
      "metadata": {
        "accuracy": 96.8,
        "linesProcessed": 4820,
        "conversionTime": 2292.4,
        "iterations": 2
      }
    },
    "analysisResults": {
      "translation": {
        "sourceCode": "      ******************************************************************\n      * PROGRAMMA:    ACCTMAIN                                         *\n      * DESCRIZIONE:  Programma principale di controllo flusso per il   *\n      *               Sistema Elaborazione Conti Bancari (SECB).       *\n      ******************************************************************\n       IDENTIFICATION DIVISION.\n       PROGRAM-ID. ACCTMAIN.\n       AUTHOR. ROSSI-MARIO.\n\n       ENVIRONMENT DIVISION.\n       INPUT-OUTPUT SECTION.\n       FILE-CONTROL.\n           SELECT LOG-FILE\n               ASSIGN TO UT-S-LOGFILE\n               FILE STATUS IS WS-LOG-STATUS.\n\n       DATA DIVISION.\n       WORKING-STORAGE SECTION.\n       01  WS-GENERALI.\n           05  WS-PROGRAMMA       PIC X(8) VALUE ''ACCTMAIN''.\n           05  WS-VERSIONE        PIC X(6) VALUE ''V2.3.1''.\n           05  WS-RC-PRINCIPALE   PIC 9(4) COMP VALUE 0.\n       01  WS-CONTATORI.\n           05  WS-CONTI-ELAB      PIC 9(8) COMP VALUE 0.\n           05  WS-CONTI-OK        PIC 9(8) COMP VALUE 0.\n           05  WS-CONTI-ERR       PIC 9(8) COMP VALUE 0.\n\n       PROCEDURE DIVISION.\n       MAIN-PROGRAM.\n           PERFORM INIZIALIZZA\n           PERFORM ELABORA-GIORNALIERA\n           PERFORM CHIUDI-ELABORAZIONE\n           STOP RUN.\n\n       INIZIALIZZA.\n           OPEN OUTPUT LOG-FILE\n           DISPLAY ''SECB V2.3.1 - Avvio elaborazione''.\n\n       ELABORA-GIORNALIERA.\n           CALL ''ACCTBAL''  USING WS-RC-PRINCIPALE\n           CALL ''ACCTOPEN'' USING WS-RC-PRINCIPALE\n           CALL ''ACCTPROC'' USING WS-RC-PRINCIPALE\n           CALL ''ACCTREPT'' USING WS-RC-PRINCIPALE.\n\n       CHIUDI-ELABORAZIONE.\n           CLOSE LOG-FILE\n           DISPLAY ''Elaborazione completata''.",
        "targetCode": "package com.secb;\n\nimport lombok.RequiredArgsConstructor;\nimport lombok.extern.slf4j.Slf4j;\nimport org.springframework.boot.CommandLineRunner;\nimport org.springframework.stereotype.Service;\n\n/**\n * ACCTMAIN — Main processing controller for SECB Banking System.\n * Manages the daily processing cycle.\n * @version 2.3.1\n * @author Converted by Scriba Engine\n */\n@Slf4j\n@Service\n@RequiredArgsConstructor\npublic class AccountMainService implements CommandLineRunner {\n\n    private final AccountBalanceService accountBalanceService;\n    private final AccountOpenService accountOpenService;\n    private final AccountProcessingService accountProcessingService;\n    private final AccountReportService accountReportService;\n\n    private int processedCount = 0;\n    private int successCount = 0;\n    private int errorCount = 0;\n\n    @Override\n    public void run(String... args) {\n        log.info(\"SECB V2.3.1 — Starting daily processing\");\n        initialize();\n        executeDailyProcessing();\n        closeProcessing();\n    }\n\n    private void initialize() {\n        log.info(\"Initializing SECB processing engine\");\n    }\n\n    private void executeDailyProcessing() {\n        accountBalanceService.process();\n        accountOpenService.process();\n        accountProcessingService.process();\n        accountReportService.process();\n    }\n\n    private void closeProcessing() {\n        log.info(\"Processing complete — {} processed, {} OK, {} errors\",\n            processedCount, successCount, errorCount);\n    }\n}",
        "sourceFile": "src/ACCTMAIN.cbl",
        "targetFile": "src/main/java/com/secb/AccountMainService.java",
        "sourceLangId": "cobol",
        "targetLangId": "java",
        "confidenceScores": [
          {"block": "IDENTIFICATION DIVISION → Class declaration", "score": 99, "lines": "1-8"},
          {"block": "WORKING-STORAGE → Fields", "score": 97, "lines": "18-26"},
          {"block": "CALL statements → Service injection", "score": 95, "lines": "37-44"},
          {"block": "PERFORM → Method calls", "score": 98, "lines": "30-35"},
          {"block": "FILE-CONTROL → SLF4J logging", "score": 93, "lines": "12-15"},
          {"block": "Error handling flow", "score": 91, "lines": "45-48"}
        ],
        "businessRules": [
          {"id": "BR-001", "description": "Daily processing must execute in sequence: Balance → Open → Process → Report", "source": "ELABORA-GIORNALIERA paragraph", "target": "executeDailyProcessing() method", "confidence": 98},
          {"id": "BR-002", "description": "Processing counters must track total, success, and error counts", "source": "WS-CONTATORI group", "target": "processedCount/successCount/errorCount fields", "confidence": 97},
          {"id": "BR-003", "description": "Log file must be opened before processing and closed after", "source": "OPEN/CLOSE LOG-FILE", "target": "SLF4J initialize()/closeProcessing()", "confidence": 95},
          {"id": "BR-004", "description": "Return code propagation between called programs", "source": "WS-RC-PRINCIPALE USING parameter", "target": "Spring service method return values", "confidence": 92}
        ],
        "explanations": [
          "COBOL CALL statements mapped to Spring @RequiredArgsConstructor dependency injection",
          "WORKING-STORAGE variables converted to private instance fields with appropriate Java types",
          "FILE-CONTROL log file replaced with SLF4J structured logging",
          "PERFORM paragraph calls converted to private method invocations",
          "PIC 9(4) COMP → int, PIC X(n) → String with preserved semantics",
          "STOP RUN replaced by CommandLineRunner lifecycle completion"
        ],
        "reasonings": [
          "Spring Boot was chosen as the target framework because it provides dependency injection, lifecycle management, and enterprise-grade features that map naturally to COBOL batch processing patterns.",
          "The CALL/USING pattern in COBOL directly maps to Spring service injection — each called program becomes a @Service bean.",
          "Lombok @RequiredArgsConstructor eliminates boilerplate while maintaining the same dependency structure as the COBOL CALL graph.",
          "SLF4J replaces the sequential log file because modern Java applications use structured logging with configurable outputs."
        ],
        "artifacts": {
          "unitTest": "package com.secb;\n\nimport org.junit.jupiter.api.Test;\nimport org.junit.jupiter.api.BeforeEach;\nimport org.junit.jupiter.api.DisplayName;\nimport static org.junit.jupiter.api.Assertions.*;\nimport static org.mockito.Mockito.*;\n\n@DisplayName(\"AccountMainService Tests\")\nclass AccountMainServiceTest {\n\n    private AccountBalanceService balanceService;\n    private AccountOpenService openService;\n    private AccountProcessingService processingService;\n    private AccountReportService reportService;\n    private AccountMainService mainService;\n\n    @BeforeEach\n    void setUp() {\n        balanceService = mock(AccountBalanceService.class);\n        openService = mock(AccountOpenService.class);\n        processingService = mock(AccountProcessingService.class);\n        reportService = mock(AccountReportService.class);\n        mainService = new AccountMainService(\n            balanceService, openService, processingService, reportService);\n    }\n\n    @Test\n    @DisplayName(\"Daily processing executes all services in order\")\n    void testDailyProcessingOrder() {\n        mainService.run();\n        var order = inOrder(balanceService, openService, processingService, reportService);\n        order.verify(balanceService).process();\n        order.verify(openService).process();\n        order.verify(processingService).process();\n        order.verify(reportService).process();\n    }\n\n    @Test\n    @DisplayName(\"Processing completes without exceptions\")\n    void testProcessingCompletes() {\n        assertDoesNotThrow(() -> mainService.run());\n    }\n}",
          "integrationTest": "package com.secb;\n\nimport org.junit.jupiter.api.Test;\nimport org.junit.jupiter.api.DisplayName;\nimport org.springframework.beans.factory.annotation.Autowired;\nimport org.springframework.boot.test.context.SpringBootTest;\nimport org.springframework.test.context.ActiveProfiles;\n\nimport static org.junit.jupiter.api.Assertions.*;\n\n@SpringBootTest\n@ActiveProfiles(\"test\")\n@DisplayName(\"SECB Integration Tests\")\nclass SecbIntegrationTest {\n\n    @Autowired\n    private AccountMainService mainService;\n\n    @Autowired\n    private AccountBalanceService balanceService;\n\n    @Test\n    @DisplayName(\"Spring context loads with all services\")\n    void contextLoads() {\n        assertNotNull(mainService);\n        assertNotNull(balanceService);\n    }\n\n    @Test\n    @DisplayName(\"Full daily processing cycle completes\")\n    void testFullCycle() {\n        assertDoesNotThrow(() -> mainService.run());\n    }\n}",
          "uiTest": "// No UI tests applicable — backend batch processing service.\n// API endpoint tests added when REST controllers are implemented.",
          "documentation": "# SECB Banking System — Migration Documentation\n\n## Overview\nThe SECB (Sistema Elaborazione Conti Bancari) system has been migrated from COBOL-85 to Java 22 with Spring Boot 3.2.\n\n## Architecture\n- **AccountMainService** — Main orchestrator (was ACCTMAIN)\n- **AccountBalanceService** — Balance calculations (was ACCTBAL)\n- **AccountOpenService** — Account opening (was ACCTOPEN)\n- **AccountCloseService** — Account closure (was ACCTCLSE)\n- **AccountProcessingService** — Transaction processing (was ACCTPROC)\n- **AccountReportService** — Report generation (was ACCTREPT)\n\n## Key Mappings\n| COBOL | Java |\n|-------|------|\n| CALL statement | @Autowired service |\n| WORKING-STORAGE | Instance fields |\n| PERFORM | Method call |\n| PIC 9(n)V99 | BigDecimal |\n| FILE-CONTROL | JPA Repository |\n\n## Testing\n- 147 unit tests (JUnit 5 + Mockito)\n- 42 integration tests (SpringBootTest)\n- 91.4% code coverage",
          "unitTestStats": {"cases": 147, "passed": 145, "failed": 2, "coverage": 91.4},
          "integrationTestStats": {"cases": 42, "passed": 41, "failed": 1, "coverage": 78.2},
          "uiTestStats": {"cases": 0, "passed": 0, "failed": 0, "coverage": 0},
          "docStats": {"pages": 12, "sections": 38, "apiEndpoints": 0},
          "archStats": {"services": 6, "repositories": 4, "controllers": 0, "configurations": 3}
        }
      },
      "verification": {
        "metrics": {"gates": 8, "passed": 8, "overallScore": 96.8}
      },
      "tests": {
        "metrics": {"tests": 189, "passed": 186, "failed": 3, "skipped": 0}
      },
      "testGeneration": {
        "metrics": {"unitTests": 147, "integrationTests": 42, "coverage": 91.4}
      },
      "performance": {
        "metrics": {"throughput": 1450, "latency": 14, "memory": 384}
      },
      "security": {
        "metrics": {"vulnerabilities": 0, "issues": 2, "score": 97.8}
      },
      "documentation": {
        "metrics": {"pages": 12, "apis": 0, "examples": 38}
      }
    }
  }'::jsonb,
  (SELECT id FROM "user" WHERE email='demo@scriba.ai')
)
ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  description = EXCLUDED.description,
  status = EXCLUDED.status,
  repo_url = EXCLUDED.repo_url,
  source_language = EXCLUDED.source_language,
  target_language = EXCLUDED.target_language,
  total_files = EXCLUDED.total_files,
  total_lines = EXCLUDED.total_lines,
  converted_files = EXCLUDED.converted_files,
  accuracy = EXCLUDED.accuracy,
  test_coverage = EXCLUDED.test_coverage,
  risk_score = EXCLUDED.risk_score,
  estimated_time = EXCLUDED.estimated_time,
  elapsed_time = EXCLUDED.elapsed_time,
  team = EXCLUDED.team,
  tags = EXCLUDED.tags,
  activity = EXCLUDED.activity,
  max_reached_step = EXCLUDED.max_reached_step,
  current_step = EXCLUDED.current_step,
  config = EXCLUDED.config,
  completed_at = EXCLUDED.completed_at,
  updated_at = NOW();
