#!/bin/bash
# MCP-ZERO Database Initializer
# Sets up SQLite databases with proper schema for MCP-ZERO components
# Implements offline-first resilience pattern with single connection attempt

# Initialize the agent memory database
initialize_agent_memory_db() {
  local db_path="$1"
  
  echo "Initializing agent memory database at ${db_path}"
  
  # Create the database and tables
  sqlite3 "${db_path}" << EOF
-- Agent memory schema with offline-first resilience support
CREATE TABLE IF NOT EXISTS agents (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  model_path TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_active INTEGER DEFAULT 1,
  offline_capable INTEGER DEFAULT 1
);

-- Memory records for agents
CREATE TABLE IF NOT EXISTS memories (
  id TEXT PRIMARY KEY,
  agent_id TEXT NOT NULL,
  content TEXT NOT NULL,
  embedding BLOB,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  context TEXT,
  FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
);

-- Track offline mode status
CREATE TABLE IF NOT EXISTS offline_status (
  id INTEGER PRIMARY KEY,
  is_offline INTEGER DEFAULT 0,
  last_connection_attempt TIMESTAMP,
  connection_failures INTEGER DEFAULT 0,
  reason TEXT,
  permanent_lock INTEGER DEFAULT 0
);

-- Offline mode lock files
CREATE TABLE IF NOT EXISTS offline_locks (
  id TEXT PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  reason TEXT,
  immutable INTEGER DEFAULT 1,
  created_by TEXT
);

-- Insert default offline status if not exists
INSERT OR IGNORE INTO offline_status (id, is_offline, last_connection_attempt, connection_failures, reason, permanent_lock)
VALUES (1, 0, NULL, 0, NULL, 0);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_memories_agent_id ON memories(agent_id);
CREATE INDEX IF NOT EXISTS idx_memories_created_at ON memories(created_at);
EOF

  echo "Agent memory database initialized successfully"
}

# Initialize the audit trail database for security and compliance
initialize_audit_db() {
  local db_path="$1"
  
  echo "Initializing audit trail database at ${db_path}"
  
  # Create the database and tables
  sqlite3 "${db_path}" << EOF
-- Immutable audit trail schema
CREATE TABLE IF NOT EXISTS audit_events (
  id TEXT PRIMARY KEY,
  event_type TEXT NOT NULL,
  event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  user_id TEXT,
  component TEXT NOT NULL,
  action TEXT NOT NULL,
  details TEXT,
  status TEXT NOT NULL,
  ip_address TEXT,
  is_offline INTEGER DEFAULT 0
);

-- Offline mode changes tracking
CREATE TABLE IF NOT EXISTS offline_mode_events (
  id TEXT PRIMARY KEY,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  previous_state INTEGER NOT NULL,
  new_state INTEGER NOT NULL,
  reason TEXT,
  triggered_by TEXT,
  is_permanent INTEGER DEFAULT 0
);

-- Security policy enforcement
CREATE TABLE IF NOT EXISTS security_events (
  id TEXT PRIMARY KEY,
  event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  event_type TEXT NOT NULL,
  severity TEXT NOT NULL,
  details TEXT,
  mitigated INTEGER DEFAULT 0,
  mitigation_details TEXT
);

-- Create indexes for fast audit searches
CREATE INDEX IF NOT EXISTS idx_audit_event_time ON audit_events(event_time);
CREATE INDEX IF NOT EXISTS idx_audit_event_type ON audit_events(event_type);
CREATE INDEX IF NOT EXISTS idx_audit_component ON audit_events(component);
CREATE INDEX IF NOT EXISTS idx_offline_changes ON offline_mode_events(changed_at);
EOF

  echo "Audit trail database initialized successfully"
}

# Initialize the metrics database for monitoring and performance tracking
initialize_metrics_db() {
  local db_path="$1"
  
  echo "Initializing metrics database at ${db_path}"
  
  # Create the database and tables
  sqlite3 "${db_path}" << EOF
-- Performance metrics schema
CREATE TABLE IF NOT EXISTS system_metrics (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  collected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  cpu_usage REAL,
  memory_usage REAL,
  disk_usage REAL,
  network_in REAL,
  network_out REAL,
  is_offline INTEGER DEFAULT 0
);

-- API metrics for monitoring
CREATE TABLE IF NOT EXISTS api_metrics (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  endpoint TEXT NOT NULL,
  method TEXT NOT NULL,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  response_time_ms INTEGER,
  status_code INTEGER,
  client_ip TEXT,
  is_offline INTEGER DEFAULT 0
);

-- Agent metrics for performance tracking
CREATE TABLE IF NOT EXISTS agent_metrics (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  agent_id TEXT NOT NULL,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  memory_usage REAL,
  processing_time_ms INTEGER,
  tokens_processed INTEGER,
  is_offline INTEGER DEFAULT 0,
  model_type TEXT
);

-- Agent connection tracking
CREATE TABLE IF NOT EXISTS connection_metrics (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  agent_id TEXT NOT NULL,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  connection_type TEXT NOT NULL,
  connection_target TEXT,
  connection_status TEXT NOT NULL,
  latency_ms INTEGER,
  failure_reason TEXT
);

-- Create indexes for time series queries
CREATE INDEX IF NOT EXISTS idx_system_metrics_time ON system_metrics(collected_at);
CREATE INDEX IF NOT EXISTS idx_api_metrics_time ON api_metrics(timestamp);
CREATE INDEX IF NOT EXISTS idx_api_metrics_endpoint ON api_metrics(endpoint);
CREATE INDEX IF NOT EXISTS idx_agent_metrics_time ON agent_metrics(timestamp);
CREATE INDEX IF NOT EXISTS idx_agent_metrics_id ON agent_metrics(agent_id);
CREATE INDEX IF NOT EXISTS idx_connection_metrics_status ON connection_metrics(connection_status);
EOF

  echo "Metrics database initialized successfully"
}

