database.py

"""
ClawVille Database Schema
Proper tokenomics with halving schedule, transaction ledger, and mining
Supports both SQLite (local) and PostgreSQL (Railway production)
"""

import os
from datetime import datetime, timedelta
from decimal import Decimal
import hashlib
import secrets
from contextlib import contextmanager

# Check for DATABASE_URL (Railway provides this for PostgreSQL)
DATABASE_URL = os.environ.get("DATABASE_URL")

if DATABASE_URL:
    # PostgreSQL mode
    import psycopg2
    from psycopg2.extras import RealDictCursor
    DB_TYPE = "postgresql"
    print("ClawVille: Using PostgreSQL")
else:
    # SQLite mode
    import sqlite3
    DB_TYPE = "sqlite"
    print("ClawVille: Using SQLite")

DB_PATH = os.environ.get("CLAWVILLE_DB", "data/clawville.db")

# ============== TOKENOMICS CONSTANTS ==============

TOTAL_SUPPLY = 21_000_000 * 100_000_000  # 21M ClawCoin in ClawSats
CLAWSATS_PER_COIN = 100_000_000  # 1 ClawCoin = 100M ClawSats

INITIAL_BLOCK_REWARD = 50 * CLAWSATS_PER_COIN  # 50 ClawCoin per block
BLOCK_TIME_SECONDS = 300  # 5 minutes per block
HALVING_INTERVAL_BLOCKS = 2016  # ~2 weeks at 5 min blocks (like Bitcoin)

# Admin wallet that receives spent coins
ADMIN_WALLET_ID = "clawville_admin"

# ============== DATABASE SETUP ==============

def get_db_path():
    if DB_TYPE == "postgresql":
        return DATABASE_URL
    db_dir = os.path.dirname(DB_PATH)
    if db_dir:
        os.makedirs(db_dir, exist_ok=True)
    return DB_PATH

@contextmanager
def get_db():
    if DB_TYPE == "postgresql":
        conn = psycopg2.connect(DATABASE_URL)
        conn.autocommit = False
        # Create a wrapper to make psycopg2 work like sqlite3
        original_execute = conn.cursor
        
        class PostgresCursor:
            def __init__(self):
                self._cursor = conn.cursor(cursor_factory=RealDictCursor)
            
            def execute(self, query, params=None):
                # Convert ? to %s for PostgreSQL
                query = query.replace("?", "%s")
                self._cursor.execute(query, params)
                return self
            
            def executescript(self, script):
                for statement in script.split(';'):
                    statement = statement.strip()
                    if statement:
                        self._cursor.execute(statement)
            
            def fetchone(self):
                return self._cursor.fetchone()
            
            def fetchall(self):
                return self._cursor.fetchall()
            
            @property
            def lastrowid(self):
                return None  # PostgreSQL doesn't have lastrowid
        
        class PostgresConnection:
            def execute(self, query, params=None):
                cursor = PostgresCursor()
                cursor.execute(query, params)
                return cursor
            
            def executescript(self, script):
                cursor = PostgresCursor()
                cursor.executescript(script)
        
        wrapper = PostgresConnection()
        try:
            yield wrapper
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()
    else:
        conn = sqlite3.connect(get_db_path(), timeout=30)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA journal_mode=WAL")
        try:
            yield conn
            conn.commit()
        finally:
            conn.close()

