CREATE TABLE profiles (
  id uuid references auth.users primary key,
  email text,
  bridge_user_uuid text,
  created_at timestamptz default now()
);

CREATE TABLE bank_items (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references profiles(id) on delete cascade,
  bridge_item_id integer,
  bank_name text,
  status text default 'active',
  created_at timestamptz default now()
);

CREATE TABLE accounts (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references profiles(id) on delete cascade,
  bridge_account_id integer,
  name text,
  balance decimal,
  currency text default 'EUR',
  type text,
  updated_at timestamptz default now()
);

CREATE TABLE transactions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references profiles(id) on delete cascade,
  account_id uuid references accounts(id) on delete cascade,
  bridge_transaction_id integer unique,
  amount decimal not null,
  label text,
  category_id integer,
  category_name text,
  date date not null,
  deleted boolean default false,
  custom_category text,
  created_at timestamptz default now()
);

CREATE INDEX idx_transactions_user_id ON transactions(user_id);
CREATE INDEX idx_transactions_date ON transactions(date);
CREATE INDEX idx_transactions_deleted ON transactions(deleted);
CREATE INDEX idx_transactions_bridge_id ON transactions(bridge_transaction_id);
CREATE INDEX idx_accounts_user_id ON accounts(user_id);
CREATE INDEX idx_bank_items_user_id ON bank_items(user_id);
