const Database = require('better-sqlite3');
const crypto = require('crypto');
const { getDbPath } = require('./config/runtime');
const { createDemoMetricsService } = require('./services/demo-metrics');

const dbPath = getDbPath();
const db = new Database(dbPath);
const demoMetrics = createDemoMetricsService({ db });

const DEFAULT_DIVISIONS = [
  [8,'DIRETTORE ESECUTIVO','Iacovici Alexandru',"VFP: Un'agenzia in costante espansione, redditizia e leader nel mercato Web/AI.","Supervisiona l'intera operazione.",'var(--color-div-1)','var(--border-div-1)'],
  [7,'DIVISIONE 7','Divisione Esecutiva',"VFP: Organizzazione strutturata, legale, operativa e con personale altamente produttivo.","Traduce la visione del Direttore in piani.",'var(--color-div-7)','var(--border-div-7)'],
  [1,'DIVISIONE 1','Divisione di Comunicazione',"VFP: Linee di comunicazione fluide e personale posizionato per la massima efficienza.","Gestisce la comunicazione interna ed esterna.",'var(--color-div-1)','var(--border-div-1)'],
  [2,'DIVISIONE 2','Divisione di Disseminazione',"VFP: Flusso abbondante e costante di nuovi clienti paganti per i pacchetti Web e AI.","Genera lead, gestisce la Local SEO, crea campagne.",'var(--color-div-2)','var(--border-div-2)'],
  [3,'DIVISIONE 3','Divisione di Tesoreria',"VFP: Fondi protetti, fatture incassate tempestivamente e riserve finanziarie crescenti.","Gestisce il flusso di cassa. Incassa i pagamenti.",'var(--color-div-3)','var(--border-div-3)'],
  [4,'DIVISIONE 4','Divisione Tecnica',"VFP: Siti web ottimizzati e automazioni AI consegnate con altissima qualità e nei tempi previsti.","Il motore dell'agenzia. Sviluppa siti web e AI.",'var(--color-div-4)','var(--border-div-4)'],
  [5,'DIVISIONE 5','Divisione Qualificazioni',"VFP: Prodotti impeccabili (Zero-Bug) e team costantemente formato sulle ultime tecnologie AI.","Testa e revisiona ogni prodotto prima del lancio.",'var(--color-div-5)','var(--border-div-5)'],
  [6,'DIVISIONE 6','Divisione del Pubblico',"VFP: Clienti entusiasti, centinaia di recensioni a 5 stelle e passaparola continuo.","Gestisce il supporto continuo e il Review Growth.",'var(--color-div-6)','var(--border-div-6)']
];

const DEFAULT_METRICS_CONFIG = [
  [8,'mrr','MRR (€)','fa-euro-sign'],[8,'active_clients','Active Clients','fa-users'],[8,'net_profit','Net Profit (€)','fa-chart-line'],
  [7,'policies','Policies Updated','fa-file-contract'],[7,'legal_audits','Legal Audits','fa-gavel'],
  [1,'emails_sent','Emails Sent','fa-envelope'],[1,'response_rate','Response Rate %','fa-reply'],
  [2,'leads','Leads Generated','fa-bullhorn'],[2,'calls','Calls Made','fa-phone'],[2,'deals','Deals Closed','fa-handshake'],
  [3,'invoiced','Amount Invoiced (€)','fa-file-invoice-dollar'],[3,'collected','Amount Collected (€)','fa-money-bill-wave'],
  [4,'tasks_done','Tasks Completed','fa-check-circle'],[4,'hours_worked','Hours Worked','fa-clock'],
  [5,'bugs_found','Bugs Found','fa-bug'],[5,'bugs_fixed','Bugs Fixed','fa-check-double'],
  [6,'reviews','Reviews Obtained','fa-star'],[6,'support_tickets','Support Tickets','fa-headset']
];

const DEFAULT_TRAININGS = [
  [8,'The Executive Routine','<h3>Daily Executive Checklist</h3><p>1. Review all division dashboards (10 min)</p><p>2. Check MRR and cash position</p><p>3. Identify the #1 bottleneck across all divisions</p><p>4. Make one strategic decision to unblock it</p><p><b>Remember:</b> Your job is to direct, not to do.</p>','5 min','fa-crown'],
  [7,'Executive Planning Framework','<h3>Weekly Executive Planning</h3><p>1. <b>Monday Morning:</b> Review all 7 division dashboards and identify the weakest division.</p><p>2. <b>Resource Allocation:</b> Shift time/budget toward the bottleneck division.</p><p>3. <b>Legal Check:</b> Ensure all active contracts are signed and filed.</p><p>4. <b>Hiring Pipeline:</b> Review if any division needs a freelancer or contractor.</p>','6 min','fa-sitemap'],
  [1,'Internal Communication SOP','<h3>Communication Flow Protocol</h3><p>1. <b>Client Emails:</b> Reply within 4 business hours. Use templates for common requests.</p><p>2. <b>Internal Updates:</b> Post daily status in the shared channel before 10am.</p><p>3. <b>Escalation Rule:</b> If a client issue is unresolved for 48h, escalate to Div 7.</p>','4 min','fa-comments'],
  [2,'Cold Email Framework for AI','<h3>The 3-Line Cold Email</h3><p><b>Subject:</b> Quick question about [their business]</p><p><b>Line 1:</b> I noticed [specific observation].</p><p><b>Line 2:</b> We help businesses like yours [result] using AI-powered [solution].</p><p><b>Line 3:</b> Would it make sense to chat 15 minutes this week?</p>','3 min','fa-envelope'],
  [3,'Invoice & Collections Protocol','<h3>Getting Paid On Time, Every Time</h3><p>1. <b>Before Work Starts:</b> Always collect 50% deposit. No exceptions.</p><p>2. <b>Invoice Format:</b> Use a professional template with payment terms (Net 7).</p><p>3. <b>Follow-Up Cadence:</b> Day 1: Invoice. Day 3: Reminder. Day 7: Firm follow-up. Day 14: Final notice + late fee.</p>','5 min','fa-file-invoice-dollar'],
  [4,'AI Agent Deployment SOP','<h3>Step-by-Step AI Agent Setup</h3><p>1. <b>Define the Use Case:</b> What repetitive task does this agent automate?</p><p>2. <b>Choose the Stack:</b> n8n for workflows, OpenAI/Ollama for LLM.</p><p>3. <b>Build the MVP:</b> Get a working prototype in 2 hours max.</p><p>4. <b>Test with Real Data:</b> Never demo with fake data to a client.</p>','7 min','fa-robot'],
  [5,'Zero-Bug QA Checklist','<h3>Pre-Launch Quality Assurance Protocol</h3><p>1. <b>Mobile Test:</b> iPhone Safari AND Android Chrome.</p><p>2. <b>Speed Test:</b> Google PageSpeed Insights — 90+ mobile score required.</p><p>3. <b>Form Test:</b> Submit every form and verify emails arrive.</p><p>4. <b>Link Audit:</b> Zero 404s allowed.</p><p>5. <b>SEO Basics:</b> Title, meta, alt text, H1 structure.</p>','8 min','fa-clipboard-check'],
  [6,'Review Growth System','<h3>The 48-Hour Review Protocol</h3><p>1. <b>Deliver the project</b> and confirm satisfaction on a call.</p><p>2. <b>Within 48 hours:</b> Send a direct Google Review link with suggested text.</p><p>3. <b>Follow up:</b> If no review in 5 days, send a gentle reminder.</p><p><b>Goal:</b> 80%+ of happy clients should leave a review.</p>','4 min','fa-star']
];