def init_db():
    """Initialize database schema."""
    with get_db() as db:
        if DB_TYPE == "postgresql":
            # PostgreSQL schema
            db.execute("""
                CREATE TABLE IF NOT EXISTS wallets (
                    id TEXT PRIMARY KEY,
                    agent_id TEXT UNIQUE,
                    balance_sats BIGINT DEFAULT 0,
                    total_received_sats BIGINT DEFAULT 0,
                    total_spent_sats BIGINT DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            db.execute("""
                CREATE TABLE IF NOT EXISTS transactions (
                    id SERIAL PRIMARY KEY,
                    tx_hash TEXT UNIQUE NOT NULL,
                    block_number INTEGER,
                    from_wallet TEXT,
                    to_wallet TEXT NOT NULL,
                    amount_sats BIGINT NOT NULL,
                    tx_type TEXT NOT NULL,
                    memo TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            db.execute("""
                CREATE TABLE IF NOT EXISTS blocks (
                    number INTEGER PRIMARY KEY,
                    hash TEXT UNIQUE NOT NULL,
                    miner_wallet TEXT,
                    reward_sats BIGINT NOT NULL,
                    difficulty INTEGER DEFAULT 1,
                    challenge_type TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            db.execute("""
                CREATE TABLE IF NOT EXISTS mining_state (
                    id INTEGER PRIMARY KEY CHECK (id = 1),
                    total_mined_sats BIGINT DEFAULT 0,
                    current_block INTEGER DEFAULT 0,
                    current_reward_sats BIGINT DEFAULT 5000000000,
                    halvings_occurred INTEGER DEFAULT 0,
                    last_halving_block INTEGER DEFAULT 0,
                    last_block_time TIMESTAMP
                )
            """)
            
            db.execute("""
                CREATE TABLE IF NOT EXISTS agents (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    api_key TEXT UNIQUE NOT NULL,
                    wallet_id TEXT NOT NULL,
                    description TEXT,
                    avatar_url TEXT,
                    auth_provider TEXT,
                    auth_id TEXT,
                    plot_x INTEGER,
                    plot_y INTEGER,
                    district TEXT,
                    xp INTEGER DEFAULT 0,
                    energy INTEGER DEFAULT 100,
                    max_energy INTEGER DEFAULT 100,
                    level INTEGER DEFAULT 1,
                    progression JSONB DEFAULT '{}',
                    stats JSONB DEFAULT '{}',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    UNIQUE(auth_provider, auth_id)
                )
            """)
            
            db.execute("""
                CREATE TABLE IF NOT EXISTS rate_limits (
                    key TEXT PRIMARY KEY,
                    action TEXT NOT NULL,
                    count INTEGER DEFAULT 0,
                    window_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    last_action TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            # PostgreSQL upsert syntax
            db.execute("""
                INSERT INTO mining_state (id, total_mined_sats, current_block, current_reward_sats)
                VALUES (1, 0, 0, %s)
                ON CONFLICT (id) DO NOTHING
            """, (INITIAL_BLOCK_REWARD,))
            
            db.execute("""
                INSERT INTO wallets (id, agent_id, balance_sats)
                VALUES (%s, 'ADMIN', 0)
                ON CONFLICT (id) DO NOTHING
            """, (ADMIN_WALLET_ID,))
            
        else:
            # SQLite schema
            db.execute("""
                CREATE TABLE IF NOT EXISTS wallets (
                    id TEXT PRIMARY KEY,
                    agent_id TEXT UNIQUE,
                    balance_sats INTEGER DEFAULT 0,
                    total_received_sats INTEGER DEFAULT 0,
                    total_spent_sats INTEGER DEFAULT 0,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            db.execute("""
                CREATE TABLE IF NOT EXISTS transactions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    tx_hash TEXT UNIQUE NOT NULL,
                    block_number INTEGER,
                    from_wallet TEXT,
                    to_wallet TEXT NOT NULL,
                    amount_sats INTEGER NOT NULL,
                    tx_type TEXT NOT NULL,
                    memo TEXT,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (from_wallet) REFERENCES wallets(id),
                    FOREIGN KEY (to_wallet) REFERENCES wallets(id)
                )
            """)
            
            db.execute("""
                CREATE TABLE IF NOT EXISTS blocks (
                    number INTEGER PRIMARY KEY,
                    hash TEXT UNIQUE NOT NULL,
                    miner_wallet TEXT,
                    reward_sats INTEGER NOT NULL,
                    difficulty INTEGER DEFAULT 1,
                    challenge_type TEXT,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (miner_wallet) REFERENCES wallets(id)
                )
            """)
            
            db.execute("""
                CREATE TABLE IF NOT EXISTS mining_state (
                    id INTEGER PRIMARY KEY CHECK (id = 1),
                    total_mined_sats INTEGER DEFAULT 0,
                    current_block INTEGER DEFAULT 0,
                    current_reward_sats INTEGER DEFAULT 5000000000,
                    halvings_occurred INTEGER DEFAULT 0,
                    last_halving_block INTEGER DEFAULT 0,
                    last_block_time TEXT
                )
            """)
            
            db.execute("""
                CREATE TABLE IF NOT EXISTS agents (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    api_key TEXT UNIQUE NOT NULL,
                    wallet_id TEXT NOT NULL,
                    description TEXT,
                    avatar_url TEXT,
                    auth_provider TEXT,
                    auth_id TEXT,
                    plot_x INTEGER,
                    plot_y INTEGER,
                    district TEXT,
                    xp INTEGER DEFAULT 0,
                    energy INTEGER DEFAULT 100,
                    max_energy INTEGER DEFAULT 100,
                    level INTEGER DEFAULT 1,
                    progression TEXT DEFAULT '{}',
                    stats TEXT DEFAULT '{}',
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (wallet_id) REFERENCES wallets(id),
                    UNIQUE(auth_provider, auth_id)
                )
            """)
            
            db.execute("""
                CREATE TABLE IF NOT EXISTS rate_limits (
                    key TEXT PRIMARY KEY,
                    action TEXT NOT NULL,
                    count INTEGER DEFAULT 0,
                    window_start TEXT DEFAULT CURRENT_TIMESTAMP,
                    last_action TEXT DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            # SQLite upsert syntax
            db.execute("""
                INSERT OR IGNORE INTO mining_state (id, total_mined_sats, current_block, current_reward_sats)
                VALUES (1, 0, 0, ?)
            """, (INITIAL_BLOCK_REWARD,))
            
            db.execute("""
                INSERT OR IGNORE INTO wallets (id, agent_id, balance_sats)
                VALUES (?, 'ADMIN', 0)
            """, (ADMIN_WALLET_ID,))
        
        # Create indexes (same syntax works for both)
        db.execute("CREATE INDEX IF NOT EXISTS idx_tx_to ON transactions(to_wallet)")
        db.execute("CREATE INDEX IF NOT EXISTS idx_tx_from ON transactions(from_wallet)")
        db.execute("CREATE INDEX IF NOT EXISTS idx_tx_block ON transactions(block_number)")
        db.execute("CREATE INDEX IF NOT EXISTS idx_agents_api_key ON agents(api_key)")
        db.execute("CREATE INDEX IF NOT EXISTS idx_agents_auth ON agents(auth_provider, auth_id)")

# ============== WALLET FUNCTIONS ==============

def create_wallet(agent_id: str) -> str:
    """Create a new wallet for an agent."""
    wallet_id = f"wallet_{secrets.token_hex(8)}"
    
    with get_db() as db:
        db.execute("""
            INSERT INTO wallets (id, agent_id, balance_sats)
            VALUES (?, ?, 0)
        """, (wallet_id, agent_id))
    
    return wallet_id

def get_wallet(wallet_id: str) -> dict:
    """Get wallet by ID."""
    with get_db() as db:
        row = db.execute("SELECT * FROM wallets WHERE id = ?", (wallet_id,)).fetchone()
        return dict(row) if row else None

def get_wallet_by_agent(agent_id: str) -> dict:
    """Get wallet by agent ID."""
    with get_db() as db:
        row = db.execute("SELECT * FROM wallets WHERE agent_id = ?", (agent_id,)).fetchone()
        return dict(row) if row else None

def get_balance(wallet_id: str) -> int:
    """Get balance in ClawSats."""
    with get_db() as db:
        row = db.execute("SELECT balance_sats FROM wallets WHERE id = ?", (wallet_id,)).fetchone()
        return row["balance_sats"] if row else 0

def get_balance_claw(wallet_id: str) -> float:
    """Get balance in ClawCoin."""
    return get_balance(wallet_id) / CLAWSATS_PER_COIN

# ============== TRANSACTION FUNCTIONS ==============

def create_tx_hash(from_wallet: str, to_wallet: str, amount: int, timestamp: str) -> str:
    """Create a unique transaction hash."""
    data = f"{from_wallet}:{to_wallet}:{amount}:{timestamp}:{secrets.token_hex(4)}"
    return hashlib.sha256(data.encode()).hexdigest()[:16]

def transfer(from_wallet: str, to_wallet: str, amount_sats: int, tx_type: str, memo: str = None, block_number: int = None) -> dict:
    """Transfer ClawSats between wallets."""
    if amount_sats <= 0:
        raise ValueError("Amount must be positive")
    
    with get_db() as db:
        # Check balance (skip for mining rewards from null)
        if from_wallet:
            balance = db.execute(
                "SELECT balance_sats FROM wallets WHERE id = ?", (from_wallet,)
            ).fetchone()
            
            if not balance or balance["balance_sats"] < amount_sats:
                raise ValueError("Insufficient balance")
            
            # Deduct from sender
            db.execute("""
                UPDATE wallets 
                SET balance_sats = balance_sats - ?, 
                    total_spent_sats = total_spent_sats + ?,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            """, (amount_sats, amount_sats, from_wallet))
        
        # Credit to receiver
        db.execute("""
            UPDATE wallets 
            SET balance_sats = balance_sats + ?, 
                total_received_sats = total_received_sats + ?,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        """, (amount_sats, amount_sats, to_wallet))
        
        # Record transaction
        timestamp = datetime.utcnow().isoformat()
        tx_hash = create_tx_hash(from_wallet or "coinbase", to_wallet, amount_sats, timestamp)
        
        db.execute("""
            INSERT INTO transactions (tx_hash, block_number, from_wallet, to_wallet, amount_sats, tx_type, memo)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        """, (tx_hash, block_number, from_wallet, to_wallet, amount_sats, tx_type, memo))
        
        return {
            "tx_hash": tx_hash,
            "from": from_wallet,
            "to": to_wallet,
            "amount_sats": amount_sats,
            "amount_claw": amount_sats / CLAWSATS_PER_COIN,
            "type": tx_type,
        }

def purchase(buyer_wallet: str, amount_sats: int, item_name: str) -> dict:
    """Process a purchase - coins go to admin."""
    return transfer(
        from_wallet=buyer_wallet,
        to_wallet=ADMIN_WALLET_ID,
        amount_sats=amount_sats,
        tx_type="purchase",
        memo=f"Purchase: {item_name}"
    )

# ============== MINING / BLOCK FUNCTIONS ==============

def get_mining_state() -> dict:
    """Get current mining state."""
    with get_db() as db:
        row = db.execute("SELECT * FROM mining_state WHERE id = 1").fetchone()
        return dict(row) if row else None

def get_current_block_reward() -> int:
    """Get current block reward in ClawSats."""
    state = get_mining_state()
    return state["current_reward_sats"] if state else INITIAL_BLOCK_REWARD

def get_current_block_reward_claw() -> float:
    """Get current block reward in ClawCoin."""
    return get_current_block_reward() / CLAWSATS_PER_COIN

def check_halving() -> bool:
    """Check if halving should occur and apply it."""
    with get_db() as db:
        state = db.execute("SELECT * FROM mining_state WHERE id = 1").fetchone()
        
        blocks_since_halving = state["current_block"] - state["last_halving_block"]
        
        if blocks_since_halving >= HALVING_INTERVAL_BLOCKS:
            new_reward = state["current_reward_sats"] // 2
            
            if new_reward < 1:  # Minimum 1 ClawSat
                new_reward = 1
            
            db.execute("""
                UPDATE mining_state
                SET current_reward_sats = ?,
                    halvings_occurred = halvings_occurred + 1,
                    last_halving_block = current_block
                WHERE id = 1
            """, (new_reward,))
            
            return True
    
    return False

def mine_block(miner_wallet: str, challenge_type: str = "work") -> dict:
    """Mine a new block and award reward to miner."""
    with get_db() as db:
        state = db.execute("SELECT * FROM mining_state WHERE id = 1").fetchone()
        
        # Check if all coins mined
        if state["total_mined_sats"] >= TOTAL_SUPPLY:
            return {"error": "All ClawCoin have been mined!"}
        
        # Check halving
        check_halving()
        state = db.execute("SELECT * FROM mining_state WHERE id = 1").fetchone()
        
        reward = state["current_reward_sats"]
        
        # Don't exceed total supply
        if state["total_mined_sats"] + reward > TOTAL_SUPPLY:
            reward = TOTAL_SUPPLY - state["total_mined_sats"]
        
        new_block = state["current_block"] + 1
        block_hash = hashlib.sha256(f"block:{new_block}:{miner_wallet}:{datetime.utcnow().isoformat()}".encode()).hexdigest()[:16]
        
        # Record block
        db.execute("""
            INSERT INTO blocks (number, hash, miner_wallet, reward_sats, challenge_type)
            VALUES (?, ?, ?, ?, ?)
        """, (new_block, block_hash, miner_wallet, reward, challenge_type))
        
        # Update mining state
        db.execute("""
            UPDATE mining_state
            SET total_mined_sats = total_mined_sats + ?,
                current_block = ?,
                last_block_time = CURRENT_TIMESTAMP
            WHERE id = 1
        """, (reward, new_block))
    
    # Award to miner
    tx = transfer(
        from_wallet=None,  # Coinbase
        to_wallet=miner_wallet,
        amount_sats=reward,
        tx_type="mining",
        memo=f"Block {new_block} reward",
        block_number=new_block
    )
    
    return {
        "block_number": new_block,
        "block_hash": block_hash,
        "reward_sats": reward,
        "reward_claw": reward / CLAWSATS_PER_COIN,
        "tx_hash": tx["tx_hash"],
        "total_mined": (state["total_mined_sats"] + reward) / CLAWSATS_PER_COIN,
        "remaining": (TOTAL_SUPPLY - state["total_mined_sats"] - reward) / CLAWSATS_PER_COIN,
    }

# ============== ECONOMY STATS ==============

def get_economy_stats() -> dict:
    """Get full economy statistics."""
    state = get_mining_state()
    
    if not state:
        return {"error": "Mining not initialized"}
    
    total_mined = state["total_mined_sats"]
    current_reward = state["current_reward_sats"]
    
    # Calculate next halving
    blocks_until_halving = HALVING_INTERVAL_BLOCKS - (state["current_block"] - state["last_halving_block"])
    time_until_halving = blocks_until_halving * BLOCK_TIME_SECONDS
    
    return {
        "total_supply_claw": TOTAL_SUPPLY / CLAWSATS_PER_COIN,
        "total_mined_claw": total_mined / CLAWSATS_PER_COIN,
        "remaining_claw": (TOTAL_SUPPLY - total_mined) / CLAWSATS_PER_COIN,
        "percent_mined": (total_mined / TOTAL_SUPPLY) * 100,
        "current_block": state["current_block"],
        "current_reward_claw": current_reward / CLAWSATS_PER_COIN,
        "current_reward_sats": current_reward,
        "halvings_occurred": state["halvings_occurred"],
        "blocks_until_halving": blocks_until_halving,
        "time_until_halving_hours": time_until_halving / 3600,
        "halving_interval_blocks": HALVING_INTERVAL_BLOCKS,
        "block_time_seconds": BLOCK_TIME_SECONDS,
    }

# ============== INITIALIZE ==============

# Auto-init on import
init_db()