sflite/services/db.ts

import * as SQLite from 'expo-sqlite';

export const dbPromise = SQLite.openDatabaseAsync('smartfarming.db');

type ColumnSpec = {
  name: string;
  type: string;
};

const userProfileColumns: ColumnSpec[] = [
  { name: 'id', type: 'INTEGER PRIMARY KEY NOT NULL' },
  { name: 'name', type: 'TEXT' },
  { name: 'farm_name', type: 'TEXT' },
  { name: 'location', type: 'TEXT' },
  { name: 'photo_uri', type: 'TEXT' },
  { name: 'language', type: 'TEXT' },
  { name: 'currency', type: 'TEXT' },
  { name: 'created_at', type: 'TEXT' },
  { name: 'updated_at', type: 'TEXT' },
];

const fieldColumns: ColumnSpec[] = [
  { name: 'id', type: 'INTEGER PRIMARY KEY NOT NULL' },
  { name: 'name', type: 'TEXT' },
  { name: 'area_ha', type: 'REAL' },
  { name: 'boundary_geojson', type: 'TEXT' },
  { name: 'notes', type: 'TEXT' },
  { name: 'photo_uri', type: 'TEXT' },
  { name: 'created_at', type: 'TEXT' },
  { name: 'updated_at', type: 'TEXT' },
];

const cropColumns: ColumnSpec[] = [
  { name: 'id', type: 'INTEGER PRIMARY KEY NOT NULL' },
  { name: 'field_id', type: 'INTEGER' },
  { name: 'crop_name', type: 'TEXT' },
  { name: 'variety', type: 'TEXT' },
  { name: 'planting_date', type: 'TEXT' },
  { name: 'expected_harvest_date', type: 'TEXT' },
  { name: 'photo_uri', type: 'TEXT' },
];

const harvestColumns: ColumnSpec[] = [
  { name: 'id', type: 'INTEGER PRIMARY KEY NOT NULL' },
  { name: 'field_id', type: 'INTEGER' },
  { name: 'crop_id', type: 'INTEGER' },
  { name: 'harvested_at', type: 'TEXT' },
  { name: 'quantity', type: 'REAL' },
  { name: 'unit', type: 'TEXT' },
  { name: 'notes', type: 'TEXT' },
  { name: 'photo_uri', type: 'TEXT' },
  { name: 'created_at', type: 'TEXT' },
];

const saleColumns: ColumnSpec[] = [
  { name: 'id', type: 'INTEGER PRIMARY KEY NOT NULL' },
  { name: 'harvest_id', type: 'INTEGER' },
  { name: 'field_id', type: 'INTEGER' },
  { name: 'crop_id', type: 'INTEGER' },
  { name: 'sold_at', type: 'TEXT' },
  { name: 'quantity', type: 'REAL' },
  { name: 'unit', type: 'TEXT' },
  { name: 'price', type: 'REAL' },
  { name: 'buyer', type: 'TEXT' },
  { name: 'notes', type: 'TEXT' },
  { name: 'created_at', type: 'TEXT' },
];

