Query

billing.db — voucher_purchases

SQL

CREATE TABLE voucher_purchases  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_name TEXT NOT NULL,
  customer_phone TEXT NOT NULL,
  amount INTEGER NOT NULL,
  description TEXT,
  type TEXT NOT NULL DEFAULT 'voucher',
  voucher_package TEXT NOT NULL,
  voucher_quantity INTEGER NOT NULL DEFAULT 1,
  voucher_profile TEXT NOT NULL,
  voucher_data TEXT,
  -- JSON data untuk menyimpan detail voucher yang digenerate
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'failed', 'cancelled')),
  payment_gateway TEXT,
  payment_transaction_id TEXT,
  payment_url TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  invoice_id TEXT,
  FOREIGN KEY (payment_transaction_id) REFERENCES payment_gateway_transactions(id)
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
customer_name TEXT Rename | Drop
customer_phone TEXT Rename | Drop
amount INTEGER Rename | Drop
description TEXT ✓ Rename | Drop
type TEXT Rename | Drop
voucher_package TEXT Rename | Drop
voucher_quantity INTEGER Rename | Drop
voucher_profile TEXT Rename | Drop
voucher_data TEXT ✓ Rename | Drop
status TEXT Rename | Drop
payment_gateway TEXT ✓ Rename | Drop
payment_transaction_id TEXT ✓ Rename | Drop
payment_url TEXT ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop
completed_at DATETIME ✓ Rename | Drop
updated_at DATETIME ✓ Rename | Drop
invoice_id TEXT ✓ Rename | Drop

Foreign Keys

Column Destination
payment_transaction_id payment_gateway_transactions.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_voucher_purchases_created created_at SQL 
CREATE INDEX idx_voucher_purchases_created
ON voucher_purchases(created_at)
Drop
idx_voucher_purchases_invoice_id invoice_id SQL 
CREATE INDEX idx_voucher_purchases_invoice_id
ON voucher_purchases(invoice_id)
Drop
idx_voucher_purchases_package voucher_package SQL 
CREATE INDEX idx_voucher_purchases_package
ON voucher_purchases(voucher_package)
Drop
idx_voucher_purchases_phone customer_phone SQL 
CREATE INDEX idx_voucher_purchases_phone
ON voucher_purchases(customer_phone)
Drop
idx_voucher_purchases_status status SQL 
CREATE INDEX idx_voucher_purchases_status
ON voucher_purchases(status)
Drop

Triggers

Name SQL Drop?
update_voucher_purchases_updated_at SQL 
CREATE TRIGGER update_voucher_purchases_updated_at
    AFTER UPDATE ON voucher_purchases
    FOR EACH ROW
BEGIN
    UPDATE voucher_purchases SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END
Drop