sflite/app/(tabs)/setup.tsx

import { useEffect, useRef, useState } from 'react';
import { KeyboardAvoidingView, Platform, Pressable, StyleSheet, TextInput, View } from 'react-native';
import { Image } from 'expo-image';
import { Asset } from 'expo-asset';
import * as ImagePicker from 'expo-image-picker';
import * as FileSystem from 'expo-file-system/legacy';
import * as Sharing from 'expo-sharing';
import ParallaxScrollView from '@/components/parallax-scroll-view';
import { ThemedText } from '@/components/themed-text';
import { ThemedView } from '@/components/themed-view';
import { IconSymbol } from '@/components/ui/icon-symbol';
import { Colors, Fonts } from '@/constants/theme';
import { useTranslation } from '@/localization/i18n';
import { dbPromise, initCoreTables } from '@/services/db';
import { ThemedButton } from '@/components/themed-button';
import { useColorScheme } from '@/hooks/use-color-scheme';

type Profile = {
  name: string;
  farmName: string;
  location: string;
  photoUri: string;
  language: 'en' | 'th';
  currency: string;
};

const emptyProfile: Profile = {
  name: '',
  farmName: '',
  location: '',
  photoUri: '',
  language: 'en',
  currency: 'THB',
};

type DemoBackup = {
  user_profile: Array<{
    id: number;
    name: string | null;
    farm_name: string | null;
    location: string | null;
    photo_uri: string | null;
    language: string | null;
    currency: string | null;
    created_at: string | null;
    updated_at: string | null;
  }>;
  fields: Array<{
    id: number;
    name: string | null;
    area_ha: number | null;
    boundary_geojson: string | null;
    notes: string | null;
    photo_uri: string | null;
    created_at: string | null;
    updated_at: string | null;
  }>;
  crops: Array<{
    id: number;
    field_id: number | null;
    crop_name: string | null;
    variety: string | null;
    planting_date: string | null;
    expected_harvest_date: string | null;
    photo_uri: string | null;
  }>;
  observations: Array<{
    id: number;
    field_id: number | null;
    crop_id: number | null;
    obs_type: string | null;
    note: string | null;
    severity: number | null;
    lat: number | null;
    lng: number | null;
    observed_at: string | null;
  }>;
  images: Array<{
    id: number;
    observation_id: number | null;
    uri: string | null;
    thumbnail_uri: string | null;
    width: number | null;
    height: number | null;
    created_at: string | null;
  }>;
  daily_tasks: Array<{
    id: number;
    name: string | null;
    description: string | null;
    default_time: string | null;
    is_active: number | null;
    created_at: string | null;
  }>;
  daily_task_entries: Array<{
    id: number;
    task_id: number | null;
    field_id: number | null;
    notes: string | null;
    status: string | null;
    completed_at: string | null;
    created_at: string | null;
    meta_json: string | null;
  }>;
  ml_inferences: Array<{
    id: number;
    image_id: number | null;
    model_name: string | null;
    model_version: string | null;
    top_label: string | null;
    top_score: number | null;
    scores_json: string | null;
    created_at: string | null;
  }>;
  harvests: Array<{
    id: number;
    field_id: number | null;
    crop_id: number | null;
    harvested_at: string | null;
    quantity: number | null;
    unit: string | null;
    notes: string | null;
    photo_uri: string | null;
    created_at: string | null;
  }>;
  sales: Array<{
    id: number;
    harvest_id: number | null;
    field_id: number | null;
    crop_id: number | null;
    sold_at: string | null;
    quantity: number | null;
    unit: string | null;
    price: number | null;
    buyer: string | null;
    notes: string | null;
    created_at: string | null;
  }>;
  costs: Array<{
    id: number;
    field_id: number | null;
    crop_id: number | null;
    category: string | null;
    amount: number | null;
    currency: string | null;
    vendor: string | null;
    notes: string | null;
    spent_at: string | null;
    photo_uri: string | null;
    created_at: string | null;
  }>;
};

