# DCA Admin Extension Database Migrations # Creates all necessary tables for Dollar Cost Averaging administration # with Lamassu ATM integration async def m001_initial_dca_schema(db): """ Create complete DCA admin schema from scratch. """ # DCA Clients table await db.execute( f""" CREATE TABLE satoshimachine.dca_clients ( id TEXT PRIMARY KEY NOT NULL, user_id TEXT NOT NULL, wallet_id TEXT NOT NULL, username TEXT, dca_mode TEXT NOT NULL DEFAULT 'flow', fixed_mode_daily_limit INTEGER, status TEXT NOT NULL DEFAULT 'active', created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}, updated_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now} ); """ ) # DCA Deposits table await db.execute( f""" CREATE TABLE satoshimachine.dca_deposits ( id TEXT PRIMARY KEY NOT NULL, client_id TEXT NOT NULL, amount INTEGER NOT NULL, currency TEXT NOT NULL DEFAULT 'GTQ', status TEXT NOT NULL DEFAULT 'pending', notes TEXT, created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}, confirmed_at TIMESTAMP ); """ ) # DCA Payments table await db.execute( f""" CREATE TABLE satoshimachine.dca_payments ( id TEXT PRIMARY KEY NOT NULL, client_id TEXT NOT NULL, amount_sats INTEGER NOT NULL, amount_fiat INTEGER NOT NULL, exchange_rate REAL NOT NULL, transaction_type TEXT NOT NULL, lamassu_transaction_id TEXT, payment_hash TEXT, status TEXT NOT NULL DEFAULT 'pending', created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now} ); """ ) # Lamassu Configuration table await db.execute( f""" CREATE TABLE satoshimachine.lamassu_config ( id TEXT PRIMARY KEY NOT NULL, host TEXT NOT NULL, port INTEGER NOT NULL DEFAULT 5432, database_name TEXT NOT NULL, username TEXT NOT NULL, password TEXT NOT NULL, source_wallet_id TEXT, commission_wallet_id TEXT, is_active BOOLEAN NOT NULL DEFAULT true, test_connection_last TIMESTAMP, test_connection_success BOOLEAN, last_poll_time TIMESTAMP, last_successful_poll TIMESTAMP, use_ssh_tunnel BOOLEAN NOT NULL DEFAULT false, ssh_host TEXT, ssh_port INTEGER NOT NULL DEFAULT 22, ssh_username TEXT, ssh_password TEXT, ssh_private_key TEXT, created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}, updated_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now} ); """ ) # Lamassu Transactions table (for audit trail) await db.execute( f""" CREATE TABLE satoshimachine.lamassu_transactions ( id TEXT PRIMARY KEY NOT NULL, lamassu_transaction_id TEXT NOT NULL UNIQUE, fiat_amount INTEGER NOT NULL, crypto_amount INTEGER NOT NULL, commission_percentage REAL NOT NULL, discount REAL NOT NULL DEFAULT 0.0, effective_commission REAL NOT NULL, commission_amount_sats INTEGER NOT NULL, base_amount_sats INTEGER NOT NULL, exchange_rate REAL NOT NULL, crypto_code TEXT NOT NULL DEFAULT 'BTC', fiat_code TEXT NOT NULL DEFAULT 'GTQ', device_id TEXT, transaction_time TIMESTAMP NOT NULL, processed_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}, clients_count INTEGER NOT NULL DEFAULT 0, distributions_total_sats INTEGER NOT NULL DEFAULT 0 ); """ ) async def m002_add_transaction_time_to_dca_payments(db): """ Add transaction_time field to dca_payments table to store original ATM transaction time """ await db.execute( """ ALTER TABLE satoshimachine.dca_payments ADD COLUMN transaction_time TIMESTAMP """ ) async def m003_add_max_daily_limit_config(db): """ Add max_daily_limit_gtq field to lamassu_config table for admin-configurable client limits """ await db.execute( """ ALTER TABLE satoshimachine.lamassu_config ADD COLUMN max_daily_limit_gtq INTEGER NOT NULL DEFAULT 2000 """ ) async def m004_convert_to_gtq_storage(db): """ Convert centavo storage to GTQ storage by changing data types and converting existing data. Handles both SQLite (data conversion only) and PostgreSQL (data + schema changes). """ # Detect database type db_type = str(type(db)).lower() is_postgres = 'postgres' in db_type or 'asyncpg' in db_type if is_postgres: # PostgreSQL: Need to change column types first, then convert data # Change column types to DECIMAL(10,2) await db.execute("ALTER TABLE satoshimachine.dca_deposits ALTER COLUMN amount TYPE DECIMAL(10,2)") await db.execute("ALTER TABLE satoshimachine.dca_payments ALTER COLUMN amount_fiat TYPE DECIMAL(10,2)") await db.execute("ALTER TABLE satoshimachine.lamassu_transactions ALTER COLUMN fiat_amount TYPE DECIMAL(10,2)") await db.execute("ALTER TABLE satoshimachine.dca_clients ALTER COLUMN fixed_mode_daily_limit TYPE DECIMAL(10,2)") await db.execute("ALTER TABLE satoshimachine.lamassu_config ALTER COLUMN max_daily_limit_gtq TYPE DECIMAL(10,2)") # Convert data from centavos to GTQ await db.execute("UPDATE satoshimachine.dca_deposits SET amount = amount / 100.0 WHERE currency = 'GTQ'") await db.execute("UPDATE satoshimachine.dca_payments SET amount_fiat = amount_fiat / 100.0") await db.execute("UPDATE satoshimachine.lamassu_transactions SET fiat_amount = fiat_amount / 100.0") await db.execute("UPDATE satoshimachine.dca_clients SET fixed_mode_daily_limit = fixed_mode_daily_limit / 100.0 WHERE fixed_mode_daily_limit IS NOT NULL") await db.execute("UPDATE satoshimachine.lamassu_config SET max_daily_limit_gtq = max_daily_limit_gtq / 100.0 WHERE max_daily_limit_gtq > 1000") else: # SQLite: Data conversion only (dynamic typing handles the rest) await db.execute("UPDATE satoshimachine.dca_deposits SET amount = CAST(amount AS REAL) / 100.0 WHERE currency = 'GTQ'") await db.execute("UPDATE satoshimachine.dca_payments SET amount_fiat = CAST(amount_fiat AS REAL) / 100.0") await db.execute("UPDATE satoshimachine.lamassu_transactions SET fiat_amount = CAST(fiat_amount AS REAL) / 100.0") await db.execute("UPDATE satoshimachine.dca_clients SET fixed_mode_daily_limit = CAST(fixed_mode_daily_limit AS REAL) / 100.0 WHERE fixed_mode_daily_limit IS NOT NULL") await db.execute("UPDATE satoshimachine.lamassu_config SET max_daily_limit_gtq = CAST(max_daily_limit_gtq AS REAL) / 100.0 WHERE max_daily_limit_gtq > 1000")