const DEFAULT_SERVICES = [
  ['Starter Website','Professional website, mobile responsive, SEO-ready','1500','EUR','5 pages, domain, 1 year hosting, contact form, Google Maps','Local SMEs, restaurants, professionals, shops','fa-globe'],
  ['AI Automation Package','Custom AI integration: chatbot, automations, intelligent workflows','2500','EUR','Trained chatbot, 3 workflow automations, monthly report','Companies that want to automate repetitive processes','fa-robot'],
  ['Monthly Support','Ongoing maintenance, SEO updates, priority technical support','300','EUR','Monthly updates, SEO report, 5 hours of technical support, optimizations','Existing clients with an active website','fa-headset']
];

const DEFAULT_POLICIES = [
  [7, 'Deposit Before Delivery', 'No billable implementation starts without an agreed scope and required deposit logged by Treasury. This protects cash flow and prevents unauthorized production.', 'active', 'Div 3 + Div 4'],
  [7, 'Written Scope Change Approval', 'Any request outside approved scope must be priced, written, and approved before work is started. This keeps delivery and treasury aligned.', 'active', 'Div 4 + Div 6'],
  [7, '48-Hour Review Request Standard', 'After successful delivery, the public/client success line must request a review within 48 hours and log the follow-up if no review is posted.', 'active', 'Div 6']
];

const DEFAULT_MEETINGS = [
  [7, 'Executive Implementation Review', 'weekly', '', 'Review active ED orders, policy gaps, legal/admin bottlenecks, and org risks. Confirm who owns each structural action before closing the meeting.', 'Confirm the next structural bottleneck and owner.']
];

const DEFAULT_RISKS = [
  [7, 'Founder dependency risk', 'Too many critical approvals or organizational decisions still depend on one person.', 'Define repeatable policy, turn recurring decisions into standards, and push ownership into the right division or hat.', 'open', 7],
  [7, 'Unapproved scope expansion', 'Production can lose margin if extra work is delivered without written approval and treasury visibility.', 'Enforce written change approvals and route all non-scoped requests through Division 7 policy control.', 'open', 7]
];

const DEFAULT_COMMUNICATION_TEMPLATES = [
  [1, 'Discovery Call Follow-Up', 'client_follow_up', 'active', 'Hi [Name], good speaking with you today. Here is the summary of the call, the next step, and the proposed timeline. Reply here if you want us to prepare the proposal.'],
  [1, 'Internal Daily Update', 'internal_update', 'active', 'Daily comms update: current client pressure, blocked replies, required approvals, and any message that needs ED or Div 7 attention before close of day.'],
  [1, 'Client Asset Request', 'asset_request', 'active', 'Hi [Name], before production can continue we still need the following assets: [list]. As soon as they are in, the workflow can keep moving without delay.'],
  [1, 'Review Request Assist', 'review_request', 'active', 'Hi [Name], glad the delivery landed well. If you are happy with the result, here is the direct review link. A short honest review helps us a lot.']
];

const DEFAULT_CAMPAIGN_BRIEFS = [
  [2, 'Local SEO + Website Upgrade Push', 'offer', 'active', 'Push the Starter Website offer to local businesses with weak websites and poor local search presence.', 'Lead with fast wins: mobile responsiveness, stronger local trust, and clearer conversion paths.'],
  [2, 'AI Automation Lead Magnet', 'campaign', 'active', 'Target repetitive-process businesses with the AI Automation Package and use workflow pain as the main hook.', 'Use cold outreach + discovery call + short ROI framing. The message should move fast from pain to specific automation outcome.']
];

const DEFAULT_AGENTS = [
  ['qa_auditor', 'general', null, 'The Auditor', 'Scans QA-ready production and flags work that can move forward cleanly.', 'Audit Division 5 and release readiness. If work is QA-complete, create the correct next action and log the event.', 'scheduled', '18:15', 'active', 8, 'fa-shield-alt', '#f59e0b'],
  ['metrics_strategist', 'general', null, 'The Strategist', 'Detects missing metrics and creates accountability tasks before blind spots spread.', 'Check divisions for missing real metrics. Create concrete follow-up tasks where numbers are missing and log the accountability action.', 'scheduled', '18:25', 'active', 8, 'fa-chess', '#60a5fa'],
  ['treasury_collector', 'general', null, 'The Collector', 'Watches unpaid treasury pressure and triggers follow-up work before cash slips.', 'Inspect Treasury invoices and cases. Create exact follow-up tasks for unpaid work and log the action.', 'scheduled', '17:45', 'active', 8, 'fa-money-bill-wave', '#34d399'],
  ['night_metrics_collector', 'general', null, 'Night Metrics Collector', 'At 23:58 it derives metrics from live operating data so the board stays current without manual logging.', 'At 23:58 collect and write derived metrics for every division from the live board, treasury, QA, and success data. Log the result in memory.', 'scheduled', '23:58', 'active', 8, 'fa-clock', '#a78bfa']
];

