# Database

- [TL;DR;](#tldr)
- [Views](#views)
  - [`AllTimeExpenses`](#alltimeexpenses)
  - [`AllTimeIncome`](#alltimeincome)
  - [`BudgetProgress`](#)
  - [`CurrentMonthExpenses`](#currentmonthexpenses)
  - [`CurrentMonthIncome`](#)
  - [`DailyIncome`](#dailyincome)
  - [`DailyExpense`](#dailyexpense)
- [RPCs](#rpcs)
  - [`get_daily_transactions`](#getdailytransactionsstartdate-date-enddate-date-requesteddata-text)
  - [`get_category_stats`](#get-category-stats-type-text)
  - [`get_pm_stats`](#get-pm-stats-type-text)
  - [`get_monthly_balance`](#get-monthly-balance-months-bigint)
  - [`get_monthly_balance_avg`](#get-monthly-balance-avg-months-bigint)
  - [`getIncome`](#getincomeuserid-uuid-startdate-date-enddate-date)
  - [`getExpense`](#getexpensesuserid-uuid-startdate-date-enddate-date)
- [`profiles`](#profiles)
- [`transactions`](#transactions)
- [`budget`](#budget)
- [`subscriptions`](#subscriptions)
- [`categories`](#categories)
- [`paymentMethods`](#paymentMethods)
- [`feedback`](#feedback)

## TL;DR;

> TODO: Add file which contains the statements to setup the database in an Supabase-environment

## Views

### `AllTimeExpenses`

```sql
CREATE OR REPLACE
  VIEW "AllTimeExpenses"
  AS SELECT
    SUM(transactions.amount),
    json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
    transactions.created_by
  FROM
    transactions
  INNER JOIN categories
    ON categories.id = transactions.category
  AND transactions.amount < 0
  AND transactions.date::DATE <= now()::DATE
  GROUP BY
    categories.id,
    transactions.created_by;
```

### `AllTimeIncome`

```sql
CREATE OR REPLACE
  VIEW "AllTimeIncome"
  AS SELECT
    SUM(transactions.amount),
    json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
    transactions.created_by
  FROM
    transactions
  INNER JOIN categories
    ON categories.id = transactions.category
  AND transactions.amount > 0
  AND transactions.date::DATE <= now()::DATE
  GROUP BY
    categories.id,
    transactions.created_by;
```

### `BudgetProgress`

```sql
CREATE OR REPLACE
  VIEW "BudgetProgress"
  AS SELECT
    budget.id,
    json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
    budget.budget,
    (SELECT
        SUM(transactions.amount)
      FROM
        transactions
      WHERE
        transactions.amount < 0
      AND
        transactions.date::DATE <= now()::DATE
      AND
        extract(month FROM transactions.date) = extract(month FROM now())
      AND
        extract(year FROM transactions.date) = extract(year FROM now())
      AND transactions.category = budget.category
    ) as "currentlySpent",
    budget.created_by,
    budget.inserted_at,
    budget.updated_at
  FROM budget
  LEFT JOIN categories
    ON categories.id = budget.category;
```

### `CurrentMonthExpenses`

```sql
CREATE OR REPLACE
  VIEW "CurrentMonthExpenses"
  AS SELECT
    SUM(transactions.amount),
    json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
    transactions.created_by
  FROM
    transactions
  INNER JOIN categories
    ON categories.id = transactions.category
  AND
    transactions.amount < 0
  AND
    transactions.date::DATE <= now()::DATE
  AND
    extract(month FROM transactions.date) = extract(month FROM now())
  AND
    extract(year FROM transactions.date) = extract(year FROM now())
  GROUP BY
    categories.id,
    transactions.created_by;
```

### `CurrentMonthIncome`

```sql
CREATE OR REPLACE
  VIEW "CurrentMonthIncome"
  AS SELECT
    SUM(transactions.amount),
    json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
    transactions.created_by
  FROM
    transactions
  INNER JOIN categories
    ON categories.id = transactions.category
  AND
    transactions.amount > 0
  AND
    transactions.date::DATE <= now()::DATE
  AND
    extract(month FROM transactions.date) = extract(month FROM now())
  AND
    extract(year FROM transactions.date) = extract(year FROM now())
  GROUP BY
    categories.id,
    transactions.created_by;
```

### `DailyIncome`

```sql
CREATE OR REPLACE
  VIEW "DailyIncome"
  AS SELECT
      transactions.date::DATE,
      sum(transactions.amount),
      transactions.created_by
    FROM transactions
    WHERE transactions.amount > 0
    AND transactions.date::DATE <= now()::DATE
    GROUP BY 1, transactions.created_by
    ORDER BY transactions.date::DATE ASC;
```

### `DailyExpense`

```sql
CREATE OR REPLACE
  VIEW "DailyExpense"
  AS SELECT
      transactions.date::DATE,
      sum(transactions.amount),
      transactions.created_by
    FROM transactions
    WHERE transactions.amount < 0
    AND transactions.date::DATE <= now()::DATE
    GROUP BY 1, transactions.created_by
    ORDER BY transactions.date::DATE ASC;
```

---

## RPCs

### `get_daily_transactions(start_date date, end_date date, requested_data text)`

```sql
CREATE OR REPLACE FUNCTION get_daily_transactions(start_date date, end_date date, requested_data text)
    RETURNS TABLE(date date, amount double precision)
    LANGUAGE sql
AS $$
  SELECT
        dates.transaction_date as date,
        (SELECT COALESCE(SUM(t.amount), 0)
          FROM transactions t
          WHERE date_trunc('day', t.date) = dates.transaction_date
            AND requested_data = TRIM(requested_data)
            AND (
              (requested_data = 'INCOME' AND t.amount >= 0)
              OR (requested_data = 'SPENDINGS' AND t.amount <= 0)
              OR (requested_data = 'BALANCE')
            ))
      FROM (
        SELECT generate_series(start_date, end_date, '1 day') AS transaction_date
      ) AS dates
      GROUP BY
        dates.transaction_date
      ORDER BY
        dates.transaction_date ASC;
$$;

```

---

### `get_category_stats(type text)`

> Possible type values are "COUNT" | "EARNINGS" | "SPENDINGS"

```sql
create function get_category_stats(type text)
    returns TABLE(value double precision, category jsonb)
    language sql
as
$$
  SELECT
        CASE
           WHEN type = 'COUNT' THEN COUNT(t."category")
           WHEN type = 'EARNINGS' OR type = 'SPENDINGS' THEN SUM(t."amount")
        END "value",
        json_build_object('id', "categories".id,'name',"categories".name, 'description', "categories".description) as "category"
      FROM transactions t
      LEFT JOIN "categories"
        ON "categories".id = t."category"
      WHERE
        (type = 'EARNINGS' AND t.amount > 0) OR (type = 'SPENDINGS' AND t.amount < 0) OR (type = 'COUNT')
      GROUP BY
        t."category", "categories".id;
$$;
```

---

### `get_pm_stats(type text)`

> Possible type values are "COUNT" | "EARNINGS" | "SPENDINGS"

```sql
create function get_pm_stats(type text)
    returns TABLE(value double precision, "paymentMethod" jsonb)
    language sql
as
$$
  SELECT
        CASE
           WHEN type = 'COUNT' THEN COUNT(t."paymentMethod")
           WHEN type = 'EARNINGS' OR type = 'SPENDINGS' THEN SUM(t."amount")
        END "value",
        json_build_object('id', "paymentMethods".id,'name',"paymentMethods".name, 'address', "paymentMethods".address, 'provider', "paymentMethods".provider, 'description', "paymentMethods".description) as "paymentMethod"
      FROM transactions t
      LEFT JOIN "paymentMethods"
        ON "paymentMethods".id = t."paymentMethod"
      WHERE
        (type = 'EARNINGS' AND t.amount > 0) OR (type = 'SPENDINGS' AND t.amount < 0) OR (type = 'COUNT')
      GROUP BY
        t."paymentMethod", "paymentMethods".id;
$$;
```

---

### `get_monthly_balance(months bigint)`

```sql
create function get_monthly_balance(months bigint)
    returns TABLE(year bigint, month bigint, sum double precision)
    language sql
as
$$
  SELECT
    extract(YEAR FROM transactions.date) as Year,
    extract(MONTH FROM transactions.date) as Month,
    SUM(transactions.amount)
  FROM
    transactions
  GROUP BY
    extract(MONTH FROM transactions.date),
    extract(YEAR FROM transactions.date)
  ORDER BY 
    extract(YEAR FROM transactions.date) DESC,
    extract(MONTH FROM transactions.date) DESC
  LIMIT months;
$$;
```
---

### `get_monthly_balance_avg(months bigint)`

```sql
create function get_monthly_balance_avg(months bigint)
    returns double precision
    language sql
as
$$
  SELECT avg(sum) FROM get_monthly_balance(months);
$$;
```

---

### `getIncome(userId uuid, startDate Date, endDate Date)`

```sql
CREATE OR REPLACE
FUNCTION "getIncome"("userId" uuid, "startDate" Date, "endDate" Date)
RETURNS TABLE (
  sum float8,
  category json,
  created_by uuid
)
as $$
  SELECT
      SUM(transactions.amount),
      json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
      transactions.created_by
    FROM
      transactions
    INNER JOIN categories
      ON categories.id = transactions.category
    AND
      transactions.amount > 0
    AND
      transactions.date::DATE >= "startDate"
    AND
      transactions.date::DATE <= "endDate"
    WHERE
      transactions.created_by = "userId"
    GROUP BY
      categories.id,
      transactions.created_by;
$$ language sql;
```

### `getExpenses(userId uuid, startDate Date, endDate Date)`

```sql
CREATE OR REPLACE
FUNCTION "getExpense"("userId" uuid, "startDate" Date, "endDate" Date)
RETURNS TABLE (
  sum float8,
  category json,
  created_by uuid
)
as $$
  SELECT
      SUM(transactions.amount),
      json_build_object('id', categories.id,'name',categories.name, 'description', categories.description) as category,
      transactions.created_by
    FROM
      transactions
    INNER JOIN categories
      ON categories.id = transactions.category
    AND
      transactions.amount < 0
    AND
      transactions.date::DATE >= "startDate"
    AND
      transactions.date::DATE <= "endDate"
    WHERE
      transactions.created_by = "userId"
    GROUP BY
      categories.id,
      transactions.created_by;
$$ language sql;
```

## `transactions`

### Columns

| Column          | Type           | Description                          |
| --------------- | -------------- | ------------------------------------ |
| `id`            | `int8`         | `PK`                                 |
| `category`      | `int8`         | `FK` `category` -> `categories.id`   |
| `paymentMethod` | `int8`         | `FK` `category` -> `categories.id`   |
| `receiver`      | `text`         |                                      |
| `amount`        | `float8`       |                                      |
| `description`   | `text or null` |                                      |
| `date`          | `timestampz`   |                                      |
| `created_by`    | `uuid`         | `FK` `created_by` -> `auth.users.id` |
| `inserted_at`   | `timestampz`   |                                      |
| `updated_at`    | `timestampz`   |                                      |

### SQL

```sql
create table transactions (
  id bigint generated by default as identity primary key,
  category bigint references public.categories not null,
  paymentMethod bigint references public."paymentMethods" not null,
  receiver text not null,
  description text,
  amount float not null,
  date timestamp with time zone not null,
  created_by uuid references auth.users not null,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);
```

### Policies

**Enable**

```sql
alter table "public"."transactions"
  enable row level security;
```

**All _(for authentificated)_**

_Used for the [web-app](https://github.com/BudgetBuddyDE/Webapp)_

```sql
CREATE POLICY "Users can handle their transactions." ON "public"."transactions"
AS PERMISSIVE FOR ALL
TO public
USING ((uid() = created_by))
WITH CHECK ((uid() = created_by))
```

**All _(all, insert alone won't work)_**

_Used for the [subscription-service](https://github.com/BudgetBuddyDE/Subscription-Service)_

```sql
CREATE POLICY "Subscription-Service can insert transactions." ON "public"."transactions"
AS PERMISSIVE FOR ALL
TO authenticated
USING ((email() = 'EMAIL'::text))
WITH CHECK ((email() = 'EMAIL'::text))
```

---

## `budget`

### Columns

| Column        | Type         | Description                          |
| ------------- | ------------ | ------------------------------------ |
| `id`          | `int8`       | `PK`                                 |
| `category`    | `int8`       | `FK` `category` -> `categories.id`   |
| `budget`      | `float8`     |                                      |
| `created_by`  | `uuid`       | `FK` `created_by` -> `auth.users.id` |
| `inserted_at` | `timestampz` |                                      |
| `updated_at`  | `timestampz` |                                      |

### SQL

```sql
create table budget (
  id bigint generated by default as identity primary key,
  category bigint references public.categories not null,
  budget float not null,
  created_by uuid references auth.users not null,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);
```

### Policies

**Enable**

```sql
alter table "public"."budget"
  enable row level security;
```

**All**

```sql
CREATE POLICY "Users can handle their budget." ON "public"."transactions"
AS PERMISSIVE FOR ALL
TO public
USING ((uid() = created_by))
WITH CHECK ((uid() = created_by))
```

---

## `subscriptions`

### Columns

| Column          | Type           | Description                          |
| --------------- | -------------- | ------------------------------------ |
| `id`            | `int8`         | `PK`                                 |
| `category`      | `int8`         | `FK` `category` -> `categories.id`   |
| `paymentMethod` | `int8`         | `FK` `category` -> `categories.id`   |
| `receiver`      | `text`         |                                      |
| `amount`        | `float8`       |                                      |
| `description`   | `text or null` |                                      |
| `execute_at`    | `int8`         |                                      |
| `created_by`    | `uuid`         | `FK` `created_by` -> `auth.users.id` |
| `inserted_at`   | `timestampz`   |                                      |
| `updated_at`    | `timestampz`   |                                      |

### SQL

```sql
create table subscriptions (
  id bigint generated by default as identity primary key,
  category bigint references public.categories not null,
  paymentMethod bigint references public."paymentMethods" not null,
  receiver text not null,
  description text,
  amount float not null,
  execute_at bigint not null,
  created_by uuid references auth.users not null,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);
```

### Policies

**Enable**

```sql
alter table "public"."subscriptions"
  enable row level security;
```

**All _(for authentificated)_**

_Used for the [app](https://github.com/BudgetBuddyDE/Webapp)_

```sql
CREATE POLICY "Authentificated users can handle their subscription." ON "public"."subscriptions"
AS PERMISSIVE FOR ALL
TO authenticated
USING ((uid() = created_by))
WITH CHECK ((uid() = created_by))
```

**Select _(for Subscription-Service)_**

_Used for the [subscription-service](https://github.com/BudgetBuddyDE/Subscription-Service)_

```sql
CREATE POLICY "Subscription-Service can select subscriptions." ON "public"."subscriptions"
AS PERMISSIVE FOR SELECT
TO authenticated
USING ((email() = 'EMAIL'::text))
```

---

## `categories`

### Columns

| Column        | Type           | Description                        |
| ------------- | -------------- | ---------------------------------- |
| `id`          | `int8`         | `PK`                               |
| `name`        | `text`         |                                    |
| `description` | `text or null` |                                    |
| `created_by`  | `uuid`         | `FK` `created_by`->`auth.users.id` |
| `inserted_at` | `timestampz`   |                                    |
| `updated_at`  | `timestampz`   |                                    |

### SQL

```sql
create table categories (
  id int8 generated by default as identity primary key,
  name text not null,
  description text,
  created_by uuid references auth.users not null,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);
```

### Policies

**Enable**

```sql
alter table "public"."categories"
  enable row level security;
```

**All**

```sql
CREATE POLICY "Users can handle their categories." ON "public"."categories"
AS PERMISSIVE FOR ALL
TO public
USING ((uid() = created_by))
WITH CHECK ((uid() = created_by))
```

---

## `paymentMethods`

### Columns

| Column        | Type           | Description                          |
| ------------- | -------------- | ------------------------------------ |
| `id`          | `int8`         | `PK`                                 |
| `name`        | `text`         |                                      |
| `provider`    | `text`         |                                      |
| `address`     | `text`         |                                      |
| `description` | `text or null` |                                      |
| `created_by`  | `uuid`         | `FK` `created_by` -> `auth.users.id` |
| `inserted_at` | `timestampz`   |                                      |
| `updated_at`  | `timestampz`   |                                      |

### SQL

```sql
create table paymentMethods (
  id int8 generated by default as identity primary key,
  name text not null,
  provider text not null,
  address text not null,
  description text,
  created_by uuid references auth.users not null,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);
```

### Policies

**Enable**

```sql
alter table "public"."paymentMethods"
  enable row level security;
```

**All**

```sql
CREATE POLICY "Users can handle their payment-methods." ON "public"."paymentMethods"
AS PERMISSIVE FOR ALL
TO public
USING ((uid() = created_by))
WITH CHECK ((uid() = created_by))
```

## `feedback`

### Columns

| Column        | Type           | Description     |
| ------------- | -------------- | --------------- |
| `id`          | `int8`         | `PK`            |
| `rating`      | `float4`       |                 |
| `text`        | `text or null` |                 |
| `share`       | `boolean`      | Default `false` |
| `author`      | `uuid or null` | Default `null`  |
| `inserted_at` | `timestampz`   |                 |

### SQL

```sql
create table feedback (
  id bigint generated by default as identity primary key,
  rating float4 not null,
  text text,
  share boolean default false,
  author uuid default null,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null
);
```