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 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);',
    ].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);
}

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};`
      );
    }
  }
}