function seedDivisions() {
  if (db.prepare('SELECT COUNT(*) as c FROM divisions').get().c > 0) return;
  const ins = db.prepare('INSERT INTO divisions (id, name, subtitle, vfp, info, color_var, border_var) VALUES (?, ?, ?, ?, ?, ?, ?)');
  DEFAULT_DIVISIONS.forEach((division) => ins.run(...division));
}

function seedMetrics() {
  if (db.prepare('SELECT COUNT(*) as c FROM metrics_config').get().c > 0) return;
  const imc = db.prepare('INSERT INTO metrics_config (division_id, metric_key, metric_label, icon) VALUES (?, ?, ?, ?)');
  DEFAULT_METRICS_CONFIG.forEach((metric) => imc.run(...metric));
  demoMetrics.seedDemoSeries();
}

function seedTrainings() {
  if (db.prepare('SELECT COUNT(*) as c FROM trainings').get().c > 0) return;
  const itr = db.prepare('INSERT INTO trainings (division_id, title, content, read_time, icon) VALUES (?, ?, ?, ?, ?)');
  DEFAULT_TRAININGS.forEach((training) => itr.run(...training));
}

function seedServices() {
  if (db.prepare('SELECT COUNT(*) as c FROM services').get().c > 0) return;
  const isv = db.prepare('INSERT INTO services (name, description, price, currency, deliverables, ideal_client, icon) VALUES (?, ?, ?, ?, ?, ?, ?)');
  DEFAULT_SERVICES.forEach((service) => isv.run(...service));
}

function seedPolicies() {
  if (db.prepare('SELECT COUNT(*) as c FROM policies').get().c > 0) return;
  const now = new Date().toISOString();
  const ins = db.prepare('INSERT INTO policies (division_id, title, content, status, scope, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)');
  DEFAULT_POLICIES.forEach((policy) => ins.run(...policy, now, now));
}

function seedMeetings() {
  if (db.prepare('SELECT COUNT(*) as c FROM meetings').get().c > 0) return;
  const now = new Date().toISOString();
  const ins = db.prepare('INSERT INTO meetings (division_id, title, cadence, meeting_date, notes, next_action, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)');
  DEFAULT_MEETINGS.forEach((meeting) => ins.run(...meeting, now, now));
}

function seedRisks() {
  if (db.prepare('SELECT COUNT(*) as c FROM risks').get().c > 0) return;
  const now = new Date().toISOString();
  const ins = db.prepare('INSERT INTO risks (division_id, title, impact, mitigation, status, owner_division_id, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)');
  DEFAULT_RISKS.forEach((risk) => ins.run(...risk, now, now));
}

function seedCommunicationTemplates() {
  if (db.prepare('SELECT COUNT(*) as c FROM communication_templates').get().c > 0) return;
  const now = new Date().toISOString();
  const ins = db.prepare('INSERT INTO communication_templates (division_id, title, template_type, status, content, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)');
  DEFAULT_COMMUNICATION_TEMPLATES.forEach((template) => ins.run(...template, now, now));
}

function seedCampaignBriefs() {
  if (db.prepare('SELECT COUNT(*) as c FROM campaign_briefs').get().c > 0) return;
  const now = new Date().toISOString();
  const ins = db.prepare('INSERT INTO campaign_briefs (division_id, title, brief_type, status, angle, notes, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)');
  DEFAULT_CAMPAIGN_BRIEFS.forEach((brief) => ins.run(...brief, now, now));
}

