# Create database roles
./scripts/create_roles.sh

# Run all migrations
./scripts/run_migrations.sh

-- Anonymous role (not logged in)
CREATE ROLE anon NOLOGIN;

-- Authenticated role (logged in users)
CREATE ROLE authenticated NOLOGIN;

-- Grant schema access
GRANT USAGE ON SCHEMA public TO anon, authenticated, 
  auth_admin, subscription_admin;

-- Authenticated users have full access to most tables
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO authenticated;

-- But cannot use TRIGGER or TRUNCATE
REVOKE TRIGGER, TRUNCATE ON ALL TABLES IN SCHEMA public 
  FROM authenticated;

-- Example: Transaction access policy
CREATE POLICY limit_transactions_select ON transactions FOR SELECT
TO authenticated USING (
  -- Personal transactions
  (
    organization_id IS NULL
    AND user_id = uid ()
  )
  -- OR organization transactions (with role check)
  OR (
    organization_id = org_id ()
    AND (
      check_ac_current ('transaction', 'list')
      OR check_ac_current ('transaction', 'get')
    )
  )
);

-- Check if role has permission for action on resource
CREATE OR REPLACE FUNCTION check_ac (
  p_role TEXT, 
  p_resource TEXT, 
  p_action TEXT
) RETURNS BOOLEAN AS $$
DECLARE
  v_allowed_actions TEXT[];
BEGIN
  CASE p_role
    WHEN 'owner', 'admin', 'editor' THEN
      CASE p_resource
        WHEN 'transaction', 'subscription', 'accounts' THEN
          v_allowed_actions := ARRAY['list', 'get', 'create', 'update', 'delete'];
        ELSE
          RETURN FALSE;
      END CASE;
    WHEN 'viewer' THEN
      CASE p_resource
        WHEN 'transaction', 'subscription', 'accounts' THEN
          v_allowed_actions := ARRAY['list', 'get'];
        ELSE
          RETURN FALSE;
      END CASE;
    ELSE
      RETURN FALSE;
  END CASE;
  
  RETURN p_action = ANY(v_allowed_actions);
END
$$ LANGUAGE plpgsql STABLE;

-- Aggregate transaction data for widgets
CREATE OR REPLACE FUNCTION get_transaction_aggregate(
  group_by_metric TEXT,
  time_interval TEXT,
  agg_function TEXT
)
RETURNS TABLE (
  period TIMESTAMP,
  metric_value TEXT,
  aggregate_result NUMERIC
) AS $$
BEGIN
  IF agg_function NOT IN ('sum', 'avg', 'count', 'min', 'max') THEN
    RAISE EXCEPTION 'Invalid aggregate';
  END IF;

  RETURN QUERY EXECUTE format(
    'SELECT
       date_trunc(%s, transaction_date) AS period,
       CAST(%s AS TEXT) AS metric_value,
       %s(amount)::NUMERIC AS aggregate_result
     FROM transactions
     GROUP BY 1, 2
     ORDER BY 1 DESC, 2 ASC',
    quote_literal(time_interval),
    quote_ident(group_by_metric),
    quote_ident(agg_function)
  );
END;
$$ LANGUAGE plpgsql;

// From packages/core/db-pool.ts
import { Pool } from "pg";

const createPool = (config: PoolConfig) => {
  return new Pool({
    database: process.env.POSTGRES_DATABASE,
    host: process.env.POSTGRES_HOST,
    port: Number(process.env.POSTGRES_PORT),
    ...config,
  });
};

export const getAuthAdminClient = () => {
  return createPool({
    user: process.env.POSTGRES_AUTH_ADMIN_USER,
    password: process.env.POSTGRES_AUTH_ADMIN_PASSWORD,
  });
};

export const getSubscriptionAdminClient = () => {
  return createPool({
    user: process.env.POSTGRES_SUBSCRIPTION_ADMIN_USER,
    password: process.env.POSTGRES_SUBSCRIPTION_ADMIN_PASSWORD,
  });
};

# postgrest.conf
db-uri = "postgres://authenticator:password@localhost:5432/budgetbee"
db-schemas = "public"
db-anon-role = "anon"
jwt-secret = "your-jwt-secret"

# Full database backup
pg_dump -U budgetbee_user -d budgetbee -F c -f budgetbee_backup.dump

# SQL format backup
pg_dump -U budgetbee_user -d budgetbee > budgetbee_backup.sql

# Backup specific table
pg_dump -U budgetbee_user -d budgetbee -t transactions > transactions_backup.sql

# Restore from custom format
pg_restore -U budgetbee_user -d budgetbee budgetbee_backup.dump

# Restore from SQL format
psql -U budgetbee_user -d budgetbee < budgetbee_backup.sql

-- Transaction queries
CREATE INDEX idx_transactions_user_id ON transactions(user_id);
CREATE INDEX idx_transactions_org_id ON transactions(organization_id);
CREATE INDEX idx_transactions_date ON transactions(transaction_date DESC);
CREATE INDEX idx_transactions_category ON transactions(category_id);

-- Category queries
CREATE INDEX idx_categories_user_id ON categories(user_id);
CREATE INDEX idx_categories_org_id ON categories(organization_id);

-- Session lookups
CREATE INDEX idx_sessions_user_id ON sessions(user_id);

max_connections = 100
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB