db.py

"""
ClawVille Database Abstraction
Supports both SQLite (local dev) and PostgreSQL (Railway production)
"""

import os
from contextlib import contextmanager
from urllib.parse import urlparse

# 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"
    
    def get_db_url():
        return DATABASE_URL
    
    @contextmanager
    def get_db():
        conn = psycopg2.connect(DATABASE_URL)
        conn.autocommit = False
        try:
            yield conn
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()
    
    @contextmanager
    def get_cursor(conn):
        cursor = conn.cursor(cursor_factory=RealDictCursor)
        try:
            yield cursor
        finally:
            cursor.close()
    
    def dict_row(row):
        """Convert row to dict (already dict with RealDictCursor)"""
        return dict(row) if row else None

else:
    # SQLite mode (local development)
    import sqlite3
    
    DB_TYPE = "sqlite"
    DB_PATH = os.environ.get("CLAWVILLE_DB", "data/clawville.db")
    
    def get_db_path():
        os.makedirs(os.path.dirname(DB_PATH) if os.path.dirname(DB_PATH) else ".", exist_ok=True)
        return DB_PATH
    
    @contextmanager
    def get_db():
        conn = sqlite3.connect(get_db_path(), timeout=30)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA journal_mode=WAL")
        try:
            yield conn
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()
    
    @contextmanager
    def get_cursor(conn):
        cursor = conn.cursor()
        try:
            yield cursor
        finally:
            pass  # SQLite cursors don't need explicit close
    
    def dict_row(row):
        """Convert sqlite3.Row to dict"""
        return dict(row) if row else None


# ============== 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

ADMIN_WALLET_ID = "clawville_admin"


# ============== SCHEMA ==============

# Use TEXT for UUIDs, BIGINT for sats (PostgreSQL) or INTEGER (SQLite)
# Use SERIAL/AUTOINCREMENT for IDs
# Use TIMESTAMP/TEXT for dates

SCHEMA_SQLITE = """
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
);

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
);

CREATE TABLE IF NOT EXISTS mining_state (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    current_block INTEGER DEFAULT 0,
    total_mined_sats INTEGER DEFAULT 0,
    last_block_time TEXT,
    difficulty INTEGER DEFAULT 1
);

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,
    UNIQUE(auth_provider, auth_id)
);

CREATE TABLE IF NOT EXISTS tasks (
    id TEXT PRIMARY KEY,
    template_id TEXT,
    title TEXT NOT NULL,
    description TEXT,
    category TEXT NOT NULL,
    difficulty INTEGER DEFAULT 1,
    xp_reward INTEGER DEFAULT 10,
    coin_reward_sats INTEGER DEFAULT 0,
    energy_cost INTEGER DEFAULT 10,
    time_limit_seconds INTEGER,
    requirements TEXT DEFAULT '{}',
    status TEXT DEFAULT 'open',
    created_by TEXT,
    assigned_to TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    expires_at TEXT,
    completed_at TEXT
);

CREATE TABLE IF NOT EXISTS task_claims (
    id TEXT PRIMARY KEY,
    task_id TEXT NOT NULL,
    agent_id TEXT NOT NULL,
    status TEXT DEFAULT 'active',
    started_at TEXT DEFAULT CURRENT_TIMESTAMP,
    completed_at TEXT,
    result TEXT
);

CREATE TABLE IF NOT EXISTS task_templates (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    category TEXT NOT NULL,
    difficulty INTEGER DEFAULT 1,
    base_xp INTEGER DEFAULT 10,
    base_coins_sats INTEGER DEFAULT 0,
    energy_cost INTEGER DEFAULT 10,
    time_limit_seconds INTEGER,
    requirements TEXT DEFAULT '{}',
    is_repeatable INTEGER DEFAULT 1,
    cooldown_seconds INTEGER DEFAULT 3600,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS agent_skills (
    agent_id TEXT NOT NULL,
    skill_name TEXT NOT NULL,
    level INTEGER DEFAULT 1,
    xp INTEGER DEFAULT 0,
    PRIMARY KEY (agent_id, skill_name)
);

CREATE TABLE IF NOT EXISTS blocks (
    agent_id TEXT NOT NULL,
    blocked_id TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (agent_id, blocked_id)
);

CREATE TABLE IF NOT EXISTS oauth_states (
    state TEXT PRIMARY KEY,
    redirect_uri TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS rate_limits (
    key TEXT PRIMARY KEY,
    count INTEGER DEFAULT 0,
    window_start TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS referral_stats (
    agent_id TEXT PRIMARY KEY,
    referral_code TEXT UNIQUE,
    referred_by TEXT,
    referral_count INTEGER DEFAULT 0,
    referral_earnings_sats INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS invite_codes (
    code TEXT PRIMARY KEY,
    created_by TEXT,
    max_uses INTEGER DEFAULT 1,
    uses INTEGER DEFAULT 0,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    expires_at TEXT
);

CREATE TABLE IF NOT EXISTS verification_requests (
    id TEXT PRIMARY KEY,
    agent_id TEXT NOT NULL,
    platform TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    verified_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_agents_api_key ON agents(api_key);
CREATE INDEX IF NOT EXISTS idx_agents_auth ON agents(auth_provider, auth_id);
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
CREATE INDEX IF NOT EXISTS idx_tasks_category ON tasks(category);
CREATE INDEX IF NOT EXISTS idx_transactions_wallet ON transactions(to_wallet);
"""

