-- Add PayPal integration tables
-- Migration for PayPal payment support alongside existing Stripe integration

-- PayPal orders for tracking transactions
CREATE TABLE IF NOT EXISTS "paypal_orders" (
	"id" text PRIMARY KEY,
	"company_id" text NOT NULL,
	"user_id" text NOT NULL,
	"amount" text NOT NULL,
	"currency" text NOT NULL DEFAULT 'EUR',
	"status" text NOT NULL DEFAULT 'CREATED',
	"capture_id" text,
	"paypal_fee" real,
	"completed_at" timestamp,
	"created_at" timestamp DEFAULT now(),
	"updated_at" timestamp DEFAULT now()
);

-- Payment methods for companies (Stripe + PayPal)
CREATE TABLE IF NOT EXISTS "payment_methods" (
	"id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	"company_id" text NOT NULL,
	"type" text NOT NULL,
	"last4" text,
	"brand" text,
	"expiry_month" integer,
	"expiry_year" integer,
	"paypal_email" text,
	"paypal_order_id" text,
	"paypal_capture_id" text,
	"account_holder_name" text,
	"bank_code" text,
	"country" text,
	"stripe_payment_method_id" text,
	"is_default" boolean DEFAULT false,
	"created_at" timestamp DEFAULT now(),
	"updated_at" timestamp DEFAULT now()
);

-- Billing transactions for all payment methods
CREATE TABLE IF NOT EXISTS "billing_transactions" (
	"id" integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	"company_id" text NOT NULL,
	"user_id" text NOT NULL,
	"type" text NOT NULL,
	"amount" real NOT NULL,
	"currency" text NOT NULL DEFAULT 'EUR',
	"status" text NOT NULL DEFAULT 'pending',
	"payment_method_type" text NOT NULL,
	"payment_method_id" text NOT NULL,
	"description" text,
	"metadata" jsonb DEFAULT '{}',
	"created_at" timestamp DEFAULT now(),
	"updated_at" timestamp DEFAULT now()
);

-- Add indexes for better performance
CREATE INDEX IF NOT EXISTS "idx_paypal_orders_company_id" ON "paypal_orders"("company_id");
CREATE INDEX IF NOT EXISTS "idx_paypal_orders_user_id" ON "paypal_orders"("user_id");
CREATE INDEX IF NOT EXISTS "idx_paypal_orders_status" ON "paypal_orders"("status");

CREATE INDEX IF NOT EXISTS "idx_payment_methods_company_id" ON "payment_methods"("company_id");
CREATE INDEX IF NOT EXISTS "idx_payment_methods_type" ON "payment_methods"("type");
CREATE INDEX IF NOT EXISTS "idx_payment_methods_is_default" ON "payment_methods"("is_default");

CREATE INDEX IF NOT EXISTS "idx_billing_transactions_company_id" ON "billing_transactions"("company_id");
CREATE INDEX IF NOT EXISTS "idx_billing_transactions_user_id" ON "billing_transactions"("user_id");
CREATE INDEX IF NOT EXISTS "idx_billing_transactions_payment_method_type" ON "billing_transactions"("payment_method_type");
CREATE INDEX IF NOT EXISTS "idx_billing_transactions_status" ON "billing_transactions"("status");

-- Add foreign key constraints
DO $$ BEGIN
	ALTER TABLE "paypal_orders" ADD CONSTRAINT "paypal_orders_company_id_fkey" FOREIGN KEY ("company_id") REFERENCES "companies"("id") ON DELETE CASCADE ON UPDATE NO ACTION;
EXCEPTION
	WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
	ALTER TABLE "paypal_orders" ADD CONSTRAINT "paypal_orders_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE NO ACTION;
EXCEPTION
	WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
	ALTER TABLE "payment_methods" ADD CONSTRAINT "payment_methods_company_id_fkey" FOREIGN KEY ("company_id") REFERENCES "companies"("id") ON DELETE CASCADE ON UPDATE NO ACTION;
EXCEPTION
	WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
	ALTER TABLE "billing_transactions" ADD CONSTRAINT "billing_transactions_company_id_fkey" FOREIGN KEY ("company_id") REFERENCES "companies"("id") ON DELETE CASCADE ON UPDATE NO ACTION;
EXCEPTION
	WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
	ALTER TABLE "billing_transactions" ADD CONSTRAINT "billing_transactions_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE NO ACTION;
EXCEPTION
	WHEN duplicate_object THEN null;
END $$;