function seedAgents() {
  const now = new Date().toISOString();
  const ins = db.prepare(`
    INSERT OR IGNORE INTO agents
      (system_key, scope_type, division_id, name, description, instructions, run_mode, run_time, status, created_by_division_id, icon, accent_color, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);
  DEFAULT_AGENTS.forEach((agent) => ins.run(...agent, now, now));
}

function seedTreasuryCases() {
  if (db.prepare('SELECT COUNT(*) as c FROM treasury_cases').get().c > 0) return;
}

function seedSkills() {
  if (db.prepare('SELECT COUNT(*) as c FROM skills').get().c > 0) return;
  const now = new Date().toISOString();
  const ins = db.prepare('INSERT INTO skills (division_id, parent_id, name, content, icon, tags, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)');
  // Core skills (parent_id = NULL) — one per division
  const coreSkills = [
    [8, null, 'Executive Operations', 'You are the Director. Your job is to direct, never to do. Every morning: review all 8 division dashboards, identify the #1 bottleneck, make ONE strategic decision to unblock it. Track MRR, active clients, and net profit weekly. Delegate everything else. If a division has no activity for 3 days, intervene.', 'fa-crown', 'executive,strategy,overview'],
    [7, null, 'Legal & Admin Operations', 'Ensure all contracts are signed before work starts. Maintain a legal template library. Review HR needs monthly. Keep business registrations current. Every new client needs: signed contract, NDA if applicable, scope document. Escalation path: any unresolved issue > 48h comes here.', 'fa-gavel', 'legal,contracts,admin,hr'],
    [1, null, 'Communication Protocol', 'All client emails replied within 4 business hours. Use templates for common requests (project update, invoice reminder, onboarding welcome). Internal status updates posted daily before 10am. When a client is unresponsive for 5+ days, escalate to Division 7.', 'fa-comments', 'email,communication,templates,response'],
    [2, null, 'Lead Generation & Sales', 'Primary channels: cold email, LinkedIn, Google Ads, referrals. Weekly targets: 20 cold contacts, 5 calls, 1 demo. The conversion sequence: Cold Contact → Discovery Call → Demo → Proposal → Close. Track leads, calls, deals daily. A lead goes cold after 48h without follow-up — send breakup email. Focus on ideal client profiles from the services catalog.', 'fa-bullhorn', 'marketing,sales,leads,cold-email,ads'],
    [3, null, 'Treasury & Invoicing', 'NEVER start work without a 50% deposit. Invoice format: professional template, Net 7 terms. Follow-up cadence: Day 1 invoice, Day 3 reminder, Day 7 firm follow-up, Day 14 final notice + late fee. Offer bank transfer AND Stripe. Monthly reconciliation on the 1st. Track invoiced vs collected amounts daily.', 'fa-coins', 'invoicing,payments,cashflow,treasury'],
    [4, null, 'Technical Production', 'Deep work mode: headphones on, notifications off, 3-hour blocks minimum. Setup staging environment before touching production. Use Git for version control. Client deliverables: staging URL for review before going live. Tech stack decisions based on client needs, not personal preference. Track tasks completed and hours worked daily.', 'fa-laptop-code', 'development,coding,production,tech'],
    [5, null, 'Quality Assurance', 'Zero-Bug Protocol: mobile test (iOS + Android), PageSpeed 90+, all forms tested, all links checked, cross-browser (Chrome/Safari/Firefox), SEO basics (title/meta/alt/H1), SSL verified. If ANY check fails, do NOT deliver. Fix first, re-test entire checklist. Track bugs found vs bugs fixed.', 'fa-shield-alt', 'qa,testing,bugs,quality'],
    [6, null, 'Customer Success', '48-Hour Review Protocol: deliver project, confirm satisfaction on call, send Google Review link within 48h with suggested copy, follow up in 5 days if no review. Goal: 80% review rate. Upsell monthly support packages. Schedule quarterly check-ins with active clients. Track reviews obtained and support tickets.', 'fa-heart', 'reviews,support,retention,upsell']
  ];
  const coreIds = {};
  coreSkills.forEach(s => {
    const r = ins.run(s[0], s[1], s[2], s[3], s[4], s[5], now);
    coreIds[s[0]] = r.lastInsertRowid;
  });
  // Sub-skills for key divisions
  const subSkills = [
    [2, coreIds[2], 'Cold Email Framework', 'The 3-Line Cold Email:\nSubject: Quick question about [their business]\nLine 1: I noticed [specific observation about their website/business].\nLine 2: We help businesses like yours [specific result] using AI-powered [solution].\nLine 3: Would it make sense to chat for 15 minutes this week?\nKey Rule: Never send more than 3 sentences. Brevity = responses.', 'fa-envelope', 'cold-email,outreach,framework'],
    [2, coreIds[2], 'Breakup Email', 'Send after 3 unanswered follow-ups. Template:\nSubject: Closing the loop\nHi [Name], I reached out a few times about [topic]. I understand now might not be the right time. I will close your file, but if things change, my door is open. Best, [Your Name]\nThis email gets the highest response rate of any template.', 'fa-door-open', 'cold-email,follow-up,breakup'],
    [2, coreIds[2], 'Discovery Call Script', 'Structure (15 min max):\n1. Rapport (2 min): Ask about their business, genuinely listen\n2. Pain (5 min): What is NOT working with their current website/marketing?\n3. Impact (3 min): What would it mean if that was solved?\n4. Solution (3 min): Here is exactly how we solve that — reference your packages\n5. Next step (2 min): Send proposal by [date]. Do NOT give pricing on the call.', 'fa-phone', 'sales-call,discovery,script'],
    [3, coreIds[3], 'Payment Follow-Up Sequence', 'Day 1: Invoice sent (professional tone)\nDay 3: Friendly reminder — "Just checking this landed in your inbox"\nDay 7: Firm follow-up — "Payment was due [date], please process at your earliest"\nDay 14: Final notice — "This is a formal notice. A late fee of X% will apply after [date]"\nDay 21: Escalate to Division 7 for legal action.', 'fa-clock', 'invoicing,follow-up,collections'],
    [4, coreIds[4], 'Staging Deployment Checklist', '1. Clone production to staging environment\n2. Apply all code changes on staging ONLY\n3. Run full build and verify no errors\n4. Test all interactive elements (forms, animations, API calls)\n5. Share staging URL with client for approval\n6. Only after written approval: deploy to production\n7. Verify production matches staging exactly\n8. Create a rollback point before every deploy', 'fa-server', 'deployment,staging,checklist'],
    [4, coreIds[4], 'Scope Creep Defense', 'When a client requests work outside the original scope:\n1. Acknowledge the request professionally\n2. Reference the signed scope document\n3. Provide a quote for the additional work with timeline\n4. Get written approval before starting any extra work\n5. Log the change request in the project notes\nNever do free work — it devalues your expertise and sets a dangerous precedent.', 'fa-hand-paper', 'scope,contracts,boundaries'],
    [5, coreIds[5], 'Performance Audit Protocol', '1. Run Lighthouse on every page (aim: 90+ Performance, 90+ Accessibility, 90+ SEO)\n2. Check Core Web Vitals: LCP < 2.5s, FID < 100ms, CLS < 0.1\n3. Test on throttled 3G connection — if unusable, optimize images and defer scripts\n4. Verify lazy loading on images below the fold\n5. Check for render-blocking CSS/JS\n6. Confirm CDN is properly configured', 'fa-tachometer-alt', 'performance,speed,lighthouse'],
    [6, coreIds[6], 'Upsell Conversation Framework', 'Best time to upsell: during the 48-hour satisfaction call.\nScript: "I am glad you are happy with the project. Many of our clients find that ongoing [monthly updates/SEO/support] keeps their investment growing. We have a [package name] at €[price]/month. Would you like me to send you the details?"\nNever push hard. Plant the seed, send info, follow up in 1 week.', 'fa-chart-line', 'upsell,retention,monthly']
  ];
  subSkills.forEach(s => ins.run(s[0], s[1], s[2], s[3], s[4], s[5], now));
}

function resetWorkspace() {
  const currentSettings = db.prepare('SELECT ai_provider, ai_model, ai_api_key, embedding_provider, embedding_model, local_runtime, local_runtime_model, agent_run_hour, app_language, show_mock_data FROM settings WHERE id = 1').get() || {};

  const tx = db.transaction(() => {
    db.prepare('DELETE FROM agent_runs').run();
    db.prepare('DELETE FROM agents').run();
    db.prepare('DELETE FROM item_division_history').run();
    db.prepare('DELETE FROM todos').run();
    db.prepare('DELETE FROM items').run();
    db.prepare('DELETE FROM battle_plans').run();
    db.prepare('DELETE FROM metrics_data').run();
    db.prepare('DELETE FROM metrics_config').run();
    db.prepare('DELETE FROM trainings').run();
    db.prepare('DELETE FROM services').run();
    db.prepare('DELETE FROM executive_orders').run();
    db.prepare('DELETE FROM policies').run();
    db.prepare('DELETE FROM meetings').run();
    db.prepare('DELETE FROM risks').run();
    db.prepare('DELETE FROM communication_templates').run();
    db.prepare('DELETE FROM campaign_briefs').run();
    db.prepare('DELETE FROM treasury_cases').run();
    db.prepare('DELETE FROM production_assets').run();
    db.prepare('DELETE FROM staging_runs').run();
    db.prepare('DELETE FROM scope_controls').run();
    db.prepare('DELETE FROM qa_checks').run();
    db.prepare('DELETE FROM release_gates').run();
    db.prepare('DELETE FROM success_loops').run();
    db.prepare('DELETE FROM invoice_line_items').run();
    db.prepare('DELETE FROM invoices').run();
    db.prepare('DELETE FROM ai_messages').run();
    db.prepare('DELETE FROM ai_conversations').run();
    db.prepare('DELETE FROM memory_logs').run();
    db.prepare('DELETE FROM skills').run();
    db.prepare(`UPDATE invoice_profile
      SET display_name = '',
          tagline = '',
          legal_name = '',
          address_line1 = '',
          address_line2 = '',
          city_postcode_country = '',
          country = '',
          utr = '',
          national_id = '',
          website = '',
          bank_holder = '',
          bank_account = '',
          bank_swift = '',
          bank_bic = '',
          bank_address = '',
          logo_url = ''
      WHERE id = 1`).run();

    db.prepare(`
      UPDATE settings
      SET onboarding_complete = 0,
          platform_tutorial_dismissed = 0,
          owner_name = '',
          company_name = '',
          city = '',
          ai_provider = ?,
          ai_model = ?,
          ai_api_key = ?,
          embedding_provider = ?,
          embedding_model = ?,
          local_runtime = ?,
          local_runtime_model = ?,
          agent_run_hour = ?,
          app_language = ?,
          show_mock_data = ?
      WHERE id = 1
    `).run(
      currentSettings.ai_provider || 'openrouter',
      currentSettings.ai_model || 'google/gemini-2.5-flash',
      currentSettings.ai_api_key || '',
      currentSettings.embedding_provider || 'ollama',
      currentSettings.embedding_model || 'nomic-embed-text',
      currentSettings.local_runtime || 'ollama',
      currentSettings.local_runtime_model || currentSettings.ai_model || 'llama3.1:8b',
      currentSettings.agent_run_hour ?? 7,
      currentSettings.app_language || 'en',
      typeof currentSettings.show_mock_data === 'number' ? currentSettings.show_mock_data : 1
    );

    const updDivision = db.prepare('UPDATE divisions SET name = ?, subtitle = ?, vfp = ?, info = ?, color_var = ?, border_var = ? WHERE id = ?');
    DEFAULT_DIVISIONS.forEach(([id, name, subtitle, vfp, info, colorVar, borderVar]) => {
      updDivision.run(name, subtitle, vfp, info, colorVar, borderVar, id);
    });

    seedMetrics();
    seedTrainings();
    seedServices();
    seedPolicies();
    seedMeetings();
    seedRisks();
    seedCommunicationTemplates();
    seedCampaignBriefs();
    seedTreasuryCases();
    seedSkills();
    seedAgents();
  });

  tx();
}

// ==========================================
// 1. CORE SCHEMAS
// ==========================================
db.exec(`
  CREATE TABLE IF NOT EXISTS divisions (
    id INTEGER PRIMARY KEY, name TEXT, subtitle TEXT, vfp TEXT, info TEXT, color_var TEXT, border_var TEXT
  );
  CREATE TABLE IF NOT EXISTS items (
    id INTEGER PRIMARY KEY AUTOINCREMENT, division_id INTEGER, title TEXT, magic_link TEXT, work_date TEXT, created_at TEXT, updated_at TEXT,
    FOREIGN KEY(division_id) REFERENCES divisions(id) ON DELETE CASCADE
  );
  CREATE TABLE IF NOT EXISTS todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT, item_id INTEGER, division_id INTEGER, text TEXT, done INTEGER DEFAULT 0, executive_order_id INTEGER, work_date TEXT, created_at TEXT, updated_at TEXT, completed_at TEXT,
    FOREIGN KEY(item_id) REFERENCES items(id) ON DELETE CASCADE
  );
  CREATE TABLE IF NOT EXISTS item_division_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id INTEGER NOT NULL,
    division_id INTEGER NOT NULL,
    start_date TEXT NOT NULL,
    end_date TEXT,
    created_at TEXT,
    updated_at TEXT,
    FOREIGN KEY(item_id) REFERENCES items(id) ON DELETE CASCADE
  );
  CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    ai_provider TEXT DEFAULT 'openrouter',
    ai_model TEXT DEFAULT 'google/gemini-2.5-flash',
    ai_api_key TEXT DEFAULT '',
    embedding_provider TEXT DEFAULT 'ollama',
    embedding_model TEXT DEFAULT 'nomic-embed-text',
    local_runtime TEXT DEFAULT 'ollama',
    local_runtime_model TEXT DEFAULT 'llama3.1:8b',
    onboarding_complete INTEGER DEFAULT 0,
    platform_tutorial_dismissed INTEGER DEFAULT 0,
    owner_name TEXT DEFAULT '',
    company_name TEXT DEFAULT '',
    city TEXT DEFAULT '',
    agent_run_hour INTEGER DEFAULT 7,
    app_language TEXT DEFAULT 'en',
    show_mock_data INTEGER DEFAULT 1
  );
