export const CREATE_TABLES = `
  CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    role TEXT NOT NULL DEFAULT 'partner_a',
    locale TEXT NOT NULL DEFAULT 'fr',
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  );

  CREATE TABLE IF NOT EXISTS households (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    invite_code TEXT UNIQUE NOT NULL,
    status TEXT NOT NULL DEFAULT 'cohabiting',
    children INTEGER NOT NULL DEFAULT 0,
    housing_type TEXT NOT NULL DEFAULT 'rent',
    objectives TEXT NOT NULL DEFAULT '[]',
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  );

  CREATE TABLE IF NOT EXISTS incomes (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    gross_annual REAL NOT NULL DEFAULT 0,
    net_monthly REAL NOT NULL DEFAULT 0,
    other_income REAL NOT NULL DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS expenses (
    id TEXT PRIMARY KEY,
    household_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    amount REAL NOT NULL,
    category TEXT NOT NULL,
    note TEXT DEFAULT '',
    date TEXT NOT NULL,
    is_common INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (household_id) REFERENCES households(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS budgets (
    id TEXT PRIMARY KEY,
    household_id TEXT NOT NULL,
    category TEXT NOT NULL,
    "limit" REAL NOT NULL,
    period TEXT NOT NULL DEFAULT 'monthly',
    FOREIGN KEY (household_id) REFERENCES households(id)
  );

  CREATE TABLE IF NOT EXISTS equity_snapshots (
    id TEXT PRIMARY KEY,
    household_id TEXT NOT NULL,
    date TEXT NOT NULL,
    income_a REAL NOT NULL,
    income_b REAL NOT NULL,
    common_charges REAL NOT NULL,
    domestic_hours_a REAL NOT NULL,
    domestic_hours_b REAL NOT NULL,
    domestic_hourly_rate REAL NOT NULL DEFAULT 18,
    ratio_a REAL NOT NULL,
    ratio_b REAL NOT NULL,
    adjusted_ratio_a REAL NOT NULL,
    adjusted_ratio_b REAL NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (household_id) REFERENCES households(id)
  );

  CREATE TABLE IF NOT EXISTS domestic_work (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    hours_per_week REAL NOT NULL DEFAULT 0,
    tasks TEXT NOT NULL DEFAULT '[]',
    FOREIGN KEY (user_id) REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
  );

  CREATE TABLE IF NOT EXISTS notifications (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    type TEXT NOT NULL DEFAULT 'info',
    read INTEGER NOT NULL DEFAULT 0,
    date TEXT NOT NULL DEFAULT (datetime('now'))
  );
`;
