124 lines
5.3 KiB
PL/PgSQL
124 lines
5.3 KiB
PL/PgSQL
-- Create enum for invoice status
|
|
CREATE TYPE invoice_status AS ENUM ('DRAFT', 'SENT', 'PAID', 'PARTIALLY_PAID', 'OVERDUE', 'VOID', 'CANCELLED');
|
|
|
|
-- Create invoices table
|
|
CREATE TABLE IF NOT EXISTS invoices (
|
|
id SERIAL PRIMARY KEY,
|
|
invoice_number VARCHAR(50) UNIQUE NOT NULL,
|
|
project_id INTEGER REFERENCES projects(id) ON DELETE SET NULL,
|
|
client_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
amount DECIMAL(15, 2) NOT NULL,
|
|
currency VARCHAR(10) NOT NULL DEFAULT 'USD',
|
|
status invoice_status NOT NULL DEFAULT 'DRAFT',
|
|
due_date DATE,
|
|
issued_date DATE,
|
|
paid_date DATE,
|
|
blockchain_tx_hash VARCHAR(255), -- For on-chain payment proof
|
|
ipfs_document_cid VARCHAR(255), -- CID for invoice PDF on IPFS
|
|
notes TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create indexes for invoices
|
|
CREATE INDEX idx_invoices_invoice_number ON invoices(invoice_number);
|
|
CREATE INDEX idx_invoices_project_id ON invoices(project_id);
|
|
CREATE INDEX idx_invoices_client_id ON invoices(client_id);
|
|
CREATE INDEX idx_invoices_status ON invoices(status);
|
|
CREATE INDEX idx_invoices_due_date ON invoices(due_date) WHERE due_date IS NOT NULL;
|
|
CREATE INDEX idx_invoices_blockchain_tx ON invoices(blockchain_tx_hash) WHERE blockchain_tx_hash IS NOT NULL;
|
|
|
|
-- Create enum for payment method
|
|
CREATE TYPE payment_method AS ENUM ('CREDIT_CARD', 'DEBIT_CARD', 'CRYPTO_BTC', 'CRYPTO_ETH', 'CRYPTO_OTHER', 'BLOCKCHAIN_TOKEN', 'BANK_TRANSFER', 'CASH', 'OTHER');
|
|
|
|
-- Create enum for payment status
|
|
CREATE TYPE payment_status AS ENUM ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'REFUNDED', 'CANCELLED');
|
|
|
|
-- Create payments table
|
|
CREATE TABLE IF NOT EXISTS payments (
|
|
id SERIAL PRIMARY KEY,
|
|
invoice_id INTEGER NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
|
|
amount DECIMAL(15, 2) NOT NULL,
|
|
currency VARCHAR(10) NOT NULL DEFAULT 'USD',
|
|
payment_method payment_method NOT NULL,
|
|
status payment_status NOT NULL DEFAULT 'PENDING',
|
|
transaction_id VARCHAR(255), -- Stripe payment intent ID, BTCPay invoice ID, or blockchain tx hash
|
|
blockchain_tx_hash VARCHAR(255), -- For crypto/blockchain payments
|
|
blockchain_network VARCHAR(50), -- e.g., 'ethereum', 'bitcoin', 'polygon'
|
|
payment_processor VARCHAR(50), -- e.g., 'stripe', 'btcpay', 'direct_blockchain'
|
|
processor_fee DECIMAL(15, 2), -- Fee charged by payment processor
|
|
metadata JSONB, -- Store processor-specific data
|
|
processed_at TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create indexes for payments
|
|
CREATE INDEX idx_payments_invoice_id ON payments(invoice_id);
|
|
CREATE INDEX idx_payments_transaction_id ON payments(transaction_id) WHERE transaction_id IS NOT NULL;
|
|
CREATE INDEX idx_payments_blockchain_tx ON payments(blockchain_tx_hash) WHERE blockchain_tx_hash IS NOT NULL;
|
|
CREATE INDEX idx_payments_status ON payments(status);
|
|
CREATE INDEX idx_payments_payment_method ON payments(payment_method);
|
|
|
|
-- Create invoice_items table for line items
|
|
CREATE TABLE IF NOT EXISTS invoice_items (
|
|
id SERIAL PRIMARY KEY,
|
|
invoice_id INTEGER NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
|
|
description VARCHAR(500) NOT NULL,
|
|
quantity DECIMAL(10, 2) NOT NULL DEFAULT 1,
|
|
unit_price DECIMAL(15, 2) NOT NULL,
|
|
tax_rate DECIMAL(5, 2) DEFAULT 0, -- Percentage
|
|
total DECIMAL(15, 2) NOT NULL, -- Calculated: quantity * unit_price * (1 + tax_rate/100)
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create index for invoice_items
|
|
CREATE INDEX idx_invoice_items_invoice_id ON invoice_items(invoice_id);
|
|
|
|
-- Create triggers for updated_at
|
|
CREATE TRIGGER update_invoices_updated_at BEFORE UPDATE ON invoices
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_payments_updated_at BEFORE UPDATE ON payments
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_invoice_items_updated_at BEFORE UPDATE ON invoice_items
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Create function to automatically update invoice status based on payments
|
|
CREATE OR REPLACE FUNCTION update_invoice_status()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
total_paid DECIMAL(15, 2);
|
|
invoice_amount DECIMAL(15, 2);
|
|
BEGIN
|
|
-- Get total paid amount for this invoice
|
|
SELECT COALESCE(SUM(amount), 0) INTO total_paid
|
|
FROM payments
|
|
WHERE invoice_id = NEW.invoice_id AND status = 'COMPLETED';
|
|
|
|
-- Get invoice amount
|
|
SELECT amount INTO invoice_amount
|
|
FROM invoices
|
|
WHERE id = NEW.invoice_id;
|
|
|
|
-- Update invoice status based on payment
|
|
IF total_paid >= invoice_amount THEN
|
|
UPDATE invoices SET status = 'PAID', paid_date = CURRENT_TIMESTAMP
|
|
WHERE id = NEW.invoice_id AND status != 'PAID';
|
|
ELSIF total_paid > 0 THEN
|
|
UPDATE invoices SET status = 'PARTIALLY_PAID'
|
|
WHERE id = NEW.invoice_id AND status NOT IN ('PAID', 'PARTIALLY_PAID');
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Create trigger to update invoice status when payment is completed
|
|
CREATE TRIGGER update_invoice_on_payment AFTER INSERT OR UPDATE ON payments
|
|
FOR EACH ROW
|
|
WHEN (NEW.status = 'COMPLETED')
|
|
EXECUTE FUNCTION update_invoice_status();
|