`);

// ==========================================
// 2. ENTERPRISE SCHEMAS
// ==========================================
db.exec(`
  CREATE TABLE IF NOT EXISTS metrics_config (
    id INTEGER PRIMARY KEY AUTOINCREMENT, division_id INTEGER, metric_key TEXT, metric_label TEXT, icon TEXT
  );
  CREATE TABLE IF NOT EXISTS metrics_data (
    id INTEGER PRIMARY KEY AUTOINCREMENT, division_id INTEGER, metric_key TEXT, value NUMERIC, date TEXT, is_mock INTEGER DEFAULT 0
  );
  CREATE TABLE IF NOT EXISTS battle_plans (
    id INTEGER PRIMARY KEY AUTOINCREMENT, division_id INTEGER, period TEXT, title TEXT, done INTEGER DEFAULT 0, created_at TEXT
  );
  CREATE TABLE IF NOT EXISTS trainings (
    id INTEGER PRIMARY KEY AUTOINCREMENT, division_id INTEGER, title TEXT, content TEXT, read_time TEXT, icon TEXT
  );
  CREATE TABLE IF NOT EXISTS services (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    price TEXT,
    currency TEXT DEFAULT 'EUR',
    deliverables TEXT,
    ideal_client TEXT,
    icon TEXT DEFAULT 'fa-box'
  );
  CREATE TABLE IF NOT EXISTS executive_orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_division_id INTEGER DEFAULT 8,
    target_division_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    body TEXT DEFAULT '',
    priority TEXT DEFAULT 'medium',
    status TEXT DEFAULT 'open',
    due_date TEXT,
    linked_item_id INTEGER,
    linked_todo_id INTEGER,
    created_at TEXT,
    updated_at TEXT,
    completed_at TEXT
  );
  CREATE TABLE IF NOT EXISTS policies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT DEFAULT '',
    status TEXT DEFAULT 'draft',
    scope TEXT DEFAULT 'all',
    created_at TEXT,
    updated_at TEXT
  );
  CREATE TABLE IF NOT EXISTS meetings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    cadence TEXT DEFAULT 'weekly',
    meeting_date TEXT,
    notes TEXT DEFAULT '',
    next_action TEXT DEFAULT '',
    created_at TEXT,
    updated_at TEXT
  );
  CREATE TABLE IF NOT EXISTS risks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    impact TEXT DEFAULT '',
    mitigation TEXT DEFAULT '',
    status TEXT DEFAULT 'open',
    owner_division_id INTEGER,
    created_at TEXT,
    updated_at TEXT
  );
  CREATE TABLE IF NOT EXISTS communication_templates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    template_type TEXT DEFAULT 'general',
    status TEXT DEFAULT 'draft',
    content TEXT DEFAULT '',
    created_at TEXT,
    updated_at TEXT
  );
  CREATE TABLE IF NOT EXISTS campaign_briefs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    brief_type TEXT DEFAULT 'campaign',
    status TEXT DEFAULT 'draft',
    angle TEXT DEFAULT '',
    notes TEXT DEFAULT '',
    created_at TEXT,
    updated_at TEXT
  );

  CREATE TABLE IF NOT EXISTS agents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    system_key TEXT UNIQUE,
    scope_type TEXT NOT NULL DEFAULT 'division',
    division_id INTEGER,
    name TEXT NOT NULL,
    description TEXT DEFAULT '',
    instructions TEXT DEFAULT '',
    run_mode TEXT NOT NULL DEFAULT 'manual',
    run_time TEXT DEFAULT '',
    status TEXT NOT NULL DEFAULT 'active',
    created_by_division_id INTEGER DEFAULT 8,
    icon TEXT DEFAULT 'fa-robot',
    accent_color TEXT DEFAULT '#8b5cf6',
    created_at TEXT,
    updated_at TEXT
  );

  CREATE TABLE IF NOT EXISTS agent_runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_id INTEGER NOT NULL,
    division_id INTEGER,
    trigger_mode TEXT DEFAULT 'manual',
    status TEXT DEFAULT 'success',
    summary TEXT DEFAULT '',
    actions_json TEXT DEFAULT '[]',
    created_at TEXT,
    FOREIGN KEY(agent_id) REFERENCES agents(id) ON DELETE CASCADE
  );

  CREATE TABLE IF NOT EXISTS treasury_cases (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL DEFAULT 3,
    item_id INTEGER,
    invoice_id INTEGER,
    client_name TEXT NOT NULL,
    stage TEXT DEFAULT 'deposit_pending',
    amount_due REAL DEFAULT 0,
    due_date TEXT,
    channel TEXT DEFAULT 'bank_transfer',
    notes TEXT DEFAULT '',
    created_at TEXT,
    updated_at TEXT
  );

  CREATE TABLE IF NOT EXISTS production_assets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL DEFAULT 4,
    item_id INTEGER,
    asset_type TEXT DEFAULT 'brief',
    label TEXT NOT NULL,
    status TEXT DEFAULT 'missing',
    source_url TEXT DEFAULT '',
    notes TEXT DEFAULT '',
    created_at TEXT,
    updated_at TEXT
  );

  CREATE TABLE IF NOT EXISTS staging_runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL DEFAULT 4,
    item_id INTEGER,
    environment_label TEXT DEFAULT 'Staging',
    staging_url TEXT DEFAULT '',
    status TEXT DEFAULT 'pending',
    last_checked_at TEXT,
    notes TEXT DEFAULT '',
    created_at TEXT,
    updated_at TEXT
  );

  CREATE TABLE IF NOT EXISTS scope_controls (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL DEFAULT 4,
    item_id INTEGER,
    title TEXT NOT NULL,
    status TEXT DEFAULT 'approved',
    impact_level TEXT DEFAULT 'medium',
    notes TEXT DEFAULT '',
    created_at TEXT,
    updated_at TEXT
  );

  CREATE TABLE IF NOT EXISTS qa_checks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL DEFAULT 5,
    item_id INTEGER,
    check_name TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    severity TEXT DEFAULT 'standard',
    notes TEXT DEFAULT '',
    created_at TEXT,
    updated_at TEXT
  );

  CREATE TABLE IF NOT EXISTS release_gates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL DEFAULT 5,
    item_id INTEGER,
    gate_name TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    notes TEXT DEFAULT '',
    created_at TEXT,
    updated_at TEXT
  );

  CREATE TABLE IF NOT EXISTS success_loops (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL DEFAULT 6,
    item_id INTEGER,
    loop_type TEXT DEFAULT 'review_request',
    title TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    next_action_date TEXT,
    notes TEXT DEFAULT '',
    created_at TEXT,
    updated_at TEXT
  );

  CREATE TABLE IF NOT EXISTS invoice_profile (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    display_name TEXT DEFAULT '',
    tagline TEXT DEFAULT '',
    legal_name TEXT DEFAULT '',
    address_line1 TEXT DEFAULT '',
    address_line2 TEXT DEFAULT '',
    city_postcode_country TEXT DEFAULT '',
    country TEXT DEFAULT '',
    utr TEXT DEFAULT '',
    national_id TEXT DEFAULT '',
    website TEXT DEFAULT '',
    bank_holder TEXT DEFAULT '',
    bank_account TEXT DEFAULT '',
    bank_swift TEXT DEFAULT '',
    bank_bic TEXT DEFAULT '',
    bank_address TEXT DEFAULT '',
    logo_url TEXT DEFAULT ''
  );

  CREATE TABLE IF NOT EXISTS invoices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL DEFAULT 3,
    item_id INTEGER,
    client_name TEXT NOT NULL,
    client_address TEXT DEFAULT '',
    client_tax_id TEXT DEFAULT '',
    client_code TEXT DEFAULT '',
    invoice_number TEXT NOT NULL,
    invoice_date TEXT,
    due_date TEXT,
    currency TEXT DEFAULT 'EUR',
    subtotal REAL DEFAULT 0,
    deposit_required REAL DEFAULT 0,
    payment_reference TEXT DEFAULT '',
    terms TEXT DEFAULT '',
    notes TEXT DEFAULT '',
    status TEXT DEFAULT 'draft',
    created_at TEXT,
    updated_at TEXT
  );

  CREATE TABLE IF NOT EXISTS invoice_line_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    invoice_id INTEGER NOT NULL,
    description TEXT NOT NULL,
    amount REAL DEFAULT 0,
    position INTEGER DEFAULT 0,
    FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
  );
