giglez/scripts/quick_db_setup.sh

#!/bin/bash
#
# Quick Database Setup for GigLez
# Sets up PostgreSQL database without requiring sudo
#

echo "=========================================="
echo "GigLez Quick Database Setup"
echo "=========================================="
echo ""

# Check if PostgreSQL is running
if ! pgrep -x postgres > /dev/null; then
    echo "❌ PostgreSQL is not running"
    echo "Please start it with: sudo systemctl start postgresql"
    exit 1
fi

echo "✅ PostgreSQL is running"
echo ""

# Try to connect as postgres user to create our user/database
echo "Creating database user and database..."
echo "This will prompt for the postgres user password (if needed)"
echo ""

# Create user and database
sudo -u postgres psql << 'EOF'
-- Create user if doesn't exist
DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_catalog.pg_user WHERE username = 'giglez_user') THEN
    CREATE USER giglez_user WITH PASSWORD 'giglez_dev_password';
  END IF;
END
$$;

-- Create database if doesn't exist
SELECT 'CREATE DATABASE giglez OWNER giglez_user'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'giglez')\gexec

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE giglez TO giglez_user;

\q
EOF

if [ $? -eq 0 ]; then
    echo ""
    echo "✅ User and database created"
else
    echo ""
    echo "❌ Failed to create user/database"
    echo "You may need to configure PostgreSQL authentication"
    exit 1
fi

# Create PostGIS extension
echo ""
echo "Enabling PostGIS extension..."
sudo -u postgres psql -d giglez -c "CREATE EXTENSION IF NOT EXISTS postgis;"

if [ $? -eq 0 ]; then
    echo "✅ PostGIS enabled"
else
    echo "⚠️  PostGIS not available (optional for basic functionality)"
fi

# Create schema
echo ""
echo "Creating database schema..."
psql -U giglez_user -d giglez -h localhost << 'EOF'
-- Don't fail if tables exist
DO $$
BEGIN

-- Devices table
CREATE TABLE IF NOT EXISTS devices (
    id SERIAL PRIMARY KEY,
    device_name VARCHAR(200),
    manufacturer VARCHAR(100),
    model VARCHAR(100),
    device_type VARCHAR(50),
    typical_frequency INTEGER,
    protocol VARCHAR(100),
    description TEXT,
    first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_verified BOOLEAN DEFAULT FALSE
);

-- Signatures table
CREATE TABLE IF NOT EXISTS signatures (
    id SERIAL PRIMARY KEY,
    device_id INTEGER REFERENCES devices(id),
    protocol VARCHAR(100),
    frequency INTEGER,
    modulation VARCHAR(50),
    bit_pattern BYTEA,
    bit_mask BYTEA,
    timing_min INTEGER,
    timing_max INTEGER,
    raw_pattern TEXT,
    confidence_threshold FLOAT DEFAULT 0.7,
    source VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Captures table (simplified)
CREATE TABLE IF NOT EXISTS captures (
    file_hash VARCHAR(64) PRIMARY KEY,
    filename VARCHAR(500),
    frequency INTEGER,
    protocol VARCHAR(100),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    captured_at TIMESTAMP,
    device_id INTEGER REFERENCES devices(id),
    match_confidence FLOAT,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_signatures_frequency ON signatures(frequency);
CREATE INDEX IF NOT EXISTS idx_signatures_device ON signatures(device_id);
CREATE INDEX IF NOT EXISTS idx_captures_frequency ON captures(frequency);

RAISE NOTICE 'Schema created successfully';

END $$;
EOF

if [ $? -eq 0 ]; then
    echo "✅ Schema created"
else
    echo "❌ Schema creation failed"
    exit 1
fi

# Test connection
echo ""
echo "Testing connection..."
psql -U giglez_user -d giglez -h localhost -c "SELECT COUNT(*) as table_count FROM information_schema.tables WHERE table_schema = 'public';"

if [ $? -eq 0 ]; then
    echo ""
    echo "=========================================="
    echo "✅ Database setup complete!"
    echo "=========================================="
    echo ""
    echo "Connection details:"
    echo "  Database: giglez"
    echo "  User: giglez_user"
    echo "  Host: localhost"
    echo "  Port: 5432"
    echo ""
    echo "Next step: Run signature import"
    echo "  python3 scripts/import_flipper_to_db.py"
else
    echo "❌ Connection test failed"
    exit 1
fi