-- Users table
CREATE TABLE users (
  id VARCHAR(36) PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255),
  key_salt VARCHAR(64) NOT NULL,
  oauth_provider ENUM('local', 'google', 'github') DEFAULT 'local',
  oauth_id VARCHAR(255),
  avatar_url TEXT,
  email_verified BOOLEAN DEFAULT FALSE,
  verification_token VARCHAR(255),
  verification_token_expires DATETIME,
  verification_blob TEXT,
  recovery_blob TEXT,
  recovery_salt VARCHAR(64),
  bip39_verified BOOLEAN DEFAULT FALSE,
  pin_attempts INT DEFAULT 0,
  pin_locked_until DATETIME,
  reset_token_hash VARCHAR(255),
  reset_token_expires DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_oauth (oauth_provider, oauth_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Accounts table (financial accounts)
CREATE TABLE accounts (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  type ENUM('personal', 'shared', 'business') DEFAULT 'personal',
  currency VARCHAR(3) DEFAULT 'USD',
  created_by VARCHAR(36) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Account keys (encrypted with User Keys)
CREATE TABLE account_keys (
  account_id VARCHAR(36) NOT NULL,
  user_id VARCHAR(36) NOT NULL,
  encrypted_account_key TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (account_id, user_id),
  FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Transactions table (E2E encrypted)
CREATE TABLE transactions (
  id VARCHAR(36) PRIMARY KEY,
  account_id VARCHAR(36) NOT NULL,
  subcategory_id VARCHAR(36),
  date DATE NOT NULL,
  description_encrypted TEXT NOT NULL,
  amount_encrypted TEXT NOT NULL,
  amount_sign ENUM('positive', 'negative', 'zero') NOT NULL,
  bank_category_encrypted TEXT,
  bank_subcategory_encrypted TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
  INDEX idx_account_date (account_id, date),
  INDEX idx_amount_sign (amount_sign)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Categories table (E2E encrypted)
CREATE TABLE categories (
  id VARCHAR(36) PRIMARY KEY,
  account_id VARCHAR(36) NOT NULL,
  name_encrypted TEXT NOT NULL,
  color VARCHAR(7) NOT NULL,
  icon VARCHAR(10),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
  INDEX idx_account (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add more tables as needed (subcategories, budgets, invitations, etc.)

# Server
PORT=3001
FRONTEND_URL=http://localhost:3000

# Database - Aiven MySQL
DB_HOST=mysql-xxxxxx.aivencloud.com
DB_PORT=25060
DB_USER=avnadmin
DB_PASSWORD=your-secure-password
DB_DATABASE=home_account

# JWT
JWT_SECRET=your-64-char-hex-secret-here
JWT_EXPIRES_IN=7d

# OAuth (optional)
GOOGLE_CLIENT_ID=your-client-id.apps.googleusercontent.com
GOOGLE_CLIENT_SECRET=your-client-secret
GITHUB_CLIENT_ID=your-github-client-id
GITHUB_CLIENT_SECRET=your-github-client-secret
OAUTH_CALLBACK_URL=http://localhost:3001

# EmailJS (optional)
EMAILJS_SERVICE_ID=service_xxxxxxx
EMAILJS_TEMPLATE_ID=template_xxxxxxx
EMAILJS_PUBLIC_KEY=your-public-key
EMAILJS_PRIVATE_KEY=your-private-key

// frontend/public/manifest.json
{
  "name": "Home Account",
  "short_name": "HomeAccount",
  "description": "Privacy-first personal finance tracker",
  "start_url": "/",
  "display": "standalone",
  "background_color": "#ffffff",
  "theme_color": "#10B981",
  "icons": [
    {
      "src": "/icon-192.png",
      "sizes": "192x192",
      "type": "image/png"
    },
    {
      "src": "/icon-512.png",
      "sizes": "512x512",
      "type": "image/png"
    }
  ]
}