cloudlense/frontend/supabase/migrations/20260306234000_admin_payments_coupons_credits.sql

-- Admin Dashboard: Payments, Coupons & Credits
-- Adds tables for payment tracking, coupon/discount codes, and account credit system.
-- Stripe fields are nullable (prepared for future integration).

-- ============================================================================
-- PAYMENTS
-- ============================================================================

CREATE TABLE IF NOT EXISTS public.payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
    amount INTEGER NOT NULL, -- amount in cents
    currency TEXT NOT NULL DEFAULT 'EUR',
    status TEXT NOT NULL DEFAULT 'completed' CHECK (status IN ('pending', 'completed', 'failed', 'refunded')),
    method TEXT CHECK (method IN ('manual', 'stripe', 'bank_transfer', 'paypal', 'other')),
    stripe_payment_id TEXT, -- nullable, for future Stripe integration
    description TEXT,
    notes TEXT, -- internal admin notes
    created_by UUID REFERENCES public.users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_payments_organization_id ON public.payments(organization_id);
CREATE INDEX idx_payments_created_at ON public.payments(created_at DESC);
CREATE INDEX idx_payments_stripe_payment_id ON public.payments(stripe_payment_id) WHERE stripe_payment_id IS NOT NULL;

-- ============================================================================
-- COUPONS
-- ============================================================================

CREATE TABLE IF NOT EXISTS public.coupons (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code TEXT NOT NULL UNIQUE,
    description TEXT,
    discount_type TEXT NOT NULL CHECK (discount_type IN ('percentage', 'fixed_amount', 'tier_upgrade')),
    discount_value INTEGER NOT NULL, -- percentage (0-100) or amount in cents
    currency TEXT NOT NULL DEFAULT 'EUR',
    max_redemptions INTEGER, -- NULL = unlimited
    current_redemptions INTEGER NOT NULL DEFAULT 0,
    applicable_tiers TEXT[] DEFAULT ARRAY['free', 'starter', 'professional'], -- which tiers can use this
    valid_from TIMESTAMPTZ NOT NULL DEFAULT now(),
    valid_until TIMESTAMPTZ, -- NULL = no expiry
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_by UUID REFERENCES public.users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_coupons_code ON public.coupons(code);

-- ============================================================================
-- COUPON REDEMPTIONS
-- ============================================================================

CREATE TABLE IF NOT EXISTS public.coupon_redemptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    coupon_id UUID NOT NULL REFERENCES public.coupons(id) ON DELETE CASCADE,
    organization_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
    redeemed_by UUID REFERENCES public.users(id) ON DELETE SET NULL,
    discount_applied INTEGER NOT NULL, -- actual discount in cents
    redeemed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_coupon_redemptions_coupon_id ON public.coupon_redemptions(coupon_id);
CREATE INDEX idx_coupon_redemptions_organization_id ON public.coupon_redemptions(organization_id);

-- ============================================================================
-- CREDIT TRANSACTIONS (ledger-style)
-- ============================================================================

CREATE TABLE IF NOT EXISTS public.credit_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
    amount INTEGER NOT NULL, -- positive = credit, negative = debit (in cents)
    balance_after INTEGER NOT NULL, -- running balance after this transaction
    type TEXT NOT NULL CHECK (type IN ('credit', 'debit')),
    reason TEXT NOT NULL, -- e.g. 'manual_credit', 'coupon_redemption', 'payment', 'usage_charge', 'refund', 'adjustment'
    reference_id UUID, -- optional reference to payment/coupon/invoice
    notes TEXT,
    created_by UUID REFERENCES public.users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_credit_transactions_organization_id ON public.credit_transactions(organization_id);
CREATE INDEX idx_credit_transactions_created_at ON public.credit_transactions(created_at DESC);

-- ============================================================================
-- INVOICES
-- ============================================================================

CREATE TABLE IF NOT EXISTS public.invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_number TEXT NOT NULL UNIQUE,
    organization_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
    amount INTEGER NOT NULL, -- total in cents
    currency TEXT NOT NULL DEFAULT 'EUR',
    status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'sent', 'paid', 'overdue', 'cancelled', 'refunded')),
    items JSONB NOT NULL DEFAULT '[]'::jsonb, -- line items: [{description, quantity, unit_price, total}]
    stripe_invoice_id TEXT, -- nullable, for future Stripe integration
    due_date TIMESTAMPTZ,
    paid_at TIMESTAMPTZ,
    notes TEXT,
    created_by UUID REFERENCES public.users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_invoices_organization_id ON public.invoices(organization_id);
CREATE INDEX idx_invoices_status ON public.invoices(status);
CREATE INDEX idx_invoices_invoice_number ON public.invoices(invoice_number);

-- ============================================================================
-- Add credit_balance column to organizations
-- ============================================================================

ALTER TABLE public.organizations
    ADD COLUMN IF NOT EXISTS credit_balance INTEGER NOT NULL DEFAULT 0;

-- ============================================================================
-- RLS Policies (admin-only access)
-- ============================================================================

ALTER TABLE public.payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.coupons ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.coupon_redemptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.credit_transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.invoices ENABLE ROW LEVEL SECURITY;

-- Admins (service role) can do everything; these policies allow authenticated admin access
CREATE POLICY "Admin full access on payments" ON public.payments
    FOR ALL USING (
        EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role IN ('owner', 'admin'))
    );

CREATE POLICY "Admin full access on coupons" ON public.coupons
    FOR ALL USING (
        EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role IN ('owner', 'admin'))
    );

CREATE POLICY "Admin full access on coupon_redemptions" ON public.coupon_redemptions
    FOR ALL USING (
        EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role IN ('owner', 'admin'))
    );

CREATE POLICY "Admin full access on credit_transactions" ON public.credit_transactions
    FOR ALL USING (
        EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role IN ('owner', 'admin'))
    );

CREATE POLICY "Admin full access on invoices" ON public.invoices
    FOR ALL USING (
        EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role IN ('owner', 'admin'))
    );

-- Org members can view their own organization's data (read-only)
CREATE POLICY "Org members can view own payments" ON public.payments
    FOR SELECT USING (
        organization_id IN (
            SELECT organization_id FROM public.users WHERE id = auth.uid()
        )
    );

CREATE POLICY "Org members can view own invoices" ON public.invoices
    FOR SELECT USING (
        organization_id IN (
            SELECT organization_id FROM public.users WHERE id = auth.uid()
        )
    );

CREATE POLICY "Org members can view own credit transactions" ON public.credit_transactions
    FOR SELECT USING (
        organization_id IN (
            SELECT organization_id FROM public.users WHERE id = auth.uid()
        )
    );

-- Anyone can view active coupons (for redemption)
CREATE POLICY "Anyone can view active coupons" ON public.coupons
    FOR SELECT USING (is_active = true);