writekit/internal/billing/store.go

package billing

import (
	"context"
	"database/sql"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
)

type Store struct {
	db *pgxpool.Pool
}

func NewStore(db *pgxpool.Pool) *Store {
	return &Store{db: db}
}

type Tier struct {
	ID             string
	TenantID       string
	Name           string
	PriceCents     int
	Description    string
	LemonVariantID string
	Active         bool
	CreatedAt      time.Time
}

type Subscription struct {
	ID                  string
	TenantID            string
	UserID              string
	TierID              string
	TierName            string
	Status              string
	LemonSubscriptionID string
	LemonCustomerID     string
	AmountCents         int
	CurrentPeriodStart  time.Time
	CurrentPeriodEnd    time.Time
	CancelledAt         *time.Time
	CreatedAt           time.Time
}

type Donation struct {
	ID           string
	TenantID     string
	UserID       string
	DonorEmail   string
	DonorName    string
	AmountCents  int
	LemonOrderID string
	Message      string
	CreatedAt    time.Time
}

type Balance struct {
	TenantID              string
	AvailableCents        int
	LifetimeEarningsCents int
	LifetimePaidCents     int
	UpdatedAt             time.Time
}

type PayoutSettings struct {
	TenantID          string
	WiseRecipientID   string
	AccountHolderName string
	Currency          string
	PayoutEmail       string
}

func (s *Store) CreateTier(ctx context.Context, tier *Tier) error {
	_, err := s.db.Exec(ctx, `
		INSERT INTO membership_tiers (tenant_id, name, price_cents, description, lemon_variant_id, active)
		VALUES ($1, $2, $3, $4, $5, $6)
	`, tier.TenantID, tier.Name, tier.PriceCents, tier.Description, tier.LemonVariantID, tier.Active)
	return err
}

func (s *Store) GetTiersByTenant(ctx context.Context, tenantID string) ([]Tier, error) {
	rows, err := s.db.Query(ctx, `
		SELECT id, tenant_id, name, price_cents, description, lemon_variant_id, active, created_at
		FROM membership_tiers
		WHERE tenant_id = $1 AND active = TRUE
		ORDER BY price_cents ASC
	`, tenantID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tiers []Tier
	for rows.Next() {
		var t Tier
		if err := rows.Scan(&t.ID, &t.TenantID, &t.Name, &t.PriceCents, &t.Description, &t.LemonVariantID, &t.Active, &t.CreatedAt); err != nil {
			return nil, err
		}
		tiers = append(tiers, t)
	}
	return tiers, rows.Err()
}

func (s *Store) CreateSubscription(ctx context.Context, sub *Subscription) error {
	_, err := s.db.Exec(ctx, `
		INSERT INTO subscriptions (tenant_id, user_id, tier_id, tier_name, status, lemon_subscription_id, lemon_customer_id, amount_cents, current_period_start, current_period_end)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
	`, sub.TenantID, sub.UserID, sub.TierID, sub.TierName, sub.Status, sub.LemonSubscriptionID, sub.LemonCustomerID, sub.AmountCents, sub.CurrentPeriodStart, sub.CurrentPeriodEnd)
	return err
}

func (s *Store) GetSubscriptionByLemonID(ctx context.Context, lemonSubID string) (*Subscription, error) {
	var sub Subscription
	var userID, tierID sql.NullString
	var cancelledAt sql.NullTime
	var periodStart, periodEnd sql.NullTime

	err := s.db.QueryRow(ctx, `
		SELECT id, tenant_id, user_id, tier_id, tier_name, status, lemon_subscription_id, lemon_customer_id,
		       amount_cents, current_period_start, current_period_end, cancelled_at, created_at
		FROM subscriptions WHERE lemon_subscription_id = $1
	`, lemonSubID).Scan(&sub.ID, &sub.TenantID, &userID, &tierID, &sub.TierName, &sub.Status,
		&sub.LemonSubscriptionID, &sub.LemonCustomerID, &sub.AmountCents, &periodStart, &periodEnd,
		&cancelledAt, &sub.CreatedAt)

	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}

	sub.UserID = userID.String
	sub.TierID = tierID.String
	if periodStart.Valid {
		sub.CurrentPeriodStart = periodStart.Time
	}
	if periodEnd.Valid {
		sub.CurrentPeriodEnd = periodEnd.Time
	}
	if cancelledAt.Valid {
		sub.CancelledAt = &cancelledAt.Time
	}
	return &sub, nil
}

func (s *Store) UpdateSubscriptionStatus(ctx context.Context, lemonSubID, status string, renewsAt *time.Time) error {
	_, err := s.db.Exec(ctx, `
		UPDATE subscriptions
		SET status = $1, current_period_end = $2, updated_at = NOW()
		WHERE lemon_subscription_id = $3
	`, status, renewsAt, lemonSubID)
	return err
}

func (s *Store) CancelSubscription(ctx context.Context, lemonSubID string) error {
	_, err := s.db.Exec(ctx, `
		UPDATE subscriptions
		SET status = 'cancelled', cancelled_at = NOW(), updated_at = NOW()
		WHERE lemon_subscription_id = $1
	`, lemonSubID)
	return err
}

func (s *Store) CreateDonation(ctx context.Context, donation *Donation) error {
	err := s.db.QueryRow(ctx, `
		INSERT INTO donations (tenant_id, user_id, donor_email, donor_name, amount_cents, lemon_order_id, message)
		VALUES ($1, $2, $3, $4, $5, $6, $7)
		RETURNING id
	`, donation.TenantID, donation.UserID, donation.DonorEmail, donation.DonorName, donation.AmountCents, donation.LemonOrderID, donation.Message).Scan(&donation.ID)
	return err
}

