source "$(dirname "${BASH_SOURCE[0]}")/env-init.sh"
#!/bin/bash

# Load credentials
if [ -f /home/intel145/websites/.env ]; then
    export $(grep -v '^#' /home/intel145/websites/.env | xargs)
else
    echo "Error: .env file not found."
    exit 1
fi

echo "Initializing architecture metadata tables in database: $DB_NAME"

# Using a heredoc to pass the SQL to mysql
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" <<EOF
-- 1. Query Metadata
CREATE TABLE IF NOT EXISTS query_metadata (
    query_id VARCHAR(50) PRIMARY KEY,
    sql_statement TEXT NOT NULL,
    description VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    cache_ttl INT DEFAULT 0
) ENGINE=InnoDB;

-- 2. Field Definitions
CREATE TABLE IF NOT EXISTS field_definitions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(50) NOT NULL,
    query_id VARCHAR(50) NOT NULL,
    field_name VARCHAR(50) NOT NULL,
    display_label VARCHAR(100),
    data_type VARCHAR(20) DEFAULT 'string',
    is_default TINYINT(1) DEFAULT 1,
    INDEX idx_query_id (query_id)
) ENGINE=InnoDB;

-- 3. User Views
CREATE TABLE IF NOT EXISTS user_views (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(50) NOT NULL,
    query_id VARCHAR(50) NOT NULL,
    owner_type VARCHAR(20) NOT NULL,
    owner_id VARCHAR(50) NOT NULL,
    view_name VARCHAR(100),
    view_config JSON NOT NULL,
    INDEX idx_view_lookup (query_id, owner_type, owner_id)
) ENGINE=InnoDB;
EOF

if [ $? -eq 0 ]; then
    echo "Architecture tables initialized successfully."
else
    echo "Error: Table initialization failed."
fi