export default function SetupScreen() {
  const { language, setLanguage, t } = useTranslation();
  const theme = useColorScheme() ?? 'light';
  const palette = Colors[theme];
  const currencyPresets = ['THB', 'USD', 'EUR', 'JPY'];
  const [profile, setProfile] = useState<Profile>(emptyProfile);
  const [status, setStatus] = useState(t('setup.loading'));
  const [saveIndicator, setSaveIndicator] = useState('');
  const [snackbarVisible, setSnackbarVisible] = useState(false);
  const [snackbarMessage, setSnackbarMessage] = useState('');
  const demoBackupRef = useRef<DemoBackup | null>(null);
  const snackbarTimerRef = useRef<ReturnType<typeof setTimeout> | null>(null);

  useEffect(() => {
    let isActive = true;

    async function init() {
      await initCoreTables();
      const db = await dbPromise;

      const row = await db.getFirstAsync<{
        name: string | null;
        farm_name: string | null;
        location: string | null;
        photo_uri: string | null;
        language: string | null;
        currency: string | null;
      }>(
        'SELECT name, farm_name, location, photo_uri, language, currency FROM user_profile WHERE id = 1;'
      );

      if (!isActive) return;

      if (row) {
        const storedLanguage =
          row.language === 'th' || row.language === 'en' ? row.language : language;
        if (storedLanguage !== language) {
          setLanguage(storedLanguage);
        }
        setProfile({
          name: row.name ?? '',
          farmName: row.farm_name ?? '',
          location: row.location ?? '',
          photoUri: row.photo_uri ?? '',
          language: storedLanguage,
          currency: row.currency ?? 'THB',
        });
        setStatus(t('setup.loaded'));
      } else {
        setProfile((prev) => ({ ...prev, language, currency: 'THB' }));
        setStatus(t('setup.none'));
      }
    }

    init().catch((error) => {
      if (isActive) setStatus(`Error: ${String(error)}`);
    });

    return () => {
      isActive = false;
      if (snackbarTimerRef.current) {
        clearTimeout(snackbarTimerRef.current);
      }
    };
  }, []);

  async function handleSave() {
    setStatus(t('setup.saving'));
    try {
      const db = await dbPromise;
      const now = new Date().toISOString();
      await db.runAsync(
        'INSERT OR REPLACE INTO user_profile (id, name, farm_name, location, photo_uri, language, currency, created_at, updated_at) VALUES (1, ?, ?, ?, ?, ?, ?, COALESCE((SELECT created_at FROM user_profile WHERE id = 1), ?), ?);',
        profile.name.trim(),
        profile.farmName.trim(),
        profile.location.trim(),
        profile.photoUri,
        profile.language,
        profile.currency.trim(),
        now,
        now
      );
      setStatus(t('setup.saved'));
      setSaveIndicator(t('setup.saveIndicator'));
      setTimeout(() => setSaveIndicator(''), 2000);
    } catch (error) {
      setStatus(`Error: ${String(error)}`);
    }
  }

  async function handleInsertDemoData() {
    try {
      await initCoreTables();
      const db = await dbPromise;
      const fieldCount = await db.getFirstAsync<{ count: number }>(
        'SELECT COUNT(*) as count FROM fields;'
      );
      if (fieldCount?.count && fieldCount.count > 0) {
        showSnackbar(t('setup.demoExists'));
        return;
      }

      const now = new Date();
      const nowIso = now.toISOString();
      const day = 24 * 60 * 60 * 1000;
      const toDateOnly = (date: Date) => date.toISOString().slice(0, 10);

      const [fieldImageUri, cropImageUri, observationImageUri] = await loadDemoImageUris();
      const fieldRows = [
        {
          name: t('demo.field.north'),
          area: 2.4,
          notes: t('demo.field.northNote'),
          photoUri: fieldImageUri,
        },
        {
          name: t('demo.field.river'),
          area: 1.2,
          notes: t('demo.field.riverNote'),
          photoUri: fieldImageUri,
        },
        {
          name: t('demo.field.greenhouse'),
          area: 0.4,
          notes: t('demo.field.greenhouseNote'),
          photoUri: fieldImageUri,
        },
        {
          name: t('demo.field.orchard'),
          area: 1.8,
          notes: t('demo.field.orchardNote'),
          photoUri: fieldImageUri,
        },
        {
          name: t('demo.field.terrace'),
          area: 0.9,
          notes: t('demo.field.terraceNote'),
          photoUri: fieldImageUri,
        },
      ];
      const extraFieldCount = 100;
      for (let i = 0; i < extraFieldCount; i += 1) {
        const seed = fieldRows[i % fieldRows.length];
        fieldRows.push({
          name: `${seed.name} ${i + 2}`,
          area: Number((seed.area + (i % 3) * 0.2).toFixed(2)),
          notes: seed.notes,
          photoUri: fieldImageUri,
        });
      }

      const fieldIds: number[] = [];
      for (const field of fieldRows) {
        const result = await db.runAsync(
          'INSERT INTO fields (name, area_ha, notes, photo_uri, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?);',
          field.name,
          field.area,
          field.notes,
          field.photoUri,
          nowIso,
          nowIso
        );
        if (result.lastInsertRowId) fieldIds.push(Number(result.lastInsertRowId));
      }

      const cropTemplates = [
        {
          name: t('demo.crop.tomato'),
          variety: t('demo.crop.tomatoVariety'),
        },
        {
          name: t('demo.crop.rice'),
          variety: t('demo.crop.riceVariety'),
        },
        {
          name: t('demo.crop.lettuce'),
          variety: t('demo.crop.lettuceVariety'),
        },
        {
          name: t('demo.crop.chili'),
          variety: t('demo.crop.chiliVariety'),
        },
        {
          name: t('demo.crop.cabbage'),
          variety: t('demo.crop.cabbageVariety'),
        },
      ];
      const cropRows = fieldIds.slice(0, cropTemplates.length).map((fieldId, index) => {
        const template = cropTemplates[index];
        return {
          fieldId,
          name: template.name,
          variety: template.variety,
          planting: toDateOnly(new Date(now.getTime() - (30 + index * 7) * day)),
          harvest: toDateOnly(new Date(now.getTime() + (25 + index * 5) * day)),
          photoUri: cropImageUri,
        };
      });
      const extraCropCount = 120;
      for (let i = 0; i < extraCropCount; i += 1) {
        const template = cropTemplates[i % cropTemplates.length];
        cropRows.push({
          fieldId: fieldIds[i % fieldIds.length],
          name: `${template.name} ${i + 2}`,
          variety: template.variety,
          planting: toDateOnly(new Date(now.getTime() - (10 + i * 3) * day)),
          harvest: toDateOnly(new Date(now.getTime() + (20 + i * 4) * day)),
          photoUri: cropImageUri,
        });
      }

      const cropIds: number[] = [];
      for (const crop of cropRows) {
        if (!crop.fieldId) continue;
        const result = await db.runAsync(
          'INSERT INTO crops (field_id, crop_name, variety, planting_date, expected_harvest_date, photo_uri) VALUES (?, ?, ?, ?, ?, ?);',
          crop.fieldId,
          crop.name,
          crop.variety,
          crop.planting,
          crop.harvest,
          crop.photoUri
        );
        if (result.lastInsertRowId) cropIds.push(Number(result.lastInsertRowId));
      }

      const observationNotes = [
        t('demo.observation.scoutingNote'),
        t('demo.observation.diseaseNote'),
        t('demo.observation.irrigationNote'),
        t('demo.observation.pestNote'),
        t('demo.observation.nutrientNote'),
      ];
      const observationTypes = [
        t('observations.type.scouting'),
        t('observations.type.disease'),
        t('observations.type.irrigation'),
        t('observations.type.pest'),
        t('observations.type.nutrients'),
      ];
      const observationRows = [
        {
          fieldId: fieldIds[0],
          cropId: cropIds[0],
          type: t('observations.type.scouting'),
          note: t('demo.observation.scoutingNote'),
          severity: 2,
          observedAt: new Date(now.getTime() - 2 * day).toISOString(),
        },
        {
          fieldId: fieldIds[1],
          cropId: cropIds[1],
          type: t('observations.type.disease'),
          note: t('demo.observation.diseaseNote'),
          severity: 5,
          observedAt: new Date(now.getTime() - day).toISOString(),
        },
        {
          fieldId: fieldIds[2],
          cropId: cropIds[2],
          type: t('observations.type.irrigation'),
          note: t('demo.observation.irrigationNote'),
          severity: null,
          observedAt: nowIso,
        },
        {
          fieldId: fieldIds[3],
          cropId: cropIds[3],
          type: t('observations.type.pest'),
          note: t('demo.observation.pestNote'),
          severity: 3,
          observedAt: new Date(now.getTime() - 3 * day).toISOString(),
        },
        {
          fieldId: fieldIds[4],
          cropId: cropIds[4],
          type: t('observations.type.nutrients'),
          note: t('demo.observation.nutrientNote'),
          severity: 4,
          observedAt: new Date(now.getTime() - 4 * day).toISOString(),
        },
      ];
      const extraObservationCount = 160;
      for (let i = 0; i < extraObservationCount; i += 1) {
        observationRows.push({
          fieldId: fieldIds[i % fieldIds.length],
          cropId: cropIds[i % cropIds.length],
          type: observationTypes[i % observationTypes.length],
          note: observationNotes[i % observationNotes.length],
          severity: (i % 5) + 1,
          observedAt: new Date(now.getTime() - (i + 5) * day).toISOString(),
        });
      }

      for (const obs of observationRows) {
        if (!obs.fieldId) continue;
        const result = await db.runAsync(
          'INSERT INTO observations (field_id, crop_id, obs_type, note, severity, observed_at) VALUES (?, ?, ?, ?, ?, ?);',
          obs.fieldId,
          obs.cropId ?? null,
          obs.type,
          obs.note,
          obs.severity,
          obs.observedAt
        );
        if (observationImageUri && result.lastInsertRowId) {
          await db.runAsync(
            'INSERT INTO images (observation_id, uri, created_at) VALUES (?, ?, ?);',
            result.lastInsertRowId,
            observationImageUri,
            obs.observedAt
          );
        }
      }

      const taskCount = await db.getFirstAsync<{ count: number }>(
        'SELECT COUNT(*) as count FROM daily_tasks;'
      );
      if (!taskCount?.count) {
        const defaultTasks = [
          { name: t('tasks.default.fieldCheck'), description: t('tasks.default.fieldCheckDesc'), time: '08:00' },
          { name: t('tasks.default.scouting'), description: t('tasks.default.scoutingDesc'), time: '10:00' },
          { name: t('tasks.default.sensors'), description: t('tasks.default.sensorsDesc'), time: '15:00' },
        ];
        for (const task of defaultTasks) {
          await db.runAsync(
            'INSERT INTO daily_tasks (name, description, default_time, is_active, created_at) VALUES (?, ?, ?, 1, ?);',
            task.name,
            task.description,
            task.time,
            nowIso
          );
        }
      }

      const taskRows = await db.getAllAsync<{ id: number; name: string }>(
        'SELECT id, name FROM daily_tasks WHERE is_active = 1 ORDER BY id ASC LIMIT 3;'
      );
      const taskNotes = [t('demo.task.note'), t('demo.task.note2'), t('demo.task.note3')];
      let entryIndex = 0;
      for (let dayOffset = 0; dayOffset < 120; dayOffset += 1) {
        for (let i = 0; i < taskRows.length; i += 1) {
          const entryTime = new Date(now.getTime() - (dayOffset + i) * day).toISOString();
          await db.runAsync(
            'INSERT INTO daily_task_entries (task_id, field_id, notes, status, completed_at, created_at, meta_json) VALUES (?, ?, ?, ?, ?, ?, ?);',
            taskRows[i].id,
            fieldIds[0] ?? null,
            taskNotes[entryIndex % taskNotes.length],
            'done',
            entryTime,
            entryTime,
            null
          );
          entryIndex += 1;
        }
      }

      const harvestRows = [
        {
          fieldId: fieldIds[0],
          cropId: cropIds[0],
          date: new Date(now.getTime() - 5 * day).toISOString(),
          qty: 120,
          unit: 'kg',
          notes: t('demo.harvest.note1'),
        },
        {
          fieldId: fieldIds[1],
          cropId: cropIds[1],
          date: new Date(now.getTime() - 3 * day).toISOString(),
          qty: 260,
          unit: 'kg',
          notes: t('demo.harvest.note2'),
        },
        {
          fieldId: fieldIds[2],
          cropId: cropIds[2],
          date: new Date(now.getTime() - 2 * day).toISOString(),
          qty: 40,
          unit: 'kg',
          notes: t('demo.harvest.note3'),
        },
      ];
      const extraHarvestCount = 100;
      for (let i = 0; i < extraHarvestCount; i += 1) {
        harvestRows.push({
          fieldId: fieldIds[i % fieldIds.length],
          cropId: cropIds[i % cropIds.length],
          date: new Date(now.getTime() - (7 + i) * day).toISOString(),
          qty: 30 + i * 12,
          unit: 'kg',
          notes: [t('demo.harvest.note1'), t('demo.harvest.note2'), t('demo.harvest.note3')][
            i % 3
          ],
        });
      }

      const harvestIds: number[] = [];
      for (const harvest of harvestRows) {
        if (!harvest.fieldId || !harvest.cropId) continue;
        const result = await db.runAsync(
          'INSERT INTO harvests (field_id, crop_id, harvested_at, quantity, unit, notes, created_at) VALUES (?, ?, ?, ?, ?, ?, ?);',
          harvest.fieldId,
          harvest.cropId,
          harvest.date,
          harvest.qty,
          harvest.unit,
          harvest.notes,
          nowIso
        );
        if (result.lastInsertRowId) harvestIds.push(Number(result.lastInsertRowId));
      }

      const saleRows = [
        {
          harvestId: harvestIds[0] ?? null,
          fieldId: fieldIds[0],
          cropId: cropIds[0],
          date: new Date(now.getTime() - 4 * day).toISOString(),
          qty: 80,
          unit: 'kg',
          price: 35,
          buyer: t('demo.sale.buyer1'),
          notes: t('demo.sale.note1'),
        },
        {
          harvestId: harvestIds[1] ?? null,
          fieldId: fieldIds[1],
          cropId: cropIds[1],
          date: new Date(now.getTime() - 2 * day).toISOString(),
          qty: 150,
          unit: 'kg',
          price: 28,
          buyer: t('demo.sale.buyer2'),
          notes: t('demo.sale.note2'),
        },
        {
          harvestId: harvestIds[2] ?? null,
          fieldId: fieldIds[2],
          cropId: cropIds[2],
          date: new Date(now.getTime() - day).toISOString(),
          qty: 25,
          unit: 'kg',
          price: 40,
          buyer: t('demo.sale.buyer3'),
          notes: t('demo.sale.note3'),
        },
      ];
      const extraSaleCount = Math.min(harvestIds.length, 120);
      for (let i = 0; i < extraSaleCount; i += 1) {
        saleRows.push({
          harvestId: harvestIds[i] ?? null,
          fieldId: fieldIds[i % fieldIds.length],
          cropId: cropIds[i % cropIds.length],
          date: new Date(now.getTime() - (3 + i) * day).toISOString(),
          qty: 20 + i * 8,
          unit: 'kg',
          price: 25 + i * 2,
          buyer: [t('demo.sale.buyer1'), t('demo.sale.buyer2'), t('demo.sale.buyer3')][i % 3],
          notes: [t('demo.sale.note1'), t('demo.sale.note2'), t('demo.sale.note3')][i % 3],
        });
      }

      for (const sale of saleRows) {
        if (!sale.fieldId || !sale.cropId) continue;
        await db.runAsync(
          'INSERT INTO sales (harvest_id, field_id, crop_id, sold_at, quantity, unit, price, buyer, notes, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);',
          sale.harvestId,
          sale.fieldId,
          sale.cropId,
          sale.date,
          sale.qty,
          sale.unit,
          sale.price,
          sale.buyer,
          sale.notes,
          nowIso
        );
      }

      const costRows = [
        {
          fieldId: fieldIds[0],
          cropId: cropIds[0],
          category: t('costs.category.seed'),
          amount: 1200,
          vendor: t('demo.cost.vendor1'),
          notes: t('demo.cost.note1'),
          date: new Date(now.getTime() - 6 * day).toISOString(),
        },
        {
          fieldId: fieldIds[1],
          cropId: cropIds[1],
          category: t('costs.category.fertilizer'),
          amount: 950,
          vendor: t('demo.cost.vendor2'),
          notes: t('demo.cost.note2'),
          date: new Date(now.getTime() - 4 * day).toISOString(),
        },
        {
          fieldId: fieldIds[2],
          cropId: cropIds[2],
          category: t('costs.category.labor'),
          amount: 600,
          vendor: t('demo.cost.vendor3'),
          notes: t('demo.cost.note3'),
          date: new Date(now.getTime() - 2 * day).toISOString(),
        },
      ];
      const extraCostCount = 140;
      const costCategories = [
        t('costs.category.seed'),
        t('costs.category.fertilizer'),
        t('costs.category.labor'),
        t('costs.category.fuel'),
        t('costs.category.equipment'),
        t('costs.category.transport'),
        t('costs.category.misc'),
      ];
      for (let i = 0; i < extraCostCount; i += 1) {
        costRows.push({
          fieldId: fieldIds[i % fieldIds.length],
          cropId: cropIds[i % cropIds.length],
          category: costCategories[i % costCategories.length],
          amount: 250 + i * 75,
          vendor: [t('demo.cost.vendor1'), t('demo.cost.vendor2'), t('demo.cost.vendor3')][i % 3],
          notes: [t('demo.cost.note1'), t('demo.cost.note2'), t('demo.cost.note3')][i % 3],
          date: new Date(now.getTime() - (8 + i) * day).toISOString(),
        });
      }

      for (const cost of costRows) {
        if (!cost.fieldId) continue;
        await db.runAsync(
          'INSERT INTO costs (field_id, crop_id, category, amount, currency, vendor, notes, spent_at, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);',
          cost.fieldId,
          cost.cropId ?? null,
          cost.category,
          cost.amount,
          profile.currency || 'THB',
          cost.vendor,
          cost.notes,
          cost.date,
          nowIso
        );
      }

      showSnackbar(t('setup.demoInserted'));
    } catch (error) {
      showSnackbar(`${t('setup.demoError')} ${String(error)}`);
    }
  }

  async function handleClearDemoData() {
    try {
      await initCoreTables();
      const db = await dbPromise;
      demoBackupRef.current = await fetchDemoBackup(db);
      await db.execAsync(
        [
          'DELETE FROM ml_inferences;',
          'DELETE FROM images;',
          'DELETE FROM observations;',
          'DELETE FROM crops;',
          'DELETE FROM sales;',
          'DELETE FROM harvests;',
          'DELETE FROM costs;',
          'DELETE FROM daily_task_entries;',
          'DELETE FROM daily_tasks;',
          'DELETE FROM fields;',
        ].join('\n')
      );
      showSnackbar(t('setup.demoClearedUndo'));
    } catch (error) {
      showSnackbar(`${t('setup.demoClearError')} ${String(error)}`);
    }
  }

  async function handlePickPhoto() {
    const result = await ImagePicker.launchImageLibraryAsync({
      mediaTypes: getImageMediaTypes(),
      quality: 1,
    });

    if (result.canceled) return;
    const asset = result.assets[0];
    setProfile((prev) => ({ ...prev, photoUri: asset.uri }));
  }

  async function handleExportData() {
    try {
      await initCoreTables();
      const db = await dbPromise;
      const backup = await fetchDemoBackup(db);
      const payload = buildCsvExport(backup);
      const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
      const fileUri = `${FileSystem.documentDirectory}smartfarming-export-${timestamp}.csv`;
      await FileSystem.writeAsStringAsync(fileUri, payload);
      const canShare = await Sharing.isAvailableAsync();
      if (canShare) {
        await Sharing.shareAsync(fileUri, {
          mimeType: 'text/csv',
          dialogTitle: t('setup.exportTitle'),
          UTI: 'public.comma-separated-values-text',
        });
        showSnackbar(t('setup.exported'));
      } else {
        showSnackbar(`${t('setup.exported')} ${fileUri}`);
      }
    } catch (error) {
      showSnackbar(`${t('setup.exportError')} ${String(error)}`);
    }
  }

  function updateLanguage(nextLanguage: 'en' | 'th') {
    setLanguage(nextLanguage);
    setProfile((prev) => ({ ...prev, language: nextLanguage }));
  }

  function showSnackbar(message: string) {
    setSnackbarMessage(message);
    setSnackbarVisible(true);
    if (snackbarTimerRef.current) {
      clearTimeout(snackbarTimerRef.current);
    }
    snackbarTimerRef.current = setTimeout(() => {
      setSnackbarVisible(false);
      demoBackupRef.current = null;
    }, 5000);
  }

  async function handleUndoClear() {
    const backup = demoBackupRef.current;
    if (!backup) return;
    try {
      const db = await dbPromise;
      await restoreDemoBackup(db, backup);
      showSnackbar(t('setup.demoUndoDone'));
    } catch (error) {
      showSnackbar(`${t('setup.demoUndoError')} ${String(error)}`);
    } finally {
      demoBackupRef.current = null;
      setSnackbarVisible(false);
      if (snackbarTimerRef.current) {
        clearTimeout(snackbarTimerRef.current);
      }
    }
  }

  const inputStyle = [
    styles.input,
    { borderColor: palette.border, backgroundColor: palette.input, color: palette.text },
  ];

  return (
    <View style={styles.screen}>
      <KeyboardAvoidingView
        behavior={Platform.OS === 'ios' ? 'padding' : undefined}
        keyboardVerticalOffset={Platform.OS === 'ios' ? 0 : 0}
        style={styles.keyboardAvoid}>
        <ParallaxScrollView
          headerBackgroundColor={{ light: '#E2E9DF', dark: '#262A24' }}
          headerImage={
            <Image
              source={require('@/assets/images/userprofile.jpg')}
              style={styles.headerImage}
              contentFit="cover"
            />
          }>
        <ThemedView style={styles.titleContainer}>
          <ThemedText type="title" style={{ fontFamily: Fonts.rounded }}>
            {t('setup.title')}
          </ThemedText>
        </ThemedView>

        <ThemedView style={styles.section}>
          <ThemedText type="subtitle">{t('setup.profile')}</ThemedText>
          {status && status !== t('setup.saved') && status !== t('setup.loaded') ? (
            <ThemedText>{status}</ThemedText>
          ) : null}
        </ThemedView>

        <ThemedView style={styles.section}>
          <ThemedText>{t('setup.photo')}</ThemedText>
          <View style={styles.photoWrap}>
            {profile.photoUri ? (
              <Image source={{ uri: profile.photoUri }} style={styles.photo} contentFit="cover" />
            ) : (
              <ThemedText>{t('setup.noPhoto')}</ThemedText>
            )}
          </View>
          <View style={styles.buttonRow}>
            <ThemedButton title={t('setup.uploadPhoto')} onPress={handlePickPhoto} />
          </View>
        </ThemedView>

        <ThemedView style={styles.section}>
          <ThemedText>{t('setup.name')}</ThemedText>
          <TextInput
            value={profile.name}
            onChangeText={(name) => setProfile((prev) => ({ ...prev, name }))}
            placeholder={t('setup.name')}
            style={inputStyle}
          />
        </ThemedView>

        <ThemedView style={styles.section}>
          <ThemedText>{t('setup.farmName')}</ThemedText>
          <TextInput
            value={profile.farmName}
            onChangeText={(farmName) => setProfile((prev) => ({ ...prev, farmName }))}
            placeholder={t('setup.farmName')}
            style={inputStyle}
          />
        </ThemedView>

        <ThemedView style={styles.section}>
          <ThemedText>{t('setup.location')}</ThemedText>
          <TextInput
            value={profile.location}
            onChangeText={(location) => setProfile((prev) => ({ ...prev, location }))}
            placeholder={t('setup.location')}
            style={inputStyle}
          />
        </ThemedView>

      <ThemedView style={styles.section}>
        <ThemedText>{t('setup.language')}</ThemedText>
        <View style={styles.languageRow}>
            <ThemedButton
              title={t('setup.lang.en')}
              onPress={() => updateLanguage('en')}
              variant={profile.language === 'en' ? 'primary' : 'secondary'}
            />
            <ThemedButton
              title={t('setup.lang.th')}
              onPress={() => updateLanguage('th')}
              variant={profile.language === 'th' ? 'primary' : 'secondary'}
            />
        </View>
      </ThemedView>

      <ThemedView style={styles.section}>
        <ThemedText>{t('setup.currency')}</ThemedText>
        <View style={styles.currencyRow}>
          {currencyPresets.map((code) => {
            const isActive = profile.currency === code;
            return (
              <Pressable
                key={code}
                onPress={() => setProfile((prev) => ({ ...prev, currency: code }))}
                style={[
                  styles.currencyChip,
                  { borderColor: palette.border },
                  isActive && {
                    borderColor: palette.success,
                    backgroundColor: palette.surface,
                  },
                ]}>
                <ThemedText style={isActive ? styles.currencyTextActive : styles.currencyText}>
                  {t(`setup.currency.${code.toLowerCase()}`)}
                </ThemedText>
              </Pressable>
            );
          })}
        </View>
        <TextInput
          value={profile.currency}
          onChangeText={(currency) => setProfile((prev) => ({ ...prev, currency }))}
          placeholder={t('setup.currencyPlaceholder')}
          style={inputStyle}
        />
      </ThemedView>

        <View style={styles.section}>
          <ThemedText type="subtitle">{t('setup.exportTitle')}</ThemedText>
          <ThemedText>{t('setup.exportHint')}</ThemedText>
          <View style={styles.buttonRow}>
            <ThemedButton title={t('setup.exportButton')} onPress={handleExportData} />
          </View>
        </View>

        <View style={styles.section}>
          <ThemedText type="subtitle">{t('setup.demoTitle')}</ThemedText>
          <ThemedText>{t('setup.demoHint')}</ThemedText>
          <View style={styles.demoRow}>
            <ThemedButton title={t('setup.demoButton')} onPress={handleInsertDemoData} />
            <ThemedButton
              title={t('setup.demoClearButton')}
              onPress={handleClearDemoData}
              variant="secondary"
            />
          </View>
        </View>

      <View style={styles.buttonRow}>
        <ThemedButton title={t('setup.save')} onPress={handleSave} />
        {saveIndicator ? (
          <ThemedText style={styles.saveIndicator}>{saveIndicator}</ThemedText>
        ) : null}
      </View>
      <View style={styles.keyboardSpacer} />
      </ParallaxScrollView>
      </KeyboardAvoidingView>

      {snackbarVisible ? (
        <View style={styles.snackbar}>
          <ThemedText style={styles.snackbarText}>{snackbarMessage}</ThemedText>
          <Pressable style={styles.snackbarAction} onPress={handleUndoClear}>
            <ThemedText style={styles.snackbarActionText}>{t('setup.demoUndo')}</ThemedText>
          </Pressable>
        </View>
      ) : null}
    </View>
  );
}