const costColumns: ColumnSpec[] = [
  { name: 'id', type: 'INTEGER PRIMARY KEY NOT NULL' },
  { name: 'field_id', type: 'INTEGER' },
  { name: 'crop_id', type: 'INTEGER' },
  { name: 'category', type: 'TEXT' },
  { name: 'amount', type: 'REAL' },
  { name: 'currency', type: 'TEXT' },
  { name: 'vendor', type: 'TEXT' },
  { name: 'notes', type: 'TEXT' },
  { name: 'spent_at', type: 'TEXT' },
  { name: 'photo_uri', type: 'TEXT' },
  { name: 'created_at', type: 'TEXT' },
];

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

  await db.execAsync(
    [
      'CREATE TABLE IF NOT EXISTS user_profile (id INTEGER PRIMARY KEY NOT NULL, name TEXT, farm_name TEXT, location TEXT, photo_uri TEXT, language TEXT, currency TEXT, created_at TEXT, updated_at TEXT);',
      'CREATE TABLE IF NOT EXISTS fields (id INTEGER PRIMARY KEY NOT NULL, name TEXT, area_ha REAL, boundary_geojson TEXT, notes TEXT, photo_uri TEXT, created_at TEXT, updated_at TEXT);',
      'CREATE TABLE IF NOT EXISTS crops (id INTEGER PRIMARY KEY NOT NULL, field_id INTEGER, crop_name TEXT, variety TEXT, planting_date TEXT, expected_harvest_date TEXT, photo_uri TEXT, FOREIGN KEY(field_id) REFERENCES fields(id));',
      'CREATE TABLE IF NOT EXISTS observations (id INTEGER PRIMARY KEY NOT NULL, field_id INTEGER, crop_id INTEGER, obs_type TEXT, note TEXT, severity INTEGER, lat REAL, lng REAL, observed_at TEXT, FOREIGN KEY(field_id) REFERENCES fields(id), FOREIGN KEY(crop_id) REFERENCES crops(id));',
      'CREATE TABLE IF NOT EXISTS images (id INTEGER PRIMARY KEY NOT NULL, observation_id INTEGER, uri TEXT, thumbnail_uri TEXT, width INTEGER, height INTEGER, created_at TEXT, FOREIGN KEY(observation_id) REFERENCES observations(id));',
      'CREATE TABLE IF NOT EXISTS ml_inferences (id INTEGER PRIMARY KEY NOT NULL, image_id INTEGER, model_name TEXT, model_version TEXT, top_label TEXT, top_score REAL, scores_json TEXT, created_at TEXT, FOREIGN KEY(image_id) REFERENCES images(id));',
      'CREATE TABLE IF NOT EXISTS sensors (id INTEGER PRIMARY KEY NOT NULL, name TEXT, type TEXT, unit TEXT, field_id INTEGER, location TEXT, created_at TEXT, FOREIGN KEY(field_id) REFERENCES fields(id));',
      'CREATE TABLE IF NOT EXISTS sensor_readings (id INTEGER PRIMARY KEY NOT NULL, sensor_id INTEGER, value REAL, observed_at TEXT, FOREIGN KEY(sensor_id) REFERENCES sensors(id));',
      'CREATE TABLE IF NOT EXISTS daily_tasks (id INTEGER PRIMARY KEY NOT NULL, name TEXT, description TEXT, default_time TEXT, is_active INTEGER DEFAULT 1, created_at TEXT);',
      'CREATE TABLE IF NOT EXISTS daily_task_entries (id INTEGER PRIMARY KEY NOT NULL, task_id INTEGER, field_id INTEGER, notes TEXT, status TEXT, completed_at TEXT, created_at TEXT, meta_json TEXT, FOREIGN KEY(task_id) REFERENCES daily_tasks(id), FOREIGN KEY(field_id) REFERENCES fields(id));',
      'CREATE TABLE IF NOT EXISTS harvests (id INTEGER PRIMARY KEY NOT NULL, field_id INTEGER, crop_id INTEGER, harvested_at TEXT, quantity REAL, unit TEXT, notes TEXT, photo_uri TEXT, created_at TEXT, FOREIGN KEY(field_id) REFERENCES fields(id), FOREIGN KEY(crop_id) REFERENCES crops(id));',
      'CREATE TABLE IF NOT EXISTS sales (id INTEGER PRIMARY KEY NOT NULL, harvest_id INTEGER, field_id INTEGER, crop_id INTEGER, sold_at TEXT, quantity REAL, unit TEXT, price REAL, buyer TEXT, notes TEXT, created_at TEXT, FOREIGN KEY(harvest_id) REFERENCES harvests(id), FOREIGN KEY(field_id) REFERENCES fields(id), FOREIGN KEY(crop_id) REFERENCES crops(id));',
      'CREATE TABLE IF NOT EXISTS costs (id INTEGER PRIMARY KEY NOT NULL, field_id INTEGER, crop_id INTEGER, category TEXT, amount REAL, currency TEXT, vendor TEXT, notes TEXT, spent_at TEXT, photo_uri TEXT, created_at TEXT, FOREIGN KEY(field_id) REFERENCES fields(id), FOREIGN KEY(crop_id) REFERENCES crops(id));',
      'CREATE TABLE IF NOT EXISTS field_media (id INTEGER PRIMARY KEY NOT NULL, field_id INTEGER, uri TEXT, media_type TEXT, created_at TEXT, FOREIGN KEY(field_id) REFERENCES fields(id));',
      'CREATE TABLE IF NOT EXISTS crop_media (id INTEGER PRIMARY KEY NOT NULL, crop_id INTEGER, uri TEXT, media_type TEXT, created_at TEXT, FOREIGN KEY(crop_id) REFERENCES crops(id));',
      'CREATE TABLE IF NOT EXISTS harvest_media (id INTEGER PRIMARY KEY NOT NULL, harvest_id INTEGER, uri TEXT, media_type TEXT, created_at TEXT, FOREIGN KEY(harvest_id) REFERENCES harvests(id));',
      'CREATE TABLE IF NOT EXISTS sale_media (id INTEGER PRIMARY KEY NOT NULL, sale_id INTEGER, uri TEXT, media_type TEXT, created_at TEXT, FOREIGN KEY(sale_id) REFERENCES sales(id));',
      'CREATE TABLE IF NOT EXISTS cost_media (id INTEGER PRIMARY KEY NOT NULL, cost_id INTEGER, uri TEXT, media_type TEXT, created_at TEXT, FOREIGN KEY(cost_id) REFERENCES costs(id));',
      'CREATE TABLE IF NOT EXISTS task_entry_media (id INTEGER PRIMARY KEY NOT NULL, entry_id INTEGER, uri TEXT, media_type TEXT, created_at TEXT, FOREIGN KEY(entry_id) REFERENCES daily_task_entries(id));',
      'CREATE INDEX IF NOT EXISTS idx_observations_field_id ON observations(field_id);',
      'CREATE INDEX IF NOT EXISTS idx_observations_crop_id ON observations(crop_id);',
      'CREATE INDEX IF NOT EXISTS idx_images_observation_id ON images(observation_id);',
      'CREATE INDEX IF NOT EXISTS idx_ml_inferences_image_id ON ml_inferences(image_id);',
      'CREATE INDEX IF NOT EXISTS idx_sensors_field_id ON sensors(field_id);',
      'CREATE INDEX IF NOT EXISTS idx_sensor_readings_sensor_id ON sensor_readings(sensor_id);',
      'CREATE INDEX IF NOT EXISTS idx_daily_task_entries_task_id ON daily_task_entries(task_id);',
      'CREATE INDEX IF NOT EXISTS idx_daily_task_entries_field_id ON daily_task_entries(field_id);',
      'CREATE INDEX IF NOT EXISTS idx_harvests_field_id ON harvests(field_id);',
      'CREATE INDEX IF NOT EXISTS idx_harvests_crop_id ON harvests(crop_id);',
      'CREATE INDEX IF NOT EXISTS idx_sales_harvest_id ON sales(harvest_id);',
      'CREATE INDEX IF NOT EXISTS idx_sales_crop_id ON sales(crop_id);',
      'CREATE INDEX IF NOT EXISTS idx_costs_field_id ON costs(field_id);',
      'CREATE INDEX IF NOT EXISTS idx_costs_crop_id ON costs(crop_id);',
      'CREATE INDEX IF NOT EXISTS idx_field_media_field_id ON field_media(field_id);',
      'CREATE INDEX IF NOT EXISTS idx_crop_media_crop_id ON crop_media(crop_id);',
      'CREATE INDEX IF NOT EXISTS idx_harvest_media_harvest_id ON harvest_media(harvest_id);',
      'CREATE INDEX IF NOT EXISTS idx_sale_media_sale_id ON sale_media(sale_id);',
      'CREATE INDEX IF NOT EXISTS idx_cost_media_cost_id ON cost_media(cost_id);',
      'CREATE INDEX IF NOT EXISTS idx_task_entry_media_entry_id ON task_entry_media(entry_id);',
    ].join('\n')
  );

  await ensureColumns(db, 'user_profile', userProfileColumns);
  await ensureColumns(db, 'fields', fieldColumns);
  await ensureColumns(db, 'crops', cropColumns);
  await ensureColumns(db, 'harvests', harvestColumns);
  await ensureColumns(db, 'sales', saleColumns);
  await ensureColumns(db, 'costs', costColumns);

  await db.execAsync(
    `INSERT INTO field_media (field_id, uri, media_type, created_at)
     SELECT f.id, f.photo_uri, 'image', f.created_at
     FROM fields f
     WHERE f.photo_uri IS NOT NULL AND TRIM(f.photo_uri) <> ''
       AND NOT EXISTS (
         SELECT 1 FROM field_media fm WHERE fm.field_id = f.id
       );`
  );

  await db.execAsync(
    `INSERT INTO crop_media (crop_id, uri, media_type, created_at)
     SELECT c.id, c.photo_uri, 'image', datetime('now')
     FROM crops c
     WHERE c.photo_uri IS NOT NULL AND TRIM(c.photo_uri) <> ''
       AND NOT EXISTS (
         SELECT 1 FROM crop_media cm WHERE cm.crop_id = c.id
       );`
  );

  await db.execAsync(
    `INSERT INTO harvest_media (harvest_id, uri, media_type, created_at)
     SELECT h.id, h.photo_uri, 'image', h.created_at
     FROM harvests h
     WHERE h.photo_uri IS NOT NULL AND TRIM(h.photo_uri) <> ''
       AND NOT EXISTS (
         SELECT 1 FROM harvest_media hm WHERE hm.harvest_id = h.id
       );`
  );

  await db.execAsync(
    `INSERT INTO cost_media (cost_id, uri, media_type, created_at)
     SELECT c.id, c.photo_uri, 'image', c.created_at
     FROM costs c
     WHERE c.photo_uri IS NOT NULL AND TRIM(c.photo_uri) <> ''
       AND NOT EXISTS (
         SELECT 1 FROM cost_media cm WHERE cm.cost_id = c.id
       );`
  );
}

async function ensureColumns(
  db: SQLite.SQLiteDatabase,
  table: string,
  columns: ColumnSpec[]
) {
  const existing = await db.getAllAsync<{ name: string }>(
    `PRAGMA table_info(${table});`
  );
  const names = new Set(existing.map((col) => col.name));

  for (const column of columns) {
    if (!names.has(column.name)) {
      await db.execAsync(
        `ALTER TABLE ${table} ADD COLUMN ${column.name} ${column.type};`
      );
    }
  }
}