-- ============================================================
-- 1) PROGETTO COMPLETATO — SECB Banking System (showcase)
-- ============================================================
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-secb-complete',
  'SECB Banking Core',
  'Full migration of SECB core banking COBOL mainframe system to Java Spring Boot 3.2 — Account management, transaction processing, daily/monthly batch routines, and reporting modules.',
  'completed',
  'https://github.com/scriba-ai-dev/test-cobol',
  'COBOL-85',
  'Java 22',
  NOW() - INTERVAL '3 days',
  NOW() - INTERVAL '1 hour',
  NOW() - INTERVAL '1 hour',
  15, 4820, 15,
  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"]'::jsonb,
  '[
    {"id":"act-100","timestamp":"2026-04-11T22:45:00Z","action":"Migration completed — 96.8% parity","user":"Scriba Engine"},
    {"id":"act-99","timestamp":"2026-04-11T22:44:00Z","action":"Final verification passed — all 8 quality gates OK","user":"Scriba Engine"},
    {"id":"act-98","timestamp":"2026-04-11T22:40:00Z","action":"Security scan completed — 0 critical vulnerabilities","user":"Scriba Engine"},
    {"id":"act-97","timestamp":"2026-04-11T22:38:00Z","action":"Test generation completed — 147 unit tests, 42 integration tests","user":"Scriba Engine"},
    {"id":"act-96","timestamp":"2026-04-11T22:30:00Z","action":"Neural translation completed — 6 source files → 18 Java classes","user":"Scriba Engine"},
    {"id":"act-95","timestamp":"2026-04-11T22:20:00Z","action":"Analysis completed — 15 files, 4,820 LOC, avg complexity 14.2","user":"Scriba Engine"},
    {"id":"act-94","timestamp":"2026-04-11T22:15:00Z","action":"Migration started","user":"Michele Laurelli"},
    {"id":"act-93","timestamp":"2026-04-11T22:10:00Z","action":"Pre-analysis completed","user":"Michele Laurelli"},
    {"id":"act-92","timestamp":"2026-04-11T21:50:00Z","action":"Repository connected","user":"Michele Laurelli"},
    {"id":"act-91","timestamp":"2026-04-08T14:00:00Z","action":"Project created","user":"Michele Laurelli"}
  ]'::jsonb,
  5, 5,
  '{
    "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",
    "conversionResult": {
      "status": "success",
      "conversionId": "cv-secb-001",
      "files": [
        {"sourcePath":"src/ACCTMAIN.cbl","outputPath":"src/main/java/com/secb/AccountMainService.java","accuracy":97,"iterations":2},
        {"sourcePath":"src/ACCTBAL.cbl","outputPath":"src/main/java/com/secb/AccountBalanceService.java","accuracy":98,"iterations":1},
        {"sourcePath":"src/ACCTOPEN.cbl","outputPath":"src/main/java/com/secb/AccountOpenService.java","accuracy":96,"iterations":2},
        {"sourcePath":"src/ACCTCLSE.cbl","outputPath":"src/main/java/com/secb/AccountCloseService.java","accuracy":97,"iterations":1},
        {"sourcePath":"src/ACCTPROC.cbl","outputPath":"src/main/java/com/secb/AccountProcessingService.java","accuracy":95,"iterations":2},
        {"sourcePath":"src/ACCTREPT.cbl","outputPath":"src/main/java/com/secb/AccountReportService.java","accuracy":98,"iterations":1}
      ],
      "metadata": {
        "accuracy": 96.8,
        "linesProcessed": 4820,
        "conversionTime": 2292.4,
        "iterations": 2,
        "warnings": ["ACCTPROC.cbl: complex nested PERFORM loop restructured as recursive method","ACCTMAIN.cbl: CALL statement mapped to Spring service injection"],
        "errors": []
      }
    },
    "analysisResults": {
      "scanResults": {
        "totalFiles": 15,
        "sourceFiles": 6,
        "copybooks": 3,
        "jclFiles": 3,
        "otherFiles": 3,
        "totalLines": 4820,
        "codeLines": 3640,
        "commentLines": 780,
        "blankLines": 400,
        "totalSizeBytes": 187400
      },
      "complexity": {
        "avgCyclomatic": 14.2,
        "maxCyclomatic": 28,
        "avgNesting": 3.8,
        "maxNesting": 7,
        "avgLinesPerFile": 607,
        "totalProcedures": 42,
        "totalSections": 18
      },
      "dependencies": {
        "totalDependencies": 24,
        "copybooks": 3,
        "externalCalls": 8,
        "circularDeps": 0,
        "items": [
          {"from":"ACCTMAIN","to":"ACCTCOMN.cpy","type":"COPY"},
          {"from":"ACCTMAIN","to":"ACCTMASTR.cpy","type":"COPY"},
          {"from":"ACCTMAIN","to":"ACCTBAL","type":"CALL"},
          {"from":"ACCTMAIN","to":"ACCTOPEN","type":"CALL"},
          {"from":"ACCTMAIN","to":"ACCTCLSE","type":"CALL"},
          {"from":"ACCTMAIN","to":"ACCTPROC","type":"CALL"},
          {"from":"ACCTPROC","to":"MOVRECORD.cpy","type":"COPY"},
          {"from":"ACCTPROC","to":"ACCTREPT","type":"CALL"}
        ]
      },
      "risk": {
        "high": 1,
        "medium": 3,
        "low": 2,
        "items": [
          {"file":"ACCTPROC.cbl","level":"high","factors":["Cyclomatic complexity 28","Nesting depth 7","3 external CALL statements"]},
          {"file":"ACCTMAIN.cbl","level":"medium","factors":["Cyclomatic complexity 22","4 CALL dispatches"]},
          {"file":"ACCTREPT.cbl","level":"medium","factors":["Complex report formatting","WRITE AFTER ADVANCING"]},
          {"file":"ACCTBAL.cbl","level":"medium","factors":["Decimal arithmetic precision"]},
          {"file":"ACCTOPEN.cbl","level":"low","factors":["Simple CRUD operations"]},
          {"file":"ACCTCLSE.cbl","level":"low","factors":["Simple status update"]}
        ]
      },
      "languages": {
        "COBOL": 72.4,
        "JCL": 15.8,
        "Copybook": 8.2,
        "Text": 3.6
      }
    },
    "completedAt": "2026-04-11T22:45:00Z"
  }'::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,
  updated_at = NOW();