# Create database migration scripts
create_migrations() {
  local migrations_dir="$1"
  
  echo "Creating database migration scripts in ${migrations_dir}"
  
  # Create initial migration script
  cat > "${migrations_dir}/001_initial_schema.sql" << EOF
-- MCP-ZERO Initial Schema
-- Version: 1.0.0
-- Date: $(date -I)

-- Agent Tables
CREATE TABLE IF NOT EXISTS agents (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  model_path TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_active INTEGER DEFAULT 1,
  offline_capable INTEGER DEFAULT 1
);

CREATE TABLE IF NOT EXISTS memories (
  id TEXT PRIMARY KEY,
  agent_id TEXT NOT NULL,
  content TEXT NOT NULL,
  embedding BLOB,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  context TEXT,
  FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
);

-- Offline Resilience Tables
CREATE TABLE IF NOT EXISTS offline_status (
  id INTEGER PRIMARY KEY,
  is_offline INTEGER DEFAULT 0,
  last_connection_attempt TIMESTAMP,
  connection_failures INTEGER DEFAULT 0,
  reason TEXT,
  permanent_lock INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS offline_locks (
  id TEXT PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  reason TEXT,
  immutable INTEGER DEFAULT 1,
  created_by TEXT
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_memories_agent_id ON memories(agent_id);
CREATE INDEX IF NOT EXISTS idx_memories_created_at ON memories(created_at);
EOF

  # Create offline-first resilience migration
  cat > "${migrations_dir}/002_offline_first_resilience.sql" << EOF
-- MCP-ZERO Offline-First Resilience Enhancement
-- Version: 1.1.0
-- Date: $(date -I)

-- Add connection attempt tracking to agents
ALTER TABLE agents ADD COLUMN IF NOT EXISTS connection_attempts INTEGER DEFAULT 0;
ALTER TABLE agents ADD COLUMN IF NOT EXISTS last_connection_attempt TIMESTAMP;
ALTER TABLE agents ADD COLUMN IF NOT EXISTS fallback_mode TEXT DEFAULT 'local';

-- Create offline operation logs
CREATE TABLE IF NOT EXISTS offline_operations (
  id TEXT PRIMARY KEY,
  operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  agent_id TEXT,
  operation_type TEXT NOT NULL,
  details TEXT,
  status TEXT NOT NULL,
  synced INTEGER DEFAULT 0,
  FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE SET NULL
);

-- Create offline sync queue
CREATE TABLE IF NOT EXISTS sync_queue (
  id TEXT PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  operation_id TEXT,
  priority INTEGER DEFAULT 1,
  attempts INTEGER DEFAULT 0,
  last_attempt TIMESTAMP,
  status TEXT DEFAULT 'pending',
  FOREIGN KEY (operation_id) REFERENCES offline_operations(id) ON DELETE CASCADE
);

-- Create index for sync queue processing
CREATE INDEX IF NOT EXISTS idx_sync_queue_status ON sync_queue(status, priority);
EOF

  echo "Database migration scripts created successfully"
}

# Create database with offline mode already enabled
create_offline_db() {
  local db_path="$1"
  local lock_path="$2"
  local reason="$3"
  
  # Initialize the database first
  initialize_agent_memory_db "${db_path}"
  
  # Now set it to offline mode permanently
  sqlite3 "${db_path}" << EOF
-- Set offline status to permanent offline mode
UPDATE offline_status 
SET is_offline = 1, 
    reason = '${reason}', 
    permanent_lock = 1, 
    last_connection_attempt = CURRENT_TIMESTAMP
WHERE id = 1;

-- Create a permanent lock record
INSERT OR IGNORE INTO offline_locks (id, reason, immutable, created_by)
VALUES (lower(hex(randomblob(16))), '${reason}', 1, 'burn_folder_initialization');
EOF

  # Create the actual lock file
  cat > "${lock_path}" << EOF
{
  "created_at": "$(date -Iseconds)",
  "reason": "${reason}",
  "immutable": true,
  "created_by": "burn_folder_initialization",
  "id": "$(uuidgen || cat /proc/sys/kernel/random/uuid)"
}
EOF
  
  # Make the lock file read-only
  chmod 444 "${lock_path}"
  
  echo "Database configured for permanent offline mode with lock file"
}

# Initialize all databases for MCP-ZERO
initialize_all_dbs() {
  local db_dir="$1"
  local locks_dir="$2"
  local offline_mode="$3"
  local migrations_dir="${db_dir}/migrations"
  
  # Ensure directories exist
  mkdir -p "${db_dir}" "${locks_dir}" "${migrations_dir}"
  
  # Initialize databases
  initialize_agent_memory_db "${db_dir}/agent_memory.db"
  initialize_audit_db "${db_dir}/audit_trail.db"
  initialize_metrics_db "${db_dir}/metrics.db"
  
  # Create migration scripts
  create_migrations "${migrations_dir}"
  
  # Set up offline mode if requested
  if [ "${offline_mode}" -eq 1 ]; then
    create_offline_db "${db_dir}/agent_memory.db" "${locks_dir}/offline.lock" "Initialized in offline mode by burn folder script"
  fi
  
  echo "All databases initialized successfully"
}

# If script is executed directly, show usage
if [[ "${BASH_SOURCE[0]}" == "${0}" ]]; then
  echo "This script is intended to be sourced by burn.sh"
  echo "Usage: source $(basename "$0")"
fi
