CREATE TABLE IF NOT EXISTS users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS categories (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(100) NOT NULL,
  UNIQUE(user_id, name)
);

CREATE TABLE IF NOT EXISTS expenses (
  id BIGSERIAL PRIMARY KEY,

  user_id BIGINT NOT NULL
    REFERENCES users(id)
    ON DELETE CASCADE,

  title VARCHAR(255) NOT NULL,

  category_id BIGINT REFERENCES categories(id),

  amount_cents BIGINT NOT NULL CHECK (amount_cents >= 0),

  currency VARCHAR(3) NOT NULL DEFAULT 'CHF',

  expense_date DATE NOT NULL DEFAULT CURRENT_DATE,

  note TEXT,

  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

CREATE INDEX IF NOT EXISTS idx_expenses_user_id
  ON expenses(user_id);

CREATE INDEX IF NOT EXISTS idx_expenses_user_date
  ON expenses(user_id, expense_date);

CREATE INDEX IF NOT EXISTS idx_expenses_category
  ON expenses(user_id, category_id);