`);

// ==========================================
// 3. SENTIENT MEMORY SCHEMAS
// ==========================================
db.exec(`
  CREATE TABLE IF NOT EXISTS memory_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER,
    type TEXT,
    content TEXT,
    timestamp TEXT,
    embedding TEXT
  );
`);

db.exec(`
  CREATE TABLE IF NOT EXISTS ai_conversations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER NOT NULL,
    title TEXT DEFAULT '',
    created_at TEXT,
    updated_at TEXT
  );
  CREATE TABLE IF NOT EXISTS ai_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    conversation_id INTEGER NOT NULL,
    role TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TEXT,
    FOREIGN KEY(conversation_id) REFERENCES ai_conversations(id) ON DELETE CASCADE
  );
`);

db.exec(`
  CREATE TABLE IF NOT EXISTS translation_cache (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_text TEXT NOT NULL,
    target_language TEXT NOT NULL,
    translated_text TEXT NOT NULL,
    created_at TEXT,
    UNIQUE(source_text, target_language)
  );
`);

// ==========================================
// 3b. SKILLS LIBRARY (Option B — Semantic RAG)
// ==========================================
db.exec(`
  CREATE TABLE IF NOT EXISTS skills (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    division_id INTEGER,
    parent_id INTEGER,
    name TEXT NOT NULL,
    content TEXT NOT NULL,
    icon TEXT DEFAULT 'fa-lightbulb',
    color TEXT DEFAULT '#34d399',
    tags TEXT DEFAULT '',
    embedding TEXT,
    use_count INTEGER DEFAULT 0,
    last_used TEXT,
    created_at TEXT,
    FOREIGN KEY(parent_id) REFERENCES skills(id) ON DELETE CASCADE
  );
