-- Schema for foi de parcurs
CREATE TABLE IF NOT EXISTS vehicles (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  plate TEXT UNIQUE NOT NULL,
  make_model TEXT,
  category TEXT NOT NULL,
  norma_l_100km REAL NOT NULL,
  odometer_start_month INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS drivers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  full_name TEXT NOT NULL,
  employee_code TEXT
);

CREATE TABLE IF NOT EXISTS clients (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  address TEXT
);

CREATE TABLE IF NOT EXISTS routes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  client_id INTEGER NOT NULL,
  from_loc TEXT NOT NULL,
  to_loc TEXT NOT NULL,
  km REAL NOT NULL,
  minutes INTEGER DEFAULT 60,
  purpose TEXT DEFAULT 'Vizita client',
  window_start TEXT,
  window_end TEXT,
  FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS month_plans (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  vehicle_id INTEGER NOT NULL,
  year INTEGER NOT NULL,
  month INTEGER NOT NULL,
  liters_est REAL NOT NULL,
  target_km REAL NOT NULL,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(vehicle_id, year, month),
  FOREIGN KEY(vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS plan_days (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  plan_id INTEGER NOT NULL,
  date TEXT NOT NULL,
  odo_start INTEGER NOT NULL,
  odo_end INTEGER NOT NULL,
  status TEXT DEFAULT 'PLANNED',
  FOREIGN KEY(plan_id) REFERENCES month_plans(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS plan_trips (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  day_id INTEGER NOT NULL,
  route_id INTEGER NOT NULL,
  start_time TEXT,
  end_time TEXT,
  km REAL NOT NULL,
  purpose TEXT NOT NULL,
  notes TEXT,
  FOREIGN KEY(day_id) REFERENCES plan_days(id) ON DELETE CASCADE,
  FOREIGN KEY(route_id) REFERENCES routes(id) ON DELETE RESTRICT
);