ProfitPulse

-- Users table for authentication and profile management
CREATE TABLE public.users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Accounts table to store connections to external platforms
CREATE TYPE platform_enum AS ENUM ('stripe', 'gumroad', 'lemonsqueezy', 'paypal');
CREATE TABLE public.accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  platform platform_enum NOT NULL,
  -- Sensitive credentials should be encrypted at the application level before storing
  access_token TEXT NOT NULL, 
  refresh_token TEXT, -- Optional, for OAuth flows
  credentials_json JSONB, -- For platform-specific keys or settings
  last_synced_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_accounts_user_id ON public.accounts(user_id);

-- Transactions table to store normalized transaction data from all platforms
CREATE TYPE transaction_type_enum AS ENUM ('sale', 'refund', 'chargeback');
CREATE TYPE transaction_status_enum AS ENUM ('completed', 'pending', 'failed');
CREATE TABLE public.transactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  account_id UUID NOT NULL REFERENCES public.accounts(id) ON DELETE CASCADE,
  platform platform_enum NOT NULL,
  external_transaction_id TEXT NOT NULL, -- Original transaction ID from the platform
  amount NUMERIC(12, 2) NOT NULL, -- Stored as cents or base unit, e.g., 1000 for $10.00
  currency CHAR(3) NOT NULL, -- ISO 4217 currency code, e.g., 'USD'
  type transaction_type_enum NOT NULL,
  status transaction_status_enum NOT NULL,
  description TEXT,
  transaction_date TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(account_id, external_transaction_id) -- Ensure no duplicate transactions per account
);
CREATE INDEX idx_transactions_user_id ON public.transactions(user_id);
CREATE INDEX idx_transactions_account_id ON public.transactions(account_id);
CREATE INDEX idx_transactions_date ON public.transactions(transaction_date DESC);

-- Reports table (optional, for caching or saving specific report configurations)
CREATE TABLE public.reports (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  config_json JSONB, -- Stores parameters used to generate the report
  generated_at TIMESTAMPTZ DEFAULT now()
);