`);

db.exec('PRAGMA foreign_keys = ON;');

// ==========================================
// 4. MIGRATIONS (safe — catch if already exist)
// ==========================================
const migrations = [
  'ALTER TABLE todos ADD COLUMN division_id INTEGER',
  'ALTER TABLE metrics_data ADD COLUMN is_mock INTEGER DEFAULT 0',
  'ALTER TABLE items ADD COLUMN magic_link TEXT',
  "ALTER TABLE items ADD COLUMN work_date TEXT",
  "ALTER TABLE items ADD COLUMN created_at TEXT",
  "ALTER TABLE items ADD COLUMN updated_at TEXT",
  'ALTER TABLE memory_logs ADD COLUMN embedding TEXT',
  "ALTER TABLE settings ADD COLUMN embedding_provider TEXT DEFAULT 'ollama'",
  "ALTER TABLE settings ADD COLUMN embedding_model TEXT DEFAULT 'nomic-embed-text'",
  "ALTER TABLE settings ADD COLUMN local_runtime TEXT DEFAULT 'ollama'",
  "ALTER TABLE settings ADD COLUMN local_runtime_model TEXT DEFAULT 'llama3.1:8b'",
  'ALTER TABLE settings ADD COLUMN onboarding_complete INTEGER DEFAULT 0',
  "ALTER TABLE settings ADD COLUMN owner_name TEXT DEFAULT ''",
  "ALTER TABLE settings ADD COLUMN company_name TEXT DEFAULT ''",
  "ALTER TABLE settings ADD COLUMN city TEXT DEFAULT ''",
  'ALTER TABLE settings ADD COLUMN agent_run_hour INTEGER DEFAULT 7',
  'ALTER TABLE settings ADD COLUMN platform_tutorial_dismissed INTEGER DEFAULT 0',
  "ALTER TABLE settings ADD COLUMN app_language TEXT DEFAULT 'en'",
  'ALTER TABLE settings ADD COLUMN show_mock_data INTEGER DEFAULT 1',
  "ALTER TABLE skills ADD COLUMN color TEXT DEFAULT '#34d399'",
  "ALTER TABLE services ADD COLUMN currency TEXT DEFAULT 'EUR'"
  ,"ALTER TABLE todos ADD COLUMN executive_order_id INTEGER"
  ,"ALTER TABLE todos ADD COLUMN work_date TEXT"
  ,"ALTER TABLE todos ADD COLUMN created_at TEXT"
  ,"ALTER TABLE todos ADD COLUMN updated_at TEXT"
  ,"ALTER TABLE todos ADD COLUMN completed_at TEXT"
  ,"ALTER TABLE executive_orders ADD COLUMN linked_item_id INTEGER"
  ,"ALTER TABLE executive_orders ADD COLUMN linked_todo_id INTEGER"
  ,"ALTER TABLE invoices ADD COLUMN payment_reference TEXT DEFAULT ''"
  ,"ALTER TABLE invoices ADD COLUMN status TEXT DEFAULT 'draft'"
  ,"ALTER TABLE treasury_cases ADD COLUMN invoice_id INTEGER"
  ,"ALTER TABLE agents ADD COLUMN icon TEXT DEFAULT 'fa-robot'"
  ,"ALTER TABLE agents ADD COLUMN accent_color TEXT DEFAULT '#8b5cf6'"
];
migrations.forEach(m => { try { db.exec(m); } catch(e) {} });
try { db.exec('UPDATE todos SET division_id = (SELECT division_id FROM items WHERE items.id = todos.item_id) WHERE division_id IS NULL'); } catch(e) {}
try { db.exec("UPDATE skills SET color = '#34d399' WHERE color IS NULL OR color = ''"); } catch(e) {}
try { db.exec('UPDATE settings SET show_mock_data = 1 WHERE show_mock_data IS NULL'); } catch(e) {}
try { db.exec("UPDATE settings SET local_runtime = 'ollama' WHERE local_runtime IS NULL OR local_runtime = ''"); } catch(e) {}
try { db.exec("UPDATE settings SET local_runtime_model = COALESCE(NULLIF(local_runtime_model, ''), NULLIF(ai_model, ''), 'llama3.1:8b')"); } catch(e) {}
try { db.exec("UPDATE services SET currency = 'EUR' WHERE currency IS NULL OR currency = ''"); } catch(e) {}
try { db.exec("UPDATE agents SET icon = 'fa-shield-alt' WHERE system_key = 'qa_auditor' AND (icon IS NULL OR icon = '' OR icon = 'fa-shield-halved')"); } catch(e) {}
try { db.exec("UPDATE agents SET icon = 'fa-money-bill-wave' WHERE system_key = 'treasury_collector' AND (icon IS NULL OR icon = '' OR icon = 'fa-sack-dollar')"); } catch(e) {}
try { db.exec("UPDATE items SET created_at = COALESCE(created_at, datetime('now')) WHERE created_at IS NULL OR created_at = ''"); } catch(e) {}
try { db.exec("UPDATE items SET updated_at = COALESCE(updated_at, created_at, datetime('now')) WHERE updated_at IS NULL OR updated_at = ''"); } catch(e) {}
try { db.exec("UPDATE items SET work_date = COALESCE(work_date, substr(created_at, 1, 10), date('now')) WHERE work_date IS NULL OR work_date = ''"); } catch(e) {}
try { db.exec("UPDATE todos SET created_at = COALESCE(created_at, datetime('now')) WHERE created_at IS NULL OR created_at = ''"); } catch(e) {}
try { db.exec("UPDATE todos SET updated_at = COALESCE(updated_at, created_at, datetime('now')) WHERE updated_at IS NULL OR updated_at = ''"); } catch(e) {}
try { db.exec("UPDATE todos SET work_date = COALESCE(work_date, substr(created_at, 1, 10), date('now')) WHERE work_date IS NULL OR work_date = ''"); } catch(e) {}
try { db.exec("UPDATE todos SET completed_at = CASE WHEN done = 1 AND (completed_at IS NULL OR completed_at = '') THEN updated_at ELSE completed_at END"); } catch(e) {}

const itemHistoryCount = db.prepare('SELECT COUNT(*) as c FROM item_division_history').get().c;
if (itemHistoryCount === 0) {
  const insertHistory = db.prepare('INSERT INTO item_division_history (item_id, division_id, start_date, end_date, created_at, updated_at) VALUES (?, ?, ?, NULL, ?, ?)');
  const rows = db.prepare('SELECT id, division_id, work_date, created_at, updated_at FROM items').all();
  rows.forEach((row) => {
    const startDate = row.work_date || String(row.created_at || '').slice(0, 10) || new Date().toISOString().slice(0, 10);
    const createdAt = row.created_at || new Date().toISOString();
    const updatedAt = row.updated_at || createdAt;
    insertHistory.run(row.id, row.division_id, startDate, createdAt, updatedAt);
  });
}

// Backfill magic links
const itemsWithoutLink = db.prepare('SELECT id FROM items WHERE magic_link IS NULL').all();
const updateMagic = db.prepare('UPDATE items SET magic_link = ? WHERE id = ?');
itemsWithoutLink.forEach(i => updateMagic.run(crypto.randomUUID(), i.id));

// ==========================================
// 5. SEED DATA
// ==========================================
db.exec(`INSERT OR IGNORE INTO settings (id, ai_provider, ai_model, ai_api_key, embedding_provider, embedding_model, local_runtime, local_runtime_model, onboarding_complete, platform_tutorial_dismissed, owner_name, company_name, city, agent_run_hour, app_language, show_mock_data)
         VALUES (1, 'openrouter', 'google/gemini-2.5-flash', '', 'ollama', 'nomic-embed-text', 'ollama', 'llama3.1:8b', 0, 0, '', '', '', 7, 'en', 1);`);
db.exec(`INSERT OR IGNORE INTO invoice_profile (id, display_name, tagline, legal_name, address_line1, address_line2, city_postcode_country, country, utr, national_id, website, bank_holder, bank_account, bank_swift, bank_bic, bank_address, logo_url)
         VALUES (1, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');`);

seedDivisions();

seedMetrics();

seedTrainings();

seedServices();
seedPolicies();
seedMeetings();
seedRisks();
seedCommunicationTemplates();
seedCampaignBriefs();
seedTreasuryCases();
seedSkills();
seedAgents();

module.exports = db;
module.exports.resetWorkspace = resetWorkspace;
module.exports.demoMetrics = demoMetrics;