-- ============================================================
-- 2) PROGETTO LIVE DEMO — Quick Demo (pronto per migrazione live)
-- ============================================================
-- Repository e Pre-Analysis pre-completati.
-- Migration (step 3) è lo step attivo → pronto per "Start Migration" dal vivo.
INSERT INTO projects (
  id, name, description, status,
  repo_url, source_language, target_language,
  created_at, updated_at,
  total_files, total_lines,
  risk_score,
  team, tags, activity,
  max_reached_step, current_step,
  config,
  user_id
) VALUES (
  'proj-quick-demo',
  'Quick Demo — Hello Banking',
  'Live demo: COBOL-85 banking inquiry program → Java Spring Boot. Single file, ~60s migration with real AI translation.',
  'analyzing',
  'https://github.com/scriba-ai-dev/test-cobol',
  'COBOL-85',
  'Java 22',
  NOW() - INTERVAL '15 minutes',
  NOW(),
  17, 1972,
  32.5,
  '[{"name":"Michele Laurelli","role":"Project Lead","avatar":"ML"}]'::jsonb,
  '["demo","cobol","java","live","banking"]'::jsonb,
  '[
    {"id":"act-3","timestamp":"2026-04-12T10:10:00Z","action":"Pre-analysis completed — 9 source files, avg complexity 12","user":"Scriba Engine"},
    {"id":"act-2","timestamp":"2026-04-12T10:05:00Z","action":"Repository connected — github.com/scriba-ai-dev/test-cobol","user":"Michele Laurelli"},
    {"id":"act-1","timestamp":"2026-04-12T10:00:00Z","action":"Project created","user":"Michele Laurelli"}
  ]'::jsonb,
  3, 3,
  '{
    "branch": "main",
    "sourcePath": "/Users/michelelaurelli/Desktop/scribademo/scriba-demo/demo-source",
    "sourceVersion": "COBOL-85",
    "targetVersion": "Java 22",
    "architecturePattern": "layered",
    "translationStrategy": "semantic",
    "selectedDatabase": "postgresql",
    "testingFramework": "junit5",
    "selectedPriority": "low",
    "riskLevel": "low",
    "selectedCompliance": ["gdpr"],
    "projectOwner": "Algoretico",
    "businessUnit": "Demo",
    "confidenceThreshold": 90,
    "maxIterations": 1,
    "targetAccuracy": 80,
    "enableSecurityScan": true,
    "preserveBusinessLogic": true,
    "namingConvention": "camelCase",
    "preAnalysis": {
      "scanResults": {
        "totalFiles": 17,
        "totalSourceFiles": 9,
        "totalDirectories": 5,
        "totalSize": 110583,
        "totalLines": 1972,
        "totalCodeLines": 1488,
        "totalCommentLines": 180,
        "totalBlankLines": 304,
        "languages": [
          {"name":"COBOL","bytes":78699,"files":9,"percentage":86.5},
          {"name":"JCL","bytes":12249,"files":3,"percentage":13.5}
        ],
        "extensions": [
          {"ext":".cbl","count":6,"percentage":35.3},
          {"ext":".cpy","count":3,"percentage":17.6},
          {"ext":".txt","count":3,"percentage":17.6},
          {"ext":".jcl","count":3,"percentage":17.6},
          {"ext":".gitignore","count":1,"percentage":5.9},
          {"ext":".md","count":1,"percentage":5.9}
        ]
      },
      "complexity": {
        "avgCyclomatic": 12,
        "maxCyclomatic": 27,
        "avgNesting": 5.3,
        "maxNesting": 12,
        "totalProcedures": 60,
        "totalSections": 78,
        "avgLines": 219,
        "maxLines": 389
      },
      "dependencies": {
        "total": 8,
        "copybooks": 8,
        "externalCalls": 0,
        "circularDeps": 0,
        "orphanFiles": 3,
        "list": ["ACCTMASTR","ACCTCOMN","WS-MOD-ACCTOPEN","WS-MOD-ACCTPROC","WS-MOD-ACCTBAL","WS-MOD-ACCTREPT","WS-MOD-ACCTCLSE","MOVRECORD"]
      },
      "risk": {
        "high": 4,
        "medium": 2,
        "low": 3,
        "files": [
          {"name":"ACCTPROC.cbl","path":"src/ACCTPROC.cbl","lines":290,"codeLines":227,"complexity":27,"nesting":12,"risk":"high","dependencies":3,"procedures":6,"size":10333},
          {"name":"ACCTBAL.cbl","path":"src/ACCTBAL.cbl","lines":268,"codeLines":206,"complexity":20,"nesting":10,"risk":"high","dependencies":2,"procedures":7,"size":9444},
          {"name":"ACCTMAIN.cbl","path":"src/ACCTMAIN.cbl","lines":241,"codeLines":180,"complexity":19,"nesting":8,"risk":"high","dependencies":6,"procedures":9,"size":8967},
          {"name":"ACCTOPEN.cbl","path":"src/ACCTOPEN.cbl","lines":308,"codeLines":243,"complexity":16,"nesting":8,"risk":"high","dependencies":2,"procedures":8,"size":10976},
          {"name":"ACCTREPT.cbl","path":"src/ACCTREPT.cbl","lines":389,"codeLines":308,"complexity":13,"nesting":5,"risk":"medium","dependencies":3,"procedures":15,"size":15200},
          {"name":"ACCTCLSE.cbl","path":"src/ACCTCLSE.cbl","lines":290,"codeLines":224,"complexity":10,"nesting":5,"risk":"medium","dependencies":2,"procedures":11,"size":10856},
          {"name":"ACCTCOMN.cpy","path":"cpy/ACCTCOMN.cpy","lines":53,"codeLines":25,"complexity":1,"nesting":0,"risk":"low","dependencies":0,"procedures":1,"size":3648},
          {"name":"ACCTMASTR.cpy","path":"cpy/ACCTMASTR.cpy","lines":56,"codeLines":30,"complexity":1,"nesting":0,"risk":"low","dependencies":0,"procedures":1,"size":3892},
          {"name":"MOVRECORD.cpy","path":"cpy/MOVRECORD.cpy","lines":77,"codeLines":45,"complexity":1,"nesting":0,"risk":"low","dependencies":0,"procedures":2,"size":5383}
        ]
      },
      "analyzedFileCount": 9
    }
  }'::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,
  risk_score = EXCLUDED.risk_score,
  team = EXCLUDED.team,
  tags = EXCLUDED.tags,
  activity = EXCLUDED.activity,
  max_reached_step = EXCLUDED.max_reached_step,
  current_step = EXCLUDED.current_step,
  config = EXCLUDED.config,
  updated_at = NOW();