func (s *Store) AddEarnings(ctx context.Context, tenantID, sourceType, sourceID, description string, grossCents, platformFeeCents, processorFeeCents, netCents int) error {
	tx, err := s.db.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	_, err = tx.Exec(ctx, `
		INSERT INTO earnings (tenant_id, source_type, source_id, description, gross_cents, platform_fee_cents, processor_fee_cents, net_cents)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
	`, tenantID, sourceType, sourceID, description, grossCents, platformFeeCents, processorFeeCents, netCents)
	if err != nil {
		return err
	}

	_, err = tx.Exec(ctx, `
		INSERT INTO balances (tenant_id, available_cents, lifetime_earnings_cents, updated_at)
		VALUES ($1, $2, $2, NOW())
		ON CONFLICT (tenant_id) DO UPDATE
		SET available_cents = balances.available_cents + $2,
		    lifetime_earnings_cents = balances.lifetime_earnings_cents + $2,
		    updated_at = NOW()
	`, tenantID, netCents)
	if err != nil {
		return err
	}

	return tx.Commit(ctx)
}

func (s *Store) GetBalance(ctx context.Context, tenantID string) (*Balance, error) {
	var b Balance
	err := s.db.QueryRow(ctx, `
		SELECT tenant_id, available_cents, lifetime_earnings_cents, lifetime_paid_cents, updated_at
		FROM balances WHERE tenant_id = $1
	`, tenantID).Scan(&b.TenantID, &b.AvailableCents, &b.LifetimeEarningsCents, &b.LifetimePaidCents, &b.UpdatedAt)

	if err == sql.ErrNoRows {
		return &Balance{TenantID: tenantID}, nil
	}
	if err != nil {
		return nil, err
	}
	return &b, nil
}

func (s *Store) GetTenantsReadyForPayout(ctx context.Context, thresholdCents int) ([]Balance, error) {
	rows, err := s.db.Query(ctx, `
		SELECT b.tenant_id, b.available_cents, b.lifetime_earnings_cents, b.lifetime_paid_cents, b.updated_at
		FROM balances b
		JOIN payout_settings ps ON b.tenant_id = ps.tenant_id
		WHERE b.available_cents >= $1 AND ps.wise_recipient_id IS NOT NULL
	`, thresholdCents)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var balances []Balance
	for rows.Next() {
		var b Balance
		if err := rows.Scan(&b.TenantID, &b.AvailableCents, &b.LifetimeEarningsCents, &b.LifetimePaidCents, &b.UpdatedAt); err != nil {
			return nil, err
		}
		balances = append(balances, b)
	}
	return balances, rows.Err()
}

func (s *Store) DeductBalance(ctx context.Context, tenantID string, amountCents int) error {
	_, err := s.db.Exec(ctx, `
		UPDATE balances
		SET available_cents = available_cents - $1,
		    lifetime_paid_cents = lifetime_paid_cents + $1,
		    updated_at = NOW()
		WHERE tenant_id = $2 AND available_cents >= $1
	`, amountCents, tenantID)
	return err
}

func (s *Store) CreatePayout(ctx context.Context, tenantID string, amountCents int, currency, wiseTransferID, wiseQuoteID, status string) (string, error) {
	var id string
	err := s.db.QueryRow(ctx, `
		INSERT INTO payouts (tenant_id, amount_cents, currency, wise_transfer_id, wise_quote_id, status)
		VALUES ($1, $2, $3, $4, $5, $6)
		RETURNING id
	`, tenantID, amountCents, currency, wiseTransferID, wiseQuoteID, status).Scan(&id)
	return id, err
}

func (s *Store) UpdatePayoutStatus(ctx context.Context, payoutID, status string, completedAt *time.Time, failureReason string) error {
	_, err := s.db.Exec(ctx, `
		UPDATE payouts
		SET status = $1, completed_at = $2, failure_reason = $3
		WHERE id = $4
	`, status, completedAt, failureReason, payoutID)
	return err
}

func (s *Store) GetPayoutSettings(ctx context.Context, tenantID string) (*PayoutSettings, error) {
	var ps PayoutSettings
	var recipientID, holderName, currency, email sql.NullString

	err := s.db.QueryRow(ctx, `
		SELECT tenant_id, wise_recipient_id, account_holder_name, currency, payout_email
		FROM payout_settings WHERE tenant_id = $1
	`, tenantID).Scan(&ps.TenantID, &recipientID, &holderName, &currency, &email)

	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}

	ps.WiseRecipientID = recipientID.String
	ps.AccountHolderName = holderName.String
	ps.Currency = currency.String
	if ps.Currency == "" {
		ps.Currency = "USD"
	}
	ps.PayoutEmail = email.String

	return &ps, nil
}

func (s *Store) SavePayoutSettings(ctx context.Context, ps *PayoutSettings) error {
	_, err := s.db.Exec(ctx, `
		INSERT INTO payout_settings (tenant_id, wise_recipient_id, account_holder_name, currency, payout_email)
		VALUES ($1, $2, $3, $4, $5)
		ON CONFLICT (tenant_id) DO UPDATE
		SET wise_recipient_id = $2,
		    account_holder_name = $3,
		    currency = $4,
		    payout_email = $5,
		    updated_at = NOW()
	`, ps.TenantID, ps.WiseRecipientID, ps.AccountHolderName, ps.Currency, ps.PayoutEmail)
	return err
}

type User struct {
	ID    string
	Email string
	Name  string
}

func (s *Store) GetUserByID(ctx context.Context, id string) (*User, error) {
	var u User
	err := s.db.QueryRow(ctx,
		`SELECT id, email, COALESCE(name, '') FROM users WHERE id = $1`,
		id).Scan(&u.ID, &u.Email, &u.Name)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &u, nil
}