-- WaterBudget Database Schema
-- SQLite with WAL mode for high-performance shared hosting

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'user' CHECK(role IN ('admin', 'user')),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT,
    deleted_at TEXT
);

-- Farms table
CREATE TABLE IF NOT EXISTS farms (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    latitude REAL NOT NULL DEFAULT 0,
    longitude REAL NOT NULL DEFAULT 0,
    location TEXT DEFAULT '',
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT,
    deleted_at TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Irrigation zones table
CREATE TABLE IF NOT EXISTS zones (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    farm_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    crop_type TEXT NOT NULL DEFAULT 'custom',
    area REAL NOT NULL DEFAULT 0,
    field_capacity REAL NOT NULL DEFAULT 180,
    wilting_point REAL NOT NULL DEFAULT 80,
    root_depth REAL NOT NULL DEFAULT 600,
    irrigation_efficiency REAL NOT NULL DEFAULT 0.85,
    crop_coefficients TEXT DEFAULT '[]',
    soil_type TEXT DEFAULT '',
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT,
    deleted_at TEXT,
    FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
);

-- Weather cache table
CREATE TABLE IF NOT EXISTS weather_cache (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    latitude REAL NOT NULL,
    longitude REAL NOT NULL,
    data TEXT NOT NULL,
    source TEXT NOT NULL DEFAULT 'open-meteo',
    fetched_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Budget results table
CREATE TABLE IF NOT EXISTS budget_results (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    zone_id INTEGER NOT NULL,
    date TEXT NOT NULL,
    eto REAL DEFAULT 0,
    etc REAL DEFAULT 0,
    rain REAL DEFAULT 0,
    irrigation REAL DEFAULT 0,
    moisture REAL DEFAULT 0,
    deficit REAL DEFAULT 0,
    irrigation_needed REAL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (zone_id) REFERENCES zones(id) ON DELETE CASCADE
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_farms_user_id ON farms(user_id);
CREATE INDEX IF NOT EXISTS idx_zones_farm_id ON zones(farm_id);
CREATE INDEX IF NOT EXISTS idx_weather_cache_lat_lon ON weather_cache(latitude, longitude);
CREATE INDEX IF NOT EXISTS idx_weather_cache_fetched ON weather_cache(fetched_at);
CREATE INDEX IF NOT EXISTS idx_budget_results_zone_date ON budget_results(zone_id, date);