SCHEMA_POSTGRESQL = """
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
);

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
);

CREATE TABLE IF NOT EXISTS mining_state (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    current_block INTEGER DEFAULT 0,
    total_mined_sats BIGINT DEFAULT 0,
    last_block_time TIMESTAMP,
    difficulty INTEGER DEFAULT 1
);

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)
);

CREATE TABLE IF NOT EXISTS tasks (
    id TEXT PRIMARY KEY,
    template_id TEXT,
    title TEXT NOT NULL,
    description TEXT,
    category TEXT NOT NULL,
    difficulty INTEGER DEFAULT 1,
    xp_reward INTEGER DEFAULT 10,
    coin_reward_sats BIGINT DEFAULT 0,
    energy_cost INTEGER DEFAULT 10,
    time_limit_seconds INTEGER,
    requirements JSONB DEFAULT '{}',
    status TEXT DEFAULT 'open',
    created_by TEXT,
    assigned_to TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    completed_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS task_claims (
    id TEXT PRIMARY KEY,
    task_id TEXT NOT NULL,
    agent_id TEXT NOT NULL,
    status TEXT DEFAULT 'active',
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,
    result TEXT
);

CREATE TABLE IF NOT EXISTS task_templates (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    category TEXT NOT NULL,
    difficulty INTEGER DEFAULT 1,
    base_xp INTEGER DEFAULT 10,
    base_coins_sats BIGINT DEFAULT 0,
    energy_cost INTEGER DEFAULT 10,
    time_limit_seconds INTEGER,
    requirements JSONB DEFAULT '{}',
    is_repeatable BOOLEAN DEFAULT TRUE,
    cooldown_seconds INTEGER DEFAULT 3600,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS agent_skills (
    agent_id TEXT NOT NULL,
    skill_name TEXT NOT NULL,
    level INTEGER DEFAULT 1,
    xp INTEGER DEFAULT 0,
    PRIMARY KEY (agent_id, skill_name)
);

CREATE TABLE IF NOT EXISTS blocks (
    agent_id TEXT NOT NULL,
    blocked_id TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (agent_id, blocked_id)
);

CREATE TABLE IF NOT EXISTS oauth_states (
    state TEXT PRIMARY KEY,
    redirect_uri TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS rate_limits (
    key TEXT PRIMARY KEY,
    count INTEGER DEFAULT 0,
    window_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS referral_stats (
    agent_id TEXT PRIMARY KEY,
    referral_code TEXT UNIQUE,
    referred_by TEXT,
    referral_count INTEGER DEFAULT 0,
    referral_earnings_sats BIGINT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS invite_codes (
    code TEXT PRIMARY KEY,
    created_by TEXT,
    max_uses INTEGER DEFAULT 1,
    uses INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS verification_requests (
    id TEXT PRIMARY KEY,
    agent_id TEXT NOT NULL,
    platform TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    verified_at TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_agents_api_key ON agents(api_key);
CREATE INDEX IF NOT EXISTS idx_agents_auth ON agents(auth_provider, auth_id);
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
CREATE INDEX IF NOT EXISTS idx_tasks_category ON tasks(category);
CREATE INDEX IF NOT EXISTS idx_transactions_wallet ON transactions(to_wallet);
"""


def init_db():
    """Initialize database schema."""
    with get_db() as db:
        schema = SCHEMA_POSTGRESQL if DB_TYPE == "postgresql" else SCHEMA_SQLITE
        
        if DB_TYPE == "postgresql":
            with get_cursor(db) as cursor:
                # Execute each statement separately for PostgreSQL
                for statement in schema.split(';'):
                    statement = statement.strip()
                    if statement:
                        cursor.execute(statement)
        else:
            # SQLite can execute multiple statements
            db.executescript(schema)
        
        # Initialize mining state if not exists
        if DB_TYPE == "postgresql":
            with get_cursor(db) as cursor:
                cursor.execute("""
                    INSERT INTO mining_state (id, current_block, total_mined_sats, difficulty)
                    VALUES (1, 0, 0, 1)
                    ON CONFLICT (id) DO NOTHING
                """)
        else:
            db.execute("""
                INSERT OR IGNORE INTO mining_state (id, current_block, total_mined_sats, difficulty)
                VALUES (1, 0, 0, 1)
            """)
        
        # Create admin wallet if not exists
        if DB_TYPE == "postgresql":
            with get_cursor(db) as cursor:
                cursor.execute("""
                    INSERT INTO wallets (id, agent_id, balance_sats)
                    VALUES (%s, %s, 0)
                    ON CONFLICT (id) DO NOTHING
                """, (ADMIN_WALLET_ID, ADMIN_WALLET_ID))
        else:
            db.execute("""
                INSERT OR IGNORE INTO wallets (id, agent_id, balance_sats)
                VALUES (?, ?, 0)
            """, (ADMIN_WALLET_ID, ADMIN_WALLET_ID))


# ============== QUERY HELPERS ==============

def placeholder(index=None):
    """Return the appropriate placeholder for the database type."""
    if DB_TYPE == "postgresql":
        return "%s"
    return "?"

def placeholders(count):
    """Return multiple placeholders."""
    return ", ".join([placeholder() for _ in range(count)])


print(f"ClawVille Database: {DB_TYPE} mode")