function getImageMediaTypes() {
  const mediaType = (ImagePicker as { MediaType?: { Image?: unknown; Images?: unknown } })
    .MediaType;
  return mediaType?.Image ?? mediaType?.Images ?? ['images'];
}

function buildCsvExport(backup: DemoBackup) {
  const header = 'table,data_json';
  const rows: string[] = [header];

  const tables: Array<{ name: string; rows: unknown[] }> = [
    { name: 'user_profile', rows: backup.user_profile ?? [] },
    { name: 'fields', rows: backup.fields },
    { name: 'crops', rows: backup.crops },
    { name: 'observations', rows: backup.observations },
    { name: 'images', rows: backup.images },
    { name: 'ml_inferences', rows: backup.ml_inferences },
    { name: 'daily_tasks', rows: backup.daily_tasks },
    { name: 'daily_task_entries', rows: backup.daily_task_entries },
    { name: 'harvests', rows: backup.harvests },
    { name: 'sales', rows: backup.sales },
    { name: 'costs', rows: backup.costs },
  ];

  for (const table of tables) {
    for (const row of table.rows) {
      const json = JSON.stringify(row);
      rows.push(`${csvEscape(table.name)},${csvEscape(json)}`);
    }
  }

  return rows.join('\n');
}

function csvEscape(value: string) {
  const escaped = value.replace(/"/g, '""');
  return `"${escaped}"`;
}

const styles = StyleSheet.create({
  screen: {
    flex: 1,
  },
  keyboardAvoid: {
    flex: 1,
  },
  keyboardSpacer: {
    height: 80,
  },
  headerImage: {
    width: '100%',
    height: '100%',
  },
  titleContainer: {
    flexDirection: 'row',
    gap: 8,
  },
  section: {
    gap: 8,
    marginBottom: 16,
  },
  languageRow: {
    flexDirection: 'row',
    gap: 12,
  },
  currencyRow: {
    flexDirection: 'row',
    flexWrap: 'wrap',
    gap: 8,
  },
  currencyChip: {
    borderRadius: 999,
    borderWidth: 1,
    borderColor: '#C6C6C6',
    paddingHorizontal: 10,
    paddingVertical: 4,
  },
  currencyChipActive: {
    borderColor: '#2F7D4F',
    backgroundColor: '#E7F3EA',
  },
  currencyText: {
    fontSize: 12,
  },
  currencyTextActive: {
    fontSize: 12,
    color: '#2F7D4F',
    fontWeight: '600',
  },
  demoRow: {
    flexDirection: 'row',
    flexWrap: 'wrap',
    gap: 12,
  },
  snackbar: {
    position: 'absolute',
    left: 16,
    right: 16,
    bottom: 24,
    backgroundColor: '#2B2B23',
    borderRadius: 14,
    paddingHorizontal: 16,
    paddingVertical: 12,
    flexDirection: 'row',
    alignItems: 'center',
    gap: 12,
  },
  snackbarText: {
    color: '#FFFFFF',
    flex: 1,
  },
  snackbarAction: {
    paddingHorizontal: 10,
    paddingVertical: 6,
    borderRadius: 999,
    borderWidth: 1,
    borderColor: '#FFFFFF',
  },
  snackbarActionText: {
    color: '#FFFFFF',
    fontWeight: '600',
    fontSize: 12,
  },
  input: {
    borderRadius: 10,
    borderWidth: 1,
    borderColor: '#B9B9B9',
    paddingHorizontal: 12,
    paddingVertical: 10,
    fontSize: 16,
  },
  buttonRow: {
    paddingTop: 4,
  },
  saveIndicator: {
    marginTop: 10,
    alignSelf: 'center',
    backgroundColor: '#E7F3EA',
    borderColor: '#2F7D4F',
    borderWidth: 1,
    borderRadius: 999,
    paddingHorizontal: 12,
    paddingVertical: 6,
    fontSize: 12,
    fontWeight: '600',
    color: '#2F7D4F',
  },
  photo: {
    height: 140,
    width: 140,
    borderRadius: 70,
  },
  photoWrap: {
    alignItems: 'center',
  },
});

async function fetchDemoBackup(db: Awaited<typeof dbPromise>): Promise<DemoBackup> {
  const user_profile = await db.getAllAsync<DemoBackup['user_profile'][number]>(
    'SELECT * FROM user_profile;'
  );
  const fields = await db.getAllAsync<DemoBackup['fields'][number]>('SELECT * FROM fields;');
  const crops = await db.getAllAsync<DemoBackup['crops'][number]>('SELECT * FROM crops;');
  const observations = await db.getAllAsync<DemoBackup['observations'][number]>(
    'SELECT * FROM observations;'
  );
  const images = await db.getAllAsync<DemoBackup['images'][number]>('SELECT * FROM images;');
  const daily_tasks = await db.getAllAsync<DemoBackup['daily_tasks'][number]>(
    'SELECT * FROM daily_tasks;'
  );
  const daily_task_entries = await db.getAllAsync<DemoBackup['daily_task_entries'][number]>(
    'SELECT * FROM daily_task_entries;'
  );
  const ml_inferences = await db.getAllAsync<DemoBackup['ml_inferences'][number]>(
    'SELECT * FROM ml_inferences;'
  );
  const harvests = await db.getAllAsync<DemoBackup['harvests'][number]>(
    'SELECT * FROM harvests;'
  );
  const sales = await db.getAllAsync<DemoBackup['sales'][number]>(
    'SELECT * FROM sales;'
  );
  const costs = await db.getAllAsync<DemoBackup['costs'][number]>(
    'SELECT * FROM costs;'
  );

  return {
    user_profile,
    fields,
    crops,
    observations,
    images,
    daily_tasks,
    daily_task_entries,
    ml_inferences,
    harvests,
    sales,
    costs,
  };
}

async function restoreDemoBackup(db: Awaited<typeof dbPromise>, backup: DemoBackup) {
  for (const field of backup.fields) {
    await db.runAsync(
      'INSERT INTO fields (id, name, area_ha, boundary_geojson, notes, photo_uri, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?);',
      field.id,
      field.name,
      field.area_ha,
      field.boundary_geojson,
      field.notes,
      field.photo_uri,
      field.created_at,
      field.updated_at
    );
  }
  for (const crop of backup.crops) {
    await db.runAsync(
      'INSERT INTO crops (id, field_id, crop_name, variety, planting_date, expected_harvest_date, photo_uri) VALUES (?, ?, ?, ?, ?, ?, ?);',
      crop.id,
      crop.field_id,
      crop.crop_name,
      crop.variety,
      crop.planting_date,
      crop.expected_harvest_date,
      crop.photo_uri
    );
  }
  for (const observation of backup.observations) {
    await db.runAsync(
      'INSERT INTO observations (id, field_id, crop_id, obs_type, note, severity, lat, lng, observed_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);',
      observation.id,
      observation.field_id,
      observation.crop_id,
      observation.obs_type,
      observation.note,
      observation.severity,
      observation.lat,
      observation.lng,
      observation.observed_at
    );
  }
  for (const image of backup.images) {
    await db.runAsync(
      'INSERT INTO images (id, observation_id, uri, thumbnail_uri, width, height, created_at) VALUES (?, ?, ?, ?, ?, ?, ?);',
      image.id,
      image.observation_id,
      image.uri,
      image.thumbnail_uri,
      image.width,
      image.height,
      image.created_at
    );
  }
  for (const task of backup.daily_tasks) {
    await db.runAsync(
      'INSERT INTO daily_tasks (id, name, description, default_time, is_active, created_at) VALUES (?, ?, ?, ?, ?, ?);',
      task.id,
      task.name,
      task.description,
      task.default_time,
      task.is_active,
      task.created_at
    );
  }
  for (const entry of backup.daily_task_entries) {
    await db.runAsync(
      'INSERT INTO daily_task_entries (id, task_id, field_id, notes, status, completed_at, created_at, meta_json) VALUES (?, ?, ?, ?, ?, ?, ?, ?);',
      entry.id,
      entry.task_id,
      entry.field_id,
      entry.notes,
      entry.status,
      entry.completed_at,
      entry.created_at,
      entry.meta_json
    );
  }
  for (const inference of backup.ml_inferences) {
    await db.runAsync(
      'INSERT INTO ml_inferences (id, image_id, model_name, model_version, top_label, top_score, scores_json, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?);',
      inference.id,
      inference.image_id,
      inference.model_name,
      inference.model_version,
      inference.top_label,
      inference.top_score,
      inference.scores_json,
      inference.created_at
    );
  }
  for (const harvest of backup.harvests) {
    await db.runAsync(
      'INSERT INTO harvests (id, field_id, crop_id, harvested_at, quantity, unit, notes, photo_uri, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);',
      harvest.id,
      harvest.field_id,
      harvest.crop_id,
      harvest.harvested_at,
      harvest.quantity,
      harvest.unit,
      harvest.notes,
      harvest.photo_uri,
      harvest.created_at
    );
  }
  for (const sale of backup.sales) {
    await db.runAsync(
      'INSERT INTO sales (id, harvest_id, field_id, crop_id, sold_at, quantity, unit, price, buyer, notes, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);',
      sale.id,
      sale.harvest_id,
      sale.field_id,
      sale.crop_id,
      sale.sold_at,
      sale.quantity,
      sale.unit,
      sale.price,
      sale.buyer,
      sale.notes,
      sale.created_at
    );
  }
  for (const cost of backup.costs) {
    await db.runAsync(
      'INSERT INTO costs (id, field_id, crop_id, category, amount, currency, vendor, notes, spent_at, photo_uri, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);',
      cost.id,
      cost.field_id,
      cost.crop_id,
      cost.category,
      cost.amount,
      cost.currency,
      cost.vendor,
      cost.notes,
      cost.spent_at,
      cost.photo_uri,
      cost.created_at
    );
  }
}

async function loadDemoImageUris() {
  const modules = [
    require('@/assets/images/fields.jpg'),
    require('@/assets/images/crops.jpg'),
    require('@/assets/images/observations.jpg'),
  ];
  await Asset.loadAsync(modules);
  const assets = modules.map((mod) => Asset.fromModule(mod));
  return assets.map((asset) => asset.localUri ?? asset.uri ?? '');
}