source "$(dirname "${BASH_SOURCE[0]}")/env-init.sh"
#!/bin/bash
# init-system-db.sh
# Requires .env with DB_HOST, DB_USER, DB_PASS

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

echo "Initializing intel145_system..."
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" intel145_system <<EOF
CREATE TABLE IF NOT EXISTS query_metadata (
    query_id VARCHAR(50) PRIMARY KEY,
    db_name VARCHAR(50) NOT NULL,
    sql_statement TEXT NOT NULL,
    description VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS field_definitions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    query_id VARCHAR(50) NOT NULL,
    field_name VARCHAR(50) NOT NULL,
    display_label VARCHAR(100),
    data_type VARCHAR(20) DEFAULT 'string',
    INDEX idx_query (query_id)
) ENGINE=InnoDB;
EOF